MySQL Bugs: #22446: MyODBC inserts wrong parameter values for prepared statements (64-bit Linux)

Bug #22446	MyODBC inserts wrong parameter values for prepared statements (64-bit Linux)
Submitted:	18 Sep 2006 16:00	Modified:	6 Dec 2006 9:40
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.13	OS:
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	SQLPrepare

Description:
When running MyODBC 3.51.13 on RHEL4 64-bit prepared statements work incorrectly whereas 32-bit Linux doesn't have such problems:
Fragment from MySQL Qurey Log:
--------------------------------------------------------------------
1 Query       DROP TABLE TESTPREPARED
1 Query       CREATE TABLE TESTPREPARED ( COL1 INTEGER )
1 Query       INSERT INTO TESTPREPARED VALUES (22)
1 Query       SELECT COL1 FROM TESTPREPARED WHERE COL1 = 8589934614
--------------------------------------------------------------------
COL1 = 8589934614 should be 22 instead of 8589934614

How to repeat:
#include <iostream>

#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));

#ifdef UNIXODBC_USE
    char version[100];
    rc = SQLGetEnvAttr(henv, SQL_ATTR_UNIXODBC_VERSION, (SQLPOINTER)version, sizeof(version), 0);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_ENV, henv, rc));
        
    std::cout << "Version of unixODBC " << version << std::endl;
#endif
    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 TESTPREPARED", SQL_NTS);

    rc = SQLExecDirect(hstmt, (SQLCHAR *)"CREATE TABLE TESTPREPARED ( COL1 INTEGER )", SQL_NTS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    rc = SQLExecDirect(hstmt, (SQLCHAR *)"INSERT INTO TESTPREPARED VALUES (22)", SQL_NTS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    rc = SQLPrepare(hstmt, (SQLCHAR *)"SELECT COL1 FROM TESTPREPARED WHERE COL1 = ?", SQL_NTS);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    SQLSMALLINT type;
    SQLUINTEGER precision;
    SQLSMALLINT scale;
    SQLSMALLINT nullable;

    rc = SQLDescribeParam(hstmt, 1, &type, &precision, &scale, &nullable);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    if (type != SQL_INTEGER)
    {
        std::cout << "Unexpected type -> " << type << std::endl;
    }

    SQLINTEGER paramValue = 22;

    rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT, SQL_C_ULONG,SQL_INTEGER,0,0, (SQLPOINTER)&paramValue, sizeof(SQLINTEGER), 0);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    rc = SQLFreeStmt(hstmt,SQL_UNBIND) ;
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
        
    rc = SQLFreeStmt(hstmt,SQL_CLOSE) ;
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    rc = SQLExecute(hstmt);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    SQLINTEGER data;
    SQLINTEGER status;

    rc = SQLBindCol(hstmt, 1, SQL_C_ULONG, &data, sizeof(data), &status);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        return(PrintError(SQL_HANDLE_STMT, hstmt, rc));

    rc = SQLFetch(hstmt);
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
        PrintError(SQL_HANDLE_STMT, hstmt, rc);
    
    std::cout << "rc=" << rc << " data=" << data << 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:
It worth rechecking platform-dependent types

posted doc info

A note has been added to the 3.5.13 changelog.