C使用SQLite的例子

2013-09-18 21:17  3246人阅读  评论 (0)
Tags: csqlite

今天在折腾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)
豫ICP备09035262号-1