C使用SQLite的例子
今天在折腾sqlite,写了C使用SQLite的例子,分享给大家
提示: 编译的使用使用gcc参数-l sqlite3, 如:gcc -l sqlite3 sql.c
#require <stdio.h>
#require <sqlite3.h>
int main(int argc, char **argv){
// 数据库文件名
char *dbname = "a.db";
// 数据库对象
sqlite3 *db;
// 返回值
int rc;
// 打开数据库
rc = sqlite3_open(dbname, &db);
// 检查错误
if( rc != SQLITE_OK ){
// 打印错误信息
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
// 关闭数据库
sqlite3_close(db);
return 1;
}
// 插入数据
char *sql = "insert into user(username, password) values(?, ?);";
// stmt对象
sqlite3_stmt *stmt_user_insert;
// OUT: Pointer to unused portion of zSql
const char *tail;
// 创建stmt对象
rc = sqlite3_prepare(db, sql, -1, &stmt_user_insert, &tail);
// 绑定参数
sqlite3_bind_text(stmt_user_insert, 1, "dotcoo2", -1, SQLITE_STATIC);
sqlite3_bind_text(stmt_user_insert, 2, "dotcoo2", -1, SQLITE_STATIC);
// 执行语句
rc = sqlite3_step(stmt_user_insert);
// 检查错误
if ( rc != SQLITE_DONE ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
// 获取最后插入的id
printf("insert ok id: %lld\n", sqlite3_last_insert_rowid(db));
// 重置stmt
sqlite3_reset(stmt_user_insert);
// 释放stmt
sqlite3_finalize(stmt_user_insert);
// 查询语句
char *sql_user_select = "select * from user";
// 查询的stmt
sqlite3_stmt *stmt_user_select;
// OUT: Pointer to unused portion of zSql
const char *tail_user_select;
// 创建stmt对象
rc = sqlite3_prepare(db, sql_user_select, -1, &stmt_user_select, NULL);
if ( rc != SQLITE_OK ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return 1;
}
// 读取数据
while (sqlite3_step(stmt_user_select) == SQLITE_ROW) {
int id = sqlite3_column_int(stmt_user_select, 0);
const char *username = sqlite3_column_text(stmt_user_select, 1);
const char *password = sqlite3_column_text(stmt_user_select, 2);
printf("row: %d, %s, %s\n", id, username, password);
}
// 重置stmt
sqlite3_reset(stmt_user_select);
// 释放stmt
sqlite3_finalize(stmt_user_select);
// 关闭数据库
sqlite3_close(db);
return 0;
}
创建表语句
CREATE TABLE book (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, price INTEGER, cover TEXT)