Autosearchcriteria and SQLString

rbuser

Using RB11.08 enterprise.

In RAP it looks like I should be able to acces the SQLString method for
the property Report.AutoSearchCriteriaByName property. But I am not
able to figure the correct syntax for this.

Thanks for your help.

Phil

nicocizik

Hi Phil,

I'm a bit unclear about what you are trying to accomplish. Are you creating
the autosearch field(s) in DADE? If so, I recommend using the TdaSQLBuilder
to access and change all items dealing with the SQL code entered including
the AutoSearch fields.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico Cizik (Digital Metaphors) wrote:
Hi Nico,

Sorry I didn't provide the full context.

I am using SQLBuilder in RAP, event BeforeopenDataPipelines to build an
sql statement.

I have a number of parameters defined in the report and want to retrieve
the values to use in where clauses. I have done this successfully
(thanks to earlier support). But one of my parameters is defined as "in
list" So I need to access the list of selected values from the
autosearch field to properly define the where clause.

The help file references the method SQLString and I thought this might
provide a correctly formatted string ready to use in a where clause.

Hope that clarifies.

Thanks,
Phil

nicocizik

Hi Phil,

Thanks for the explaination.

Looking at the AutoSearchField code, it does not look like the SQLString
function is exposed to RAP. I will add it to our todo list to possibly add
this for a later release. Currently you could piece the where clause
together using the TdaCriteria properties or you could access the SQLString
property using a RAP pass-thru function.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico Cizik (Digital Metaphors) wrote:



Hi Nico,

I decided to try doing this using a pass-thru function. Using your
demos, I learned do to do a basic function. But when I modify it to do
what I need, I get an access violation error when trying to use the
function.

Here is the RAP code invoking the function:
showmessage(SQLString(Report, 'HB_Program', 'Program')) ;

My Delphi code defining the function (not including the declarations:

procedure TmySQLStringFunction.ExecuteFunction(aParams: TraParamList);
var
lppReport: TppReport;
lPipeline: string;
lAutoSearchFieldName: string;
lsResult: String;
begin
getParamValue(0, lppReport);
getParamValue(1, lPipeline);
getParamValue(2, lAutoSearchFieldName);
lsResult :=
lppReport.AutoSearchFieldByName(lAutoSearchFieldName).SQLString;
SetParamValue(3, lsResult);
end;

class function TmySQLStringFunction.GetSignature: String;
begin
Result := 'function SQLString(const ppReport: TppAutoSearchField;
const Pipeline, AutoSearchFieldName: String): string;';
end;

Can you tell me what I am doing wrong?

Thanks!

Phil

nicocizik

Hi Phil,

The first thing that stands out is in your routine signature you have the
TppReport parameter declared as a TppAutoSearchField type. Be sure that the
signature represents the exact routine used in RAP.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

Right - missed that.

After more exploring, I am able to get this work with the following
code. I suspect that something is not right still - perhaps in my
definition of my parameter.

The SQLstring returned does not include the field name. I don't know if
this is intended or I am missing something.

For example, if I select two items from the list of options, the
returned SQLString is: ". IN(1080,1081)" - no quotes of course.

Is there are property that I might be missing in the parameter definition?

procedure TmySQLStringFunction.ExecuteFunction(aParams: TraParamList);
var
{include a local var for each parameter and the Result value}
lppReport: TppReport;
liIndex: Integer;
lAutoSearchField: TppAutoSearchField;
lsResult: String;
begin
getParamValue(0, lppReport);
getParamValue(1, liIndex);

lsResult := '';
lAutoSearchField := lppReport.AutoSearchFields[liIndex];

{if valid entry, get search expression in SQL format}
if lAutoSearchField.Valid then
lsResult := lAutoSearchField.SQLString;

lsResult:= trim(lsResult);
lsResult := Copy(lsResult, 2, length(lsResult) - 1) ;


SetParamValue(2, lsResult);
end;

class function TmySQLStringFunction.GetSignature: String;
begin
Result := 'function SQLString(const ppReport: TppReport; const
SearchFieldIndex: integer): string;';
end;

{ TMyNewCategoryFunction }

Thanks again.

p.s. I must say, I continue to be delighted to discover what
ReportBuilder can do!

rbuser

A related question, I think-

As mentioned I am using an IN autosearch field. I want to print a the
values selected by the user when running the report. That is, I want to
print the selected items from the NameField in the Lookup List.

How might I do this? Hope the question is clear.

Thanks,

nicocizik

Hi Phil,

Looking at the SQLString code in ppAsField.pas, it looks like it combines
the TableName, FieldName, Operator, and Expression into a single string.
The table and field names should be included. You might try tracing into
your passthru function to see exactly what is happening in the code.

If you just want the selected values from your user, you might just be able
to use the FormattedExpression routine or simply the Values property.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com