Subject Re: [IBO] Execute procedure with Returning
Author Helen Borrie
At 10:12 AM 19/10/2009, you wrote:
>I have a procedure that inserts a record and returns a value. I need to
>call it with Execute procedure otherwise the insert fails. When I try
>'Execute procedure xxx(...) RETURNING fieldName' I get an error 'Token
>unknown RETURNING'. I am using FB 2.0.x and IBOQuery, IB version 4.8.7
>and D2007. Any idea what is wrong?

RETURNING isn't valid SQL.

In DDL (defining your procedure) the keyword RETURNS () defines the output set

In PSQL, the keyword RETURNING_VALUES() returns the output values to local variables in side the keyword mode. These "local variables" can be the individual members of the output set.

>While I am asking am I correct that I would use the params field of the
>component to read back the value returned?

Yes. But with IBOQuery the Param.AsSomething and Params[].Value methods don't work for all data types when the TDataset creates its TField objects internally and tries to convert the data to Pascal types. In some situations you will find you need to add the Param objects explicitly and configure them yourself.