Devart Forums

Posted: **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

Posted: **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)))

Posted: **Thu 26 Oct 2017 15:01**

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

Henrik

Devart Forums

Using NUMERIC (ftFMTBcd) parameter in a prepared query

Using NUMERIC (ftFMTBcd) parameter in a prepared query

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

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