Altering DADE SQLText at Run Time with RAP

rbuser

In the End User Application I have Contructed a data view called qCustomer.
I went to the SQL tab right clicked and selected Edit SQL. I manaully typed
in the following SQL:

SELECT CUSTOMER.CUSTOMERPK CustomerPK,
CUSTOMER.CUSTOMERID CustomerID,
CUSTOMER.FNAME+ ', ' + CUSTOMER.LNAME CustomerName
FROM CUSTOMER

This successfully creates a DataView called qCustomer. Now I want to append
the following text to this SQL Statement at runtime using RAP:

WHERE CUSTOMER.CUSTOMERPK='PK'

'PK' is selected by the user From a selection form that allows them to
select a customer from a list (This form is used all through the system and
it's operation is familia - so I need to keep it).

I have a Passthrough Function called "SelectCustomer". I added this in to
call up the form by following the example in the "Displaying Delphi Forms
Form RAP" Tutorial in the User Guide. THe routing will pass back the
CustomerPK Field once the user has selected it.

Now how do I update the SQL in the Report.OnItializeParameters so I can add
the extra lines and thus only query the database for the selected customer
record?

Nigel.

nardmoseley

- rather than using a RAP passs-thru function to modify the SQL, I recommend
using the TdaSQLBuilder class. The TdaSQLBuilder class is designed for
eactly this purpose - to provide programmatic access to the TdaSQL object
associated with a QueryDataView. It can be used via RAP code or Delphi code.
It is organized in the same manner as the QueryDesigner - SelectTables,
SelectFields, SearchCriteria, etc.

- access the RBuilder.hlp topic for TdaSQLBuilder and then traverse to the
subtopics which contain sample code.

- TdaSQLBuilder also enables autosearch criteria to be defined. I recommend
using autosearch criteria for the ask at runtime parameters. Then RB will
create the search dialog and will dynamically modify the SQL for you.

lSQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);

lSQLBuilder.Clear;
lSQLBuilder.SelectTables.Add('Customer');
lSQLBuilder.SelectFields.AddAllFields;
lSQLBuilder.SearchCriteria.AddAutoSearch('Customer', 'Company', 'Like',
'S');
lSQLBuilder.ApplyUpdates;

lSQLBuilder.Free;





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

rbuser

> - rather than using a RAP passs-thru function to modify the SQL, I

Yes that works - if I used the visual tools to do the query . In this case
I diddn't - I manually typed in the SQL myself (Since I am retrieving a
datset with multiple outer joins and a sub select query). When I click on
Edit SQL in the designer it told me I could no longer use the visual tools
to alter the query - so I am assuming the Properties like SelectFileds,
SearchCriteria etc will also no longer be usable.


Are there any examples on using Pass through functions to aappend to the SQL
I Manaully typed into the query designer? Can this be done? Can it be done
directly in RAP via a property of the TdaSQLBuilder Class I am not aware of?


Did that on my first attempt (with a simple query). However the line

lSQLBuilder.ApplyUpdates;

that appears in the example will not compile under RAP. Once I worked that
out it was fine. However it still doesn't get me around the manual SQL
Editing problem.

nardmoseley

Here is an example that I created - it shows how to use SQLBuilder to edit
the SQLText and use AutoSearch. The example is implemented with Delphi code,
however it can be adapted to RAP by coding some RAP pass-thru functions.

www.digital-metaphors.com/tips/SQLBuilder-EditSQLAndSearch.zip


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

rbuser

I looked at the example you have provided and got a good idea of where we
are going. The Help File Topic for the TdaSQLBuilder doesn't mention the
SQL property of the class or that and the entry for the TdaSQL class doesn't
mention the SQLText property. I have been using Reportbuilder since version
7 so I could still have older versions of the files.

Anyway I tried using your example in RAP and came accross a very interesting
result. It seems that altering the SQL Text using RAP generates an
EDatabaseException. You can reproduce it (Using Delphi 7 and RB 10.4) as
follows:

