Microsoft KB Archive/250286

= FIX: Error "Datetime field overflow" When Inserting Negative (B.C.) Dates with Oracle ODBC Driver =

Article ID: 250286

Article Last Modified on 10/12/2005

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5

-



This article was previously published under Q250286



SYMPTOMS
When using parameter binding to insert negative (B.C.) dates through the Microsoft ODBC Driver for Oracle, the following error is thrown:

SQLState = 22007 NativeError = 0

[Microsoft][ODBC Driver for Oracle]Error in parameter 1: Datetime field overflow

This error occurs with all versions of the Microsoft ODBC driver for Oracle, but does not occur with Oracle's ODBC driver.



CAUSE
Microsoft's Oracle ODBC driver is incorrectly interpreting year values that are less than zero as an invalid year.



RESOLUTION
A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next Microsoft Data Access Components service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

NOTE: In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The typical support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

The English version of this fix should have the following file attributes or later:   Date      Time    Version      Size     File name     Platform --  12/30/99          2.573.4830   143,632  msorcl32.dll



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Manual Installation

 * 1) Quit any applications or services that are using Msorcl32.dll. This may include Internet Information Server (IIS), Microsoft Transaction Server (MTS), and any ODBC, ADO, or OLE DB applications.
 * 2) Download the hotfix into a temporary directory.
 * 3) Locate and rename the current versions of Msorcl32.dll, which should be in the \Winnt\System32 folder for computers running Windows NT, and in the \Windows\System folder for computers running Windows 95 and Windows 98.
 * 4) Copy the hotfix file into the same location, and then restart your services and applications.

Steps to Reproduce Behavior
Copy the following code into a console application (modifying your Oracle server, user name and password as necessary), compile and run it.

NOTE: The following syntax can be used in SQL*Plus to insert and view negative (B.C.) dates in an Oracle table:

    INSERT INTO dates (d) VALUES ( TO_DATE ('03/03/-455', 'MM/DD/SYYYY')) ...and... SELECT TO_CHAR(D, 'MM/DD/SYYYY') FROM DATES


 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 

SQLHANDLE hEnv; SQLHANDLE hConn; SQLHANDLE hStmt;

void ShowErrors(HENV, HDBC, HSTMT);

void main(void) {

RETCODE rc; SQLCHAR Statement[255]; strcpy((char *) Statement, "INSERT INTO dates (d) VALUES (?)");

SQLCHAR StatementCreateTable[255]; strcpy((char *) StatementCreateTable, "create table DATES (d DATE)");

SQLCHAR dsn[255]; strcpy((char *) dsn,"OracleServer"); SQLCHAR user[255]; strcpy((char *) user,"scott"); SQLCHAR pass[255]; strcpy((char *) pass,"tiger"); // January 3, 500 B.C.   SQL_TIMESTAMP_STRUCT value; value.hour = 0; value.minute = 0; value.second = 0; value.year = -499; value.month = 1; value.day = 3; value.fraction = 0;

rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv); if (rc == SQL_SUCCESS) {       rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,             (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER); }   if (rc == SQL_SUCCESS) {       rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hConn); }   rc = SQLConnect(hConn, dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, (SQLCHAR *) pass, SQL_NTS); rc = SQLAllocHandle(SQL_HANDLE_STMT,hConn,&hStmt);

//create the Dates table rc = SQLExecDirect(hStmt, StatementCreateTable, SQL_NTS); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) ShowErrors(hEnv, hConn, hStmt); SQLFreeStmt(hStmt, SQL_CLOSE);

//Bind the SQL_TIMESTAMP_STRUCT as the input parameter rc = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 23, 3,       (SQLPOINTER) &value, 0, NULL);

//Execute the INSERT statement rc = SQLExecDirect(hStmt, Statement, SQL_NTS); if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) ShowErrors(hEnv, hConn, hStmt);

SQLFreeStmt(hStmt, SQL_CLOSE); SQLFreeHandle(SQL_HANDLE_STMT, hStmt); SQLDisconnect(hConn); SQLFreeHandle(SQL_HANDLE_DBC, hConn); }

void ShowErrors (HENV henv, HDBC hdbc, HSTMT hstmt) {

//Variables for SQLGetDiagRec SQLCHAR sqlState[20]; SQLCHAR errorMsg[1000]; SQLSMALLINT errorMsgLen; SQLINTEGER nativeError; char szTemp[4096]; //Retrieve the Raised error message SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,sqlState,       &nativeError,errorMsg,1000,&errorMsgLen);

//Display the size of the returned error message, and the message itself sprintf(szTemp, "Length=[%d] Text=%s", strlen((char*)errorMsg), errorMsg ); printf("ERROR! %s\n\n\n", szTemp);

printf("Press any key..."); getchar;

SQLFreeStmt(hStmt, SQL_CLOSE); SQLFreeHandle(SQL_HANDLE_STMT, hStmt); SQLDisconnect(hConn); SQLFreeHandle(SQL_HANDLE_DBC, hConn);

exit(0);

}

Additional query words: oracle odbc driver datetime field overflow negative b.c. timestamp bc 22007

Keywords: kbbug kbfix kboracle kbqfe kbmdac210sp2fix kbmdacnosweep kbhotfixserver KB250286

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.