sqlite3数据库c语言常用接口应用实例



#include <stdio.h>#include <string.h>#include <sqlite3.h>/***************************typedef int (*sqlite3_callback)(void*,    // Data provided in the 4th argument of sqlite3_exec()int,        // The number of columns in row char**,   // An array of strings representing fields in the row char**    // An array of strings representing column names );***************************//* callback函数只有在对数据库进行select, 操作时才会调用 */static int select_callback(void *data, int argc, char **argv, char **azColName){   int i;   printf("%s", (char*)data);   for(i=0; i < argc; i++){      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");   }   printf("\n");   return 0;}int main(int argc, char* argv[]){   sqlite3 *db;   char *zErrMsg = 0;   int rc;   /* 数据库创建或打开 */   rc = sqlite3_open("test.db", &db);   if( rc ){      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));      exit(0);   }else{      fprintf(stderr, "Opened database successfully\n");   }   char* sql;   sql = "create table healthinfo (" \           "sid int primary key not null," \           "name text not null," \           "ishealth char(4) not null);";   /* 创建表 */   rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);   if( rc != SQLITE_OK ){      fprintf(stderr, "SQL error: %s\n", zErrMsg);      sqlite3_free(zErrMsg);   }else{      fprintf(stdout, "Table created successfully\n");   }   sql = "insert into healthinfo (sid, name, ishealth)" \           "values (201601001, 'xiaowang', 'yes');" \           "insert into healthinfo (sid, name, ishealth)" \           "values (201601002, 'xiaoli', 'yes');" \           "insert into healthinfo (sid, name, ishealth)" \           "values (201601003, 'xiaozhang', 'no');" \           "insert into healthinfo (sid, name, ishealth)" \           "values (201601004, 'xiaozhou', 'yes');" \           "insert into healthinfo (sid, name, ishealth)" \           "values (201601005, 'xiaosun', 'yes');";    /* 插入数据 */   rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);   if( rc != SQLITE_OK ){      fprintf(stderr, "SQL error: %s\n", zErrMsg);      sqlite3_free(zErrMsg);   }else{      fprintf(stdout, "Table insert data successfully\n");   }   char* strname = "xiaoyang";   //char strname[256] = {'x','i','a','o','y','a','n','g'};   char sql2[256] = {'0'};   /* 不推荐使用这种方式 */   sprintf(sql2, "insert into healthinfo (sid, name, ishealth) values (201601006, '%s', 'yes');", strname);    /* 插入数据 */   rc = sqlite3_exec(db, sql2, NULL, NULL, &zErrMsg);   if( rc != SQLITE_OK ){      fprintf(stderr, "SQL error: %s\n", zErrMsg);      sqlite3_free(zErrMsg);   }else{      fprintf(stdout, "Table insert data successfully\n");   }   /***********  存数据和取数据的第二种方法***********/     sql = "insert into healthinfo (sid, name, ishealth)" \           "values (:sid, :name, :ishealth);";   /* 注: ":sid" 为命名参数 也可以用? 号*/   sqlite3_stmt *stmt;   /* 准备一个语句对象 */   sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);    /* 语句对象绑定的参数个数也就是上面sql语句values括号中的参数 */   printf("max_parameter_count = %d\n", sqlite3_bind_parameter_count(stmt));   /* 只有上面指定了:sid这个名字才可以用 */   printf("sid parameter index = %d\n", sqlite3_bind_parameter_index(stmt, ":sid"));    printf("name parameter index = %d\n", sqlite3_bind_parameter_index(stmt, ":name"));   printf("ishealth parameter index = %d\n", sqlite3_bind_parameter_index(stmt, ":ishealth"));   /* 如果是?号命名的则返回的文本为null */   printf("index = 1 's parameter's name = %s\n", sqlite3_bind_parameter_name(stmt, 1));    sqlite3_bind_int(stmt, 1, 201601007);   sqlite3_bind_text(stmt, 2, "xiaoqian", -1, NULL); /* 第四个参数设为负数则自动计算第三个参数的长度 */   sqlite3_bind_text(stmt, 3, "yes", 3, NULL);   //sqlite3_bind_blob(stmt, 1, sectionData, 4096, SQLITE_STATIC); /* 将sectonData 绑定到stmt对象 */      /* 执行sql 语句对象并判断其返回值       发现如果不是select 这样会产生结果集的操作       返回值为SQLITE_DONE 或者出错，只有执行sql语句会产生       结果集执行step函数才返回SQLITE_ROW*/   rc = sqlite3_step(stmt);     printf("step() return %s\n", rc == SQLITE_DONE ? "SQLITE_DONE" \                                          : rc == SQLITE_ROW ? "SQLITE_ROW" : "SQLITE_ERROR");    sqlite3_reset(stmt);  /* 如果要重新绑定其他值要reset一下 */    sqlite3_bind_int(stmt, 1, 201601008);    sqlite3_bind_text(stmt, 2, "xiaowu", -1, NULL); /* 重新绑定值 */    sqlite3_bind_text(stmt, 3, "yes", 3, NULL);    sqlite3_step(stmt);  /* 再执行 */      /* 销毁prepare 创建的语句对象 */   sqlite3_finalize(stmt);     /* 取数据 */   //sql = "select * from healthinfo;";   sql = "select * from healthinfo limit 4 offset 2;";  /* 限制返回4行且从第3行开始 */   sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL);   printf("total_column = %d\n", sqlite3_column_count(stmt));      /* 遍历执行sql语句后的结果集的每一行数据 */   while(sqlite3_step(stmt) == SQLITE_ROW){       /* 获得字节数，第二个参数为select结果集合的列号 */       /* 由于select 的结果集只有section这一列，因此为0 */       int len_sid = sqlite3_column_bytes(stmt, 0);       int len_name = sqlite3_column_bytes(stmt, 1);       int len_ishealth = sqlite3_column_bytes(stmt, 2);              printf("sid = %d, len = %d\n", sqlite3_column_int(stmt, 0), len_sid);       printf("name = %s, len = %d\n", sqlite3_column_text(stmt, 1), len_name);       printf("ishealth = %s, len = %d\n", sqlite3_column_text(stmt, 2), len_ishealth);       //unsigned char* srcdata = sqlite3_column_blob(stmt, 0);  /* 取得数据库中的blob数据 */   }   printf("\n");   sqlite3_finalize(stmt);   /******************* end ****************************/      const char* data = "select call back function call!\n";   /* select 使用*/   sql = "select * from healthinfo where ishealth == 'yes';";   rc = sqlite3_exec(db, sql, select_callback, data, &zErrMsg);   if( rc != SQLITE_OK ){      fprintf(stderr, "SQL error: %s\n", zErrMsg);      sqlite3_free(zErrMsg);   }else{      fprintf(stdout, "Table select successfully\n");   }   data = "update call back function call!\n";   /* update 使用*/   sql = "update healthinfo set ishealth = 'no' where name='xiaoli';" \            "select * from healthinfo where ishealth == 'yes';";   rc = sqlite3_exec(db, sql, select_callback, data, &zErrMsg);   if( rc != SQLITE_OK ){      fprintf(stderr, "SQL error: %s\n", zErrMsg);      sqlite3_free(zErrMsg);   }else{      fprintf(stdout, "Table update successfully\n");   }         /* 删除表 */   sql = "drop table healthinfo;";   rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);   if( rc != SQLITE_OK ){      fprintf(stderr, "SQL error: %s\n", zErrMsg);      sqlite3_free(zErrMsg);   }else{      fprintf(stdout, "Table droped successfully\n");   }   char sql5[256];   char* tname = "abc";   sprintf(sql5, "create table if not exists %s ("\                    "id int not null," \                    "name text not null);", tname);   printf("%s\n", sql5);   /* 创建表 */   rc = sqlite3_exec(db, sql5, NULL, NULL, &zErrMsg);   if( rc != SQLITE_OK ){      fprintf(stderr, "SQL error: %s\n", zErrMsg);      sqlite3_free(zErrMsg);   }else{      fprintf(stdout, "Table created successfully\n");   }      sqlite3_close(db);}

学了一天的sqlite3编程，学习用例贴出来，基本的函数都用到了，也进行了详细的说明，希望对看的人有帮助。