sqlite学习总结

来源：互联网发布：淘宝发货物流单号修改编辑：程序博客网时间：2024/06/05 14:46
////  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, nullptr, &errmsg);    if (ret != SQLITE_OK)    {        printf("err: %s\n", errmsg);    }        // 创建表    ret = sqlite3_exec(hSqlite,"create table mytables (id integer primary key, value text, name text);",nullptr, nullptr, &errmsg);    if (ret != SQLITE_OK)    {        printf("err: %s\n", errmsg);    }        // 插入数据    ret = sqlite3_exec(hSqlite,"insert into mytables values(1, 'des1', 'name1');",nullptr,                 nullptr, &errmsg);    if (ret != SQLITE_OK)    {        printf("err: %s\n", errmsg);    }    ret = sqlite3_exec(hSqlite,"insert into mytables values(2, 'des2', 'name2');",nullptr,                       nullptr, &errmsg);    if (ret != SQLITE_OK)    {        printf("err: %s\n", errmsg);    }    ret = sqlite3_exec(hSqlite,"insert into mytables values(3, 'des3', 'name3');",nullptr,                       nullptr, &errmsg);    if (ret != SQLITE_OK)    {        printf("err: %s\n", 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(), -1, SQLITE_TRANSIENT);// 绑定数据    //sqlite3_bind_int(write, 1, 6);    //sqlite3_bind_text(write, 2, value.c_str(), -1, SQLITE_TRANSIENT);//绑定数据    //sqlite3_bind_blob(write, 3, value.c_str(), -1, SQLITE_TRANSIENT);//绑定数据    sqlite3_step(write);            // 执行    sqlite3_reset(write);    sqlite3_finalize(write);        // 释放        // 读出二进制    sqlite3_stmt* read;    sqlite3_prepare(hSqlite, "select * from mytables", -1, &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-%s\n", id, text.c_str(), name.c_str());    }    sqlite3_finalize(read);    printf("----二进制查询结果---\n");            // 查询数据    ret = sqlite3_exec(hSqlite,"select * from mytables;",loadMyInfo,                       (void*)"param", &errmsg);    if (ret != SQLITE_OK)    {        printf("err: %s\n", errmsg);    }        // 不使用回调的查询    char** dbResult;    int row, column;    ret = sqlite3_get_table(hSqlite, "select * from mytables;", &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,int column, char** columnValue, char** columnName){    printf("param: %s\n", (char*)param);    printf("**********\n");    for (int i = 0; i< column; i++)    {        printf("[%s]：%s,", columnName[i], columnValue[i]);    }    printf("\n**********\n");    return 0;}
0 0