[fpc-pascal] How can retrive value from OUT parameter?

[Marcos Douglas]

On Sat, Apr 20, 2013 at 2:47 AM, Toru Takubo <takubo at e-parcel.co.jp> wrote:
> (2013/04/19 21:22), Marcos Douglas wrote:
>>
> (snip)
>
>>>
>>> With analogical thinking, I expected that the following might work
>>> for TSQLQuery in FPC:
>>>
>>> var
>>>    r,i: Integer;
>>>    DBQ: TSQLQuery;
>>> begin
>>> ...
>>>     DBQ.SQL.Text:='IncValue :@Param';
>>>     DBQ.Params.CreateParam(ftInteger,'@RETURN_VALUE',ptResult);
>>>
>>> DBQ.Params.CreateParam(ftInteger,'@Param',ptInputOutput).AsInteger:=3;
>>>     DBQ.ExecSQL;
>>>     r:=DBQ.ParamByName('@RETURN_VALUE').AsInteger; //got r=0, NG
>>>     i:=DBQ.ParamByName('@Param').AsInteger; //got i=3, unchanged NG
>>>
>>> It does not work as I expected.
>>>
>>>
>>> I read fpc db tutorials and googled, but I could not find the answer for
>>> this specific issue. Would anyone have an idea which of the following
>>> is the truth?
>>>
>>> 1. My program is wrong or incomplete.
>>> 2. TSQLQuery (or TMSSQLConnection) does not support OUTPUT parameter.
>>> 3. TSQLQuery should be able to handle OUTPUT parameter, but currently a
>>> bug
>>> exists.
>>
>>
>> Hi Toru,
>>
>> You can use SQLdb, MSSQL, StoredProcs and OUTPUT params, without
>> problems... but you have to change a little bit your code.
>> 1- Use a TSQLScript
>> 2- Following your example above, use this SQL:
>>      declare @param int = :param
>>      exec IncValue @param output
>>      select @param as value
>> 3- Call Open instead of ExecSQL
>>
>> You can try Greyhound[1] to abstract this. See the example:
>>
>> (snip)
>>
>
> Hi Marcos,
>
> As to TSQLScript, it does not seem to have "Open" method. It may be
> for execute multiple SQLs which does not return result set.

You right, sorry...

> And I checked your library. It looks simple and nice. But your
> suggestion is based on the same idea shown by Dimitry in previous
> mail in this thread, which require modification of existing stored
> procedures. Unfortunately, I can't modify them.

(Thank you)
Not so fast. I used your SP, without changes!
The tip:
https://github.com/mdbs99/Greyhound/blob/master/src/gh_sqldblib.pas#L115

For MSSQL, the TStatementType need to be [stExecProcedure]. The
StrToStatementType method is protected so, you need to code a new
class... or use Greyhound.

Did you test the code as I sent before?
Take this example:
https://github.com/mdbs99/Greyhound/blob/master/examples/mssql/t1.pas
...and modify it to use the code I sent before.

> One more thing is that some procedures return both result set and
> value through OUTPUT parameter. In this case, if I would retrieve
> OUTPUT parameter as a result set, I must handle multiple result sets.

I never tried to use a result set as output parameter before. But you
can use another SP to call the "real SP" just for the application. ;-)

Regards,
Marcos Douglas