sqlite3学习

// main.cpp

// sqlite

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

#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 ", 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 ", errmsg);

}

// 插入数据

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

nullptr, &errmsg);

if (ret != SQLITE_OK)

{

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

}

ret = sqlite3_exec(hSqlite,"insert into mytables values(2, 'des2', 'name2');", nullptr,

nullptr, &errmsg);

if (ret != SQLITE_OK)

{

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

}

ret = sqlite3_exec(hSqlite,"insert into mytables values(3, 'des3', 'name3');", nullptr,

nullptr, &errmsg);

if (ret != SQLITE_OK)

{

printf("err: %s ", 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("----二进制查询结果--- ");

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 ", id, text.c_str(), name.c_str());

}

sqlite3_finalize(read);

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

// 查询数据

ret = sqlite3_exec(hSqlite,"select * from mytables;", loadMyInfo,

(void*)"param", &errmsg);

if (ret != SQLITE_OK)

{

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

}

// 不使用回调的查询

char** dbResult;

int row, column;

ret = sqlite3_get_table(hSqlite, "select * from mytables;", &dbResult, &row, &column, &errmsg);

if (ret == SQLITE_OK)

{

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

printf("查到%d条结果 ", 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(" ");

}

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

}

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 ", (char*)param);

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

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

{

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

}

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

return 0;

}