ODBC 예제

#include 
#include 

#include 

#include 
#include 

#define MAX_STR_LEN 1024
#define MAX_DATA_LEN 10000

SQLHENV     hEnv        = SQL_NULL_HENV;
SQLHDBC     hDbc        = SQL_NULL_HDBC;

SQLCHAR *   szConnStrIn = "DRIVER=MySQL ODBC 3.51 Driver;UID=pharvey;PWD=pharvey;Server=192.168.0.16;Port=3306;Database=test;Option=1;";

void do_errors( SQLRETURN nReturn, int nLine, SQLSMALLINT nHandleType, SQLHANDLE h )
{
    if ( h )
    {
        SQLSMALLINT nRec = 1;
        SQLCHAR     szSQLState[6];
        SQLINTEGER  nNative;
        SQLCHAR     szMessage[MAX_STR_LEN];
        SQLSMALLINT nMessage;

        *szSQLState = '\0';
        *szMessage  = '\0';

        while ( SQL_SUCCEEDED( SQLGetDiagRec( nHandleType,
                                              h,
                                              nRec,
                                              szSQLState,
                                              &nNative,
                                              szMessage,
                                              MAX_STR_LEN,
                                              &nMessage ) ) )
        {
            szSQLState[5]               = '\0';
            szMessage[MAX_STR_LEN - 1]  = '\0';

            printf( "[%s][%d][%s] %ld %s\n", __FILE__, nLine, szSQLState, nNative, szMessage );
            nRec++;

            *szSQLState = '\0';
            *szMessage  = '\0';
        }
    }

    if ( !SQL_SUCCEEDED( nReturn ) )
    {
      printf( "[%s][%d] test failed\n", __FILE__, nLine );
      exit( 1 );
    }
}

void do_init()
{
    SQLRETURN   nReturn;
    SQLCHAR     szConnStrOut[MAX_STR_LEN];
    SQLSMALLINT nConnStrOut;

    nReturn = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HENV, &hEnv );
    do_errors( nReturn, __LINE__, SQL_HANDLE_ENV, hEnv );

    nReturn = SQLSetEnvAttr( hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0 );
    do_errors( nReturn, __LINE__, SQL_HANDLE_ENV, hEnv );

    nReturn = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc );
    do_errors( nReturn, __LINE__, SQL_HANDLE_ENV, hEnv );

    nReturn = SQLDriverConnect( hDbc, NULL, szConnStrIn, MAX_STR_LEN, szConnStrOut, MAX_STR_LEN, &nConnStrOut, SQL_DRIVER_NOPROMPT );
    do_errors( nReturn, __LINE__, SQL_HANDLE_DBC, hDbc );

    nReturn = SQLSetConnectAttr( hDbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_ON, 0 );
    do_errors( nReturn, __LINE__, SQL_HANDLE_DBC, hDbc );
}

void do_fini()
{
    SQLRETURN   nReturn;

    if ( hDbc )
    {
        nReturn = SQLFreeHandle( SQL_HANDLE_DBC, hDbc );
        hDbc = SQL_NULL_HDBC;
    }

    if ( hEnv )
    {
        nReturn = SQLFreeHandle( SQL_HANDLE_ENV, hEnv );
        hEnv = SQL_NULL_HENV;
    }
}

void do_create()
{
    SQLHSTMT    hStmt;
    SQLRETURN   nReturn;

    nReturn = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
    do_errors( nReturn, __LINE__, SQL_HANDLE_DBC, hDbc );

    nReturn = SQLExecDirect( hStmt, "CREATE TABLE `tb3194` (`id` int(11) NOT NULL default '0',`image` longblob) TYPE=MyISAM", SQL_NTS );
    do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

    nReturn = SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
}

void do_drop()
{
    SQLHSTMT    hStmt;
    SQLRETURN   nReturn;

    nReturn = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
    do_errors( nReturn, __LINE__, SQL_HANDLE_DBC, hDbc );

    nReturn = SQLExecDirect( hStmt, "DROP TABLE IF EXISTS tb3194", SQL_NTS );
    do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

    nReturn = SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
}

