C++访问SQLite

来源：互联网发布：光大证券 mac 编辑：程序博客网时间：2024/05/21 19:41

1.安装SQLite

sudo apt-get install sqlite3

2.安装SQLite开发包

sudo apt-get install libsqlite3-dev

3.创建表

$sqlite3 Acceptor_0.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE maxPromised(id BIGINT auto_increment primary key, lock_name varchar(128), instance_no BIGINT, ballot_no BIGINT);

sqlite> CREATE TABLE maxAccepted(id BIGINT auto_increment primary key, lock_name varchar(128), instance_no BIGINT, ballot_no BIGINT, usid char(37), val_len int, val_str varchar(128));

4.代码片段

#include <sqlite3.h>sqlite3 *conn;char * sErrMsg = 0;//databasestring dbfile = Utils::string_format("%s/Acceptor_%d.db",LOG_DIR, id);int result = sqlite3_open_v2( dbfile.c_str(), &conn, SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, NULL );//sqlite3_exec(conn, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);//sqlite3_exec(conn, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);if (result) {LOGGER(ERROR)<<"Can't open database: "<<sqlite3_errmsg(conn);exit(0);}//插入//permanent mediasqlite3_prepare(acceptor->conn, "INSERT INTO maxAccepted(lock_name, instance_no, ballot_no, usid, val_len, val_str) VALUES(?, ?, ?, ?, ?, NULL);", -1, &stmt, &zTail      );sqlite3_bind_text(stmt, 1, field, -1, SQLITE_STATIC);sqlite3_bind_int64(stmt,2, prepare->instanceNo);sqlite3_bind_int64(stmt,3, accRec->bno);sqlite3_bind_text(stmt, 4, accRec->usid, SessionId::size, SQLITE_STATIC);sqlite3_bind_int(stmt,  5, accRec->vallen);//sqlite3_bind_text(stmt, 6, accRec->value);int result = sqlite3_step(stmt);if (result != SQLITE_DONE) {LOGGER(ERROR)<<sqlite3_errmsg(acceptor->conn);}sqlite3_finalize(stmt);//更新sqlite3_prepare(acceptor->conn, "UPDATE maxPromised set ballot_no = ? WHERE lock_name = ? AND instance_no = ?;", -1, &stmt, &zTail      );sqlite3_bind_int64(stmt,1, prepare->ballotNo);sqlite3_bind_text(stmt, 2, field, -1, SQLITE_STATIC);sqlite3_bind_int64(stmt,3, prepare->instanceNo);int result = sqlite3_step(stmt);if (result != SQLITE_DONE) {LOGGER(ERROR)<<sqlite3_errmsg(acceptor->conn);}sqlite3_finalize(stmt);//查询const char* field = NULL;uint64_t iid = 0;uint64_t bno = 0;sqlite3_stmt * stmt; const char *zTail; //recover maxAccepted//permanent mediasqlite3_prepare(conn, "SELECT * FROM maxAccepted",-1, &stmt, &zTail );while ( (result = sqlite3_step(stmt)) == SQLITE_ROW) {field = (const char*)sqlite3_column_text(stmt,1);iid   = sqlite3_column_int64(stmt,2);bno   = sqlite3_column_int64(stmt,3);printf("%s %lu %lu\n", field, iid, bno);}sqlite3_finalize(stmt);//close databasesqlite3_close(conn);

参考资料：

1.SQLite Embedded Database http://www.yolinux.com/TUTORIALS/SQLite.html

2.How do I improve the performance of SQLite? http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite