Devart Forums

Posted: **Tue 21 Dec 2010 15:16**

Hi Folks,

I have to evaluate your components for a company I'm currently working for, and while doing so ... i am encountering a few probles ... which I hope someone could help me with.

All problems can be simulated using a simple table (yes I'm evaluating dbExpress and a few other Component Sets too) :

Code: Select all

CREATE TABLE [dbo].[tblTestDBExpress](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Naam] [nvarchar](50) NOT NULL,
	[Omschrijving] [nvarchar](50) NULL,
	[Aantal] [int] NULL,
	[CreatedBy] [nvarchar](50) NULL,
	[CreatedOn] [datetime] NULL,
	[UpdatedBy] [nvarchar](50) NULL,
	[UpdatedOn] [datetime] NULL,
 CONSTRAINT [PK_tblTestDBExpress] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Here is a list of issues I'm currently running in :

Stored Procedures with Optional / Default Parameters

I have the following Stored Procedure :

Code: Select all

CREATE PROCEDURE [dbo].[stpTestDBExpress2] 
	-- Add the parameters for the stored procedure here
	@Name           NVARCHAR( 50 ), 
	@Description    NVARCHAR( 50 ),
	@ID				INTEGER OUTPUT,
	@Quantity		INTEGER = 88
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO tblTestDBExpress
			   ([Naam]
			   ,[Omschrijving]
			   ,[Aantal] )
		 VALUES
			   ( @Name
			   , @Description
			   , @Quantity)

	SET @ID = IDENT_CURRENT( 'tblTestDBExpress' )
END

Clearly ... the last parameter is optional and the Stored Procedure will use the default value of 88 if the parameter isn't supplied.

Now I tried using a TMSStoredProcedure component and the following code :

Code: Select all

  MSStp1.StoredProcName := 'stpTestDBExpress2';
  MSStp1.PrepareSQL;
  MSStp1.ParamByName( 'Name' ).Value := 'Design Name';
  MSStp1.ParamByName( 'Description' ).Value := 'Design Description';
  MSStp1.ExecProc;
  aNewID := MSStp1.ParamByName( 'ID' ).Value;
  AddLogMessage( Format( 'dsgn Added test record with ID %d', [ aNewId ] ) );

If you do that, you will see that the system still passes NULL as the value for the Optional parameter, which is incorrect ... it should be Unassinged (Unassigned is NOT the same as NULL, unassigned means nothing was supplied, NULL means the NULL value was supplied).

Using the default ADO / dbGo components the generated SQL looks like :

declare @p3 int
set @p3=124
exec stpTestDBExpress2 N'Design Name',N'Design Description',@p3 output,default
select @p3

Using your components, the generated SQL looks like :

Code: Select all

declare @p3 int
set @p3=126
exec stpTestDBExpress2 N'Design Name',N'Design Description',@p3 output,NULL
select @p3

Stored Procedures with Return Value and a ResultSet

The Stored Procedure :

Code: Select all

CREATE PROCEDURE [dbo].[stpTestDBExpress4] 
	-- Add the parameters for the stored procedure here
	@Name           NVARCHAR( 50 ), 
	@Description    NVARCHAR( 50 ),
	@ID				INTEGER OUTPUT,
	@Quantity		INTEGER = 88
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for procedure here
	INSERT INTO tblTestDBExpress
			   ([Naam]
			   ,[Omschrijving]
			   ,[Aantal] )
		 VALUES
			   ( @Name
			   , @Description
			   , @Quantity)

	SET @ID = IDENT_CURRENT( 'tblTestDBExpress' )
	Return 333
	--EXIT 1
	SELECT * FROM tblTestDBExpress WHERE Id > 5
END

And with the Delphi Code :

Code: Select all

  MSStp1.StoredProcName := 'stpTestDBExpress4';
  MSStp1.ParamByName( 'Name' ).Value := 'Design Name';
  MSStp1.ParamByName( 'Description' ).Value := 'Design Description';
  MSStp1.ExecProc;
  aNewID := MSStp1.ParamByName( 'ID' ).Value;
  aResult := MSStp1.ParamByName('RETURN_VALUE').Value;
  AddLogMessage( Format( 'Added test record with ID %d', [ aNewId ] ) );
  AddLogMessage( Format( 'RETURN_VALUE was %d', [ aResult ] ) );
  AddLogMessage( Format( 'Number of Records Returned %d', [ MSStp1.RecordCount ] ) );

Still ... the Default value of the parameter isn't used, but I also get 0 records back, although I already have quite a few records in my table which meet the criteria.

I also tried using .Open instead of ExecProc, but then I receive an Exception saying that the Query should return exactly one resultset and I should use Execute instead.

So, I went ahead and tried the Execute method too, but that seemed to fail as well.

I seem to be unable to get both the Return_Value and a resultset from a stored procedure.

Maybe I am doing something wrong, but I couldn't get those 2 things working the way I would expect them to work.

Any help or info concerning these problems are welcome of course. Maybe it's an option or a setting I need to tweak ...

Regards,

Stefaan

Posted: **Thu 23 Dec 2010 12:24**

Eeu ... a little update ...

Stored Procedures with Return Value and a ResultSet

This does work ... I just made the mistake of putting the SELECT Statement after the RETURN Statement.

Stored Procedures with Optional / Default Parameters

I got this working be 'unbinding' all input parameters which didn't get a value using the following code which gets executed in the BeforeExecute and the BeforeOpen of the stored procedure :

Code: Select all

procedure TdtmTestSDAC.UnbindUnassignedParameters(aDataSet: TCustomMSDataSet);
var
  iParamIndex: Integer;
  oParam     : TMSParam;
begin
  if ( Assigned( aDataSet ) ) then
  begin
    for iParamIndex := 0 to Pred( aDataSet.ParamCount ) do
    begin
      oParam := aDataSet.Params[ iParamIndex ];

      if ( oParam.ParamType = ptInput ) and
         ( oParam.Value = Unassigned ) then
      begin
        oParam.Bound := False;
      end;
    end;
  end;
end;

This last one seems to be a bug of some kind to me.

Regards,

Stefaan[/u]

Posted: **Mon 27 Dec 2010 10:22**

Hello,

The point is that SQL server doesn't allow receiving default values for parameters of stored procedures. That's why SDAC transfers to server NULL value for parameters that weren't assigned. To avoid this problem you should set the TParam.Bound property to False (as you wrote earlier).
ADO always uses DEFAULT value, even if parameter doesn't have default value. That's why if you create a stored procedure without default value and don't assign the value of a parameter, execution attempt with ADO will cause an exception "Parameter value was not supplied".

Devart Forums

Problems while evaluating SDAC

Problems while evaluating SDAC