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

LacaK lacak at zoznam.sk
Mon Apr 22 10:06:57 CEST 2013


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.




More information about the fpc-pascal mailing list