Microsoft KB Archive/239099

= FIX: Microsoft ODBC Driver for Oracle Truncates Error Messages =

Article ID: 239099

Article Last Modified on 9/22/2005

-

APPLIES TO


 * 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
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q239099



SYMPTOMS
In the Microsoft Open Database Connectivity (ODBC) Driver for Oracle, error messages greater than 255 characters are randomly truncated. The number of characters returned to the application may vary depending on the number of characters in the error message. This problem does not occur when using the ODBC driver from Oracle.

The ODBC header file SQL.H defines SQL_MAX_MESSAGE_LENGTH to be 512 characters, permitting messages of up to 511 characters, plus a Null termination character, to be returned.



CAUSE
Internal to the ODBC driver, the error message buffer is declared as a UCHAR datatype. The UCHAR type has a maximum size of 255 (0xFF) characters.



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

This problem was corrected in MDAC 2.5 and later.



Steps to Reproduce Behavior
Create a new console application in Visual C++ by using the following code. This code creates a stored procedure in Oracle that deliberately raises an Oracle error, and then returns a large error message.

When the code is run, the number of characters returned in the error message is not the maximum defined size of 512 (511 plus the Null termination character).

Please note that you must modify the code to use the correct ODBC DSN, userId, and password. Also, coping and pasting this code might produce compiler errors because of lost text formatting. Correct the text formatting in a Visual C++ editor before compiling by bringing appropriate statements to new line.
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 

main {   RETCODE rc; HENV henv; HDBC hdbc; HSTMT hstmt;

char * dsn = "YourServer"; char * uid = "scott"; char * pwd = "tiger"; char * SQLStr = "{call TESTERR}";

SQLCHAR createProc[] = "CREATE OR REPLACE PROCEDURE TESTERR AS output_err_msg VARCHAR2(1000); raised_error_string " "VARCHAR2(2); BEGIN raised_error_string := 'This assignment will raise an exception'; EXCEPTION WHEN " "OTHERS THEN output_err_msg :='This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "This error message should contain 700 characters. " "Errors!';raise_application_error(-20250, output_err_msg);END;";

// Variables for SQLGetDiagRec. SQLCHAR sqlState[20]; SQLCHAR errorMsg[1000]; SQLSMALLINT errorMsgLen; SQLINTEGER nativeError; char szTemp[4096];

rc = SQLAllocEnv(&henv); rc = SQLAllocConnect(henv, &hdbc);

rc = SQLConnect(hdbc, (unsigned char *)dsn,       SQL_NTS, (unsigned char *)uid,        SQL_NTS, (unsigned char *)pwd, SQL_NTS);

rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc,&hstmt);

// Create the stored procedure in Oracle. rc = SQLExecDirect(hstmt, (unsigned char *)createProc, SQL_NTS); SQLFreeStmt(hstmt, SQL_CLOSE);

// Run the newly created stored procedure. rc = SQLExecDirect(hstmt, (unsigned char *)SQLStr, SQL_NTS);

// 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 ); MessageBox(NULL, (LPSTR) szTemp,"ODBC Error Message",0);

SQLFreeStmt(hstmt, SQL_CLOSE); SQLFreeStmt(hstmt, SQL_DROP); SQLDisconnect(hdbc); SQLFreeConnect(hdbc); SQLFreeEnv(henv); return(TRUE); };

Additional query words: oracle odbc driver truncate error messages 255 512 sql_max_message_length hotfix

Keywords: kbbug kbfix kboracle kbmdac250fix kbqfe kbdatabase kbdriver kbmdacnosweep kbhotfixserver KB239099

-

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

© Microsoft Corporation. All rights reserved.