DOCUMENT:Q239099 08-MAY-2002 [odbc] TITLE :FIX: Microsoft ODBC Driver for Oracle Truncates Error Messages PRODUCT :Open Database Connectivity (ODBC) PROD/VER::2.1,2.5,Build 2.573.2927,Build 2.573.3513,Build 2.573.3711,Build 2.573.4202,Build 2.57 OPER/SYS: KEYWORDS:kbDatabase kbDriver kbODBC kbOracle kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft ODBC for Oracle version 2.5, versions Build 2.573.2927, Build 2.573.3513, Build 2.573.3711, Build 2.573.4202, Build 2.573.4403 - Microsoft Data Access Components versions 2.1, 2.5 ------------------------------------------------------------------------------- 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 this to be 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. MORE INFORMATION ================ 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. #include #include #include #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 : kbDatabase kbDriver kbODBC kbOracle kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC250fix kbMDACNoSweep Technology : kbAudDeveloper kbODBCSearch kbMDACSearch kbMDAC210 kbMDAC250 kbODBCOracle25732927 kbODBCOracle25733513 kbODBCOracle25733711 kbODBCOracle25734202 kbODBCOracle25734403 kbODBCOracle250Search Version : :2.1,2.5,Build 2.573.2927,Build 2.573.3513,Build 2.573.3711,Build 2.573.4202,Build 2.573.4403 Hardware : ALPHA x86 Issue type : kbbug Solution Type : kbfix ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2002.