Manipulate SQL Query programmatically

rbuser

Hi!
I want to enable end users of my application to use variables in their SQL
Queries. Which are replaced programmatically, each time before the
datapipeline
is opened by values from a selction of my program.

Background is:

Users have a list of shops, where they can select multiple (Within main
application)
Users can select 2 dates. (Within main application)

I want to use ReportTemplate files to display only the data, the users have
selected
in my main application without the need for the user to edit a report or a
query.

Therefore I want to create DadeViews which look like "select * from foo
where (shop = '%S')"

Now programmatically the %S should be changed to i.e. "1".

What I tried is that:

procedure TForm1.ppReport1BeforeOpenDataPipelines(Sender: TObject);
var
i : Integer;
lSQLBuilder : TdaSQLBuilder;
begin
myList := TList.Create;
ppReport1.GetRBDataModulePipelines(mylist);
for i := 0 to mylist.Count-1 do
begin
showmessage(TppDBPipeline(mylist.Items[i]).UserName);
lSQLBuilder := TdaSQLBuilder.Create(TppDBPipeline(mylist.Items[i]).sql);
memo1.lines.AddStrings(lSQLBuilder.SQL.GetMagicSQLText);
lSQLBuilder.SQL.MagicSQLText.Clear;
lSQLBuilder.SQL.MagicSQLText.Add('select * from foo');
lSQLBuilder.ApplyUpdates;
lSQLBuilder.Free;
end;

end;

It doesn't work. The SQL is not changed. What do i wrong ? Has anyone a
better idea for my
problem, maybe it is possible to use a QueryTemplate instead ? I am still
searching for someone
who want to consult me a little with all that questions and sends me
invoices :-)

Philip

rbuser

Maybe it is not a good idea to edit & parse the whole SQL string but
use SQLBuilder to add some filters instead. How would this look like
in my example below ? Currently I cannot apply my programmatical
changes to the data view.

nardmoseley

For future reference please post questions to a single destination - either
the newsgroups or support@digital-metaphors.com

From your description, I understand that the main application will have some
global parameters:

1. List of Shops
2. Pair of Dates

Are these application wide? Or do you want the user to specify them for each
report?

For the reports, are you defining all of the queries, or is the end-user
going to have the ability to create completely new queries?






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

rbuser

>

The user specifies them after program start and then selects a report
template he wants
to use with this data. The idea is that the user not necessarily has to work
with sql
queries and that we ship some standard reports which will work for all
users. If a user
wants to go deeper he can build his own reports. The main problem is that
the variations
of shops and dates will always change, everything else stays equal.

Some time a user might want to compare 2 shops & a date range with each
other.
Then he might want to get a list with all shops or a list with a special
shop group etc tec


The end user will have the ability to create completely new queries.
Especially because
it might happen that he wants to connect completely new data sources.

Therefore I thought it would be great if I could pass the shop & date
selection from my
application directly to the individual SQL query by some kind of variable /
placeholder.

nardmoseley

Here is a downloadable example that I put together. It shows how to use
ReportBuilder's AutoSearchFields to do what you describe. I used the Query
Designer to add search criteria that are designated as AutoSearch. I used
the Report.Template.OnLoadEnd event to initialize the search paramters with
the application param values.

www.digital-metaphors.com/tips/ApplyGlobalVariablesToAutoSearch.zip


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

rbuser

thank you! i look at it. it doesnt compile and says the ressource file was
corrupted

nardmoseley

Close the project, delete the .res and then re-open the project - Delphi
will recreate the .res. I used D2007 to create the example.

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

rbuser

wrong button :-) i use delphi 7 as well. now it comes up with:

[Error] Unit1.pas(63): PROCEDURE or FUNCTION expected
[Error] Unit1.pas(66): ',' or ':' expected but 'CLASS' found
[Error] Unit1.pas(70): ',' or ':' expected but '=' found
[Error] Unit1.pas(97): Undeclared identifier: 'SetCompany'
[Error] Unit1.pas(112): Undeclared identifier: 'SetCompany'
[Error] Unit1.pas(143): Identifier redeclared: 'TmyReportManager'
[Error] Unit1.pas(149): Undeclared identifier: 'aReport'
[Error] Unit1.pas(152): 'DO' expected but identifier 'AutoSearchFieldCount'
found
[Error] Unit1.pas(157): Identifier redeclared: 'TmyReportManager'
[Error] Unit1.pas(160): Undeclared identifier: 'aAutoSearchField'
[Error] Unit1.pas(160): 'THEN' expected but identifier 'FieldAlias' found
[Error] Unit1.pas(174): Undeclared identifier: 'Result'
[Error] Unit1.pas(177): Statement expected but 'CLASS' found
[Error] Unit1.pas(177): Undeclared identifier: 'SetCompany'
[Error] Unit1.pas(179): Undeclared identifier: 'aCompany'
[Error] Unit1.pas(66): Unsatisfied forward or external declaration:
'.3.SetCompany'
[Error] Unit1.pas(67): Unsatisfied forward or external declaration:
'.3.Company'
[Hint] Unit1.pas(72): Private symbol 'PrepareSearchField' declared but never
used
[Error] Unit1.pas(74): Unsatisfied forward or external declaration:
'.5.PrepareReport'
[Fatal Error] Project5.dpr(5): Could not compile used unit 'Unit1.pas'

nardmoseley

Try downloading the example again. I updated it, it now works with D7 in my
testing here.

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