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

[Toru Takubo]

(2013/04/19 21:22), Marcos Douglas wrote:
> 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
>> ----------------------------------------------
>>
(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:
>
> 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
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>

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.

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.

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.

Thank you and all lists,

Toru