freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
[freetds] SQLBindParameter using TIMESTAMP_STRUCT: Fractional part is wrong
- From: Sebastien FLAESCH <sf AT 4js.com>
- To: freetds AT lists.ibiblio.org
- Subject: [freetds] SQLBindParameter using TIMESTAMP_STRUCT: Fractional part is wrong
- Date: Mon, 07 Jan 2008 18:28:51 +0100
I believe that the fractional part is wrong inserted when using the
TIMESTAMP_STRUCT type:
rcode = SQLBindParameter(m_hstmt1, 1, SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 23, 0,
&v_ts, 0, &v_ts_ind);
In ODBC, the "fraction" field of a TIMESTAMP_STRUCT represents a number of
nanoseconds.
-> The value for a half second would be 500,000,000.
In Transact-SQL, datetime columns have fraction part expressed in
milliseconds, with an
accuracy of 3,33 milliseconds.
So when inserting a datetime value with the string 'yyyy-mm-dd hh:mi:se.003',
it means
3 milliseconds, which should be:
3 * 1000 * 1000 = 3,000,000 nanoseconds
... in a TIMESTAMP_STRUCT. Right?
But when using this value in a TIMESTAMP_STRUCT binding, I get .000 in the
database.
Looks like the value is rounded before it is sent to the server...
Note that the value of 18,000,000 nanoseconds (18 milliseconds = .018) is
rounded to
.017 in the database (as expected)...
See attached example to reproduce...
Resources:
http://msdn2.microsoft.com/en-us/library/ms714556.aspx
http://support.microsoft.com/kb/263872
Thanks
Seb
/* Testing TIMESTAMP parameter binding - using fractions */
static const char * g_dbname = "msvtest1_nc";
static const char * g_usernm = "msvuser";
static const char * g_passwd = "fourjs";
#ifdef _WIN32
#include <WINDOWS.H>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
static SQLHENV m_henv;
static SQLHDBC m_hdbc;
#define CHECK_RCODE(t,h,m) \
if ( rcode != SQL_NO_DATA \
&& rcode != SQL_SUCCESS \
&& rcode != SQL_SUCCESS_WITH_INFO \
&& rcode != SQL_NEED_DATA ) { \
fprintf(stderr,"Error %d at: %s\n",rcode,m); \
getErrorInfo(t,h); \
exit(1); \
}
void getErrorInfo(SQLSMALLINT sqlhdltype, SQLHANDLE sqlhandle)
{
SQLRETURN rcode = 0;
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER naterror = 0;
SQLCHAR msgtext[SQL_MAX_MESSAGE_LENGTH + 1];
SQLSMALLINT msgtextl = 0;
int ifxerror = 0;
rcode = SQLGetDiagRec((SQLSMALLINT) sqlhdltype,
(SQLHANDLE) sqlhandle,
(SQLSMALLINT) 1,
(SQLCHAR *) sqlstate,
(SQLINTEGER *) & naterror,
(SQLCHAR *) msgtext,
(SQLSMALLINT) sizeof(msgtext),
(SQLSMALLINT *) & msgtextl);
fprintf(stderr, "Diagnostic info:\n");
fprintf(stderr, " SQL State: %s\n", (char *) sqlstate);
fprintf(stderr, " SQL code : %d\n", (int) naterror);
fprintf(stderr, " Message : %s\n", (char *) msgtext);
}
void exec_direct(int check, const char * stmt)
{
SQLRETURN rcode;
SQLHSTMT stmth = 0;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hdbc, (SQLHANDLE *) & stmth);
CHECK_RCODE(SQL_HANDLE_STMT,stmth,"SQLAllocHandle");
rcode = SQLExecDirect(stmth, (SQLCHAR *) stmt, SQL_NTS);
if (check) {
CHECK_RCODE(SQL_HANDLE_STMT,stmth,"SQLExecDirect");
}
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) stmth);
CHECK_RCODE(SQL_HANDLE_STMT,stmth,"SQLFreeHandle");
}
main(int argc,char **argv)
{
SQLRETURN rcode;
SQLHSTMT m_hstmt1;
SQLHSTMT m_hstmt2;
const char * dbname;
const char * usernm;
const char * passwd;
TIMESTAMP_STRUCT v_ts; SQLINTEGER v_ts_ind;
if (argc == 4) {
dbname = argv[1];
usernm = argv[2];
passwd = argv[3];
} else {
dbname = g_dbname;
usernm = g_usernm;
passwd = g_passwd;
}
rcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
CHECK_RCODE(SQL_HANDLE_ENV,NULL,"SQLAllocHandle EnvH");
rcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"ODBC V3");
m_hdbc = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_henv, (SQLHANDLE *) &m_hdbc);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLAllocHandle DbcH");
rcode = SQLConnect((SQLHANDLE) m_hdbc,
(SQLCHAR *) dbname,
(SQLINTEGER) SQL_NTS,
(SQLCHAR *) usernm,
(SQLINTEGER) SQL_NTS,
(SQLCHAR *) passwd,
(SQLINTEGER) SQL_NTS);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLConnect");
exec_direct(0, "DROP TABLE t1");
exec_direct(1, "CREATE TABLE t1 ( k INT, ts DATETIME )");
m_hstmt1 = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt1);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle 1");
rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLSetStmtAttr SQL_ATTR_CURSOR_SCROLLABLE");
rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SENSITIVITY,
(SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLSetStmtAttr SQL_ATTR_CURSOR_SENSITIVITY");
rcode = SQLPrepare(m_hstmt1, "INSERT INTO t1 VALUES (1, ?)", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare 1");
rcode = SQLBindParameter(m_hstmt1, 1, SQL_PARAM_INPUT,
SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 23, 0,
&v_ts, 0, &v_ts_ind);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLBindParameter 1");
v_ts.year = 2008;
v_ts.month = 1;
v_ts.day = 3;
v_ts.hour = 11;
v_ts.minute = 22;
v_ts.second = 33;
v_ts.fraction = 18 * 1000 * 1000; /* 18 milliseconds */
v_ts_ind = 0;
rcode = SQLExecute(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute 1");
v_ts.year = 2008;
v_ts.month = 1;
v_ts.day = 3;
v_ts.hour = 11;
v_ts.minute = 22;
v_ts.second = 33;
v_ts.fraction = 3 * 1000 * 1000; /* 3 milliseconds */
v_ts_ind = 0;
rcode = SQLExecute(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute 2");
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLFreeHandle StmtH 1");
exec_direct(1, "INSERT INTO t1 VALUES ( 2, '2007-12-24 11:22:33.003' )");
exec_direct(1, "INSERT INTO t1 VALUES ( 2, '2007-12-24 11:22:33.018' )");
/* Get values back */
m_hstmt2 = NULL;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt2);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle 2");
rcode = SQLExecDirect(m_hstmt2, "SELECT ts FROM t1 ORDER BY k", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 2");
rcode = SQLBindCol(m_hstmt2, 1, SQL_C_TYPE_TIMESTAMP, &v_ts, 0, &v_ts_ind);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLBindCol 3.1");
v_ts.year = 0;
v_ts.month = 0;
v_ts.day = 0;
v_ts.hour = 0;
v_ts.minute = 0;
v_ts.second = 0;
v_ts.fraction = 0;
v_ts_ind = 0;
rcode = SQLFetchScroll(m_hstmt2, SQL_FETCH_NEXT, 0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLFetchScroll 1");
fprintf(stdout, "TS Value 1: (ind=%d) %04d-%02d-%02d %02d:%02d:%02d.%03d\n",
v_ts_ind, v_ts.year, v_ts.month, v_ts.day, v_ts.hour, v_ts.minute, v_ts.second, v_ts.fraction / 1000000);
rcode = SQLFetchScroll(m_hstmt2, SQL_FETCH_NEXT, 0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLFetchScroll 2");
fprintf(stdout, "TS Value 2: (ind=%d) %04d-%02d-%02d %02d:%02d:%02d.%03d\n",
v_ts_ind, v_ts.year, v_ts.month, v_ts.day, v_ts.hour, v_ts.minute, v_ts.second, v_ts.fraction / 1000000);
rcode = SQLFetchScroll(m_hstmt2, SQL_FETCH_NEXT, 0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLFetchScroll 3");
fprintf(stdout, "TS Value 3: (ind=%d) %04d-%02d-%02d %02d:%02d:%02d.%03d\n",
v_ts_ind, v_ts.year, v_ts.month, v_ts.day, v_ts.hour, v_ts.minute, v_ts.second, v_ts.fraction / 1000000);
rcode = SQLFetchScroll(m_hstmt2, SQL_FETCH_NEXT, 0);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLFetchScroll 4");
fprintf(stdout, "TS Value 4: (ind=%d) %04d-%02d-%02d %02d:%02d:%02d.%03d\n",
v_ts_ind, v_ts.year, v_ts.month, v_ts.day, v_ts.hour, v_ts.minute, v_ts.second, v_ts.fraction / 1000000);
rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt2);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLFreeHandle StmtH 2");
rcode = SQLDisconnect(m_hdbc);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLDisconnect");
rcode = SQLFreeHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_hdbc);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLFreeHandle DbcH");
rcode = SQLFreeHandle(SQL_HANDLE_ENV, (SQLHANDLE) m_henv);
CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLFreeHandle EnvH");
}
-
[freetds] SQLBindParameter using TIMESTAMP_STRUCT: Fractional part is wrong,
Sebastien FLAESCH, 01/07/2008
-
Re: [freetds] SQLBindParameter using TIMESTAMP_STRUCT: Fractional partis wrong,
ZIGLIO, Frediano, VF-IT, 01/08/2008
-
Re: [freetds] SQLBindParameter using TIMESTAMP_STRUCT: Fractional partis wrong,
Sebastien FLAESCH, 01/08/2008
-
[freetds] SQLExecDirect() of transaction statement returns SQL_NO_DATA,
Sebastien FLAESCH, 01/08/2008
-
Re: [freetds] SQLExecDirect() of transaction statement returnsSQL_NO_DATA,
ZIGLIO, Frediano, VF-IT, 01/08/2008
-
Re: [freetds] SQLExecDirect() of transaction statement returnsSQL_NO_DATA,
Sebastien FLAESCH, 01/08/2008
-
[freetds] SQLNumResultCols() is wrong (+1),
Sebastien FLAESCH, 01/08/2008
- Re: [freetds] SQLNumResultCols() is wrong (+1), ZIGLIO, Frediano, VF-IT, 01/08/2008
- Re: [freetds] SQLNumResultCols() is wrong (+1), Frediano Ziglio, 01/10/2008
- Re: [freetds] SQLNumResultCols() is wrong (+1), ZIGLIO, Frediano, VF-IT, 01/10/2008
- Re: [freetds] SQLNumResultCols() is wrong (+1), Sebastien FLAESCH, 01/10/2008
-
[freetds] SQLNumResultCols() is wrong (+1),
Sebastien FLAESCH, 01/08/2008
-
Re: [freetds] SQLExecDirect() of transaction statement returnsSQL_NO_DATA,
Sebastien FLAESCH, 01/08/2008
-
Re: [freetds] SQLExecDirect() of transaction statement returnsSQL_NO_DATA,
ZIGLIO, Frediano, VF-IT, 01/08/2008
-
[freetds] SQLExecDirect() of transaction statement returns SQL_NO_DATA,
Sebastien FLAESCH, 01/08/2008
-
Re: [freetds] SQLBindParameter using TIMESTAMP_STRUCT: Fractional partis wrong,
Sebastien FLAESCH, 01/08/2008
-
Re: [freetds] SQLBindParameter using TIMESTAMP_STRUCT: Fractional partis wrong,
ZIGLIO, Frediano, VF-IT, 01/08/2008
Archive powered by MHonArc 2.6.24.