Subject Re: [firebird-support] Re: Problem with my first stored procedure
Author Helen Borrie
At 04:20 AM 19/04/2005 +0000, you wrote:

>Hi Grant,
>Not bad for your first SP. I am not familiar with FIB so I can't tell
>you if you are using that right, but here is some observations from
>the firebird code.
>Your stored procedure is a selectable stored procedure (because it
>returns something).

Not necessarily. An executable procedure will return something too, if it
has a RETURNS clause.

>select Result_ID
>from P_SAVE_DATA_LIST_ITEM (1,'test',1);

Nope. He doesn't want a proc that returns a result set, he wants one that
executes some DML and (if he wants) returns a final, single tuple as output.

>should return you something if you run it in iSQL or any other
>Firebird tool. (replace the parameters with something that makes
>sense in your database).
>Your first problem is that it will not return anything. Firebird uses
>the keyword suspend to output a record from a stored procedure. Every
>time you call suspend, whatever is sitting in the output variables of
>the stored procedure is returned.

Wrong. Firebird PSQL uses the keyword SUSPEND to suspend execution while
waiting for the client to fetch a row of output for a multi-row output
set. You don't use SUSPEND in an executable procedure.

For the rest, Grant, attend to Dmitry Sibiryakov's comments; however, Dimi
is mistaken when he tells you not to test for InTransaction. It doesn't
eat anything; and an exception will occur if one attempts to call the
Commit method on a transaction object that is not InTransaction.

I use IBO, not FIB+, so I'm not certain whether FIB (like IBO) uses
Params[] for input arguments and Fields[] for output arguments. I think
it's at least possible that FIB+ might follow the VCL model, in making all
arguments Params and forcing you to distinguish between ptInput and
ptOutput. You need to RTM and/or ask on the FIB+ list. Firebird-support
isn't a Delphi support list....

Grant, please don't send your company footers to the lists. (Fourth ask!)