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

[dmitry boyarintsev]

It appears that SQLdb doesn't support (MS) SQL variables.

thanks,
Dmitry


On Fri, Apr 19, 2013 at 8:29 AM, Marcos Douglas <md at delfire.net> wrote:

> 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
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal at lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20130419/77fdb696/attachment.html>