Bug #30399 Data(SQL_CHAR)'s length is incorrect when SQLFetch multiple rows

• Submitted: 14 Aug 2007 6:56
• Modified: 17 Aug 2007 4:17
• Reporter: x l
• Status: Closed
• Category: Connector / ODBC
• Severity: S1 (Critical)
• Version: 3.51.17
• OS: IBM AIX (unixODBC + MyODBC)
• CPU Architecture: Any
• Tags: AIX, ODBC API, SQLBindCol, SQLFetch

[14 Aug 2007 6:56] x l

Description:
Copy the text below to Notepad will get a better formed view.

mysql> select names,length(names) from n1;
+-----------+---------------+        for( icol=0; icol<220; icol+=4 )
| names     | length(names) |            printf("%d  %d  %d  %d\n",
+-----------+---------------+            colLen[icol], colLen[icol+1], colLen[icol+2], colLen[icol+3]);
| rb6       |             3 |    ->      3  0  0  0
| rb5       |             3 |    ->      3  0  0  0
| rb1       |             3 |    ->      3  0  0  0
| rb2       |             3 |    ->      3  0  0  0
| rb3       |             3 |    ->      3  0  0  0
| rb4       |             3 |    ->      3  0  0  0
| rb11      |             4 |    ->      4  0  0  0
| blhydra1  |             8 |    ->      8  0  0  0
| rb13      |             4 |    ->      4  0  0  0
| rb12      |             4 |    ->      4  0  0  0
| mtc106    |             6 |    ->      6  0  0  0
| mtc001    |             6 |    ->      6  0  0  0
| mtc016    |             6 |    ->      6  0  0  0
| rfcomp19  |             8 |    ->      8  0  0  0
| mb3       |             3 |    ->      3  0  0  0
| mtc046    |             6 |    ->      6  0  0  0
| mb1       |             3 |    ->      3  0  0  0
| mb5       |             3 |    ->      3  0  0  0
| mb11      |             4 |    ->      4  0  0  0
| mb9       |             3 |    ->      3  0  0  0
| rfcomp2   |             7 |    ->      7  0  0  0
| mb7       |             3 |    ->      3  0  0  0
| rfcomp1   |             7 |    ->      7  0  0  0
| rfcomp25  |             8 |    ->      8  0  0  0
| shrk15h   |             7 |    ->      7  0  0  0
| mtc014    |             6 |    ->      6  0  0  0
| mtc045    |             6 |    ->      6  0  0  0
| rfcomp37  |             8 |    ->      8  0  0  0
| fieldmm1  |             8 |    ->      8  0  0  0
| rfcomp24  |             8 |    ->      8  0  0  0
| jemt11s1  |             8 |    ->      8  0  0  0
| jemt11s2  |             8 |    ->      8  0  0  0
| reef049   |             7 |    ->      7  0  0  0
| mb2       |             3 |    ->      3  0  0  0
| mb10      |             4 |    ->      4  0  0  0
| mb12      |             4 |    ->      4  0  0  0
| mtc039    |             6 |    ->      6  0  0  0
| mmsls2    |             6 |    ->      6  0  0  0
| mtc044    |             6 |    ->      6  0  0  0
| mtc018    |             6 |    ->      6  0  0  0
| mtc043    |             6 |    ->      6  0  0  0
| m1180     |             5 |    ->      5  0  0  0
| mtc027    |             6 |    ->      6  0  0  0
| m21280    |             6 |    ->      6  0  0  0
| m21281    |             6 |    ->      6  0  0  0
| m1510     |             5 |    ->      5  0  0  0
| m1590     |             5 |    ->      5  0  0  0
| rfcomp39  |             8 |    ->      8  0  0  0
| rfcomp40  |             8 |    ->      8  0  0  0
| mtc009    |             6 |    ->      6  0  0  0
| mtc720    |             6 |    ->      6  0  0  0
| ermega1s1 |             9 |    ->      9  0  0  0
+-----------+---------------+
52 rows in set (0.08 sec)

The left side is got from MySQL client, and the right side is the "length(names)" value got from my ODBC application.
They are different!
colLen[0], colLen[1], colLen[2], colLen[3], colLen[4], colLen[5], colLen[6], colLen[7] ... should be 3, 3, 3, 3, 3, 3, 4; but not 3, 0, 0, 0, 3, 0, 0, 0.

How to repeat:
This is the C code, just change your DSN, UID, PWD value in the code and create a table: n1(names char(20)) in MySQL Database, then insert into some rows just as above. 

#include <stdlib.h>
#include <memory.h>
#include <string.h>
#include <stdio.h>
#include <locale.h>
#include <sqlext.h>
#include <sql.h>

