Group Error when SQL is changed dynamically

rbuser

We have a EndUser application based on Report Explorer. Users can
select a Report from the rb_Item table and they can modify the SQL
Query to filter the data, using the Where clause.

The application also has an interface to allow the user to select and
reorder one of the 5 pre-defined Groups in the Report (i.e. SalesGoup,
SalesRep, AcctID, ProdType, Items).

The first time the Report runs everything is fine, when the user
changes the Groups the results are incorrect. Only the first group
breaks correctly, the other groups only shows the header from the
first subgroup and the footer of the last subgroup.

The app reads the SQL Query stored inside the report and based on user
selection modifies the Query, but apparently the report cannot see the
new statements. I attached a grid to display the results of the
dataset and they look OK. Why the report cannot see the new data
when the dataset is modified ???

Here is a snippet of the code:

//--- load report definitions from rb_item into memory
ppReport.Template.DatabaseSettings.Name :=
rb_itemitem_name.AsString;
ppReport.Template.LoadFromDatabase;

//--- verify if the report has a valid DataPipeline
if ppReport.DataPipeline = nil then begin
ShowMessage('This Report does not have a DataPipeline');
exit;
end;

//--- load Groups into the dynamic-global array wGroups
SetLength(wGroups,0);
for i:=0 to ppReport.GroupCount-1 do begin
SetLength(wGroups, length(wGroups)+1);
wGroups[length(wGroups)-1] := ppReport.Groups[i];
end;

//--- extract SQL statement from Report and display it into memSQL
wSQLBuilder := TdaSQLBuilder.Create(ppReport);
memSQL.Lines := wSQLBuilder.SQL.SQLText;

//--- set command text with new SQL Query
dSet.Close;
dSet.CommandText := memSQL.Text;
dSet.Open;

Then after the user modifications:

ppReport.BeginUpdate;

try

editGroup.Text := '';
editOrder.Text := '';

//--- hide header & footer bands and remove current groups in RB
for i:=ppReport.GroupCount-1 downto 0 do begin
ppReport.Groups[i].HeaderBand.Visible := false;
ppReport.Groups[i].FooterBand.Visible := false;
ppReport.RemoveGroup(ppReport.Groups[i]);
end;

for i:=0 to ListView1.Items.Count-1 do begin

ListItem := ListView1.Items.Item[i];
subField := ListItem.SubItems.Strings[0];
subHeader := ListItem.SubItems.Strings[1];
subFooter := ListItem.SubItems.Strings[2];
// TppGroup Object
subGroup := TppGroup(ListItem.SubItems.Objects[3]);

// Group No selected
if (subHeader = ' ') and (subFooter = ' ') then continue;

ppReport.AddGroup( subGroup );
ppReport.Groups[i].HeaderBand.Visible := (subHeader = 'x');
ppReport.Groups[i].FooterBand.Visible := (subFooter = 'x');

//--- update edit fields to use in the SQL Query
if editGroup.Text<>'' then editGroup.Text := editGroup.Text+',';
if editOrder.Text<>'' then editOrder.Text := editOrder.Text+',';

editGroup.Text := editGroup.Text + subField; editGroup.Refresh;
editOrder.Text := editOrder.Text + subField; editOrder.Refresh;

//--- set header and footer bands object variables
ppHBand := ppReport.GroupHeader[i];
ppFBand := ppReport.GroupFooter[i];

end;

//--- modify SQL based on user's selections
SetWhere;
SetGroup;
SetOrder;

//--- new Dataset contains the right data, but RB cannot see it
dSet.Close;
dSet.CommandText := memSQL.Text;
dSet.Open;

except
ShowMessage('Error Creating SQL Statement. See MIS');
end;

ppReport.EndUpdate;


We are using Delphi 6, RB 9.0

Thanks for your attention,
Jorge.

nardmoseley

1. The standard way to support a dynamic SQL Where clause is to use the
AutoSearch facilities built into the product. See the article below
"Creating AutoSearch parameters via DADE".

2. TdaSQLBuilder can be used to modify a QueryDataView's TdaSQL object. The
standard way to do this is to use the methods of the TdaSQL object. See the
TdaSQLBuilder class reference in the RBuilder.hlp file.

When you use the methods of the TdaSQL object, the SQL is maintained as a
set of objects: SelectFields[], SelectTables[], etc. In this way the TdaSQL
object can dynamically generate the SQL Text which the QueryDataView assigns
to the DataSet.

It is possible to directly modify the TdaSQL object's SQLText:

lSQLBuilder := TdaSQLBuilder.Create(ppReport);
lSQLBuilder.SQL.EditSQLAsText := True;
lSQLBuilder.SQL.SQLText := 'Select * from customer';
lSQLBuilder.ApplyUpdates;

lSQLBuilder.Free;










----------------------------------------------------
Tech Tips: Creating AutoSearch parameters via DADE
----------------------------------------------------


1. Access the Data workspace of the Report Designer.

2. Create a DataView using the Query Wizard or Query Designer.

2. A DataView tool window will be displayed in the data workspace.

3. Press the Search button. The Search tab of the Query Designer will be
displayed.

4. Add a search criteria item and select the AutoSearch checkbox. You can
optionally select Mandatory as desired.

5. Close the query designer.

6. Now when you select the Preview tab of the Report Designer. A search
button will appear on the toolbar. This displays the AutoSearch dialog.

7. By calling Report.Print in code, or by selecting print/preview from the
report explorer the AutoSearch dialog will be automatically be displayed.



--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Hi Nard,

