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

[Toru Takubo]

(2013/04/22 10:46), Marcos Douglas wrote:
> On Sun, Apr 21, 2013 at 10:05 PM, Toru Takubo <takubo at e-parcel.co.jp> wrote:
>>
>> (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.
>
> You need to override StrToStatementType method of TMSSQLConnection,
> not TSQLQuery.

Sorry, my mistake.

> The problem is the connection is global to all TSQLQuery instances so,
> you need to 'save' the status before call StrToStatementType to
> restore after.
> The idea to create a SP to call the real SP that have OUTPUT
> parameters is to facilitate the integration with MSSQL... but I never
> needed to do this before.

Thank you for your hint.

>
>> I will try to proceed. On the other hand, I'm wondering if TSQLQuery
>> (or TMSSQLConnection) would natively support OUTPUT parameter and
>> RETURN_VALUE...
>
> Well, even using Delphi (4~7) I never used RETURN_VALUE. I always used
> MSSQL but I never needed this feature.

All my stored procedures use RETURN_VALUE in order to return error code.
I think it useful, but I also know everyone has his/her own programming style.

>
> Did you try Greyhound?

I haven't dig into it yet, but I will try later.

Thank you!

Toru