Support Firebird 'Execute Block' in RB?

rbuser

Hello,
I'm looking to support the Firebird 'Execute Block' statement in
report builder as it would allow for much more sophisticated sql than
is currently possible in RB. The main problem I am coming up against is
the way RB parses its parameters out of SQL, and the way it replaces
the parameter tag with the sql value. For instance the example block at
the end of this post results in parameters for

in_status_id
out_action_sequence_id

This is incorrect because the only parameter in this case is
'in_status_id' Execute block should only be parsed for parameters as
far as the word 'as'

Is it possible to add support for the execute block statement? Some
ideas/questions I have are

1.) Is there a way of using the underlying TDataset descendent to parse
the sql statement for parameters.
2.) Is it possible to use the underlying dataset to 'set' the parameter
values rather than replace the tag with the text value. (This cannot
work with execute block as the text value of a parameter will not parse
in the DB engine.)
3.) Is there a way of telling RB to ignore a parameter name and just
pass it to the db engine unaltered?


I'm currently using the midas data source for RB and I can see in the
code that there is a GetFieldsForSQL method. Is there an equivalent
GetParamsForSQL method somewhere?


execute block
(in_Status_id integer = :in_status_id)
returns
(out_action_sequence_id integer)
as
begin
for
select first 100
action_sequence_id
from actions
where actions.status_id = :in_status_id
into :out_action_sequence_id
do
begin
suspend ;
end
end

Regards,
Will.

nardmoseley

Will,

Check the Designer Help | About box to determine the RB version you are
using. Are you using the latest release, RB 14.08? If not download a trial
version and give it a try - I believe that will do exactly what you want. I
made some enhancements recently to allow parameter references for which no
corresponding Report.Parameters[ ] item exists.

Best regards,

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

rbuser

Nard,
Thanks, I'm not on 14.08 yet but will update and give it a go. I'm
not sure you picked up on the whole of my question though.
:in_status_id is a parameter passed into the execute block statement
but there are a couple of gotchas.

1.) Only the first occurence of the parameter should be supplied not
the second one further down the sql. Is that possible?

2.) The first occurence of parameter does need to be supplied BUT it
has to be done via the TParams property and cannot be substitiuted in.
So say I wanted to pass in a 2 for :in_status_id I think RB would send
the following to the server (assuming the other parameters were ignored)

execute block
(in_Status_id integer = 2) --This is not correct Firebird will object
returns
(out_action_sequence_id integer)
as
begin
for
select first 100
action_sequence_id
from actions
where actions.status_id = 2 --This should be left as the parameter
name.
into :out_action_sequence_id
do
begin
suspend ;
end
end


Given the change you mentioned in 14.08 it might be possible to rewrite
the query as follows. Note that in_status_id is no longer a real
parameter but RB should replace it with an actual value. I will try it
and let you know.

execute block
returns
(out_action_sequence_id integer)
as
declare variable v_in_status_id integer;
begin
v_in_status_id = :in_Status_id ;

for
select first 100
action_sequence_id
from actions
where actions.status_id = :v_in_status_id
into :out_action_sequence_id
do
begin
suspend ;
end
end




--
www.cohesis.co.uk

rbuser

Nard,
I have installed version 14.08 build 48. It does not appear to be
working as expected. Is there something I have to do to enable the new
behavior? If I try the sql below and declare a variable for
in_Status_id the sql designer raises an error when I click the 'OK'
button.

Invalid SQL Statement
No Report Parameter found for: v_in_status_id



execute block
returns
(out_action_sequence_id integer)
as
declare variable v_in_status_id integer;
begin
v_in_status_id = :in_Status_id ;

for
select first 100
action_sequence_id
from actions
where actions.status_id = :v_in_status_id
into :out_action_sequence_id
do
begin
suspend ;
end
end



--
www.cohesis.co.uk

nardmoseley

I tried a simple test using RB 14.08 and Interbase via IBExpress. I created
a new report. I have no report parameters defined. The SQL statement below
is accepted. As a second test, I defined a report parameter, called
ppParameter1, the SQL statement below is still accepted.

Try a simple test example like the one below. Does that work? The
enhancement I made to our code base can be found in daSQLText.pas, the
method TdaSQLText.GetCommandText.

To answer your other question, there is no feature in RB that will ignore
one occurrence of a :Parameter and replace another one with a
report.Parameters[ ] value.

SELECT CUSTOMER_1.ADDR1, CUSTOMER_1.ADDR2,
CUSTOMER_1.CITY, CUSTOMER_1.COMPANY,
CUSTOMER_1.CONTACT, CUSTOMER_1.COUNTRY,
CUSTOMER_1.CUSTNO, CUSTOMER_1.FAX,
CUSTOMER_1.LASTINVOICEDATE,
CUSTOMER_1.PHONE, CUSTOMER_1.STATE,
CUSTOMER_1.TAXRATE, CUSTOMER_1.ZIP
FROM CUSTOMER CUSTOMER_1
Where customer_1.CustNo = :CustNo


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

rbuser

Nard,
Thankyou for your help. I found the problem in the midas/datasnap
provider for RB. Where the commandtext is set and still includes the
':paramname' items the clientdataset raises an exception when opened.
RB then replacs that error with the folowing

Invalid SQL Statement
No Report Parameter found for:

I added a call to TClientDataSet.Params.Clear whereever the
commandtext was set in the midas provider and it now works. You might
want to consider making this change in your code as RB should never
create parameters at the dataset level.

Although I can't use the standard input parameter syntax for execute
block, the alternative workaround I suggested in a previous post works
as expected.

Thanks again.
Will.

nardmoseley

Great to hear you got it working. To be clear, ReportBuilder is not creating
the DataSet params. Check out the Delphi source code to
TCustomClientDataSet.SetCommandText in Datasnap.DBClient.pas. The
SetCommandText method parses the SQL and creates the params.

Try a test without RB, using a TClientDataSet on a form. Does that work
differently? Seems like it would parse the SQL and create the params.


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

rbuser

Nard Moseley (Digital Metaphors) wrote:


Nard,
I totally agree that it is the TClientDataSet code which is parsing
the SQL and creating the params. This is done Client side not Server
side and as the built in parser has no knowledge of Execute Block it
causes invalid parameters to be generated.
Where I feel that RB could handle this better would be in the midas
provider code. RB will not create sql parameters in any Commandtext
sent to TClientDataSet so Calling Params.clear after setting
CommandText works well to resolve this issue.
The other area where RB caused a little confusion is how it handled
the error raised by TClientDataSet when Open was called. TClientDataSet
complained that the parameters were not set, but RB changed that to the
error I quoted in previous posts. Which Is why I raised this support
request rather than just heading straight for the TClientdataset code.

Regards,
Will.