Description:
The wrong type is returned by SQLColAttribute() for fields that were declared as an NVARCHAR or BINARY.
How to repeat:
#include <iostream>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#ifndef TRUE
#define TRUE 1
#endif
#define MAXCOLS 100
#define MAX_DATA_LEN 255
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLINTEGER PrintError(SQLSMALLINT handleType, SQLHANDLE handle, SQLRETURN rcode)
{
SQLRETURN rc = SQL_ERROR;
SQLCHAR sqlState[6];
SQLCHAR eMsg[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER nError;
SQLSMALLINT msgLen;
if (rcode == SQL_NO_DATA)
{
std::cout << "NO DATA .. .. .." << std::endl;
return(SQL_NO_DATA);
}
else
{
rc = SQLGetDiagRec(handleType, handle, 1, sqlState, &nError, eMsg, sizeof(eMsg), &msgLen);
if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
{
std::cout << " Error: " << eMsg << std::endl;
}
return (SQL_ERROR);
}
}
int main(int argc, char * argv[])
{
SQLRETURN rc;
if (argc != 4)
{
std::cout << "USAGE: DBClassTet <DSN name> <username> <password>" << std::endl;
return -1;
}
rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_ENV, henv, rc));
rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_ENV, henv, rc));
rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_ENV, henv, rc));
std::cout << "Connecting to " << argv[1] << std::endl;
rc = SQLConnect(hdbc, (SQLCHAR *)argv[1], SQL_NTS,
(SQLCHAR *)argv[2], SQL_NTS,
(SQLCHAR *)argv[3], SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_DBC, hdbc, rc));
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_DBC, hdbc, rc));
/*************************************************************************************/
rc = SQLExecDirect(hstmt, (SQLCHAR *)"DROP TABLE TESTBINARY", SQL_NTS);
rc = SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE TESTBINARY ( COL1 BINARY(10), COL2 NVARCHAR(10) )", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
rc = SQLExecDirect(hstmt, (SQLCHAR *)"INSERT INTO TESTBINARY VALUES ('01', N'ABC' )", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
rc = SQLExecDirect(hstmt, (SQLCHAR *)"SELECT COL1, COL2 FROM TESTBINARY", SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
SQLSMALLINT rs;
rc = SQLColAttribute(hstmt, 1, SQL_DESC_CONCISE_TYPE, 0, 0, 0, &rs);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
if (rs != SQL_BINARY)
{
std::cout << "UNEXPECTED TYPE FOR BINARY COL -> " << rs << std::endl;
}
rc = SQLColAttribute(hstmt, 2, SQL_DESC_CONCISE_TYPE, 0, 0, 0, &rs);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
if (rs != SQL_WVARCHAR)
{
std::cout << "UNEXPECTED TYPE FOR NVARCHAR COL -> " << rs << std::endl;
}
/*************************************************************************************/
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
rc = SQLDisconnect(hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_DBC, hdbc, rc));
rc = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_DBC, hdbc, rc));
rc = SQLFreeHandle(SQL_HANDLE_ENV, henv);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_ENV, henv, rc));
return(0);
}
Suggested fix:
For BINARY fields, the patch below addresses the problem.
--- MResult.cpp (revision 663)
+++ MResult.cpp (working copy)
@@ -4762,12 +4762,22 @@
case MYSQL_TYPE_STRING:
pDescriptorRecord->setDisplaySize( pField->length ? pField->length : 255 );
pDescriptorRecord->setLength( pField->max_length );
- pDescriptorRecord->setLiteralPrefix( "\"'" );
- pDescriptorRecord->setLiteralSuffix( "\"'" );
pDescriptorRecord->setNumPrecRadix( 0 );
pDescriptorRecord->setOctetLength( pField->max_length );
- pDescriptorRecord->setTypeName( "char" );
- pDescriptorRecord->setConciseType( SQL_CHAR );
+ if ( pField->flags & BINARY_FLAG )
+ {
+ pDescriptorRecord->setLiteralPrefix( "" );
+ pDescriptorRecord->setLiteralSuffix( "" );
+ pDescriptorRecord->setTypeName( "binary" );
+ pDescriptorRecord->setConciseType( SQL_BINARY );
+ }
+ else
+ {
+ pDescriptorRecord->setLiteralPrefix( "\"'" );
+ pDescriptorRecord->setLiteralSuffix( "\"'" );
+ pDescriptorRecord->setTypeName( "char" );
+ pDescriptorRecord->setConciseType( SQL_CHAR );
+ }
break;
case MYSQL_TYPE_TINY:
pDescriptorRecord->setLength( pField->max_length );