Home RAP
New Blog Posts: Merging Reports - Part 1 and Part 2

Modifying SQL Text in RAP

edited September 2009 in RAP
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

Comments

  • edited September 2009

    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

    Best regards,

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



  • edited September 2009

    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

    Best regards,

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

    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

    Best regards,

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

    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

    Best regards,

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