Thanks for your answer. Now I'm modifying the SQL externally and
posting it into the SQLText, it works great when the query to execute
lasts less than 30 seconds, but when I try to execute a long query
(2min) I got a 'Timeout Error'.

How can you increase the time assigned to executed a query posted as
SQLText ?

I have no DataPipeline connected to the report, so the CommandTimeout
in any of the TDataSet descendants in meaningless in this case. I
tired increasing the TADOConnection Timeouts with no results.

Kind regards,
Jorge

nardmoseley

Set TADOConnection.CommanTimeOut.



--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

nardmoseley

Sorry, it should read TADOConnection.CommandTimeOut

(That value will be applied to the ADOQuery objects created by DADE).


--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Thanks, but apparently CommandTimeOut is not a public member of
TADOConnection, I can't see it when I try a code completion.

On Wed, 5 Oct 2005 10:11:13 -0500, "Nard Moseley \(Digital

rbuser

Hello Nard,

I found the way to change the value, but I'm still getting the
infamous: "Invalid SQL Statement. Timeout Expired".

This is part of the code:

TdaADOSession.GetDefaultADOConnection.CommandTimeout := 600;

iSQLBuilder := TdaSQLBuilder.Create(ppReport);
iSQLBuilder.SQL.EditSQLAsText := true;
iSQLBuilder.SQL.SQLText.Clear;
iSQLBuilder.SQL.SQLText := memSQL.Lines;
iSQLBuilder.ApplyUpdates;
iSQLBuilder.Free;

What's wrong here ? Where should I change the CommandTimeOut value ?

Thanks a lot Nard, appreciate your help,
Jorge.

nardmoseley

The ADOConenction object resides on the end-user form.
TADOConnection.CommandTimeOut is a published property. You can set it at
Delphi design-time via the object inspector.



--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Well, I thought that internally RB created another connection, so I
was looking for it. Also I was looking for a class var which will do
the trick, or some internal DataSet created 'on the fly' by RB.

My TADOConnection.CommandTimeOut in the form is 600 seconds, so
definitely that's not the value I need to change.

In my case, I am working with a 'disconnected' report, not using a
pipeline connected to a dataset. The report is stored in a table, when
the user selects the report I load it in memory, pull the SQLText and
put it into a TMemo. After the user changes the Memo, I post the
modified SQL into the report using you recommendation:

iSQLBuilder.SQL.EditSQLAsText := true;
iSQLBuilder.SQL.SQLText.Clear;
iSQLBuilder.SQL.SQLText := memSQL.Lines;
iSQLBuilder.ApplyUpdates;
iSQLBuilder.Free;

When RB is trying to copy the MemoLines (line 3) to SQLText it stays
there forever (trying to execute the SQL I assume) and finally boom,
the timeout message, after exactly 45 seconds.

When I execute the same query with a where condition to reduce the
number of returned records it works fine. When I execute the full
query in SQL Query Analyzer it takes 1:25 min to return the data.

Any other idea ?

Thanks again,
J.L.

nardmoseley

Trace the RB Source code for the unit daADO.pas.

The ADOQueryDataView.SQLChanged method is where the query gets fired.

The Designer.DataSettings should be configured to use the ADOConnection that
resides on the end-user form. This is the only connection that should be
used. The DataSettings.DatabaseName is saved as part of the report
definition. So you do not want to change that name, otherwise the object
reference cannot be resolved and then the default conneciton will be used.

Search the unit for occurences of CommandTimeOut. The property value from
the ADOConnection on your form should be propogated to the ADO datasets.


--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Nard, I did my homework and these are the results,

try

iSQLBuilder := TdaSQLBuilder.Create(ppReport);

//--- this returns 600, is reading from my TADOConnection

ShowMessage(IntToStr(TADOConnection(iSQlBuilder.SQL.Session.GetDatabaseForName('euDataBase')).CommandTimeout));

//--- to make sure I tested this, it returns 30 the first time

ShowMessage(IntToStr(TdaADoSession.GetDefaultADOConnection.CommandTimeout));

//--- changed to 600
TdaADoSession.GetDefaultADOConnection.CommandTimeout := 600;

//--- it took it, now returns 600

ShowMessage(IntToStr(TdaADoSession.GetDefaultADOConnection.CommandTimeout));

iSQLBuilder.SQL.EditSQLAsText := true;
iSQLBuilder.SQL.SQLText.Clear;

//--- never can pass this line, after 45 seconds, timeout !!!
iSQLBuilder.SQL.SQLText := memSQL.Lines;
iSQLBuilder.ApplyUpdates;
iSQLBuilder.Free;

except
on E: Exception do ShowMessage(E.Message);
end;

Based on the reasearch, I copied the full SQL (1.25 min to execute)
into the Query Designer and press OK to save it... the timeout again.

So, the same problem occurs when RB tries to 'interpret' my SQL
command in Query Designer before save it. If I put a where condition
to return less records and press OK, it works perfect.

Is there any way to increase that timeout value ? Or, how can I bypass
the execution del SQL when is being saved ? Could you please explain
what's going on behind the scenes ?

Thank you,
Jorge.


On Wed, 5 Oct 2005 11:55:19 -0500, "Nard Moseley \(Digital

nardmoseley

Try modifying your Delphi library path to include RBuilder\Source (just
above the entry for RBuilder\Lib). Then run in the debugger and step thru
the code.

If you would like to create a simple example that uses ADO and MS Access
Northwinds data, you can email in zip format to
support@digital-metaphors.com and we can run it here in the debugger.


--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Thanks Nard, I am going to debug the app with the RB units.

On Thu, 6 Oct 2005 11:19:34 -0500, "Nard Moseley \(Digital