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

[Toru Takubo]

(2013/04/20 23:06), Marcos Douglas wrote:
> 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
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>

Thank you again for your advice. Now I understand what you mean.
Actually I made a descendant class of TSQLQuery, which override
StrToStatementType to force TStatementType on stExecPrcedure, but
it did not work then. Your suggestion is that in addition to it,
I should create a small sub-query to get OUTPUT parameter as result set.

I will try to proceed. On the other hand, I'm wondering if TSQLQuery
(or TMSSQLConnection) would natively support OUTPUT parameter and
RETURN_VALUE...

Toru