SQLite 테스트...

64bit SQLite 라이브러리를 테스트 하려고 했는데 잘 되지 않아서 우선 32bit 라이브러리를 테스트 해 보았다.

동적 라이브러리와 정적 라이브러리를 테스트 해 보았는데 문제가 없었다.

아래는 C++ Builder 에서 테스트한 소스이다.

#include "SQLite3.h"

#pragma link "sqlite3.lib"
//#pragma link "SQLite_Static.lib"

void TfrmMain::Test()
{
    sqlite3*         pSQLite3     = NULL;
    sqlite3_stmt*    pStmt        = NULL;
    char*            szErrMsg     = NULL;
    const char*      szErr;

    String strMsg;

    // Open Database
    int rst = sqlite3_open( "test.db", &pSQLite3 );

    memProc->Lines->Clear();
    memProc->Lines->Append( L"Test Start...\n" );

    if ( rst )
    {
        strMsg.sprintf( L"     Can't open database: %s\n", sqlite3_errmsg( pSQLite3 ) );
        memProc->Lines->Append( strMsg );
    }
    else
    {
        memProc->Lines->Append( L"     Database (test.db) Opened...\n" );

        AnsiString strQuery;

        // 테이블 생성
        strQuery = "CREATE TABLE IF NOT EXISTS TestTable ("
            "     name       VARCHAR(20)"
            "    ,age        int"
            "    ,country    VARCHAR(20)"
            ")";
        rst = sqlite3_exec(pSQLite3, strQuery.c_str(), NULL, 0, &szErrMsg);

        if ( rst == SQLITE_OK )
        {
            memProc->Lines->Append( L"     Test Table Created... OK\n" );
        }
        else
        {
            memProc->Lines->Append( L"     Test Table Created... FAILE\n" );

            strMsg.sprintf( L"         Error %d : %s", sqlite3_errcode( pSQLite3 ), sqlite3_errmsg16( pSQLite3 ) );
            memProc->Lines->Append( strMsg );
        }

        // Inserting...
        memProc->Lines->Append( L"     Inserting... \n" );
        strQuery = "INSERT INTO TestTable ( name, age, country) VALUES ( ?, ?, ? );";
        if ( sqlite3_prepare_v2( pSQLite3, strQuery.c_str(), strQuery.Length(), &pStmt, &szErr ) == SQLITE_OK )
        {
            for ( int i = 0; i < 10; ++i )
            {
                AnsiString strName;
                strName.sprintf( "name%d", i );

                sqlite3_bind_text( pStmt, 1, strName.c_str(), strName.Length(), NULL );
                sqlite3_bind_int(  pStmt, 2, i );

                AnsiString strCountry;
                sqlite3_bind_text( pStmt, 3, strCountry.c_str(), strCountry.Length(), NULL );

                sqlite3_step( pStmt );

                int nLast = sqlite3_last_insert_rowid( pSQLite3 );

                strMsg.sprintf( L"         Inserted... OK (%d)\n", nLast );
                memProc->Lines->Append( strMsg );

                sqlite3_reset( pStmt );
            }

            sqlite3_finalize( pStmt );
        }
        else
        {
            memProc->Lines->Append( L"     Inserting... FAILE\n" );

            strMsg.sprintf( L"         Error %d : %s", sqlite3_errcode( pSQLite3 ), sqlite3_errmsg16( pSQLite3 ) );
            memProc->Lines->Append( strMsg );
        }

        memProc->Lines->Append( L"     Selecting... \n" );
        strQuery = "SELECT COUNT(*) FROM TestTable;";
        if ( sqlite3_prepare_v2( pSQLite3, strQuery.c_str(), strQuery.Length(), &pStmt, &szErr ) == SQLITE_OK )
        {
            int nRow = sqlite3_data_count( pStmt );
            int nCol = sqlite3_column_count( pStmt );

            const char* col1name = sqlite3_column_name( pStmt, 0 );

            int nRowCnt = 0;
            while ( sqlite3_step( pStmt ) == SQLITE_ROW )
            {
                ++nRowCnt;

                int Count = sqlite3_column_int( pStmt, 0 );

                strMsg.sprintf( L"         Row Count ... %d\n", Count );
                memProc->Lines->Append( strMsg );
            }
        }
        else
        {
            memProc->Lines->Append( L"     Selecting... FAILE\n" );

            strMsg.sprintf( L"         Error %d : %s", sqlite3_errcode( pSQLite3 ), sqlite3_errmsg16( pSQLite3 ) );
            memProc->Lines->Append( strMsg );
        }

        // Updating...
        memProc->Lines->Append( L"     Updating... \n" );
        strQuery = "UPDATE TestTable SET country = :country WHERE age < :age;";
        if ( sqlite3_prepare_v2( pSQLite3, strQuery.c_str(), strQuery.Length(), &pStmt, &szErr ) == SQLITE_OK )
        {
            sqlite3_bind_text( pStmt, 1, "KOREA", 5, NULL );
            sqlite3_bind_int( pStmt, 2, 5 );

            sqlite3_step( pStmt );
            int nUpdated = sqlite3_changes( pSQLite3 );

            strMsg.sprintf( L"         Updated... %d Rows", nUpdated );
            memProc->Lines->Append( strMsg );
        }
        else
        {
            memProc->Lines->Append( L"     Updating... FAILE\n" );

            strMsg.sprintf( L"         Error %d : %s", sqlite3_errcode( pSQLite3 ), sqlite3_errmsg16( pSQLite3 ) );
            memProc->Lines->Append( strMsg );
        }

        // Deleting...
        memProc->Lines->Append( L"     Deleting... \n" );
        strQuery = "DELETE FROM TestTable WHERE age < :age;";
        if ( sqlite3_prepare_v2( pSQLite3, strQuery.c_str(), strQuery.Length(), &pStmt, &szErr ) == SQLITE_OK )
        {
            sqlite3_bind_int( pStmt, 1, 2 );

            sqlite3_step( pStmt );
            int nUpdated = sqlite3_changes( pSQLite3 );

            strMsg.sprintf( L"         Deleted... %d Rows", nUpdated );
            memProc->Lines->Append( strMsg );
        }
        else
        {
            memProc->Lines->Append( L"     Updating... FAILE\n" );

            strMsg.sprintf( L"         Error %d : %s", sqlite3_errcode( pSQLite3 ), sqlite3_errmsg16( pSQLite3 ) );
            memProc->Lines->Append( strMsg );
        }

        // 테이블 삭제
        strQuery = "DROP TABLE IF EXISTS TestTable;";
        rst = sqlite3_exec(pSQLite3, strQuery.c_str(), NULL, 0, &szErrMsg);

        if ( rst == SQLITE_OK )
        {
            memProc->Lines->Append( L"     Test Table Deleted... OK\n" );
        }
        else
        {
            memProc->Lines->Append( L"     Test Table Deleted... FAILE\n" );

            strMsg.sprintf( L"         Error %d : %s", sqlite3_errcode( pSQLite3 ), sqlite3_errmsg16( pSQLite3 ) );
            memProc->Lines->Append( strMsg );
        }

        sqlite3_close( pSQLite3 );
        memProc->Lines->Append( L"     Database (test.db) Closed...\n" );

        pSQLite3 = NULL;
    }

    memProc->Lines->Append( L"Test End...\n" );
}
//---------------------------------------------------------------------------

// Output
Test Start...
     Database (test.db) Opened...
     Test Table Created... OK
     Inserting... 
         Inserted... OK (1)
         Inserted... OK (2)
         Inserted... OK (3)
         Inserted... OK (4)
         Inserted... OK (5)
         Inserted... OK (6)
         Inserted... OK (7)
         Inserted... OK (8)
         Inserted... OK (9)
         Inserted... OK (10)
     Selecting... 
         Row Count ... 10
     Updating... 
         Updated... 5 Rows
     Deleting... 
         Deleted... 2 Rows
     Test Table Deleted... OK
     Database (test.db) Closed...
Test End...

위의 테스트 소스로 64bit 에서도 테스트를 해 보아야겠다.

테스트 프로젝트

SQLiteTest.7z
Original Post : http://neodreamer-dev.tistory.com/272