原 linux下C语言编程操作数据库（sqlite3） (转载）

网址：

https://www.sqlite.org/download.html
https://blog.csdn.net/zouleideboke/article/details/73649886
http://blog.chinaunix.net/uid-20680669-id-3124348.html

sqlite3的图片的（二进制数据）存取操作
https://www.cnblogs.com/liuroy/p/5616327.html

linux下C语言编程操作数据库（sqlite3）

前言：C语言中通过调用 sqlite 的函数接口来实现对数据库的管理(创建数据库、创建表格、插入数据、查询、数据、删除数据等)，掌握sqlite数据库的语法，以及sqlite提供的函数接口,那么在linux下C语言编程操作数据库就变得简单了。

Centos系统一般默认是没有安装sqlite3数据库的，所以我们要到其官网下载，我下载的版本是 sqlite-3.6.16.tar.gz

wget http://www.sqlite.org/sqlite-3.6.16.tar.gz

[zoulei@CentOS sqlite-3.6.16]$ ./configure
[zoulei@CentOS sqlite-3.6.16]$ make
[zoulei@CentOS sqlite-3.6.16]$ sudo make install

#include <stdio.h>
#include <sqlite3.h>

//查询的回调函数声明
int select_callback(void * data, int col_count, char ** col_values, char ** col_Name);

int main(int argc, char * argv[])
{
  const char * sSQL1 = "create table users(userid varchar(20) PRIMARY KEY, age int, birthday datetime);";
  char * pErrMsg = 0;
  int result = 0;
  // 连接数据库
  sqlite3 * db = 0;
  int ret = sqlite3_open("./test.db", &db);
  if( ret != SQLITE_OK ) {
    fprintf(stderr, "无法打开数据库: %s", sqlite3_errmsg(db));
    return(1);
  }
  printf("数据库连接成功!\n");

  // 执行建表SQL
  sqlite3_exec( db, sSQL1, 0, 0, &pErrMsg );
  if( ret != SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", pErrMsg);
    sqlite3_free(pErrMsg);
  }

  // 执行插入记录SQL
  result = sqlite3_exec( db, "insert into users values('张三',20,'2011-7-23');", 0, 0, &pErrMsg);
  if(result == SQLITE_OK){
    printf("插入数据成功\n");
  }
  result = sqlite3_exec( db, "insert into users values('李四',20,'2012-9-20');", 0, 0, &pErrMsg);
  if(result == SQLITE_OK){
    printf("插入数据成功\n");
  }

  // 查询数据表
  printf("查询数据库内容\n");
  sqlite3_exec( db, "select * from users;", select_callback, 0, &pErrMsg);

  // 关闭数据库
  sqlite3_close(db);
  db = 0;
  printf("数据库关闭成功!\n");

  return 0;
}

int select_callback(void * data, int col_count, char ** col_values, char ** col_Name)
{
  // 每条记录回调一次该函数,有多少条就回调多少次
  int i;
  for( i=0; i < col_count; i++){
    printf( "%s = %s\n", col_Name[i], col_values[i] == 0 ? "NULL" : col_values[i] );
  }

  return 0;
}

sqlite3编程—处理二进制blob

#include "sqlite3.h"
#include <stdio.h>
#include <string.h>
#include <errno.h>
 
#define DEMO_DB     "/tmp/demo.db"
#define DEMO_TABLE  "bb"
 
struct employee_s {
    int empno;
    char ename[32];
    char job[16];
    int deptno;
};
 
int sqlite3_db_blob_insert()
{
    sqlite3 *db = NULL;
    sqlite3_stmt *stmt = NULL;
    char sql[1024];
    int ret = 0;
    struct employee_s emp = {1, "tom", "programmer", 1035};
 
    ret = sqlite3_open(DEMO_DB, &db);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    memset(sql, 0, sizeof(sql));
    sprintf(sql, "insert into %s values(?, ?)", DEMO_TABLE);
    printf("sql select: \"%s\"\n", sql);
    ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db prepare fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    /* 绑定id值 */
    ret = sqlite3_bind_int(stmt, 1, 1001);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db bind fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    /* 绑定content值 */
    ret = sqlite3_bind_blob(stmt, 2, &emp, sizeof(emp), NULL);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db bind fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    ret = sqlite3_step(stmt);
    if (ret != SQLITE_DONE) {
        fprintf(stderr, "db insert fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    sqlite3_finalize(stmt);
    return 0;
}
 
int sqlite3_db_blob_select()
{
    sqlite3 *db = NULL;
    sqlite3_stmt *stmt = NULL;
    char sql[1024];
    int ret = 0;
 
    ret = sqlite3_open(DEMO_DB, &db);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    memset(sql, 0, sizeof(sql));
    sprintf(sql, "select id, content from %s", DEMO_TABLE);
    printf("sql select: \"%s\"\n", sql);
    ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db prepare fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    for ( ; ; ) {
        ret = sqlite3_step(stmt);
        if (ret == SQLITE_ROW) {
            int id, len;
            void *content = NULL;
            struct employee_s *emp = NULL;
            int empno, deptno;
            char ename[32], job[16];
 
            /* 索引从0开始 */
            id = sqlite3_column_int(stmt, 0);
            content = (void *)sqlite3_column_blob(stmt, 1);
            len = sqlite3_column_bytes(stmt, 1);
            emp = (struct employee_s *)content;
            empno = emp->empno;
            strcpy(ename, emp->ename);
            strcpy(job, emp->job);
            deptno = emp->deptno;
 
            printf("len=%d, sizeof(employee_s)=%d\n", len, sizeof(struct employee_s));
            printf("id=%d, empno=%d, ename=%s, job=%s, deptno=%d\n",
                    id, empno, ename, job, deptno);
        } else if (ret == SQLITE_DONE) {
            printf("select done!\n");
            break;
        } else {
            fprintf(stderr, "db step fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
            break;
        }
    }
 
    sqlite3_finalize(stmt);
    return 0;
}
 
int sqlite3_db_table_exist(char *dbfile, const char *tablename)
{
    sqlite3 *db = NULL;
    sqlite3_stmt *stmt = NULL;
    char sql[1024];
    int ret = 0, rows = 0;
 
    ret = sqlite3_open(dbfile, &db);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return 0;
    }
 
    memset(sql, 0, sizeof(sql));
    sprintf(sql, "select count(1) from sqlite_master where type='table' and name='%s'", tablename);
    ret = sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db prepare fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    ret = sqlite3_step(stmt);
    if (ret == SQLITE_ROW)  {
        rows = sqlite3_column_int(stmt, 0);
    } else if (ret == SQLITE_DONE) {
        printf("no data!\n");
        rows = 0;
    } else {
        fprintf(stderr, "db step fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        rows = 0;
    }
 
    sqlite3_finalize(stmt);
    sqlite3_close(db);
 
    if (rows > 0) {
        return 1;
    } else {
        return 0;
    }
}
 
int sqlite3_db_create()
{
    sqlite3 *db = NULL;
    char sql[1024];
    char *errmsg = NULL;
    int ret = 0;
 
    if (sqlite3_db_table_exist(DEMO_DB, DEMO_TABLE)) {
        printf("table exist\n");
        return 0;
    }
 
    ret = sqlite3_open(DEMO_DB, &db);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "db open fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_close(db);
        return -1;
    }
 
    memset(sql, 0, sizeof(sql));
    sprintf(sql, "create table %s ("\
                 "id      int(4) not null, "\
                 "content blob)", DEMO_TABLE);
 
    printf("sql create: \"%s\"\n", sql);
    ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    if (ret != SQLITE_OK) {
        fprintf(stderr, "create table fail, errcode[%d], errmsg[%s]\n", ret, sqlite3_errmsg(db));
        sqlite3_free(errmsg);
        errmsg = NULL;
        sqlite3_close(db);
        return -1;
    }
 
    sqlite3_close(db);
    return 0;
}
 
int main()
{
    sqlite3_db_create();
    sqlite3_db_blob_insert();
    sqlite3_db_blob_select();
    return 0;
}