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

[Marcos Douglas]

On Fri, Apr 19, 2013 at 4:26 AM, Toru Takubo <takubo at e-parcel.co.jp> wrote:
> 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.

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:

procedure TForm1.ToolButton9Click(Sender: TObject);
var
  SC: TghSQLClient;
  DS: TDataSet;
begin
  DS := nil;
  SC := TghSQLClient.Create(DM.coRemote);
  try
    SC.Script.Text := 'declare @param int = :param '#13
                    + 'exec IncValue @param output '#13
                    + 'select @param as value';
    SC.Params['param'].AsInteger := 3;
    SC.IsBatch := True;
    SC.Open(DS);
    FOwnerForm.MsgInfo(DS.FieldByName('value').AsString);
  finally
    DS.Free;
    SC.Free;
  end;
end;

[1] https://github.com/mdbs99/Greyhound
(please, see the dev-0.1.6 branch for new features and changes)

Marcos Douglas