SQLite入門（二）讀寫二進位制資料

//讀二進位制資料的函式
BOOL OpenBinDataFile(BYTE **pBUf,UINT &len)
{
    if (pBUf == NULL)
    {
        return FALSE;
    }

    std::ifstream fs;
    fs.open(g_szBinDataPath,ios::binary);
    if (!fs.is_open())
    {
        cout<<"File:"<<g_szBinDataPath<<"not Open"<<endl;
        return FALSE;
    }

    fs.seekg(0,ios::end);
    len = fs.tellg();
    fs.seekg(0,ios::beg);

    *pBUf = new BYTE[len];
    if (NULL == *pBUf)
    {
        cout<<"記憶體分配不足"<<endl;
        return FALSE;
    }

    fs.read((char*)(*pBUf),len);

    fs.close();
    return TRUE;

}

//#define _USE_TRANSACTION //是否使用事務
#define _DB_TABLE_COUNTS 10 //100張表
#define LOOPSCOUNT 20 //記錄數
#define TABLEFLAG 10 //第十張表寫入1000個記錄
#define RESETEST
BOOL SqlitePerfWithUnicode()//Unicode
{

    cout<<"UNICODE TEST:"<<endl;

    BYTE *pBuf=NULL;
    UINT len = 0;
    if (!OpenBinDataFile(&pBuf,len))
    {
        cout<<"開啟二進位制資料失敗"<<endl;
        if (pBuf)
        {
            delete []pBuf;
        }
        return FALSE;
    }

    sqlite3 *db = NULL;
    TCHAR *errMsg = NULL;
    sqlite3_stmt *pstmt=NULL;
    TCHAR *psql = NULL;

    int nRet = -1,nRows=-1;
    nRet = sqlite3_open16(L"F:\\UnicodeTest.db",&db);
    if (nRet)
    {
        wcout<<L"無法開啟UnicodeTest資料庫:"<<sqlite3_errmsg16(db)<<endl;
        //sqlite3_free(errMsg);
        sqlite3_close(db);
        cin.get();

        return 1;
    }
    else
    {
        wcout<<L"成功開啟UnicodeTest.db"<<endl;

    }

    TCHAR szNum[100]={0};
    TCHAR table[100]={0};
    //std::vector<std::wstring> vecTableNames;
    std::vector<std::wstring> vecID;
    std::wstring wst;

    std::vector<std::wstring> vecInsertStr,vecReadStr;
    vecInsertStr.reserve(_DB_TABLE_COUNTS);
    vecReadStr.reserve(_DB_TABLE_COUNTS);

    for (int j=0;j<LOOPSCOUNT*10;++j)
    {
        _itot(j,szNum,10);
        vecID.push_back(szNum);
    }
    for (int i=0;i<_DB_TABLE_COUNTS;++i)//建立100張表
    {
        _itot(i,szNum,10);
        //vecID.push_back(szNum);//不夠
        wst = L"unicodetest_";
        wst += szNum;//表命名：unicodetest_x
        //vecTableNames.push_back(wst);
        psql = L"create table if not exists %s(id nchar(20) primary key,name blob)";
        _snwprintf_s(table,_countof(table),_TRUNCATE,psql,wst.c_str());
        //建立 _DB_TABLE_COUNTS 張表
        psql = table;
        sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
        sqlite3_step(pstmt);
        sqlite3_reset(pstmt);

        psql = L"insert into %s values(?,?);";
        _snwprintf_s(table,_countof(table),_TRUNCATE,psql,wst.c_str());
        //psql = table;
        vecInsertStr.push_back(table);

        psql = L"select id,name from %s;";
        _snwprintf_s(table,_countof(table),_TRUNCATE,psql,wst.c_str());
        //psql = table;
        vecReadStr.push_back(table);

    }
   
   

    //psql = L"create table if not exists unicodetest(id nchar(20) primary key,name blob)";
    //sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
    //sqlite3_step(pstmt);
    //sqlite3_reset(pstmt);
   

    DWORD dwBegin = GetTickCount();
#ifdef _USE_TRANSACTION
    sqlite3_exec(db,"begin transaction;",NULL,NULL,NULL);
#endif
    for(int j=1;j<_DB_TABLE_COUNTS;++j)
    {
        psql = const_cast<TCHAR*>(vecInsertStr[j].c_str());
#ifdef RESETEST
        sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
#endif
        for (int i=0;i<LOOPSCOUNT;++i)//寫
        {
#ifndef RESETEST
            sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
#endif
           
            sqlite3_bind_text16(pstmt,1,vecID[i].c_str(),-1,NULL);
            sqlite3_bind_blob(pstmt,2,pBuf,len,NULL);
            sqlite3_step(pstmt);
#ifdef RESETEST
            sqlite3_reset(pstmt);
#else
            sqlite3_finalize(pstmt);
#endif

                   
        }

    }

    psql = const_cast<TCHAR*>(vecInsertStr[0].c_str());//id為"0"的表寫入1000個記錄
    for (int i=0;i<LOOPSCOUNT*10;++i)//寫
    {
        sqlite3_prepare16(db,psql,-1,&pstmt,NULL);
        sqlite3_bind_text16(pstmt,1,vecID[i].c_str(),-1,NULL);//這裡也相應的擴大
        sqlite3_bind_blob(pstmt,2,pBuf,len,NULL);
        sqlite3_step(pstmt);

        sqlite3_reset(pstmt);       
    }

#ifdef _USE_TRANSACTION
    sqlite3_exec(db,"commit transaction;",NULL,NULL,NULL);
#endif
    DWORD dwEnd = GetTickCount();

    cout<<"共有 "<< _DB_TABLE_COUNTS <<" 張表，每張表"<<"插入 "<<LOOPSCOUNT<<" 份記錄（每份大小["<<len<<"B].）"<<endl;       
    cout<<"其中最後一張表插入"<<LOOPSCOUNT*10<<"記錄"<<endl;
    cout<<"總耗時："<<dwEnd-dwBegin<<endl;

   
    dwBegin = GetTickCount();
#ifdef _USE_TRANSACTION
    sqlite3_exec(db,"begin transaction;",NULL,NULL,NULL);
#endif
    for (int i=0;i<_DB_TABLE_COUNTS;++i)//讀
    {       
        sqlite3_prepare16(db,vecReadStr[i].c_str(),-1,&pstmt,NULL);
        nRet = sqlite3_step(pstmt);
        while(nRet == SQLITE_ROW)
        {
            sqlite3_column_text16(pstmt,0);
            sqlite3_column_blob(pstmt,1);//資料先不讀出
            nRet = sqlite3_step(pstmt);
        }
        sqlite3_reset(pstmt);
    }
#ifdef _USE_TRANSACTION
    sqlite3_exec(db,"commit transaction;",NULL,NULL,NULL);
#endif
    dwEnd = GetTickCount();

    cout<<"依次讀 "<<_DB_TABLE_COUNTS<<" 張表內的 "<<LOOPSCOUNT<<"份記錄,總耗時："<<dwEnd-dwBegin<<endl;

    sqlite3_reset(pstmt);   
    sqlite3_finalize(pstmt);
    sqlite3_close(db);
    if (pBuf)
    {
        delete []pBuf;
    }

    //cin.get();
    return TRUE;

}