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

Changing a datapipeline's recordset on the fly with RAP

edited June 2008 in RAP
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

Comments

  • edited June 2008
    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

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited June 2008
    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

  • edited June 2008

    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

    Best regards,

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

  • edited June 2008

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


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

    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • edited June 2008
    I extended DADE to create a plug-in for the Pervasive database engine.

  • edited June 2008

    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

    Best regards,

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

  • edited June 2008

    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

    Best regards,

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