Are FMTBCD parameters (fully) supported?

[Mark de Waal]

Hello,

I am experimenting with the DBExpress driver for MS SQL Server 5.01 and am having problems with numeric(m, n) parameters when m>14 and/or n>4

Problem 1
When n>4 it seems that inputparameter values are rounded to 4 decimals; this can even lead to an exception:

Project x raised exception class EMSError with message 'Error converting data type money to numeric.'

In this case the parameter is Numeric(6,5) and the value is 9.99999

When checking the SQL statement with SQL Profiler I see (something) like this:

declare @P2 varchar(67)
select @P2 = NULL
exec dbo.MyStoredProc $10.0000, @P2 output
select @P2

So the value is rounded to 10.0000 and now longer fits into numeric(6,5). (When I execute the above statement in SQL Server Management Studio, it gives the same error message)

Question 1:
Why is the money datatype/notation used for inputparameter, while the outputparameter is used as varchar (note the @P2 parameter is defined as numeric(6,5) in the stored procedure.
Can the inputparameter be forced to be also varchar and not be rounded to 4 decimals?

Note: when I use the standard DBExpress driver by Embarcadero (D2010), the inputparametervalues are set via varchar, so they are not rounded and have the correct numeric scale.

Problem 2:
When m>14 I get an exception

Project x raised exception class EInvalidOp with message 'Invalid floating point operation'

I guess this has to do with the rounding/manipulation of inputparameter values when the SQL statement is constructed

Question 2:
How can I prevent this exception?

[AndreyZ]

Hello,

1. We have reproduced the problem and investigation of the problem is in progress.
2. Please specify the exact situation when this exception occurs.

[Mark de Waal]

Hello AndreyZ,

The test case is simple. Create a stored procedure like this:

Code: Select all

CREATE PROCEDURE dbo.TestNumeric
( @InNumeric6_5 numeric(6,5) = NULL
, @InNumeric15_0 numeric(15,0) = NULL
, @OutNumeric6_5 numeric(6,5) = NULL output
, @OutNumeric15_0 numeric(15,0)= NULL output
)
AS
BEGIN
   SELECT @OutNumeric6_5 = @InNumeric6_5
   ,      @OutNumeric15_0 = @InNumeric15_0
   
   RETURN 0
END

In Delphi:
- Create a VCL Forms application
- Add a TSQLConnection and set it up (I added ParamPrefix=True)
- Add a TSQLStoredProc, connect it to the TSLQConnection and set SchemaName to 'dbo' and StoredProcName to 'TestNumeric'
- Add two TButtons and implement the OnClick like this:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLStoredProc1.ParamByName('@InNumeric6_5').AsFMTBCD := StrToBCD('9,99999'); // 5 decimals round to 10.0000 --> Exception!; 4 decimals are ok
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.ParamByName('@InNumeric6_5').AsString);
end;

and

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
begin
  SQLStoredProc1.ParamByName('@InNumeric15_0').AsFMTBCD := StrToBCD('999999999999999'); // 15 digits give exception; 14 or less are ok
  SQLStoredProc1.ExecProc;
  ShowMessage(SQLStoredProc1.ParamByName('@InNumeric15_0').AsString);
end;

[Mark de Waal]

Extra information:

When I use SQL Server Profiler to examine te statements that are executed, I see this (when I click button1):

Code: Select all

declare @p3 varchar(67)
set @p3=NULL
declare @p4 varchar(67)
set @p4=NULL
exec dbo.TestNumeric 10.0000,NULL,@p3 output,@p4 output
select @p3, @p4

(Note: Do not be confused about parameter @p4; it is not used in this test)

I re-installed version 4.80 of the driver and now I see this SQL statement in SQL Server Profiler:

Code: Select all

declare @p3 money
set @p3=NULL
declare @p4 money
set @p4=NULL
exec dbo.TestNumeric 10.0000,NULL,@p3 output,@p4 output
select @p3, @p4

Note: The output parameter has been changed from money to varchar(67), I think when solving this problem: http://www.devart.com/forums/viewtopic.php?t=21750

When I use the standard Embarcadero driver for Delphi2010, I see this:

Code: Select all

declare @p3 varchar(49)
set @p3=NULL
declare @p4 varchar(33)
set @p4=NULL
exec [dbo].[TestNumeric] '9.99999',NULL,@p3 output,@p4 output
select @p3, @p4

The outputparameter @p3 is varchar, but the (first) inputparameter is also varchar. So this driver has no problems with more than 4 decimals.

With this driver, the second problem (>14 digits) can also be tested:

Code: Select all

declare @p3 varchar(49)
set @p3=NULL
declare @p4 varchar(33)
set @p4=NULL
exec [dbo].[TestNumeric] NULL,'999999999999999',@p3 output,@p4 output
select @p3, @p4

The (second) inputparameter is again a string (with 15 digits) and the statement excutes with no problems.

Question:
Is the SQL statement that is shown in SQL Server Profiler, constructed bij the DevArt and Embarcadero driver? Or is it created by the native client library (sqlncli10.dll)?
Can you change the inputparameter to be a string/varchar, so no rounding will occur and more than 14 digits are supported?

[AndreyZ]

Thank you for the information. We have reproduced and fixed both problems. This fix will be included in the next dbExpress driver for SQL Server build.

[Mark de Waal]

Thank you!

[alexer]

Hi, have the same issue.
When will we get new version?
Thanks.

[AndreyZ]

We plan to release a new dbExpress driver for SQL Server version this or next week.