TMSStoredProc does not clear output params - it is correct?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Alexander_73
Posts: 52
Joined: Mon 24 Mar 2008 13:22

TMSStoredProc does not clear output params - it is correct?

Post by Alexander_73 » Mon 12 Jul 2010 17:21

RAD Studio 2007, SDAC 4.0.57.
An stored procedure have input and output parameters:

Code: Select all

CREATE PROCEDURE [dbo].[atisp_ConvertATIFirmToPublishingFirm] (@ATIFirmID INT, @PubFirmID INT OUT)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT @PubFirmID=FirmID FROM Firms 
  WHERE ATIFirmID=@ATIFirmID;
  IF @PubFirmID IS NOT NULL 
    RETURN;
      
  SET XACT_ABORT ON;        
  BEGIN TRAN

  INSERT INTO Firms (CityID, BrandName, ProfileID, Attributes, ATIFirmID)
  SELECT CityID, BrandName, ProfileID, 256, @ATIFirmID
  FROM ATI.dbo.Firms
  WHERE FirmID=@ATIFirmID;
 
  SELECT @PubFirmID=SCOPE_IDENTITY();
  ...
  COMMIT TRAN;
END
In a Delphi project I call this procedure by TMSStoredProc object:

Code: Select all

  sp_ConvertATIFirmToPubFirm.Prepare;
  try
    sp_ConvertATIFirmToPubFirm.FindParam('ATIFirmID').AsInteger:=ATIFirmID;
    sp_ConvertATIFirmToPubFirm.Execute;
    NewPubFirmID:=sp_ConvertATIFirmToPubFirm.FindParam('PubFirmID').AsInteger
  finally
    sp_ConvertATIFirmToPubFirm.UnPrepare
  end
If I call procedure for the first time, the parameter PubFirmID is correct. But all other calls of procedure always return only the first value while I shall not use before a call so:

Code: Select all

    sp_ConvertATIFirmToPubFirm.FindParam('PubFirmID').Clear;
    sp_ConvertATIFirmToPubFirm.Execute;
    NewPubFirmID:=sp_ConvertATIFirmToPubFirm.FindParam('PubFirmID').AsInteger
it is correct? Why the returned parameter is not updated without clearing?

Challenger
Devart Team
Posts: 925
Joined: Thu 17 Nov 2005 10:53

Post by Challenger » Wed 14 Jul 2010 15:50

I could not reproduce the described behavior. Please send us a small sample to reproduce the problem. We can also send you our sample and you will modify it to get the described behavior.

Post Reply