Using NUMERIC (ftFMTBcd) parameter in a prepared query

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Henrik
Posts: 2
Joined: Fri 13 Oct 2017 14:07

Using NUMERIC (ftFMTBcd) parameter in a prepared query

Post by Henrik » Fri 13 Oct 2017 14:34

We try to use a NUMERIC (ftFMTBcd) parameter in a prepared query.
Take a look at this simple example (Microsoft SQL Server):
- an SQL Table with two entries:

Code: Select all

CREATE TABLE TESTNUM(NUMFIELD NUMERIC(22,10));
INSERT INTO TESTNUM VALUES(0.0000000001);
INSERT INTO TESTNUM VALUES(0.0000000002);
We want to execute the following simple SQL query:

Code: Select all

SELECT * FROM TESTNUM
WHERE (NUMFIELD = :NF) OR (:NF = 1)
(select rows with the specified parameter value
  or all rows when the parameter equals the constant value.)
For every TMSConnection object we set

Code: Select all

   Options.NumericType := TDANumericType.ntFmtBCD;
to get real NUMERIC fields (this works).

Before opening the query (q: TMSQuery), we are defining the following parameter:
(which exists twice in Params object)

Code: Select all

   q.Options.DescribeParams := False;
   q.Params[0].DataType := ftFMTBcd;
   q.Params[0].Precision := 22;
   q.Params[0].NumericScale := 10;

   q.Params[1].DataType := ftFMTBcd;
   q.Params[1].Precision := 22;
   q.Params[1].NumericScale := 10;
After preparing the query with

Code: Select all

   q.Prepared;
SQL Server Profiler shows the following prepared SQL:

Code: Select all

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 money OUTPUT,@P2 money OUTPUT',N'SELECT * FROM TESTNUM
WHERE (NUMFIELD = @P1)
OR    (@P2 = 1)',1
select @p1
As you can see, the data type of either parameter is money.

After setting the parameter values, we open the query with the following example:

Code: Select all

   q.Params[0].AsFMTBCD := '0,0000000001';
   q.Params[1].AsFMTBCD := '0,0000000001';
   q.Open;
SQL Server Profiler shows the following SQL:

Code: Select all

exec sp_execute 1,$0.0000,$0.0000
The expected result set is one entry, but the result set is empty because the parameter value is cut off to money scale of 4 digits.

The question is:
How can we treat the data type of the parameter as ftFMTBcd (NUMERIC) with the defined precision and scale?

SDAC V. 7.3.16, Delphi 10.1 Berlin

Thx, Henrik

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Using NUMERIC (ftFMTBcd) parameter in a prepared query

Post by azyk » Wed 18 Oct 2017 12:33

Set TMSQuery.Options.DescribeParams to True, in order for SDAC to map the NUMERIC(22,10) type to ftFMTBcd for dataset parameters. For example:

Code: Select all

  q.Options.DescribeParams := True;
  q.Prepare;
  q.Params[0].AsFMTBCD := '0,0000000001';
  q.Params[1].AsFMTBCD := '0,0000000001';
  q.Open;
In order for SDAC to map the second parameter type as ftFMTBcd, you should explicitly specify a constant type in the WHERE clause. For example:

Code: Select all

SELECT * FROM TESTNUM
WHERE (NUMFIELD = :NF) OR (:NF = CAST(1 as NUMERIC(22,10)))

Henrik
Posts: 2
Joined: Fri 13 Oct 2017 14:07

Re: Using NUMERIC (ftFMTBcd) parameter in a prepared query

Post by Henrik » Thu 26 Oct 2017 15:01

Hi azyk,
thank you for your response. Your solution works as described.

Henrik

Post Reply