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.
Thank you very much for reading my long mail.
Toru
(2013/04/19 12:47), dmitry boyarintsev wrote:
Hello Toru,
Not sure if it works with SQLdb or ADO, but it has been working for some other
tasks that I've been involved with (not FPC, but delphi based, though).
You can have the result as through the select statement.
Have a TSQLQuery to run the following query:
SET NOCOUNT ON
declare @var1 int; -- whatever type of the out parameter you need.
exec YourStoredProc @outvar = @var1 output
select @var1 as var1
then
var q: TSQLQuery;
i:integer;
...
q.Active:=true;
if not q.eof then
i:=q.FieldsByName('var1').asInteger;
Hope that helps.
thanks,
Dmitry
On Wed, Apr 17, 2013 at 11:45 PM, Toru Takubo <tak...@e-parcel.co.jp
<mailto:tak...@e-parcel.co.jp>> wrote:
Hi All,
I have an application build with Delphi7+ADO connecting
to MSSQL database, and planning to migrate to FPC+SQLdb.
It has many stored procedures which have OUT parameters
to retrieve value from database. I am trying to migrate
by using TMSSQLConnection+TSQLQuery, but still I can not
settle it. I can execute and pass values to stored procedures,
but not the other way. The RETURN_VALUE (default result)
can not be retrieved either.
I would appreciate any advice.
Toru
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
<mailto: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
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
