BUG: Time Value Converted to Datetime Using Access ODBC Driver

• Microsoft Open Database Connectivity, version 1.0

BUG# ODBCDBASE: 2128 (1.01.1928)

SYMPTOMS

When a value of type time is converted and inserted into a column in a Microsoft Access data source, defined as of type datetime, the converted value is incorrectly interpreted by Microsoft Access.

For example, consider the following SQL statements, executed using ODBC calls:

   SQLAllocEnv(&henv);
   SQLAllocConnect (henv, &hdbc);
   SQLDriverConnect (hdbc, hwnd, "", 0,
     szConnStrOut,cbConnStrOutMax,&cbConnStrOut, SQL_DRIVER_PROMPT);
   SQLAllocStmt(hdbc, &hstmt);

Prepared Execution

   SQLPrepare(hstmt,"insert into test values(?)",SQL_NTS);

   //Bind the parameter, define rgbValue to be a TIME_STRUCT

   SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_TIME,
                SQL_TIMESTAMP,16,rgbValue,cbValueMax,
   pcbValue);

   //Set appropriate time values in the rgbValue

   //Execute the prepared statement
   SQLExecute(hstmt);

Direct Execution

   SQLExecDirect(hstmt,"insert into test values({t '10:10:10'})",SQL_NTS);

      SQLFreeStmt (hstmt, SQL_DROP);
      SQLDisconnect(hdbc);
      SQLFreeConnect(hdbc);
      SQLFreeEnv(henv);

   0000-00-00 10:10:10

WORKAROUND

Use the timestamp value with a date part as well as time part when inserting or doing the conversion.

For SQLBindParameter, change the fCType to SQL_C_TIMESTAMP and define rgbValue to be TIMESTAMP_STRUCT, and set appropriate values in date and time parts of the structure.

For SQLExecDirect, use escape clause for timestamp data instead of the escape clause for time data.

   //Bind the parameter, define rgbValue to be a TIMESTAMP_STRUCT

   SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,
   SQL_C_TIMESTAMP,SQL_TIMESTAMP,16,rgbValue,cbValueMax,pcbValue);

Direct Execution

   SQLExecDirect(hstmt, "insert into test values({ts '1990-01-01
   10:10:10'}",SQL_NTS)

STATUS

Microsoft has confirmed this to be a problem in the Microsoft Access ODBC driver version 1.01.1928. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.