Modifying SQL Text in RAP

rbuser

I am using RAP with RB11.05 and Delphi 2007.

I have a report with a number of sub-reports. The SQLs are too
complicated to create using the Query designer. The SQL is keyed and
the WHERE clause is appended to the SQL text based on the report
parameters in the OnInitializeParameters event.

procedure ReportOnInitializeParameters(var aCancel: Boolean);
var
aSQLBuilder1: TdaSQLBuilder;
aParameter1: TppParameter;

begin
aParameter1 := Report.Parameters.Items['ClientID'];

aSQLBuilder3 := TdaSQLBuilder.Create(ClientCategories);
{ following statement takes 30 - 60 seconds }
aSQLBuilder3.SQL.SQLText.Text :=
aSQLBuilder3.SQL.SQLText.Text +
' WHERE ClientID = ' +
IntToStr(aParameter1.Value);
aSQLBuilder3.ApplyUpdates;
end;

This works OK if the number of rows is small but can take up to 60
seconds to execute the SQLText.Text assignment statement as the tables
increases in size. It takes longer to perform this statement than to
execute the query.

Clicking on the SQL button in the Query Designer takes a similar amount
of time.

Does this make sense? Do you have any suggestions what I could do to
make it more efficient?

Richard Harding

nardmoseley

RB 11 enables you to use bind a parameter to manually edited SQL by using
:ParameterName notation. At run-time RB will be automatically replace the
:ParameterName with the paramter value.

Example:

1. Define a Report.Parameter named pCustNo

2. Use the parameter in manually edited SQL

Select Company
from customers
where CustNo = :pCustNo




--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Thanks Nard,

I am using ElevateDB which seems to be significant.

When I try to add a report parameter in the form below, I get 2 messages
"Invalid SQL statement. Elevate DB Error #700 An error was found in the
statement (Missing expression)"

Select Company from customers where CustNo = :pCustNo


I can enter the following SQL statements OK:
Select Company from customers
Select Company from customers where CustNo = 1

If enter a report parameter which has NOT been defined as a report
parameter such as
Select Company from customers where CustNo = :X

then I get the message "Invalid SQL statement. Elevate DB Error #700 An
error was found in the statement (Missing expression)" followed by
another message "No Report Parameter found for :X"

Richard Harding

nardmoseley

The feature is not dependent upon any particular database engine. ElevateDB
will work great.

The feature requires you to first define the Report.Parameter and configure
the Parameter with a DataType, Name, and default Value.

Manually edited SQL text that refrences a :ParamName, will result in the
ParamName being replaced by the parameter value, prior to the SQL being
submitted to the database engine.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hi Nard,



This works for me with a Paradox database, but not with an ElevatDB
database. I have created a new project with a new ElevateDB database.


The report parameter has been defined with the DateType (Integer), the
name and a default value of 0.


When I enter the statement (1) into the SQL window of the Query
Designer, I get 2 messages, both displaying "Invalid SQL statement.
Elevate DB Error #700 An error was found in the statement (Missing
expression)".

(1) SELECT Company FROM customers WHERE CustNo = :pCustNo


This indicates that the parameter has not been replaced and that the
resultant SQL statement is:

(2) SELECT Company FROM customers WHERE CustNo =

If I enter a query parameter which has NOT been defined as a report
parameter such as

(3) SELECT Company FROM customers WHERE CustNo = :X

then I get the message "Invalid SQL statement. Elevate DB Error #700 An
error was found in the statement (Missing expression)" followed by
another message "No Report Parameter found for :X".


Richard Harding

nardmoseley

It works correctly in my testing here. In re-reading this thread I notice
you are using 11.05, try updating to 11.06.

In my testing, I used ElevateDB with RB 11.06 and D2007.

I ran the Demos\EndUser Databases\ElevateDB example installed with RB.

I created a new report and defined a parameter and then created a Query with
manually edited SQL that references the parameter.




--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Nard Moseley (Digital Metaphors) wrote:

Nard,

I created a new report and created new datasources (with much thanks to
the person who invented Copy and Paste) and I can now edit the SQL and
add the WHERE clause that references the report parameter.

I tried creating a new datasource to the existing report but still ended
up with the "Missing expression" error.

I cannot see any difference in the rtm files of the 2 reports so it is
probably one of these little unsolvable mysteries.

Richard Harding

nardmoseley

Definitely a mystery. Perhaps dues to an issue with a prior release. Using
RB 11.06, you might try deleting and then re-adding the Report.Parameter for
the existing report that has the error.

--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com