Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    2

    Red face Unanswered: MySQL, ODBC, mediumblob: strange limit in ~1M

    MySQL 5.0.22 on Linux
    Connector: ODBC: 5.1 (Windows XP SP2)
    Option "FLAG_BIG_PACKETS" is ON: options=8

    I need write binary data (several megabytes) into field of type mediumblob.
    Cycle with SQLPutData:

    Code:
    for( int index=0 ; index<(4*1024) ; ++index ){
    SQLRETURN sqlres2 = SQLPutData(
    sqlhStatement//SQLHSTMT StatementHandle,
    , (SQLPOINTER)parameter2//SQLPOINTER DataPtr,
    , 256//SQLLEN StrLen_or_Ind););
    );
    if( sqlres2==SQL_ERROR ) throw sqlres2;
    printf( "=" );
    }
    This code causes SQL_ERROR
    But if I replace (4*1024) with (4*1024-1), no error occurs.
    Summary: the system can't let me send 1Mb data, but 1Mb-256b is transferred OK.

    Full sample here:
    Code:
    // OdbcTest.cpp : Defines the entry point for the console application.
    //
    
    #include "mystdincludes.h"
    using namespace std;
    
    //#include <sql.h>
    #include <sqlext.h>
    
    #include "stdafx.h"
    
    void showSqlReturn( SQLRETURN sqlres )
    {
    switch( sqlres ){
    case SQL_SUCCESS: printf( "SQL_SUCCESS\n" ); break;
    case SQL_SUCCESS_WITH_INFO: printf( "SQL_SUCCESS_WITH_INFO\n" ); break;
    case SQL_NO_DATA: printf( "SQL_NO_DATA\n" ); break;
    case SQL_NEED_DATA: printf( "SQL_NEED_DATA\n" ); break;
    case SQL_ERROR: printf( "SQL_ERROR\n" ); break;
    case SQL_INVALID_HANDLE: printf( "SQL_INVALID_HANDLE\n" ); break;
    default: printf( "Unknown SQLRETURN code 0x%x (%d)\n",sqlres,sqlres); break;
    
    }
    }
    
    
    int main( int argc, const char** args )
    {
    SQLRETURN sqlres = 0;
    SQLHANDLE sqlhEnvironment = SQL_NULL_HENV;
    SQLHANDLE sqlhConnection = SQL_NULL_HDBC;
    SQLHANDLE sqlhStatement = SQL_NULL_HSTMT;
    try{
    sqlres = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &sqlhEnvironment );
    showSqlReturn( sqlres );
    if( sqlres!=SQL_SUCCESS ) throw sqlres;
    
    
    sqlres = SQLSetEnvAttr( sqlhEnvironment, SQL_ATTR_ODBC_VERSION
    , (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER );
    showSqlReturn( sqlres );
    if( sqlres!=SQL_SUCCESS ) throw sqlres;
    
    
    sqlres = SQLAllocHandle( SQL_HANDLE_DBC, sqlhEnvironment, &sqlhConnection );
    showSqlReturn( sqlres );
    if( sqlres!=SQL_SUCCESS ) throw sqlres;
    
    SQLCHAR* connStrIn = new SQLCHAR[SQL_MAX_OPTION_STRING_LENGTH];
    strcpy( (char*)connStrIn, "\
    DRIVER={MySQL ODBC 5.1 Driver};\
    UID=fr;PWD=vinda;server=192.168.73.1;database=fromfr;option=8\
    " );
    SQLSMALLINT connStrOutLength = SQL_MAX_OPTION_STRING_LENGTH;
    SQLCHAR* connStrOut = new SQLCHAR[connStrOutLength];
    sqlres = SQLDriverConnectA(
    sqlhConnection//SQLHDBC ConnectionHandle, /* hdbc */
    , NULL//SQLHWND WindowHandle, /* hwnd */
    , connStrIn//SQLCHAR *InConnectionString, /* szConnStrIn */
    , strlen((char*)connStrIn)//SQLSMALLINT InConnectionStringLength, /* cbConnStrIn */
    , connStrOut//SQLCHAR *OutConnectionString, /* szConnStrOut */
    , connStrOutLength//SQLSMALLINT OutConnectionStringCapacity, /* cbConnStrOutMax */
    , &connStrOutLength//SQLSMALLINT *OutConnectionStringLengthPtr, /* pcbConnStrOut */
    , SQL_DRIVER_NOPROMPT//SQLUSMALLINT DriverCompletion /* fDriverCompletion */
    );
    showSqlReturn( sqlres );
    if( sqlres==SQL_ERROR ) throw sqlres;
    connStrOut[ connStrOutLength ]=0;
    printf( "Connection string returned: \"%s\"", connStrOut );
    
    
    sqlres = SQLAllocHandle( SQL_HANDLE_STMT, sqlhConnection, &sqlhStatement );
    showSqlReturn( sqlres );
    if( sqlres==SQL_ERROR ) throw sqlres;
    
    
    SQLCHAR* statementText = new SQLCHAR[1024];
    strcpy( (char*)statementText, "insert into test(`101`,`102`,`PAGE_IMAGES`) values( 1,?,?)" );
    sqlres = SQLPrepareA(
    sqlhStatement//SQLHSTMT StatementHandle, /* hstmt */
    , statementText//SQLCHAR *StatementText, /* szSqlStr */
    , strlen((char*)statementText)//SQLINTEGER TextLength /* cbSqlStr */
    );
    showSqlReturn( sqlres );
    if( sqlres==SQL_ERROR ) throw sqlres;
    
    long parameter1 = 0;
    SQLLEN parameter1Length = sizeof parameter1;
    sqlres = SQLBindParameter(
    sqlhStatement//SQLHSTMT StatementHandle,
    , 1//SQLUSMALLINT ParameterNumber,
    , SQL_PARAM_INPUT//SQLSMALLINT InputOutputType,
    , SQL_C_SLONG//SQLSMALLINT ValueType,
    , SQL_BIT//SQLSMALLINT ParameterType,
    , 0//SQLULEN ColumnSize,
    , 0//SQLSMALLINT DecimalDigits,
    , (SQLPOINTER)&parameter1//SQLPOINTER ParameterValuePtr,
    , 0//SQLINTEGER BufferLength,
    , &parameter1Length//SQLLEN * StrLen_or_IndPtr);
    );
    showSqlReturn( sqlres );
    if( sqlres==SQL_ERROR ) throw sqlres;
    
    char* parameter2 = new char[1024];
    for( int index=0 ; index<1024 ; ++index ) parameter2[index]='A'+index%26;
    SQLLEN parameter2Indicator = SQL_DATA_AT_EXEC;
    
    sqlres = SQLBindParameter(
    sqlhStatement//SQLHSTMT StatementHandle,
    , 2//SQLUSMALLINT ParameterNumber,
    , SQL_PARAM_INPUT//SQLSMALLINT InputOutputType,
    , SQL_C_BINARY//SQLSMALLINT ValueType,
    , SQL_VARBINARY//SQLSMALLINT ParameterType,
    , 10*1024*1024//SQLULEN ColumnSize,
    , 0//SQLSMALLINT DecimalDigits,
    , (SQLPOINTER)222//SQLPOINTER ParameterValuePtr,
    , 3*1024*1024//SQLINTEGER BufferLength,
    , &parameter2Indicator//SQLLEN * StrLen_or_IndPtr);
    );
    showSqlReturn( sqlres );
    if( sqlres==SQL_ERROR ) throw sqlres;
    
    
    sqlres = SQLExecute( sqlhStatement );
    
    if( sqlres==SQL_NEED_DATA ){
    SQLPOINTER sqlp = 0;
    sqlres = SQLParamData(
    sqlhStatement//SQLHSTMT StatementHandle,
    , &sqlp//SQLPOINTER * ValuePtrPtr
    );
    showSqlReturn( sqlres );
    if( sqlres==SQL_ERROR ) throw sqlres;
    if( sqlres==SQL_NEED_DATA && sqlp==(SQLPOINTER)222 ){
    for( int index=0 ; index<(4*1024) ; ++index ){
    SQLRETURN sqlres2 = SQLPutData(
    sqlhStatement//SQLHSTMT StatementHandle,
    , (SQLPOINTER)parameter2//SQLPOINTER DataPtr,
    , 256//SQLLEN StrLen_or_Ind););
    );
    if( sqlres2==SQL_ERROR ) throw sqlres2;
    printf( "=" );
    }
    }
    }
    SQLPOINTER sqlp = 0;
    sqlres = SQLParamData(
    sqlhStatement//SQLHSTMT StatementHandle,
    , &sqlp//SQLPOINTER * ValuePtrPtr
    );
    showSqlReturn( sqlres );
    if( sqlres==SQL_ERROR ) throw sqlres;
    
    }catch( const char* E ){
    printf( "Error: %s\n", E );
    return 1;
    }catch( const string& E ){
    printf( "Error: %s\n", E.c_str() );
    return 2;
    }catch( const wchar_t* E ){
    printf( "Error: %S\n", E );
    return 3;
    }catch( SQLRETURN E ){
    printf( "Exception handling...\nSql Error: 0x%x (%ld)\n", E, E );
    SQLRETURN sqlres2 = SQL_SUCCESS;
    SQLCHAR* sqlstate = new SQLCHAR[ 16 ];
    SQLINTEGER nativeError = 0;
    SQLSMALLINT bufferLength = 1024;
    SQLCHAR* message = new SQLCHAR[ bufferLength ];
    SQLSMALLINT textLength = 0;
    
    sqlres2 = SQLGetDiagRecA
    ( SQL_HANDLE_DBC//SQL_HANDLE_ENV//SQLSMALLINT HandleType,
    , sqlhConnection//sqlhEnvironment//SQLHANDLE Handle,
    , 1//SQLSMALLINT RecNumber,
    , sqlstate//SQLCHAR * SQLState,
    , &nativeError//SQLINTEGER * NativeErrorPtr,
    , message//SQLCHAR * MessageText,
    , bufferLength//SQLSMALLINT BufferLength,
    , &textLength//SQLSMALLINT * TextLengthPtr
    );
    showSqlReturn( sqlres2 );
    if( sqlres2!=SQL_SUCCESS ) return 3;
    printf( "SQL State is: %s\n", &sqlstate );
    printf( "Message is: %s\n", message );
    
    return 3;
    }catch( ... ){
    printf( "Error: Unknown.\n" );
    return 10;
    }
    
    return 0;
    }

  2. #2
    Join Date
    May 2008
    Posts
    2

    Smile I figure out the problem

    by adding into /etc/my.cnf into section [mysqld] the string:
    Code:
    set-variable = max_allowed_packet=16M

  3. #3
    Join Date
    Feb 2009
    Posts
    1
    Well, you helped me on how to connect C++ & SQL and I'll solve your doubt

    When you connect and then send the data to C++ & viceversa, you really send 1MB (with your piece of code). Remember that the 0 is also a number, so when you say that you're sending 1MB (1024) through the socket you're really sending from 0 bit to 1023 bits (= 1024 = 1MB) so instead of that, if I were you I would add a comment above that line to help you to remember it.

    Perhaps other languages allow you to write 1024 without no errors, not sure if Delphi or Pascal does it.

    Regards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •