sqlite3的基本操作（插入，删除，更新，查询）
2013-10-08 22:31:33
- #include <stdio.h>
- #include <stdlib.h>
- #include <string.h>
- #include "sqlite3.h"
- sqlite3 * db = NULL;
- static int sn = 0;
- void create_table(char * filename)
- {
- char * sql;
- char * zErrMsg = 0;
- int rc;
- rc = sqlite3_open(filename,&db);
- //rc = sqlite3_open_v2(filename,&db,sqlITE_OPEN_READWRITE | sqlITE_OPEN_CREATE, NULL);
- if (rc)
- {
- fprintf(stderr,"can't open database%s\n",sqlite3_errmsg(db));
- sqlite3_close(db);
- }
- sql = "CREATE TABLE save_data(num integer primary key,id int,data text,time text)";
- sqlite3_exec(db,sql,0,&zErrMsg);
- }
- void close_table(void)
- {
- sqlite3_close(db);
- }
- void insert_record(char * table,int id,char * data,char * time)
- {
- char * sql;
- char * zErrMsg = NULL;
- sql = sqlite3_mprintf("insert into %s values(null,%d,'%s','%s')",table,id,data,time);
- sqlite3_exec(db,&zErrMsg);
- sqlite3_free(sql);
- }
- int sqlite_callback(void * userData,int numCol,char ** colData,char **colName)
- {
- int i,offset = 0;
- char * buf,* tmp;
- buf = (char *)malloc(40 * sizeof(char));
- tmp = buf;
- memset(buf,40);
- //printf("%d %d\n",sizeof(buf),strlen(buf));
- for (i = 1;i < numCol;i++)
- {
- buf = buf + offset;
- sprintf(buf,"%s ",colData[i]);
- offset = strlen(colData[i]) + 1; //it's need one place for put a blank so the lenght add 1
- //printf("i %d offset %d\n",i, offset);
- }
- printf("%.4d. %s \n",++sn,tmp);
- free(tmp);
- tmp = NULL;
- buf = NULL;
- return 0;
- }
- void search_all(char * table)
- {
- char * sql;
- char * zErrMsg = 0;
- sn = 0;
- sql = sqlite3_mprintf("select * from %s",table);
- sqlite3_exec(db,&sqlite_callback,&zErrMsg);
- sqlite3_free(sql);
- }
- void search_by_id(char * table,char * id)
- {
- char * sql;
- char * zErrMsg = 0;
- sn = 0;
- sql = sqlite3_mprintf("select * from %s where id=%s",id);
- sqlite3_exec(db,&zErrMsg);
- sqlite3_free(sql);
- }
- void delete_by_id(char * table,char * id)
- {
- int rc ;
- char * sql;
- char * zErrMsg = 0;
- sql = sqlite3_mprintf("delete from %s where id=%s",id);
- rc = sqlite3_exec(db,&zErrMsg);
- sqlite3_free(sql);
- }
- void delete_all(char * table)
- {
- char * sql;
- char * zErrMsg = 0;
- sql = sqlite3_mprintf("delete from %s",&zErrMsg);
- sqlite3_free(sql);
- }
- int main(int agrc,char * argv[])
- {
- char * filename = "data.db";
- int i ;
- create_table(filename);
- for (i = 0 ;i < 10;i++)
- {
- insert_record("save_data",2000,"5678","2012-03-12 09:43:56");
- insert_record("save_data",2001,2002,2003,2004,2005,2006,2007,"2012-03-12 09:43:56");
- }
- search_all("save_data");
- close_table();
- return 0;
- }
