Incorrect conversion of time values when retrieved as string [ODBC192]
firebird-automations opened this issue · 5 comments
Submitted by: Enno Kehrer (enno)
Attachments:
FirebirdODBC_time.cpp
My test table looks like this:
CREATE TABLE test_time (id int, val time);
INSERT INTO test_time VALUES (0, NULL);
INSERT INTO test_time VALUES (1, '00:00:00');
INSERT INTO test_time VALUES (2, '00:00:00.1');
INSERT INTO test_time VALUES (3, '00:00:00.01');
INSERT INTO test_time VALUES (4, '00:00:00.001');
INSERT INTO test_time VALUES (5, '00:00:00.0001');
I connect to Firebird using ODBC and run the query:
SELECT id, val FROM test_time ORDER BY id;
Reading the time value using the TIME_STRUCT I get no fractional seconds,
therefore I tried reading it as strings using the following code:
std::string readString(HSTMT hstmt, SQLUSMALLINT col) {
SQLLEN ind;
std::string val(1024, 0);
RETCODE rc = SQLGetData(hstmt, col, SQL_C_CHAR, (SQLPOINTER)(val.data()), val.size(), &ind);
if (!SQL_SUCCEEDED(rc)) { reportErrors(SQL_HANDLE_STMT, hstmt); }
return (ind != SQL_NULL_DATA) ? std::string(val.c_str()) : nullValue;
}
the result looks like this:
'0','NULL'
'1','00:00:00'
'2','00:00:00.1000'
'3','00:00:00.100'
'4','00:00:00.10'
'5','00:00:00.1'
=> notice that the fracional seconds are wrong.
I looked in conversion code an found in
OdbcConvert::convTimeToString and
OdbcConvert::convTimeToStringW
that the format string
"%02d:%02d:%02d.%lu"
is wrong and should look like
"%02d:%02d:%02d.%04lu"
and the nnano value must be divided by 10000 to get 4 digits precision.
=> workaround: retrieve the time value as timestamp (SQL_C_TIMESTAMP using the TIMESTAMP_STRUCT) and extract the time part.
Modified by: Enno Kehrer (enno)
Attachment: FirebirdODBC_time.cpp [ 12798 ]
description: My test table looks like this:
CREATE TABLE test_time (id int, val time);
INSERT INTO test_time VALUES (0, NULL);
INSERT INTO test_time VALUES (1, '00:00:00');
INSERT INTO test_time VALUES (2, '00:00:00.1');
INSERT INTO test_time VALUES (3, '00:00:00.01');
INSERT INTO test_time VALUES (4, '00:00:00.001');
INSERT INTO test_time VALUES (5, '00:00:00.0001');
I connect to Firebird using ODBC and run the query:
SELECT id, val FROM test_time ORDER BY id;
Reading the time value using the TIME_STRUCT I get no fractional seconds,
therefore I tried reading it as strings using the following code:
std::string readString(HSTMT hstmt, SQLUSMALLINT col) {
SQLLEN ind;
std::string val(1024, 0);
RETCODE rc = SQLGetData(hstmt, col, SQL_C_CHAR, (SQLPOINTER)(val.data()), val.size(), &ind);
if (!SQL_SUCCEEDED(rc)) { reportErrors(SQL_HANDLE_STMT, hstmt); }
return (ind != SQL_NULL_DATA) ? std::string(val.c_str()) : nullValue;
}
the result looks like this:
'0','NULL'
'1','00:00:00'
'2','00:00:00.1000'
'3','00:00:00.100'
'4','00:00:00.10'
'5','00:00:00.1'
=> notice that the fracional seconds are wrong.
I looked in conversion code an found in
OdbcConvert::convTimeToString and
OdbcConvert::convTimeToStringW
that the format string
"%02d:%02d:%02d.%lu"
is wrong and should look like
"%02d:%02d:%02d.%04lu"
=> workaround: retrieve the time value as timestamp (SQL_C_TIMESTAMP using the TIMESTAMP_STRUCT) and extract the time part.
=>
My test table looks like this:
CREATE TABLE test_time (id int, val time);
INSERT INTO test_time VALUES (0, NULL);
INSERT INTO test_time VALUES (1, '00:00:00');
INSERT INTO test_time VALUES (2, '00:00:00.1');
INSERT INTO test_time VALUES (3, '00:00:00.01');
INSERT INTO test_time VALUES (4, '00:00:00.001');
INSERT INTO test_time VALUES (5, '00:00:00.0001');
I connect to Firebird using ODBC and run the query:
SELECT id, val FROM test_time ORDER BY id;
Reading the time value using the TIME_STRUCT I get no fractional seconds,
therefore I tried reading it as strings using the following code:
std::string readString(HSTMT hstmt, SQLUSMALLINT col) {
SQLLEN ind;
std::string val(1024, 0);
RETCODE rc = SQLGetData(hstmt, col, SQL_C_CHAR, (SQLPOINTER)(val.data()), val.size(), &ind);
if (!SQL_SUCCEEDED(rc)) { reportErrors(SQL_HANDLE_STMT, hstmt); }
return (ind != SQL_NULL_DATA) ? std::string(val.c_str()) : nullValue;
}
the result looks like this:
'0','NULL'
'1','00:00:00'
'2','00:00:00.1000'
'3','00:00:00.100'
'4','00:00:00.10'
'5','00:00:00.1'
=> notice that the fracional seconds are wrong.
I looked in conversion code an found in
OdbcConvert::convTimeToString and
OdbcConvert::convTimeToStringW
that the format string
"%02d:%02d:%02d.%lu"
is wrong and should look like
"%02d:%02d:%02d.%04lu"
and the nnano value must be divided by 100000 to get 4 digits precision.
=> workaround: retrieve the time value as timestamp (SQL_C_TIMESTAMP using the TIMESTAMP_STRUCT) and extract the time part.
Modified by: Enno Kehrer (enno)
description: My test table looks like this:
CREATE TABLE test_time (id int, val time);
INSERT INTO test_time VALUES (0, NULL);
INSERT INTO test_time VALUES (1, '00:00:00');
INSERT INTO test_time VALUES (2, '00:00:00.1');
INSERT INTO test_time VALUES (3, '00:00:00.01');
INSERT INTO test_time VALUES (4, '00:00:00.001');
INSERT INTO test_time VALUES (5, '00:00:00.0001');
I connect to Firebird using ODBC and run the query:
SELECT id, val FROM test_time ORDER BY id;
Reading the time value using the TIME_STRUCT I get no fractional seconds,
therefore I tried reading it as strings using the following code:
std::string readString(HSTMT hstmt, SQLUSMALLINT col) {
SQLLEN ind;
std::string val(1024, 0);
RETCODE rc = SQLGetData(hstmt, col, SQL_C_CHAR, (SQLPOINTER)(val.data()), val.size(), &ind);
if (!SQL_SUCCEEDED(rc)) { reportErrors(SQL_HANDLE_STMT, hstmt); }
return (ind != SQL_NULL_DATA) ? std::string(val.c_str()) : nullValue;
}
the result looks like this:
'0','NULL'
'1','00:00:00'
'2','00:00:00.1000'
'3','00:00:00.100'
'4','00:00:00.10'
'5','00:00:00.1'
=> notice that the fracional seconds are wrong.
I looked in conversion code an found in
OdbcConvert::convTimeToString and
OdbcConvert::convTimeToStringW
that the format string
"%02d:%02d:%02d.%lu"
is wrong and should look like
"%02d:%02d:%02d.%04lu"
and the nnano value must be divided by 100000 to get 4 digits precision.
=> workaround: retrieve the time value as timestamp (SQL_C_TIMESTAMP using the TIMESTAMP_STRUCT) and extract the time part.
=>
My test table looks like this:
CREATE TABLE test_time (id int, val time);
INSERT INTO test_time VALUES (0, NULL);
INSERT INTO test_time VALUES (1, '00:00:00');
INSERT INTO test_time VALUES (2, '00:00:00.1');
INSERT INTO test_time VALUES (3, '00:00:00.01');
INSERT INTO test_time VALUES (4, '00:00:00.001');
INSERT INTO test_time VALUES (5, '00:00:00.0001');
I connect to Firebird using ODBC and run the query:
SELECT id, val FROM test_time ORDER BY id;
Reading the time value using the TIME_STRUCT I get no fractional seconds,
therefore I tried reading it as strings using the following code:
std::string readString(HSTMT hstmt, SQLUSMALLINT col) {
SQLLEN ind;
std::string val(1024, 0);
RETCODE rc = SQLGetData(hstmt, col, SQL_C_CHAR, (SQLPOINTER)(val.data()), val.size(), &ind);
if (!SQL_SUCCEEDED(rc)) { reportErrors(SQL_HANDLE_STMT, hstmt); }
return (ind != SQL_NULL_DATA) ? std::string(val.c_str()) : nullValue;
}
the result looks like this:
'0','NULL'
'1','00:00:00'
'2','00:00:00.1000'
'3','00:00:00.100'
'4','00:00:00.10'
'5','00:00:00.1'
=> notice that the fracional seconds are wrong.
I looked in conversion code an found in
OdbcConvert::convTimeToString and
OdbcConvert::convTimeToStringW
that the format string
"%02d:%02d:%02d.%lu"
is wrong and should look like
"%02d:%02d:%02d.%04lu"
and the nnano value must be divided by 10000 to get 4 digits precision.
=> workaround: retrieve the time value as timestamp (SQL_C_TIMESTAMP using the TIMESTAMP_STRUCT) and extract the time part.
Commented by: @alexpotapchenko
Fixed in CVS
Modified by: @alexpotapchenko
status: Open [ 1 ] => Resolved [ 5 ]
resolution: Fixed [ 1 ]
Fix Version: 2.0.4 [ 10662 ]
Modified by: @alexpotapchenko
status: Resolved [ 5 ] => Closed [ 6 ]