int main(int argc, char * argv[])
{
    HDBC     hdbc;
    HENV     henv;
    HSTMT	   hstmt;
    RETCODE	 rc;
    char     uid[32];
    char     pwd[32];
    char     dataSource[32];
    char     sqlStatement[1024];
    SWORD    numCols = 0 ;
    SWORD    icol ;

    SQLLEN  colLen[300];
    long    attrValue=0;
    char    outBuff[1024];
    for(icol=0; icol<300; icol++) colLen[icol]=0;
	
    setlocale(LC_ALL,"");

    strcpy(dataSource,"mysql");
    strcpy(uid,"uid");
    strcpy(pwd,"password");
    dataSource[strlen(dataSource)]='\0';
    uid[strlen(uid)]='\0';
    pwd[strlen(pwd)]='\0';
    printf("\n");
    printf ("Data source name : %s\n", dataSource) ;
    printf ("User name        : %s\n", uid) ;
    printf ("Password         : %s\n", pwd) ;
    /*Init Handle*/
    printf("Init Handles ......\n");
    rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)){
        printf("SQLSAllocHandle SQL_HANDLE_ENV Failed.\n");
        exit(255);
    }

    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

    rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)){
        printf("SQLALLocHandle SQL_HANDLE_DBC Failed.\n");
        exit(255);
    }

    /* Connect */
    printf("Connect to data source ......\n");
    rc = SQLConnect ((SQLHDBC) hdbc, (unsigned char*)dataSource, SQL_NTS,
            (unsigned char*)uid, SQL_NTS, (unsigned char*)pwd, SQL_NTS);
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
        printf("SQLConnect Failed.\n");
        exit(255);
    }

    /*Init Stmt*/
    printf("Init statement ......\n");
    rc = SQLAllocStmt ((SQLHDBC)hdbc, (SQLHSTMT *)&hstmt);
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO)) {
        SQLDisconnect ((SQLHDBC)hdbc);
        SQLFreeConnect ((SQLHDBC)hdbc);
        SQLFreeEnv ((SQLHENV)henv);
        printf("SQLAllocStmt Failed.\n");
        exit (255);
    }

    /*Exec SQL*/
    printf("Execute SQL statement ......\n");
    strcpy(sqlStatement,"select names from n1");
    sqlStatement[strlen(sqlStatement)]='\0';
    rc = SQLExecDirect ((SQLHSTMT)hstmt, (unsigned char*)sqlStatement, SQL_NTS) ;
    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
    {
        printf("SQLExecDirect Failed.\n");
        exit (255);
    }
    attrValue=1169;
    SQLSetStmtAttr((SQLHSTMT)hstmt,SQL_ATTR_ROW_ARRAY_SIZE, (void *)attrValue, 0);
    attrValue=0;
    SQLSetStmtAttr((SQLHSTMT)hstmt,SQL_ATTR_ROWS_FETCHED_PTR, &attrValue, 0);
    attrValue=0;
    SQLSetStmtAttr((SQLHSTMT)hstmt,SQL_ATTR_ROW_STATUS_PTR, (void *)attrValue, 0);

    /*Bind Column, just to get the length*/
    printf("Bind column ......\n");
    rc = SQLBindCol ( (SQLHSTMT)hstmt,
        (UWORD) (1),
        (SWORD) SQL_CHAR,
        (PTR) NULL,
        (SDWORD) 255,
        colLen);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO ){
        printf("SQLBindCol Failed.\n");
        exit(255);
    }

    /*Fetch Value*/
    printf("Fetch Value ......\n");
    rc = SQLFetch(hstmt) ;
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO ){
        printf("SQLFetch Failed.\n");
        exit(255);
    }

    /*Print column length*/
    printf("Print column length ......\n");
    for( icol=0; icol<220; icol+=4 )
        printf("%d  %d  %d  %d\n", colLen[icol], colLen[icol+1], colLen[icol+2], colLen[icol+3]);

    SQLFreeStmt ((SQLHSTMT)hstmt, SQL_UNBIND) ;
    SQLFreeStmt ((SQLHSTMT)hstmt, SQL_CLOSE) ;
    SQLDisconnect ((SQLHDBC)hdbc);
    SQLFreeConnect ((SQLHDBC)hdbc);

    return 0;
}

[15 Aug 2007 9:37] x l

This is a bug of version 3.51.17, and I cannot reproduce it at version 3.51.19.

[15 Aug 2007 9:59] x l

The new output of the reproducing code is:
(why the "SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);" failed?)

Output:
Init Handles ......
Connect to data source ......
[MYODBCUtilReadDataSource.c][222][ERROR] Unknown attribute (Address).
Init statement ......
Execute SQL statement ......
Bind column ......
Fetch Value ......
Print column length ......
3  3  3  3
3  3  4  8
4  4  6  6
6  8  3  6
3  3  4  3
7  3  7  8
7  6  6  8
8  8  8  8
7  3  4  4
6  6  6  6
6  5  6  6
6  5  5  8
8  6  6  9
0  0  0  0
0  0  0  0
0  0  0  0
0  0  0  0
...
...

[15 Aug 2007 12:27] Susanne Ebrecht

Hi x l,

Thank you for writing a bug report.
does it mean, we can close this bug?

Regards,

Susanne

[15 Aug 2007 14:00] x l

Yes.
This bug seems have been fixed in the new version.

But by the way, can you explain why the "SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);" failed when the version is 3.51.19, while it run successfully when the version is 3.5.17?

Thank you very much!

[15 Aug 2007 16:05] Jess Balint

xl, What is the return code from SQLSetEnvAttr()? Is it SQL_ERROR? Have you tried retrieving the diagnostic message?

[16 Aug 2007 1:49] x l

rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);

I got rc==SQL_SUCCESS !!

So, where is the message from: "[MYODBCUtilReadDataSource.c][222][ERROR] Unknown attribute (Address)." ?

[16 Aug 2007 23:29] Jess Balint

x_l, I suspect you have some line like the following in your odbc.ini:

Address = something

The warning is that the "Address" attribute on the DSN is unrecognized. I will close this bug as the original problem is not reproducible.

[17 Aug 2007 1:34] x l

OK.

Thank you for your infomation!

[17 Aug 2007 1:38] x l

You are right.
Thank you!

[17 Aug 2007 6:20] Tonci Grgin

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation: Fixed in release 3.51.19GA.