TUniLoader and DateTime with milliseconds [SQL Server]

[martin.trummer]

for MySQL the way to insert microseconds was to specify ftString for the Column and then add a string value like this: '2014-05-20 15:49:14.000000'

the same trick does not work with SQL Server.

according to the SQL server doc's the column type datetime(2) can support a fractional second precision up to 7 digits. And according to some other doc-page, SQL server supports ISO 8601 compliant date-time literals: http://msdn.microsoft.com/en-us/library/bb677335.aspx
I've tested this in the SQL Server Management Studio the following query works-column data type is datetime2(6):

Code: Select all

insert into [Test]
values
('2014-08-08T14:31:11.12345')

but in my test-code for TUniLoader it does not work:

• FAIL: Sender.PutColumnData(0, I, '01-01-2010T01:00:00');
• FAIL: Sender.PutColumnData(0, I, '01-01-2010 01:00:00.123');
• this is okay: Sender.PutColumnData(0, I, '01-01-2010 01:00:00');

so how can we store a date/time value with microsecond precision to SQL Server?
I'm using version 5.3.9 and SQL Server 2014.

[azyk]

TDALoader works with SQL Server via the OLE DB interface, that requires each value to be transmitted to server in the data type, that corresponds to the field of the table.

To solve the issue, try to transmit the already converted value (instead of the T-SQL-like value) to the TDALoader.PutColumnData method, for example:

Code: Select all

procedure TMainForm.UniLoaderPutData(Sender: TDALoader);
var
  MyDateTime : TDateTime;
begin
  MyDateTime := EncodeDateTime(2010, 1, 1, 1, 0, 0, 123);
  Sender.PutColumnData(0, 1, MyDateTime);
end;

[martin.trummer]

I cannot use this, because we also need sub-millisecond accuracy and the delphi TDateTime type only supports milliseconds

[azyk]

To solve the issue, in TUniLoader set the value for the TDAColumn.FieldType property in ftWideString for the field with the datetime2 datatype. For example:

Code: Select all

UniLoader.Columns[0].FieldType := ftWideString;

In this case, to transmit the date string value in the field with the datetime2 datatype, OLEDB is required to use the following format 'yyyy-mm-dd hh:mm:ss[.fffffff]' in the TDALoader.PutColumnData method. For example:

Code: Select all

procedure TMainForm.UniLoaderPutData(Sender: TDALoader);
begin
  Sender.PutColumnData(0, 1, '2010-01-01 01:00:00.123');
end;

For more information on string formats for converting into the datetime2 datatype, refer to the MSDN specifications: http://msdn.microsoft.com/en-us/library/bb677303.aspx

[martin.trummer]

thanks a lot.
Actually I had year-month-day in the wrong order. Now it works (also with field type ftString).

[azyk]

Feel free to contact us if you have any further questions.

[martin.trummer]

some update on this:
one a customer PC the date-time with millis for SQL Server failed:

Code: Select all

EMSError: Conversion failed when converting date and/or time from character string.
Exception: Conversion failed when converting date and/or time from character string.

the problem was, that the native OLEDBProvider for SQL server was not installed.
The default OLEDBProvider setting for a SQL Server connection is prAuto (see UniDac Help).
Thus prSQL (which has limited functionality and does not support microseconds) was used on the customer PC which caused the error.
On our development and test-machines we had installed the full SQL Server client (including the native OLEDBProvider) - thus it worked for our tests.

Here's a link how to install only the SQL Server native client on a customers PC.

[azyk]

We have tried to reproduce the problem for OLE DB Provider according to your recommendations, but it wasn't reproduced. Please try to compose a small sample to demonstrate the problem and send it to andreyz*devart*com, including the tables creating script.

[azyk]

We have received your test sample and fixed this problem. This fix will be included in the next UniDAC build.

[martin.trummer]

verified that the bug is fixed in V6.1.6