It appears that SQLdb doesn't support (MS) SQL variables.
thanks,
Dmitry
On Fri, Apr 19, 2013 at 8:29 AM, Marcos Douglas <m...@delfire.net> wrote:
> On Fri, Apr 19, 2013 at 9:22 AM, Marcos Douglas <m...@delfire.net> wrote:
> > On Fri, Apr 19, 2013 at 4:26 AM, Toru Takubo <tak...@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@lists.freepascal.org
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
