Bug #24097 Wrong concise type returned for NVARCHAR and BINARY fields
Submitted: 8 Nov 2006 18:11 Modified: 4 Oct 2007 21:06
Reporter: Jim Winstead Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.0.06 OS:Any
Assigned to: CPU Architecture:Any
Tags: BINARY, NVARCHAR, SQLColAttribute

[8 Nov 2006 18:11] Jim Winstead
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 );
[9 Nov 2006 22:51] Jess Balint
Thanks Jim. Applied patch to fix BINARY/CHAR and fixed VARBINARY/VARCHAR in r665.

VARCHAR/NVARCHAR is still undetermined.
[4 Oct 2007 21:06] Jim Winstead
C/ODBC 5.0 is dead.
[31 Dec 2007 9:07] Tonci Grgin
For me it works as expected with MyODBC 5.1.2