QueryDataView.OnBeforeGenerateSQL

rbuser

All through the versions of RB 10 (Enterprise) (Delphi 6) I've been making
use of the QueryDataView.OnBeforeGenerateSQL and OnAfterGenerateSQL events
to perform tasks to the query before and after the report is run (regarding
which records the user can see).

I have just switched to RB11.03 and this is no longer working, because the
OnBeforeGenerateSQL event is being called over and over, eventually causing
Delphi to crash with too many exceptions.

Example:

procedure SetSecurityEventsForReport(aReport: TppReport; aDesigner:
TppDesigner; UserName: String);
var
FQueryDataView: TdaQueryDataView;
wDataViewCount: Word;
begin
sRBUserName := UserName;
aRBDesigner := aDesigner;
aRBReport := aReport;

wDataViewCount := TmyJoinBuilder.GetQueryDataViewCount(aReport);

if (wDataViewCount > 0) then
begin
for i := 0 to Pred(wDataViewCount) do
begin
if TmyJoinBuilder.GetQueryDataView(aReport, FQueryDataView, i) then
begin
if not Assigned(FQueryDataView.OnBeforeGenerateSQL) then
begin
FQueryDataView.OnBeforeGenerateSQL :=
TmyJoinBuilder.BeforeGenerateSQLEvent;
FQueryDataView.OnAfterGenerateSQL :=
TmyJoinBuilder.AfterGenerateSQLEvent;
FQueryDataView.OutOfSync;
end;
end; {if GetQueryDataView}
end; {for i}
end; {if wDataViewCount > 0}

class function TmyJoinBuilder.GetQueryDataView(aReport: TppReport;
var aDataView: TdaQueryDataView; iReturnDataViewIndex: Integer = 0):
Boolean;
var
lDataModule: TdaDataModule;
begin
{This function returns a dataview from the report, if there are any. By
default it will return the first one found, but if iReturnDataViewIndex
is
not zero then it will return the data view with that index, if
applicable}

aDataView := nil;

{get the datamodule}
lDataModule := daGetDataModule(aReport);

{get the query dataview}
if (lDataModule <> nil) then
if (lDataModule.DataViewCount > 0) then
aDataView :=
TdaQueryDataView(lDataModule.DataViews[iReturnDataViewIndex]);

Result := (aDataView <> nil);
end;

class procedure TmyJoinBuilder.BeforeGenerateSQLEvent(Sender: TObject; aSQL:
TdaSQL);
begin
aRBDesigner.Form.StatusBar.SimpleText := 'Applying employee
security...';
AddUserSecurityToReport(aRBReport, sRBUserName); {this procedure adds a
special table to the query}
aRBDesigner.Form.StatusBar.SimpleText := '';
end;

class procedure TmyJoinBuilder.AfterGenerateSQLEvent(Sender: TObject; aSQL:
TdaSQL);
begin
{Remove the special security table that was temporarily added during
the designer.
Use ReportBuilder's SQLBuilder}
RemoveUserSecurityFromReport(aRBReport, sRBUserName);
end;

Please help as this is crucial to our application and has been working fine
for years until this upgrade.

Thanks,
Jason.
--
Jason Sweby
Software Development Manager,
Carval Computing Limited, Plymouth, UK

Payroll - HR - T&A - Access Control

rbuser

Forgot to mention, this works fine when running the report, it only appears
to execute once, but the problem is in the designer where I also need this
to happen. It is there that the infinite loop occurs.

Jason.

rbuser

I have stepped into the RB code and can confirm that in the designer, the
TdaQueryDataView.DoBeforeGenerateSQL procedure (daQueryDataView.pas)
procedure is repeatedly executed, which in turn repeatedly fires the
FOnBeforeGenerateSQL event, if assigned.

nardmoseley

I created a simple test report and put a stop in the DoBeforeGenerateSQL. I
tested using the end-user Designer. When I select the Preview tab to
generate the report, the DoBeforeGenerateSQL fires a single time. So then I
tried adding an autoserch field. Same result - DoBeforeGenerateSQL fires
once.

Try creating a simple test case using the end-user demo installed to
RBuilder\Demos\EndUser\ReportExplorer. Put a stop in the
TdaQueryDataView.DoBeforeGenerateSQL method and trace in the debugger. If it
fires more than once for each query dataview, then save the report
definition to an .rtm file and send in zip format to
support@digital-metaphors.com and we can check it out here.


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

rbuser

Hi Nard

Thanks for the prompt reply. I have recreated my issue using your end user
demo, by adding the code I'm using to assign an event handler to the
OnBeforeGenerateSQL event. This causes the demo to go into an infinite loop
in the same place as before, usually culminating in a stack overflow.

I have emailed the revised MyEURpt.pas to your support address. Perhaps you
can guide me in what needs to be changed, or whether a bug has crept in.

Kind regards,
Jason.

rbuser

This issue has been fixed by email, a patch was issued for 2 files.

Nard, will these changes make it as standard into the next release of RB?

Thanks,
Jason.

nardmoseley

> Nard, will these changes make it as standard into the next release of RB?

Yes.

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