1) Open the Demos\3.EndUser\1.ReportExplorer\EndUser.dpr project (or run the
exe if you don't want to use Delphi). You wil have to compile it with RAP
enabled first to get the Calc tab to appear.

2) In report Explorer go to the Customers Folder and open the Customer List
report in the designer.

3) on the Data Tab go to the Customers.pl dataview right click and select
SQL.

4) The Visual Query Designer Tool should appear.

5) Go toi the Sql Tab in the designer and in the SQL Panel right Click and
select Edit SQL. You will get a warning about not being able to use the
visual tools after this. Click OK.

6) Clickl the OK button to save the SQL.

7) Go to the Calc Tab and Select the Report->BeforeOpenDataPipelines event.

8) Click in the code window to change the event to the following:

procedure ReportBeforeOpenDataPipelines;
var
LSQLBuilder : TdaSQLBuilder;
begin
LSQLBuilder := TdaSQLBuilder.Create(plCustomer);
LSQLBuilder.SQL.SQLText.Clear;
LSQLBuilder.SQL.SQLText.Add('SELECT customer.CustNo, customer.Company, ');
LSQLBuilder.SQL.SQLText.Add('customer.Addr1, customer.Addr2, ');
LSQLBuilder.SQL.SQLText.Add('customer.City, customer.State, ');
LSQLBuilder.SQL.SQLText.Add('customer.Zip, customer.Country, ');
LSQLBuilder.SQL.SQLText.Add('customer.Phone, customer.FAX, ');
LSQLBuilder.SQL.SQLText.Add('customer.TaxRate, customer.Contact, ');
LSQLBuilder.SQL.SQLText.Add('customer.LastInvoiceDate ');
LSQLBuilder.SQL.SQLText.Add('FROM "customer.db" customer ');
LSQLBuilder.Free;
end;

9) Save the report (to a new name if you wish).

10) Now preview the report and you will get an error that says Invalid SQL
Statement.

What seems to be hapening is that RBuilder is attempting to run (or parse)
the query every time the SQLText is updated. This error message pops up
when you call the clear command. Aftert that there are 7 more error messages
(one for each line of text added) until the final line is added in which
case the report then prints.

Even though this is in the before open pipelins event it would seem that the
pipeline is open and a query is being run against the database.

Is there a way to prevent this from happening while I am updating the text?
I have save the report to a file and can send it to you if you wish.

Nigel.

rbuser

As a Followup the following does work:

var
LSQLBuilder : TdaSQLBuilder;
LSQL : TStringList;
begin
LSQL := TStringList.Create;
LSQL.Clear;
LSQL.Add('SELECT customer.CustNo, customer.Company, ');
LSQL.Add('customer.Addr1, customer.Addr2, ');
LSQL.Add('customer.City, customer.State, ');
LSQL.Add('customer.Zip, customer.Country, ');
LSQL.Add('customer.Phone, customer.FAX, ');
LSQL.Add('customer.TaxRate, customer.Contact, ');
LSQL.Add('customer.LastInvoiceDate ');
LSQL.Add('FROM "customer.db" customer ');

LSQLBuilder := TdaSQLBuilder.Create(plCustomer);
LSQLBuilder.SQL.SQLText := LSQL;
LSQLBuilder.Free;

LSQL.Free;
end;

It seems that if I construct a query in a sepearte string list and assign
the whole thing to the LSQLBuilder.SQL.SQLText property then it works.

Delving into the source I found that the answer seems to lie with the
SetSQLText method of the TdaSQL class.

Lookng at it it seems to attempt to create database fields and send a
modified notifcation message very time it is assinged. It loooks as though
Clear, Add etc causes this method to be called which causes the query to
attempt to execute. If a user is adding directly to the SQLText TStrings
object on a line by line basis it will be called for each line.

For a future feature request it would be nice to have methods (lieke the
DisableControls/EnableControls of the TClientDataset) that can suspend and
then reinstate the updating while the SQL Text is being set. In the
meantime the workaround described above seems to work.

Nigel.

nardmoseley

Thanks for the feeback - it could definitely be implemented better -
something like a BeginUpdate/EndUpdate feature would be nice.


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