Changing a datapipeline's recordset on the fly with RAP

rbuser

I've created a few passthrough functions that can be called from RAP which
will present different forms for data-constraint entry (like two date-time
pickers for a date range). I'm able to call them and get a return value
back into Report Builder, but now I need to modify my datapipeline so that
it's limited by the date range. My problem is, I'm not too sure how to do
this. Look through, I feel like it's been suggested that I use the
SQLBuilder... so I put the following in BeforeOpenDataPipelines.

var
SQLBuilder : TdaSQLBuilder;
begin
{ Passthrough function which displays my Delphi form, and the datetime
picker result is returned in DateConstraint, a TDateTime }
SingleDate(DateConstraint);
{ Reportbuilder procedure which sets the values of Sunday and Saturday (two
TDateTime's) used below }
FindWeekdays(DateConstraint);

{ RptProduction is the data pipeline I have set for the report, and it
contains all the fields I need to work with (it's a single table in my
database)}
SQLBuilder := TdaSQLBuilder.Create(RptProduction);
SQLBuilder.Sql.SqlText.Text := 'SELECT * FROM RptProduction WHERE ShipDate
+ ''' AND ShipDate <= ''' +
DateToStr(Saturday) + '''';
SQLBuilder.ApplyUpdates;
SQLBuilder.Free;
end;


So I'm not too sure how to actually limit the data pipeline, as I recognize
that this by itself doesn't actually affect it. Any advice would be most
appreciated.
Thank you for your time,
-Steve

nicocizik

Hi Steve,

The TdaSQLBuilder object is not designed to simply give you access to the
SQL object, it is a wrapper around the SQL object making it easier to
manage. I would suggest trying to use some of the built-in routines
included with the object. Take a look at the TdaSQLBuilder topic in the
RBuilder help for code examples and extensive information...

lSQLBuilder.SelectTables.Add('RptProduction');
lSQLBuilder.SelectFields.AddAllFields;
lSQLBuilder.SearchCriteria.Add('RptProduction', 'ShipDate', '=',
DateToStr(Sunday));
lSQLBuilder.SearchCriteria.Add('RptProduction', 'ShipDate', '<=',
DateToStr(Saturday));

lSQLBuilder.ApplyUpdates;
...

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

That makes sense. Thank you for your help. I've changed my code to reflect
your suggested method of using the TdaSQLBuilder.

However, I've hit a new snag. I added the ShowMessage line to test what
might be causing the error. (the dates are hard coded here to explain the
error)
SQLBuilder := TdaSQLBuilder.Create(Report.Datapipeline);
SQLBuilder.SelectTables.Add('RptProduction');
SQLBuilder.SelectFields.AddAllFields;
SQLBuilder.SearchCriteria.Add('RptProduction', 'ShipDate', '>=',
'2008-06-02');
SQLBuilder.SearchCriteria.Add('RptProduction', 'ShipDate', '<=',
'2008-06-03');
ShowMessage(SQLBuilder.SQL.SQLText.Text);
SQLBuilder.ApplyUpdates;
SQLBuilder.Free;

The result of the show message ends with the lines
WHERE ( RptProduction.ShipDate >= )
AND ( RptProduction.ShipDate <= )

What could be going on that my dates aren't being saved to the WHERE
criteria? If I change the first one from '2008-06-02' to '06/02/2008' , it
shows up in the WHERE line. However, my database engine doesn't accept
dates in that format.

Thank you,
-Steve

nardmoseley

When using the Query Designer or SQLBuilder to specify a search date, use
the locale format for your environment - MM/DD/YYYY.

When TdaSQL generates the SQL command, it will translate the date into the
format required by your database. You can specify the database using the
Designer.DataSettings.DatabaseType property.


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

rbuser

Thank you. Is there an example of extending the TppDatabaseType? I'm using
a Pervasive database, and have had to extend DADE for it. But in trying a
few of the available database types, none seem to match up to what's needed
(that particular date format), so I believe I'll have to create a new one.

Thanks,
-Steve

nardmoseley

Which DADE plug-in are you using are you using?


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

rbuser

I extended DADE to create a plug-in for the Pervasive database engine.

nardmoseley

Your custom plug-in can specify the date/time formats that the SQL requires.

See daDB.pas, the class TdaSession defines virtual methods for
GetSearchCriteriaDateFormat and GetSearchCritieriaTimeFormat. In your
TxxPervasiveSeession class, you can implement overrides for those two
methods.

If you would like to make your Persavaive DADE plug-in available for others,
we can list it on our DADE plug-ins page...

http://www.digital-metaphors.com/download/dade_plugins.html


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

rbuser

That worked wonderfully, thank you.

If I get the plugin working in all situations, I'll gladly submit it.
There's one case I've been dodging (a particular setup of using the Query
Wizard to do joins) that is giving an error, so I'll track that down
eventually.
Thank you very much,
-Steve

rbuser

On a related note, I'm wondering if there's any sort of feedback why a
particular SearchCriteria wouldn't end up being used? I have my Report's
datapipeline set to a query I built with the query designer on the data tab.
I ended up modifying the SQL directly, so I understand that prevents me from
using the visual design tools. Will that also prevent me from making
changes with a TdaSQLBuilder? If not, I have the below code which is
showing me that my additional criteria is not being added to the
datapipeline's SQL.

SQLBuilder := TdaSQLBuilder.Create(Report.DataPipeline);
SQLBuilder.SearchCriteria.Add('LensMfgr', 'Name','=', 'Test');
SQLBuilder.ApplyUpdates;
memoText := SQLBuilder.SQL.SQLText.Text; {memoText is a string variable
which I later put in a memo field to view the contents }
SQLBuilder.Free;

Thank you,
-Steve

nardmoseley

QueryDesigner and SQLBuilder both provide an easy way to configure the
TdaSQL object assoicated with the QueryDataView. The TdaSQL object has two
modes, it either contains an object based description that it uses to
generate SQL text OR it simply holds the SQL text string.

Here is an example that shows how to manually edit the sql text and
implement autosearch.

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


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