nanodbc
Binding of nanodbc::timestamp fraction field fails
From @bangusi on September 19, 2017 1:58
Environment
- nanodbc version: Latest
- DBMS name/version: SQL Server 14
- ODBC connection string: Driver SQL Server 13
- OS and Compiler: MSVC 2017 ( v 15.3
- CMake settings:
Actual behavior
It seems nanodbc::timestamp fractional field does not store nanoseconds like the odbc timestaruct Or may be it does but the value is truncated
Runtime exception: [Microsoft][ODBC Driver 13 for SQL Server]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.
Expected behavior
Minimal Working Example
CREATE TABLE [dbo].[z_tests2](
[time_t] [datetime2](7) NULL
)
void foo(nanodbc::connection& conn)
{
nanodbc::statement statement(conn);
std::wstring sql = L"insert into dbo.z_tests2(time_t) values(?)";
prepare(statement, sql);
nanodbc::timestamp dt;
dt.year = 2009;
dt.month = 6;
dt.day = 9;
dt.hour = 23;
dt.min = 59;
dt.sec = 59;
dt.fract = 9999999; // we should be able to specify up to 9 digits see tagTIMESTAMP_STRUCT defined here https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/c-data-types
nanodbc::transaction trx(conn);
statement.bind(0, &dt);
statement.execute();
trx.commit();
}
Copied from original issue: lexicalunit/nanodbc#290
From @mloskot on September 19, 2017 10:27
From https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql
Milliseconds have a scale of 3 (.123), microseconds have a scale of 6 (.123456), And nanoseconds have a scale of 9 (.123456789). The time, datetime2, and datetimeoffset data types have a maximum scale of 7 (.1234567).
That is:
-
DATETIME2allows scale of 7 (fractional second digits). -
DATETIME2ranges from 00:00:00 through 23:59:59.9999999
The TIMESTAMP_STRUCT documentation you referred at https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/c-data-types, says:
[b] The value of the fraction field is the number of billionths of a second and ranges from 0 through 999,999,999 (1 less than 1 billion). For example, the value of the fraction field for a half-second is 500,000,000, for a thousandth of a second (one millisecond) is 1,000,000, for a millionth of a second (one microsecond) is 1,000, and for a billionth of a second (one nanosecond) is 1.
That is a general truths about the range of nanosecond.
However, if you confront the ranges on both sides, ODBC C API as value source and SQL as value sink, you have to ensure value of the fractional second can fit the scale of 7.
nanodbc::timestamp d{2000, 1, 2, 23, 59, 59, 0};
d.fract = 999999900; // -> '2000-01-02 23:59:59.9999999'
d.fract = 123456700; // -> '2000-01-02 23:59:59.1234567'
d.fract = 500000000; // -> '2000-01-02 23:59:59.5000000' // half of second
See the answer to How can one bind datetime2(SQL_C_TYPE_TIMESTAMP) using SQLBindParameter?.
You may consider opening an Issue for the confusing wording used on https://github.com/MicrosoftDocs/sql-docs/blob/live/docs/odbc/reference/appendixes/c-data-types.md and suggest Microsoft to make the "ranges from 0 through 999,999,999 (1 less than 1 billion)" clearer regarding limits on SQL side.
I hope my own understanding is correct and the above makes sense. Otherwise, feel free to re-open and clarify things.
By the way, it would be good to have the DATETIME2 bindings with fractional second covered with tests.
From @bangusi on September 19, 2017 14:25
Did you compile and run the code by any chance? MingW on Windows 7, SQL server 2016, Driver v 13 If I bind tm.fract = 9999999 the library inserts 0090000.
From @mloskot on September 19, 2017 14:45
nanodbc version: Latest
What is latest? Latest release or latest master branch?
Did you compile and run the code by any chance?
Yes, of course. BTW, I'm preparing unit test.
MingW on Windows 7, SQL server 2016, Driver v 13
I never use MinGW, so I can't tell anything related to this toolset.
If I bind tm.fract = 9999999 the library inserts 0090000. ---------------------^^^^^^ number of 7 digits here has no correspondence with 7 digits in SQL
Can you even insert this?
tm.fract=9999999 nanosec fraction -> 0.009999999 sec in SQL
That is supposed to be Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding
Have you tried inserting tm.fract = 1, for example?
Hint:
tm.fract=1 nanosec fraction -> 0.000000001 sec in SQL
Should be same error as above.
Consider some more examples:
0.5 sec time -> 500000000 nanosec fraction -> .5000000 sec in SQL
0.9999999 sec time -> 999999900 nanosec fraction -> .9999999 sec in SQL
You will need to read the docs linked above carefully.
If you are still in doubt about nanodbc, try binding 'manually' yourself. That is what nanodbc provides these members for:
connection::native_env_handle
connection::native_dbc_handle
statement::native_statement_handle
result::native_statement_handle`
FWIW, nanodbc is a very thin wrapper on top of ODBC C API.
From @bangusi on September 21, 2017 0:25
@mloskot Its all good. The bug was in my code. nanodbc is awsome and I am beginning to use it for real now. Great job!
From @mloskot on September 27, 2017 6:52
Reopening to keep it as reminder to add related test cases