Board index » delphi » Additional Information for Posting on No Output Params on Stored Procedure

Additional Information for Posting on No Output Params on Stored Procedure

I have an update to my original message that I posted yesterday on this
issue with the subject "Problem with ADO and Output Params of Stored
Procedures".

After doing further reasearch, it looks like this may have to do with MTS.
I can get the output parameters from a TADOStoredProc when I create a test
client/server application.  When I create a MTS DataModule with the same
type of call, passing the output parameters back to a client app, making it
a 3-tier application, the output parameters do not come back.

I debugged the MTS DataModule to find that those value really are not
getting set, because all of the output parameters of the stored procedure
are NULL.

Has anyone seen this problem before?  I am at a loss here.  Any help would
be greatly appreciated.

Thanks,

Ray Oberg
Senior Developer
The Credit Store
rob...@creditstore.com

 

Re:Additional Information for Posting on No Output Params on Stored Procedure


Hi,
I did have a problem getting output params back from the MS ADO
Library(before D5) using a Command object. They would come back NULL as you
described. It turned out that the result of the Command.Execute Method must
be assigned to a RecordSet Object before ODBC would query the output params.
MS says this is by design. I don't know if this is your situation but it
might be worth a look.

<Example>

  ADSN := 'DRIVER=SQL Server;' +
          'SERVER='+ fServerName +';';
  AdoCn := CoConnection.Create;
  AdoCn.Provider := 'MSDASQL';
  AdoCmd := CoCommand.Create;
  AdoCmd.Set_CommandText(ProcName);
  AdoCmd.CommandType := adCmdStoredProc;
  try
    AdoCn.Open(ADSN, fUserName, fPassword, 0);
    AdoCn.DefaultDatabase := fDatabaseName;
    AdoCmd.Set_ActiveConnection(AdoCn);
    AdoCmd.Parameters.Refresh;
    ...
    {Fill Your Input Params here}
    ...
{**********************************************************}
    AdoCmd.Execute(EmptyParam,EmptyParam,0);   {Generates NULL Output
Params}
{**********************************************************}

    {Must do this before Output Params are Queried by ODBC}
    { "http://support.microsoft.com/support/kb/articles/Q167/9/08.asp"}
    rs := AdoCmd.Execute(EmptyParam,EmptyParam,0);
    ...
    {get your Output Params Here}
    ...
   finally
    if AdoCn <> nil then
    begin
      if AdoCn.State = adStateOpen then AdoCn.Close;
      AdoCn := nil;
    end;
    if AdoCmd <> nil then AdoCmd := nil;
    if rs <> nil then rs := nil;
  end;
</Example>

HTH,
Gregg

Other Threads