void do_insert()
{
    SQLHSTMT    hStmt;
    SQLRETURN   nReturn;

    SQLINTEGER  nIDInd      = 0;
    SQLINTEGER  nImageInd   = 0;

    SQLUINTEGER nID     = 1;            /*!< image id                           */ 
    SQLINTEGER  nParam  = 2;            /*!< extra info for image param         */
    SQLPOINTER  pParam;                 /*!< extra info from image param        */
    SQLCHAR     pData[MAX_DATA_LEN];    /*!< read buffer                        */
    SQLINTEGER  nRead;                  /*!< number of bytes read into pData    */
    FILE *      hFile;

    hFile = fopen( "in.jpg", "rb" );
    if ( !hFile )
    {
      printf( "[%s][%d] Could not open image file.\n", __FILE__, __LINE__ );
      exit( 1 );
    }

    nReturn = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
    do_errors( nReturn, __LINE__, SQL_HANDLE_DBC, hDbc );

    nReturn = SQLBindParameter( hStmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0, &nID, 0, &nIDInd );
    do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

    nReturn = SQLBindParameter( hStmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY, SQL_LONGVARBINARY, 0, 0, (SQLPOINTER)nParam, 0, &nImageInd );
    do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

    nImageInd = SQL_LEN_DATA_AT_EXEC(0);

    nReturn = SQLPrepare( hStmt, "INSERT INTO tb3194 (id, image) VALUES (?, ?)", SQL_NTS );
    do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

    nReturn = SQLExecute( hStmt );
    if ( nReturn != SQL_NEED_DATA )
        do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

    while ( nReturn == SQL_NEED_DATA ) 
    {
        nReturn = SQLParamData( hStmt, &pParam );
        if ( nReturn == SQL_NEED_DATA && (SQLINTEGER)pParam == nParam ) 
        {
            while ( 1 )
            {
                if ( feof( hFile ) )
                    break;
                nRead = fread( pData, 1, MAX_DATA_LEN, hFile );
                do_errors( SQLPutData( hStmt, pData, nRead ), __LINE__, SQL_HANDLE_STMT, hStmt );
            }
        }
    }

    nReturn = SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
    fclose( hFile );
}

void do_select()
{
    SQLHSTMT    hStmt;
    SQLRETURN   nReturn;
    SQLCHAR     pData[MAX_DATA_LEN];    /*!< write buffer                       */
    SQLINTEGER  nLen;                   /*!< number of bytes remaining in col   */
    SQLINTEGER  nLenToWrite;            /*!< number of bytes to write           */
    SQLINTEGER  nWrite;                 /*!< number of bytes written to file    */
    FILE *      hFile;

    hFile = fopen( "out.jpg", "w+b" );
    if ( !hFile )
    {
      printf( "[%s][%d] Could not create image file.\n", __FILE__, __LINE__ );
      exit( 1 );
    }

    nReturn = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt );
    do_errors( nReturn, __LINE__, SQL_HANDLE_DBC, hDbc );

    nReturn = SQLExecDirect( hStmt, "SELECT * FROM tb3194", SQL_NTS );
    do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

    nReturn = SQLFetch( hStmt );
    if ( nReturn != SQL_NO_DATA )
    {
        do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

        while ( SQL_NO_DATA != (nReturn = SQLGetData( hStmt, 2, SQL_C_BINARY, pData, MAX_DATA_LEN, &nLen )) )
        {
            /* ignore warnings as there will be many truncated warnings - which we assume */
            if ( nReturn != SQL_SUCCESS_WITH_INFO )
                do_errors( nReturn, __LINE__, SQL_HANDLE_STMT, hStmt );

            nLenToWrite = (nLen > MAX_DATA_LEN) || (nLen == SQL_NO_TOTAL) ? MAX_DATA_LEN : nLen;
/* printf( "[PAH][%s][%d] nLen = %ld nLenToWrite = %ld \n", __FILE__, __LINE__, nLen, nLenToWrite ); */
            nWrite = fwrite( pData, 1, nLenToWrite, hFile );
            if ( nWrite != nLenToWrite )
            {
              printf( "[%s][%d] Could not write to file.\n", __FILE__, __LINE__ );
              exit( 1 );
            }
        }
    }
    else
      printf( "[%s][%d] No data\n", __FILE__, __LINE__ );

    nReturn = SQLFreeHandle( SQL_HANDLE_STMT, hStmt );
    fclose( hFile );
}


int main(int argc, char **argv)
{
    printf( "[%s][%d] Init...\n", __FILE__, __LINE__ );
    do_init();

    printf( "[%s][%d] Drop...\n", __FILE__, __LINE__ );
    do_drop();

    printf( "[%s][%d] Create...\n", __FILE__, __LINE__ );
    do_create();

    printf( "[%s][%d] Insert...\n", __FILE__, __LINE__ );
    do_insert();

    printf( "[%s][%d] Select...\n", __FILE__, __LINE__ );
    do_select();

    printf( "[%s][%d] Fini...\n", __FILE__, __LINE__ );
    do_fini();

    return 0;
}

인터넷에서 그냥 보이는 거라 정확한 출처는 알 수 없습니다.