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

[Toru Takubo]

(2013/04/22 17:06), LacaK wrote:
> Toru Takubo  wrote / napísal(a):
>> Hi Dmitry,
>>
>> Thank you very much for your advice. I am sure your
>> code should work, but unfortunately I can not modify
>> the existing stored procedures.
>>
>> I will explain what I have been trying so far.
>> Suppose you are using the following stored procedure,
>>
>> ----------------------------------------------
>> CREATE procedure [dbo].[IncValue]
>>
>>   @Param int OUTPUT
>>
>> AS
>>
>> SET NOCOUNT ON
>>
>> set @Param = @Param + 1
>>
>> return 100
>> ----------------------------------------------
>>
>>
>> The original program uses TADOStoredProc in Delphi 7,
>> which is very easy to handle stored procedure.
>> How to use it is like this:
>>
>> var
>>   r,i: Integer;
>>   DBP: TADOStoredProc;
>> begin
>> ...
>>    DBP.ProcedureName:='IncValue';
>>    DBP.Parameters.Refresh;
>>    DBP.Parameters.ParamValues['@Param']:=3; //set @Param as 3
>>    DBP.ExecProc;
>>    r:=DBP.Parameters.ParamValues['@RETURN_VALUE']; //got r=100, OK!
>>    i:=DBP.Parameters.ParamValues['@Param']; //got i=4, incremented OK!
>>
>>
>>
>>
>>
>> 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.
> Your program is ok from your point of view ;-))
>
>> 2. TSQLQuery (or TMSSQLConnection) does not support OUTPUT parameter.
> TMSSQLConnection does not support handling of return status and output parameters of stored procedures
> Theoretically it can be added, but it will complicate things ;-)
>
>> 3. TSQLQuery should be able to handle OUTPUT parameter, but currently a bug exists.
>>
> it is not a bug in TSQLQuery, but a missing feature in TMSSQLConnection
>
> -Laco.
>

Hi Laco,

Thank you for your clarification on the above issue.

TMSSQLConnection is newly supported on FPC 2.6 so I don't expect
everything at this moment, but I would appreciate if this feature
could be supported at some future time.

Toru