Board index » delphi » Output parameter in stored proc. using ADO

Output parameter in stored proc. using ADO

I have imported ADO typelib to use it for invoking stored procedure in MSSQL
server. The procedure has output parameter which is correctly returned to me
under ISQL. Following is the code to do this in Delphi. The problem is : I
cannot get back output value(in the code variable id is always 0). What I am
doing wrong?
//--Procedure declaration
CREATE PROC prProcName(@str  varchar(100), @id int OUTPUT) AS

//-------------
   pCommand := CoCommand.Create;
   pCommand.Set_ActiveConnection (pConnection);
   pCommand.Set_CommandType(adCmdStoredProc);
   pCommand.Set_CommandText ('prProcName');

   input  := pCommand.CreateParameter('@str',
                                       adChar,
                                       adParamInput,
                                       100, res);
   output := pCommand.CreateParameter('@id',
                                       adInteger,
                                       adParamReturnValue,
                                       4, res);
   output.Set_Direction(adParamReturnValue);

   pCommand.Get_Parameters.Append(input);
   pCommand.Get_Parameters.Append(output);

   V := VarArrayOf(['test',10]);
   pCommand.Execute(res, v, adCmdStoredProc);
   id := output.value;    // in the debug mode output.value is shown as
'unassigned'
   result := id;

 

Re:Output parameter in stored proc. using ADO


Hello,

What happens if you try the same exact thing from VB?

--
Binh Ly
Brickhouse Data Systems, Inc.
http://www.brickhouse.com

Quote
MiB wrote in message <6sf803$9m...@forums.borland.com>...
>I have imported ADO typelib to use it for invoking stored procedure in
MSSQL
>server. The procedure has output parameter which is correctly returned to
me
>under ISQL. Following is the code to do this in Delphi. The problem is : I
>cannot get back output value(in the code variable id is always 0). What I
am
>doing wrong?
>//--Procedure declaration
>CREATE PROC prProcName(@str  varchar(100), @id int OUTPUT) AS

>//-------------
>   pCommand := CoCommand.Create;
>   pCommand.Set_ActiveConnection (pConnection);
>   pCommand.Set_CommandType(adCmdStoredProc);
>   pCommand.Set_CommandText ('prProcName');

>   input  := pCommand.CreateParameter('@str',
>                                       adChar,
>                                       adParamInput,
>                                       100, res);
>   output := pCommand.CreateParameter('@id',
>                                       adInteger,
>                                       adParamReturnValue,
>                                       4, res);
>   output.Set_Direction(adParamReturnValue);

>   pCommand.Get_Parameters.Append(input);
>   pCommand.Get_Parameters.Append(output);

>   V := VarArrayOf(['test',10]);
>   pCommand.Execute(res, v, adCmdStoredProc);
>   id := output.value;    // in the debug mode output.value is shown as
>'unassigned'
>   result := id;

Re:Output parameter in stored proc. using ADO


I have had similar problems using the followiing code:

function TNetCareDB.GetNextId(IdType: Integer): Integer;
var
  Cmd: _Command;
//  Recs: OleVariant;
//  PNil: ^OleVariant;
begin
  Cmd := CoCommand.Create;
  Cmd.Set_ActiveConnection(FADOConnection);
  Cmd.CommandType := adCmdStoredProc;
  Cmd.CommandText := 'fnGetNextId';
  Cmd.Parameters.Append(Cmd.CreateParameter('', adInteger,
adParamReturnValue, 0, 0));
  Cmd.Parameters.Append(Cmd.CreateParameter('IdType', adInteger,
adParamInput, 0, IdType));
  { Use late binding to call Execute because there are problems with
the
    early binding version - the return value parameter always seems to
be
    set to Unassigned after the call }
// PNil := nil;
//  Cmd.Execute(Recs, PNil^, -1);
  Variant(Cmd).Execute;
  Result := Cmd.Parameters.Item[0].Value;
  if Result <= 0 then
    raise Exception.Create('GetNextId failed.');
end;

The lines which are commented should use the early bound side of the
dual interface but they do not work - the same thing works fine in VB
and VC++ and I have not found any reason for this behaviour.

Casting Cmd to a variant forces Delphi to use the IDispatch side of
the dual interface which works fine but suffers the normal performance
hit.

The results are the same for Delphi3 and Delphi4.

Any ideas?

Graham{*word*19}ell
GC Consulting Services Ltd

Other Threads