sqlite学习总结

//

//  main.cpp

//  sqlite

//

//  Created by yuer on 14-9-13.

//  Copyright (c) 2014年 yuer. All rights reserved.

//




#include <iostream>

#include <stdio.h>

#include <stdlib.h>

using namespace std;

#include "sql/sqlite3.h"




int loadMyInfo(void * param,int column,char** columnValue,char** columnName);




int main(int argc,const char * argv[])

{

    sqlite3*    hSqlite = nullptr;

    char*       errmsg  = nullptr;// 错误信息

    

    // 打开数据库，没有则创建test.db

    int ret = sqlite3_open("test.db",&hSqlite);

    if (ret != SQLITE_OK)

    {

        cout<<"Open sqlite is failed!"<<endl;

    }

    

    // 删除表

    ret = sqlite3_exec(hSqlite,"DROP table mytables;",nullptr,&errmsg);

    if (ret != SQLITE_OK)

    {

        printf("err: %sn",errmsg);

    }

    

    // 创建表

    ret = sqlite3_exec(hSqlite,"create table mytables (id integer primary key,value text,name text);",errmsg);

    }

    

    // 插入数据

    ret = sqlite3_exec(hSqlite,"insert into mytables values(1,'des1','name1');",errmsg);

    }

    ret = sqlite3_exec(hSqlite,"insert into mytables values(2,'des2','name2');","insert into mytables values(3,'des3','name3');",errmsg);

    }

    

    // 二进制写入

    sqlite3_stmt* write;

    sqlite3_prepare_v2(hSqlite,"insert into ? values(18,'test','test');",-1,&write,0);

    std::string value ="10";

    std::string dbName ="mytables";

    sqlite3_bind_blob(write,1,dbName.c_str(),SQLITE_TRANSIENT);// 绑定数据

    //sqlite3_bind_int(write,6);

    //sqlite3_bind_text(write,2,value.c_str(),SQLITE_TRANSIENT);//绑定数据

    //sqlite3_bind_blob(write,3,SQLITE_TRANSIENT);//绑定数据

    sqlite3_step(write);            // 执行

    sqlite3_reset(write);

    sqlite3_finalize(write);        // 释放

    

    // 读出二进制

    sqlite3_stmt* read;

    sqlite3_prepare(hSqlite,"select * from mytables",&read,0);

    printf("----二进制查询结果---n");

    while (sqlite3_step(read) ==SQLITE_ROW)

    {

        int id = sqlite3_column_int(read,0);

        const void * pText =sqlite3_column_blob(read,1);

        int len = sqlite3_column_bytes(read,1);

        std::string text((char*)pText,len);

        const void * pName =sqlite3_column_blob(read,2);

        int nameLen = sqlite3_column_bytes(read,2);

        std::string name((char*)pName,nameLen);

        printf("%d-%s-%sn",id,text.c_str(),name.c_str());

    }

    sqlite3_finalize(read);

    printf("----二进制查询结果---n");

    

    

    // 查询数据

    ret = sqlite3_exec(hSqlite,"select * from mytables;",loadMyInfo,(void*)"param",errmsg);

    }

    

    // 不使用回调的查询

    char** dbResult;

    int row,column;

    ret = sqlite3_get_table(hSqlite,&dbResult,&row,&column,&errmsg);

    if (ret == SQLITE_OK)

    {

        printf("----<>-----n");

        printf("查到%d条结果n",row);

        int index = column;

        for (int i =0; i< row; i++)

        {

            for (int j =0; j< column; j++)

            {

                printf("[%s]:%s,",dbResult[j],dbResult[index++]);

            }

            printf("n");

        }

        printf("----<>-----n");

    }

    sqlite3_free_table(dbResult);//释放查询结果

    sqlite3_errcode(hSqlite);

    // 关闭数据库

    sqlite3_close(hSqlite);

    return 0;

}







// 每查到一条结果就调用一次

int loadMyInfo(void * param,char** columnName)

{

    printf("param: %sn",(char*)param);

    printf("**********n");




    for (int i = 0; i< column; i++)

    {

        printf("[%s]：%s,columnName[i],columnValue[i]);

    }

    printf("n**********n");

    return 0;

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!