Stored Procedures and AutoSearch

rbuser

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

rbuser

Sorry, didn't mean to double post. Didn't think the first message was
posted...

nicocizik

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

rbuser

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

rbuser

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.

nicocizik

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