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

[Marcos Douglas]

On Fri, Apr 19, 2013 at 9:22 AM, Marcos Douglas <md at delfire.net> 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
>> ----------------------------------------------
>>
>>
>> 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

Sorry, I did not explained these:
DM.coRemote = DM is a DataModule and coRemote is a instance of
TghSQLConnector class.
FOwnerForm.MsgInfo = change this for ShowMessage function.

Best regards,
Marcos Douglas