Bug #37621 SQLDescribeCol returns incorrect values of SQLTables data
Submitted: 25 Jun 2008 7:13 Modified: 24 May 2010 10:29
Reporter: Chi Lam Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51, 5.1 OS:Microsoft Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: SQLDescribeCol, SQLTables

[25 Jun 2008 7:13] Chi Lam
Description:
SQLDescribeCol returns incorrect column definitions of SQLTables result.

How to repeat:
typedef struct DataBinding 
{
   SQLSMALLINT iType;
   SQLPOINTER pvValue;
   SQLINTEGER iBufLen;
   long lLen;

} DATA_BINDING;

static int MySQLSuccess( RETCODE rc )
{
   return ( rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO );
}

static void MySQLTables( HDBC hdbc )
{
   int bufferSize = 1024, i, numCols = 5;
   RETCODE ret;
   SQLHSTMT hstmt;
   DATA_BINDING * pTable = (DATA_BINDING*) malloc( numCols * sizeof(DATA_BINDING) );

   for ( i = 0 ; i < numCols ; i++ )
   {
      pTable[i].iType = SQL_C_CHAR;
      pTable[i].iBufLen = (bufferSize + 1);
      pTable[i].pvValue = malloc( sizeof(unsigned char) * pTable[i].iBufLen );
   }

   ret = SQLAllocStmt( hdbc, &hstmt );
   if ( MySQLSuccess( ret ) )
   {
      for ( i = 0 ; i < numCols ; i++ )
      {
         ret = SQLBindCol( hstmt, (SQLUSMALLINT)i + 1, pTable[i].iType, pTable[i].pvValue, pTable[i].iBufLen, &(pTable[i].lLen) );
      }

      ret = SQLTables( hstmt, NULL, 0, NULL, 0, NULL, 0, NULL, 0 );
      if ( MySQLSuccess( ret ) )
      {
         unsigned char szColName[128];
         short iName;
         short iType;
         unsigned long uiDef;
         short iScale;
         short iNullable;

         for ( i = 0; i < numCols; i++ )
         {
            ret = SQLDescribeCol( hstmt, i + 1, szColName, sizeof(szColName), &iName, &iType, &uiDef, &iScale, &iNullable );
            printf( "%s (%d): Type(%d), Def(%d), Scale(%d), Nullable(%d)\n", szColName, iName, iType, uiDef, iScale, iNullable );
         }
      }

      SQLFreeStmt( hstmt, SQL_DROP );
   }

   for ( i = 0 ; i < numCols ; i++ )
   {
      free( pTable[i].pvValue );
   }

   free( pTable );
}

The returned column definitions are:

TABLE_CAT (9): Type(-9), Def(64), Scale(0), Nullable(1)
TABLE_SCHEM (11): Type(-9), Def(64), Scale(0), Nullable(1)
TABLE_NAME (10): Type(-9), Def(64), Scale(0), Nullable(1)
TABLE_TYPE (10): Type(-9), Def(64), Scale(0), Nullable(1)
REMARKS (7): Type(-9), Def(64), Scale(0), Nullable(1)
[30 Jun 2008 5:22] Chi Lam
When the table's comment is longer than 64 bytes.
[3 Jul 2008 13:23] Bogdan Degtyariov
Verified with the latest versions of MyODBC 3.51 and 5.1
[10 Nov 2009 6:30] Bogdan Degtyariov
patch and test case

Attachment: patch37621.diff (text/x-diff), 1.80 KiB.

[11 Mar 2010 18:48] Lawrenty Novitsky
I think the patch should be changed in analogy to the patch for the Bug#34272
i.e. field length should be multiplied by SYSTEM_CHARSET_MBMAXLEN

With such change I can approve it. If Jim agrees - I'll apply this change and push the patch.
[23 Mar 2010 21:12] Jim Winstead
I would take another look at the MYODBC_FIELD_STRING() macros and why it is not multiplying the field length for 5.1 and later, but is for 4.1 and 5.0. I suspect that's the real bug here and for Bug #34272.
[31 Mar 2010 18:20] Paul Rampel
Excel spreadsheet illustrating difference between two versions

Attachment: MYSQL 5.1.6 vs. 5.1.5.zip (application/x-zip-compressed, text), 15.12 KiB.

[31 Mar 2010 18:22] Paul Rampel
The behavior described by this bug also appears to manifest when using the SQLGetTypeInfo ODBC call.  the preceding file illustrates the diffent results when using 5.1.5 connector vs. 5.1.6 - results were readily obtained using ODBC Test application (32 bit) in ANSI mode.
[31 Mar 2010 18:33] Paul Rampel
Further to my comment re. the possible nature of this bug as it relates to SQLDescribeCol and SQLGetTypeInfo.  It appears that the "LITERAL_SUFFIX" column is reported to be zero characters long, so any pre-fix returned is causing truncation...Hope this helps...
[7 Apr 2010 14:57] Lawrenty Novitsky
Updated patch - fixed MYODBC_FIELD_STRING and introduced MYODBC_FIELD_NAME for NAME_LEN len

Attachment: patch37621v2.diff (text/x-diff), 11.07 KiB.

[7 Apr 2010 15:22] Lawrenty Novitsky
Paul, updated patch will fix similar bugs you reported, too.

latest edition of the patch have been pushed to working_tree branch as rev#883
[21 May 2010 8:02] Lawrenty Novitsky
pushed to the trunk
[24 May 2010 10:29] Tony Bedford
An entry has been added to the 5.1.17  changelog:

SQLDescribeCol returned incorrect column definitions for SQLTables result.
[25 Aug 2010 11:25] Tonci Grgin
Tony, "An entry has been added to the 5.1.17" should actually read "An entry has been added to the 5.1.7" right?