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

Problem with Passthrough Function for Modifying SQL

edited April 2003 in RAP
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.

Comments

  • edited May 2003
    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


  • edited May 2003
    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?

  • edited May 2003
    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


  • edited May 2003
    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

  • edited May 2003
    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


  • edited May 2003
    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:
  • edited May 2003
    I sent a simple example to support@digital-metaphors.com.

  • edited May 2003
    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


  • edited May 2003
    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.

  • edited May 2003
    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


  • edited May 2003
    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.

  • edited May 2003
    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


This discussion has been closed.