Problem with Passthrough Function for Modifying SQL

rbuser

I am using Delphi 5.0 with RB 6.03. I am trying to add a passthrough
function that will allow end users to add manual raw sql to the query before
it executes. I know you guys are going to tell me that I should use the
InsertCriteria method instead, but I need to edit the raw sql without losing
the autosearch criteria.

My function works great, except when in the designer and changing the
autosearch criteria via the magnifying glass button on the preview tab. I
get an access violation. If the user runs the report, it works great.

Here is an example of the function call in an end user designer report:

procedure GlobalOnCreate;
begin
SQLAddRawCriteria(Report.MainReport, 'AND CLIENTS.LASTNAME >
CLIENTS.FIRSTNAME');
end;

Here is the passthrough function (I can send GetSQLObject and
GetSQLCriteriaIndex if needed):

procedure TRWSQLAddManualCriteria.ExecuteFunction(aParams: TraParamList);
var
TempReport: TppCustomReport;
TempList: TStrings;
TempSQL : TdaSQL;
TempBool : boolean;
TempNewSQL : string;
Index : integer;
begin
TempBool := False;
GetParamValue(0, TempReport);
GetParamValue(1, TempNewSQL);
if (TempReport <> Nil) and (TempNewSQL <> '') then
begin
if GetSQLObject(TempReport, TempSQL) then
begin
// I could not modify the SQLText directly so a templist was needed
TempList := TStringList.Create;
TempList.Assign(TempSQL.SQLText);
Index := GetSQLCriteriaIndex(TempSQL.SQLText);
if Index <> (-1) then
TempList.Insert(Index, TempNewSQL)
else
TempList.Add(TempNewSQL);
TempSQL.SQLText := TempList;
TempList.Free;
TempBool := True;
end;
end;
SetParamValue(2, TempBool);
end;

Any suggestions would be great. I feel that I am very close here.

Thanks in advance.

digitalmetaphors

The first thing to do before setting the SQLText is to set the EditSQLasText
property on the TdaSQL object to true.

There is an Exit call in the setter. Try pointing your library path to
RBuilder/Source temporarily and place a breakpoint in daSQL.pas in the
procedure TdaSQL.SetSQLText to make sure it is not exiting and FSQLText is
getting set to your new SQL.

Also, after the creation of the TStringlist, place a try, and then a finally
where you free it, just to be sure if anything bad happens (an exception),
your stringlist will still be freed.

Cheers,

Jim Bennett
Digital Metaphors

rbuser

I commented out the exit, added EditSQLasText and try/finally. I still get
the access violation. The violation occurs when I click the OK button on the
autosearch form. So the AV occurs before the call to my passthrough
function.

Thanks for the help so far.

Do you have any other suggestions? Should I be using the GlobalOnCreate
event to modify the SQL? Is there a better event that I should expose?

digitalmetaphors

Place break points in every one of your pass through function's code to make
sure they are firing. Then you can see in which one it is AV'ing
I didn not intend to direct you to change our source. Do not comment out the
exit call. I just wanted you to see if it was getting in that code and
passing the exit call. If it is exiting, then we need to try something
different to get it to work correctly. Can you provide us with a simple
example. I'll be happy to take a look at it. Email it to
support@digital-metaphors.com


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I can remeber that I got the AV too by
- open/create a report at runtime
- set the EditSQL to true in DADE
- try to select a tab other than "preview sql"

No passthru functions were involved.

Bernd

digitalmetaphors

Bernd,

Can you send us an example that gives an AV at runtime using RB 7.02 after
editing the SQL text? Send a sample project to support@digital-metaphors.com
and we will research the issue.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I just tried to reproduce it, but it seems to be fixed in RB 7.02. The tabs
become disabled and stay disabled. Worls ok for me.

rgds
Bernd

support@digital-metaphors.com
Newsbeitrag
the
tab.
report:

rbuser

I sent a simple example to support@digital-metaphors.com.

digitalmetaphors

I ran your example. What I see is that you are editing the SQL text. This is
not going to work in conjunction with autosearch criteria on the dataview.
Don't edit the SQL by setting the SQL text. You should dynamically create
more search fields on the SQL object and force the dataview to regenerate
with more criteria objects so that the SQL is correctly generated with the
new addition to the WHERE clause. Here is an example showing how to create a
criteria object on a dataview:

http://www.digital-metaphors.com/tips/AddSearchCriteria.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I think we are going in circles. My original newsgroup posting (see bottom)
showed that I am editing the SQLText property. As mentioned earlier, I
cannot use the standard addcriteria method because I need to compare two
different fields in the same query. Also it would be nice to be able to use
IN SELECT statements as well. These features aren't available in RB (AFAIK
they are not going to be available). This is why you gave users the ability
to add their own functionality via passthrough functions and RTTI. Great
feature by the way.

So, I am trying to add this functionality (other newsgroup users have asked
for this as well). The standard newsgroups answer is to use the ExstractSQL
example. I followed that example and almost got my passthrough function to
work. Like I said earlier, the report/function works great if an end user is
running the report. I get an access violation on the autosearch dialog (only
when recalled from the preview screen). You told me to send you an example
that displayed the AV. I understand that Digital Metaphors doesn't want to
have to support end user's "monkeying" with the SQL. But Access Violation
should still be handled. Since I can't use the AddSearchCriteria example, I
must find another route.

Did you encounter the AV using the example I sent? If so, did you find out
why the access violation occurs? Would removing the Added SQL before
displaying the Autosearch form help? If so, what event would that be? I
would like to still evaluate this option (since I am so close) even if it
means handling the exception in the RB source.

Thanks for your help so far.

digitalmetaphors

You can't use autosearch criteria in a dataview where the SQL is edited.
However you can create autosearch fields. I'd like to give a brief
background of the objects involved. Autosearch criteria are persistent and
saved in a dataview. Autosearch fields are dynamically created at runtime
only and are placed on an array property of the report at runtime. You can
create autosearch fields manually as shown in the autosearch demos or allow
autosearch fields to be automatically created based on the autoserach
criteria objects you have defined on the dataview.

I created an example that starts with a dataview to generate some SQL, then
creates autosearch fields (not criteria) and uses them to update the TdaSQL
object by manually inserting SQL into the SQLText property. Here is a Delphi
event handler example. Based on this example, the
Report.CreateAutosearchField is not surfaced in RAP and also the SQL object
text manipulation will also have to be ported into a RAP pass thru.

http://www.digital-metaphors.com/tips/EditSQLAndSearch.zip


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I was trying your example. Is it possible to create autosearch controls at
runtime? I am guessing you can, but what event would I use? The
BeforeAutoSearchDialogCreate only fires if there are autosearch controls.

digitalmetaphors

The demo is an adaptaion of one of our standard autosearch demos. The
difference is that instead of adding to the WHERE clause of a TQuery, it
adds to a dataview's SQL object. Try using the RAP global OnCreate event to
create the autosearch fields.


Cheers,

Jim Bennett
Digital Metaphors