/*
程序功能：   使用SQLite的C API操作SQLite数据库，存取二进制数据.
             用来测试的文件都小于65535字节，没有测试更大的文件!
参考文档：   http://www.cntxk.com/CataNews/56/info8106.html
编译环境：   codeblock 10.05(svn 6906)
*/
#include <stdio.h>
#include <memory.h>
#include <sqlite3.h>

int main(int argc,char *argv[])
{
    sqlite3*    pDB=NULL;
    char*       pErrMsg;

    if(SQLITE_OK==sqlite3_open("./mydb.db",&pDB))
    {
        sqlite3_exec(pDB,"create table bList(fileName varchar(16) primary key, binData blob);",NULL,NULL,&pErrMsg);
        {
            char            buffer[65535];
            int             iLen=0;
            sqlite3_stmt*   stmt;

            {//存储二进制数据
                unsigned char hex[16]={0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15};
                sqlite3_prepare(pDB,"insert into bList values ('hex',?);",-1,&stmt,NULL);
                sqlite3_bind_blob(stmt,1,hex,16,NULL);
                sqlite3_step(stmt);
            }

            stmt=NULL;
            {//存储exe文件
                memset(buffer,0,65535);
                {
                    FILE* fp=fopen(argv[0],"rb");
                    iLen=fread(buffer,1,65535,fp);
                    fclose(fp);
                }
                sqlite3_prepare(pDB,"insert into bList values ('me.exe',?);",-1,&stmt,NULL);
                sqlite3_bind_blob(stmt,1,buffer,iLen,NULL);
                sqlite3_step(stmt);
            }

            stmt=NULL;
            {//存储普通c文档
                memset(buffer,0,65535);
                {
                    FILE* fp=fopen("../main.c","rb");
                    iLen=fread(buffer,1,65535,fp);
                    fclose(fp);
                }
                sqlite3_prepare(pDB,"insert into bList values ('main.txt',?);",-1,&stmt,NULL);
                sqlite3_bind_blob(stmt,1,buffer,iLen,NULL);
                sqlite3_step(stmt);
            }
            stmt=NULL;
            {//从数据库中读取txt文件数据
                char *data=NULL;
                memset(buffer,0,65535);
                sqlite3_prepare(pDB, "select binData from bList where fileName='main.txt';", -1, &stmt, 0);
                sqlite3_step(stmt);
                data= (char *)sqlite3_column_blob(stmt,0);//得到纪录中的BLOB字段
                iLen= sqlite3_column_bytes(stmt, 0);//得到字段中数据的长度
                memmove(buffer,data,iLen);
                printf("%s\n",buffer);
            }
        }
        sqlite3_close(pDB);
    }

    {
        puts("Press any key to exit...");
        getchar();
    }
    return 0;
}