FreeTDS Development Group

[Sebastien FLAESCH <sf AT 4js.com>]

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");
}