Devart Forums

Hello Azyk,

Thank you for your response.

Did you make some tests using "datetime" field and querying you table against that field using a parameter ?
Currently, DbExpress or any other client layer (Native Client Client ?) is mapping the Delphi Query parameter to Datetime2(7)....
With compatibility Level to 130 this is wrong... "datetime" and "datetime2" are differents...

Let's assume you created your table like this....

CREATE TABLE [dbo].[MY_TEST_TABLE](
[MY_ID] [int] NOT NULL,
[MY_LABEL] [varchar](50) NULL,
[MY_DATETIME] [datetime] NULL,
CONSTRAINT [PK_MY_TEST_TABLE] PRIMARY KEY CLUSTERED
(
[MY_ID] ASC
)
)

The Delphi code bellow will ends with no records on the "Select" query... Unless an explicit CAST is added !!!!

procedure TForm2.btnTestClick(Sender: TObject);
var
ADateTimeValue : TDateTime;
ARecCount : Integer;
begin
MyQuery.SQL.Text := 'DELETE FROM MY_TEST_TABLE';
MyQuery.ExecSQL;

ADateTimeValue := Now();
MyQuery.SQL.Text := 'INSERT INTO MY_TEST_TABLE(MY_ID, MY_LABEL, MY_DATETIME)'
+ ' VALUES(:MY_ID, :MY_LABEL, :MY_DATETIME)';

MyQuery.Params.ParamByName('MY_ID').AsInteger := 1;
MyQuery.Params.ParamByName('MY_LABEL').AsString := 'DATA LABEL';
MyQuery.Params.ParamByName('MY_DATETIME').AsDateTime := ADateTimeValue;

MyQuery.ExecSQL;

MyQuery.SQL.Text := 'SELECT * FROM MY_TEST_TABLE WHERE MY_DATETIME = :MY_DATETIME';
MyQuery.Params.ParamByName('MY_DATETIME').AsDateTime := ADateTimeValue;
MyQuery.Open;

end;

The reason is the parameter mapping to "datetime2(7)"

You can reproduce directly the problem outside Delphi and DbExpress... For example, in Management Studio.

- Create the table
- Insert a record like this

exec sp_executesql N'INSERT INTO MY_TEST_TABLE
(
MY_ID,
MY_LABEL,
MY_DATETIME
)
VALUES
(
@P1,
@P2,
@P3
)',N'@P1 int,@P2 varchar(50),@P3 datetime2(7)',1,'DATA LABEL','2017-07-16 15:22:54.1830000'


- Process the Select query, using datetime2(7) parameter

exec sp_executesql N'SELECT
*
FROM
MY_TEST_TABLE
WHERE
MY_DATETIME = @P1',N'@P1 datetime2(7)','2017-07-16 15:22:54.1830000'

=> No result !!

Hello,

We are facing problmes in conditions bellow

- SQL Server 2016
- Data base with Compatibility level 130 (It's necessary)
- DB Provider to SQL Native Client (It's necessary)
- "Datetime" Fields
- "Datetime" parameters

All "datetime" parameters seems to be mapped to "detetime2(7)"

The problem is that with compatibilty level 130, conversions from "datetime2(7)" to "datetime" dosen't match... for precision reasons...
Shall we move all ower table fields to "datetime2(3)" (which seems to be correct) or is there a way to customize the default mappings ?

Ali

Hello,

As stated on Oracle Database download page http://www.oracle.com/technetwork/datab ... eId=ocomen A Full Release, 11.2.0.3, is available through OTN. This release Fix this problem.

Hello,

As stated on Oracle Database download page http://www.oracle.com/technetwork/datab ... eId=ocomen A Full Release, 11.2.0.3, is available through OTN. This release Fix this problem.

Hello,

I experience some troubles with IntegerPrecision set to 10 under Oracle 11g R2 64 Bits 11.2.0.1.0 Windows from a 32 Bits client application.

Using Devart DBExpress drivers (version 4.x or 5.x) (under the specified version of Oracle), we have fields created this way

ALTER TABLE MY_TABLE ADD MY_INT_FIELD NUMBER(10) DEFAULT 0 NOT NULL;

The fields are mapped to TFloatField instead of TIntegerField.

When the default values specified are removed, thus by creating the fields like this

ALTER TABLE MY_TABLE ADD MY_INT_FIELD NUMBER(10) NOT NULL;

The fields are correctly mapped to TIntegerField.

We first met these troubles under Oracle 11g R1 11.1.0.6.0, Windows.

How to solve this problem?
We can recompile the driver if necessary...

CheckCompactDBFile uses a file stream to check DB file version. The stream is created with the line "fs := TFileStream.Create(FDatabase, fmOpenRead);". This leads to some problems in multi connexions environment. This code could be improved with a "fs := TFileStream.Create(FDatabase, fmOpenRead or fmShareDenyNone);" line.