Stored Procedures and AutoSearch

edited October 2010 in DADE
Hi all,

Is there any way to use AutoSearch parameters to populate SQL parameters
in a dataview with manual SQL?

I am trying to move to utilizing stored procedures to server data for my
reports. To do this I must be able to compose a select statement
including the parameteres for the stored procedure (i.e. "Select * from
SP_GetAllSales(:Begin_Date,:End_Date)"). I get an error "Token unknown
line 1, char 31 ,". I guess the core of this question is: Is there any
way to use parametrized SQL and link the SQL parameters to report
parameters/autosearch for end-user input?

If there is not a straight-forward way to do this, is it possible the
"manually"/programatically set the SQL text of a dataview via a RAP pass
through function? And if so, will this affect any links setup to other
dataviews?

--
Regards,
Branden Johnson
Integrity Software Design

Comments

  • edited October 2010
    Sorry, didn't mean to double post. Didn't think the first message was
    posted...
  • edited October 2010
    Hi Branden,

    You have two options.

    Depending on the version of ReportBuilder you are using (RB 11+) you can
    manually edit the SQL text and still link your datasets. You could manually
    create your queries including parameter syntax, then use the Parameters
    feature in ReportBuilder to use AutoSearch to update the stored proc
    parameters. This would be the first option.

    The second option would be to create custom dataviews for the stored
    procedures you would like to use. There is an example of how this can be
    done with SQL Server located in the \Demos\4. EndUser Databases\SQL
    Server\2. ADO\StoredProc\... directory. If you are not using SQL Server the
    same concepts will apply.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2010
    I am using RB 12.01 with daIBO; dtFirbird, IBOSession

    I am currently getting an error with the following SQL Text:
    Select * from SP_GetAllSales(:Begin_Date,:End_Date)

    Error:

    Invalid SQL Statement
    Dynamic SQL Error
    SQL error code = -104
    Token unknown - line 1, char 37
    ,



    What is parameter syntax if this is not? I cannot find any
    documentation on this...

    --
    Regards,
    Branden Johnson
    Integrity Software Design



  • edited October 2010
    I got this partially figured out. I stepped through the code and saw
    that I had to setup report level parameters that were named the same as
    the SQL parameters before doing the manual SQL. Otherwise it replaces
    populates the parameters with nothing since no default report parameter
    value is found. Once I got past that in the code the query was able to
    open in the TdaSQL.CreateSelectFieldsFromSQLText, but I am now running
    into an access violation.
    In stepping through the code it appears that
    CreateSelectFieldsFromSQLText is called twice, invoking
    TdaSQL.GetSQLCommandText and after the second time procedure
    TdaIBOQueryDataView.SQLChanged is invoked, which calls
    TdaSQL.GetMagicSQLText, which also calls GetSQLCommandText a third
    time. At this point, however, an access violation is raised due to the
    TdaSQL.dataView.Report object no longer being assigned.

    I have a somewhat hoaky work-around:
    changed daIBO.pas line 600 from

    FQuery.SQL := SQL.MagicSQLText;

    to

    if Assigned(SQL.DataView) and Assigned(SQL.DataView.Report) then
    FQuery.SQL := SQL.MagicSQLText;

    This does not raise the access violation and allows the dataview to be
    created. However, you cannot preview the dataview. Attempting to do
    this raises the error "Cannot prepare a blank statement". But, If I go
    to the SQL tab once more and click OK on the Query Designer it will
    correctly set the FQuery.SQL and I can then preview dataview.

    Something if freeing the SQL.DataView.Report object before the
    SQLChanged procedure is called...but only on the initial QueryDesigner
    "OK" click.


  • edited October 2010
    Hi Branden,

    Try upgrading to RB 12.02 and see if that helps the issue. We made a few
    fixes relating to manual SQL and parameters that could solve the problem.
    For upgrade instructions, contact info@digital-metaphors.com with your
    serial number and purchasing email address.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.