Unassigned in OUTPUT parameter

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
gootchick
Posts: 1
Joined: Thu 22 Apr 2010 12:18

Unassigned in OUTPUT parameter

Post by gootchick » Thu 22 Apr 2010 12:38

I decide to convert my db engine from ADO to SDAC. I realized different behavior of returned OUTPUT parameters from stored procedures. In this case, returned value of AIUD variable was NULL in ADO engine, but Unassigned in SDAC. I can fix it directly in this place, but these places in my code are really frequent. Where is the problem?

here is the code in Delphi2010 (TlLowStoredProc is successor of TMSStoredProc, AUID is OleVariant):

Code: Select all

LStored := TlLowStoredProc.Create(nil);
  try
    try
      with LStored do
      begin
        StoredProcName := sSP_LoginUser;
        Connection := AConnection;
        PrepareSQL;
        ParamByName(sSP_ALogin).Value := ALogin;
        ParamByName(sSP_APass).Value := APass;
        ParamByName(sSP_AUID).Value := Null;
        ExecProc;
        AUID := ParamByName(sSP_AUID).Value;
        Result := ParamByName(AConnection.ReturnField).Value;
      end;
    except
    end;
  finally
    LStored.Free;
  end
and here is the stored procedure in MSSQL 2005/2008:

Code: Select all

CREATE PROCEDURE DSP_LoginUser
@ALogin nvarchar(50),
@APass nvarchar(50),
@AUID uniqueidentifier OUTPUT
AS
  SET NOCOUNT ON

  SET @AUID = NULL

  SELECT @AUID = User.UID 
  FROM dbo.User 
  WHERE User.LOGIN = @ALogin AND User.PASS= @APass AND User.DELETED = 0

RETURN (@@ERROR)

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 22 Apr 2010 13:37

SDAC sets value to Unassigned and the IsNull property to True for empty parameters. Therefore to solve the problem you can use the following code:

Code: Select all

  if ParamByName(sSP_AUID).IsNull then
    AUID := Null
  else
    AUID := ParamByName(sSP_AUID).Value;

Post Reply