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

View products that this article applies to.

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.

↑ Back to the top

Cause

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

↑ Back to the top

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

↑ Back to the top

Status

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

↑ Back to the top

More information

Manual Installation

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.
Download the hotfix into a temporary directory.
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.
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

#include <windows.h>
#include <stdio.h>
#include <iostream.h>
#include <sql.h>
#include <sqlext.h>


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


}

↑ Back to the top