INFO: SQLState Returned by Microsoft Oracle ODBC Driver on Input Parameter Truncation (306006)

SUMMARY

When using an input parameter in a WHERE clause whose size is greater than the column size in the database, the Microsoft ODBC for Oracle driver returns SQL_SUCCESS_WITH_INFO on the SQLExecute call with a SQLState 01004.

MORE INFORMATION

In drivers compliant with ODBC 3.0 and later, truncated input parameters should result in a SQL_ERROR with SQLState 22001. However, the ODBC 2.5 specification requires that such cases result in SQL_SUCCESS_WITH_INFO with SQLState 01004 instead. Because the Microsoft Oracle driver is ODBC 2.5-compliant, this behavior is by design.

The following sample code reproduces this behavior:

   char   szStmt[] = "Select name, state from customer where state = ?";

   SQLRETURN  nResult;
   SWORD      nNumParams = 0;
   SQLINTEGER strLen = SQL_NTS;

   char       szState[80] = "CAL";  // Anything over 2 characters will do.

   nResult = SQLPrepare( hstmt, szStmt, SQL_NTS );
   nResult = SQLNumParams( hstmt, &nNumParams );
   nResult = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, 
                               SQL_C_CHAR, SQL_CHAR,
                               2, 0, szState, sizeof(szState), &strLen );
   nResult = SQLFreeStmt( hstmt, SQL_CLOSE );
   nResult = SQLExecute( hstmt );