Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Incorrect conversion of time values when retrieved as string [ODBC192] #179

Closed
firebird-automations opened this issue Aug 21, 2015 · 5 comments

Comments

@firebird-automations
Copy link

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.

Commits: 81d2de8 b3e29a1

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Commented by: @alexpotapchenko

Fixed in CVS

@firebird-automations
Copy link
Author

Modified by: @alexpotapchenko

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.0.4 [ 10662 ]

@firebird-automations
Copy link
Author

Modified by: @alexpotapchenko

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants