Home DADE

Altering DADE SQLText at Run Time with RAP

edited August 2006 in DADE
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.

Comments

  • edited August 2006

    - 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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited August 2006
    > - 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.

  • edited August 2006

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited August 2006
    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.




  • edited August 2006
    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.
  • edited August 2006

    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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.