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

Toru Takubo takubo at e-parcel.co.jp
Fri Apr 19 09:26:31 CEST 2013


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!



If you don't have TADOStored, still you can do the equivalent thing
by using TADOQuery. (a little tricky, though...)

var
   r,i: Integer;
   DBQ: TADOQuery;
begin
...
    DBQ.SQL.Text:='IncValue';
    DBQ.Parameters.CreateParameter('@RETURN_VALUE',ftInteger,pdReturnValue,0,0);
    DBQ.Parameters.CreateParameter('@Param',ftInteger,pdInputOutput,0,3); //set @Param as 3
    DBQ.Parameters.Command.CommandType:=cmdStoredProc;
    DBQ.ExecSQL;
    r:=DBQ.Parameters.ParamValues['@RETURN_VALUE']; //got r=100, OK!
    i:=DBQ.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.
2. TSQLQuery (or TMSSQLConnection) does not support OUTPUT parameter.
3. TSQLQuery should be able to handle OUTPUT parameter, but currently a bug exists.


Thank you very much for reading my long mail.

Toru


(2013/04/19 12:47), dmitry boyarintsev wrote:
> Hello Toru,
>
> Not sure if it works with SQLdb or ADO, but it has been working for some other tasks that I've been involved with (not FPC, but delphi based, though).
>
> You can have the result as through the select statement.
> Have a TSQLQuery to run the following query:
>
> SET NOCOUNT ON
> declare @var1 int; -- whatever type of the out parameter you need.
> exec YourStoredProc  @outvar = @var1 output
> select @var1 as var1
>
> then
> var q: TSQLQuery;
>   i:integer;
> ...
> q.Active:=true;
> if not q.eof then
>    i:=q.FieldsByName('var1').asInteger;
>
> Hope that helps.
>
> thanks,
> Dmitry
>
> On Wed, Apr 17, 2013 at 11:45 PM, Toru Takubo <takubo at e-parcel.co.jp <mailto:takubo at e-parcel.co.jp>> wrote:
>
>     Hi All,
>
>     I have an application build with Delphi7+ADO connecting
>     to MSSQL database, and planning to migrate to FPC+SQLdb.
>
>     It has many stored procedures which have OUT parameters
>     to retrieve value from database. I am trying to migrate
>     by using TMSSQLConnection+TSQLQuery, but still I can not
>     settle it. I can execute and pass values to stored procedures,
>     but not the other way. The RETURN_VALUE (default result)
>     can not be retrieved either.
>
>     I would appreciate any advice.
>
>     Toru
>
>
>        _______________________________________________
>     fpc-pascal maillist  - fpc-pascal at lists.freepascal.org <mailto:fpc-pascal at lists.freepascal.org>
>     http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>
>
>
>
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>



More information about the fpc-pascal mailing list