How to amend SQL in RAP?

rbuser

Hi,

RB Ent 9.03 / Delphi 6...

I have a stand-alone report template (for creating letter then a report)
that uses 3 data pipelines to retrieve 3 rows from 3 separate tables as
follows:

dp1: row from the dataset that the user wants to print (e.g. reprint an
order or invoice), choosing which one via AutoSearchDialog
dp2: row from a system table which holds the installed software's company
name, address, logo etc. (to use as the letterhead)
dp3: row from a User table which holds the name, email address and telephone
number of the currently logged in user (to 'sign off' the letter).

What I want to do in RAP is to control which rows are retrieved for any of
the Data Pipelines...

dp1 needs to be selected by the user when they run the report. Have added a
line to the search criteria tab in DADE and have enabled the AutoSearch
property to allow the user to select the correct value at runtime. Works
fine...

dp2 needs to always select row with key ID of 1. Have added a line to the
search criteria tab in DADE and have entered the appropriate value and have
NOT enabled the AutoSearch property. Works fine...

dp3 needs to load the correct record for the user currently logged in. Have
added a line to the search criteria tab in DADE and have entered a value and
have NOT enabled the AutoSearch property. I am passing the UserID to the
report via a pass-thu function, which itself is working fine (I guess I
could now use the Report.Parameters property too?)

My problems is that, whatever I do in RAP , I don't seem to be able to
CHANGE the search value of dp3 at runtime...

If I examine AutoSearchFields, there is only one entry (for dp1) and the
other two do not appear, presumably because they have the AutoSearch
property disabled?

If I add the search criteria for dp3 in RAP instead, it then appears on the
AutoSearchDialog screen that the user sees, which I do not want to happen.

It seems I need to get directly to the SQL code of each of the data
pipelines to be able to amend the WHERE clause for dp3 to do what I want.

However, I am not sure how to as I cannot see how I can get to dp3's SQL as
there appears to be no method of iterating through the three data pipelines
in RAP (the Report.DataPipeline property only points to the first pipeline,
which is dp1).

And is there an example of how to change the SQL of a query IN RAP as all
the examples in the RB Demo's directory refer to working with queries in a
Delphi unit rather than within RAP itself?

My issue appears to be that all the demos do either all or nothing e.g. all
search criteria coded in RAP or all via the AutoSearchDialog. I need to be
able to control all the search criteria in RAP, including what the user gets
asked!

Any suggestion would be much appreciated.

Regards,

Pete Colson.

nardmoseley

ReportBuilder 9 includes adds a new TdaSQLBuilder class that can be used to
modify the SQL associated with a datapipeline. There is a new
Report.BeforeOpenDataPipelines event that can be used to modify the SQL
prior to the datapipelines being opened/initialized by the report engine.

See the RBuilder.hlp topic for TdaSQLBuilder for details and traverse the
linked topics for examples.

Here is a simple example of modifying the criteria


lSQLBuilder := TdaSQLBuilder.Create(myDataPipeline);

lSQLBuilder.SearchCriteria[0].Value := {some value}

lSQLBuilder.Free;


And here is an example from the help file...


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;

rbuser

Nard,

Thanks for this, I think it helps answer one part of my question, but it
still doesn't answer the other, which is how to get the the THIRD data
pipeline to make the necessary changes?

In RAP!

I can get to the first no problem (with Report.DataPipeline), but what about
the other pipelines? I cannot see a way of getting to each pipeline
separately, when there are more than 1...


Earlier comment was...

Regards,

Pete Colson.

nardmoseley

The available DataPipelines are shown in the RAP Code Toolbox. The name
displayed for each pipeline is the value of the DataPipeline.UserName. RAP
supports a special syntax in which you can use the UserName directly in your
code as if it were the datapipeline object.


example:

lSQLBuilder := TdaSQLBuilder.Create(Customer);


another example

lsCompanyName := Customer['CompanyName'];

rbuser

It's that simple?

If only I had known!

Thanks again, knew there would be a way, but coundn't find it! The
documentation did not make it clear (to me, anyway!).

Regards,

Pete Colson.

rbuser

Nard,

Have tried what you suggested - it is definitely changing the
SearchCriteria.Value, but does not appear to update the query, so I am not
getting back the correct record.

In the USERS data pipeline I have 1 search criteria which is USERS.USR_ID =
5.

The currently logged-in user has a USR_ID of 2 so I have added the following
code (have tried both Report.OnCreate AND ReportBeforeOpenDataPipelines
events):

lSQLBuilder := TdaSQLBuilder.Create( USERS );
ShowMessage( 'Value BEFORE change: ' +
lSQLBuilder.SearchCriteria[0].Value ); // displays '5'
lSQLBuilder.SearchCriteria[0].Value := '2';
ShowMessage( 'Value AFTER change: ' +
lSQLBuilder.SearchCriteria[0].Value ); // displays '2'
lSQLBuilder.Free;

ShowMessage is clearly showing that the value is changed from 5 before the
change, to 2 after the change.

I cannot use the lSQLBuilder.ApplyUpdate method as it is not exposed to RAP.

The help system for TdaSQLBuilder.ApplyUpdates states

'If the SQLBuilder detects that the SQL has been updated, this method is
called automatically when the TdaSQLBuilder instance is destroyed'

so this should be forcing the SQL query update anyway...

When I run the report and display the USERS.USR_ID fields on the first page,
it is 5 instead of 2!!!

What am I doing wrong, or missing?

Regards,

Pete Colson

nardmoseley

I tested here using RB 9.03 and was able to get it working.

RB 9.03 recognizes the TdaSQLBuilder. ApplyUpdates method in RAP.

The call to ApplyUpdates is necessary to force the SQL to regenerate (I
tested with and without).

TSQLBuilder does not always detect that the TdaSQLBuilder has been
modified - and this is one such case.

rbuser

Sorry to say mine doesn't!

With previous code sample sent, I have added the following line before the
lSQLBuilder.Free line...

lSQLBuilder.ApplyUpdates;

but it won't compile (Expected: '(' or '[', but found 'ApplyUpdates'
instead.

So, how did you get it working in RAP?

Do I need to create a pass-thru function to do this, or is it definitely
already there in RAP?

Regards,

Pete Colson

rbuser

Nard,

FYI Due to pressure to release a new version of our software, I have added
ApplyUpdates as a RAP pass-thru function -and it now works...

If we now have the ability to manipulate the SQL query at runtime (very
useful and welcome!) it would be nice if we also had access to the necessary
methods in RAP to be able to make it work, so suggest the ApplyUpdates
procedure is exposed to RAP for the next version!

In the meantime I now have a much better understanding about how to modify
the SQL and AutoSearch search criteria in RAP and have now got a working
solution, so many thanks for your help.

Regards,

Pete Colson.

nardmoseley

Check the Help | About box of the report designer to determine the exact RB
version that you are using.

When I test using RB 9.03, RAP recognizes the TdaSQLBuilder.ApplyUpdates
method. This is method was not exposed to RAP in earlier releases.

rbuser

Oops!!

I seem to be on 9.02 and not 9.03 - apologies for the misleading info!

Regards,

Pete Colson