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

[Marcos Douglas]

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.
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.

> 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.

Did you try Greyhound?

Marcos Douglas