//
// 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;
}