/* 项目名称：用c语言动态操作sqilite3数据库 * * 项目成员：张双喜 * * 编译环境：gcc * * 项目功能： * 1、动态创建表 * 2、动态实现对表的基本操作（增、删、改、查） * 3、清空表数据、删除表（附加功能） * * 项目总结： * 1、涉及的技术： * * 1、int sqlite3_exec(sqlite3*,const char *sql,sqlite_callback,void*,char**); * 　　　　　欲实现动态操作数据库，必须动态构造sql语句，构造sql语句可以用strcat函数，逐步拼接，直至完整构造出sql语句。 * * 2、欲实现简单菜单选项效果，须分离出实现每个子功能的函数，在switch中，选择的调用 * * 3、 * * 2、意外收获： * * 1、想输入一条带有空格的字符串，不能用scanf函数，应为scanf函数遇到空格、换行、回车自动结束输入，所以scanf不能达到预期效果 * 　，解决该问题可以使用gets函数，但是gets是个危险函数，c语言不提倡使用，在使用gets函数前，一定要清空缓冲区，否则。会出现 * 　意想不到的输入结果，这就是先前没有清空缓冲区造成的。 * * 2、linux c 清空缓冲区的问题： * 个人见解： * 经测试，linux c 使用fflush(stdin)无法清空缓冲区，使用fflush(stdin)后，再使用gets函数，gets还是会从缓冲区中读取东西， * 因为缓冲区经fflush(stdin)没有被清空； * 经过在网上找资料，得知setbuf(stdin,NULL);这样一个函数，经测试，清理缓冲区成功。 * * 简言之：linux c 清空缓冲区的函数：setbuf(stdin,NULL); * * 3、编程中问题及其解决方法： * * 1、更新数据库update正确使用方法： * update table_name set name = 'new_name',sex = 'new_sex' where id = id_val; //是正确的 * 上句中的,不能换成and， * 即： * update table_name set name = 'new_name' and sex = 'new_sex' where id = id_val; //是错误的 * * 2、字符串的问题： * char * p = "hello world!"; //该字符串是只读的不能修改字符串，不能改写，不能strcat(p,"jack"); * * char str[] = "hello world!" //这样可以通过指针或不通过指针对字符串进行任意操作 * char *p; * p = str; * * 3、字符串清理零： * char str[10]; * * memset(str,sizeof(str)); * * * * * 张双喜 * 2010.9.29 14:39 * * * */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include "sqlite3.h" /*该结构体是方便对表内数据操作*/ typedef struct table_column { char column_name[100]; //列名 char column_type[200]; //列的数据类型 }COLUMN; void db_create(sqlite3 * db,char * pTable_name,int column_num,COLUMN column[]); int rscallback(void * p,int column,char ** column_val,char ** column_name); void db_show(sqlite3 * db,char * pTable_name); void db_insert(sqlite3 * db,COLUMN column[]); void db_update(sqlite3 * db,int column_Num,COLUMN column[]); void db_delete(sqlite3 * db,char * pTable_name); void db_empty(sqlite3 * db,char * pTable_name); void db_drop(sqlite3 * db,char *pTable_name); int main(int argc,char* argv[]) { sqlite3 * db; int empty = 1; int ret = 0; char * errmsg = NULL; int choice = -1; char table_name[20]; char * pTable_name; int column_num; int i; COLUMN column[10]; pTable_name = table_name; ret = sqlite3_open("student.db",&db); if(ret != sqlITE_OK) { perror("slqite3_open"); exit(1); } while(choice != 0) { printf("please input your choise:/n"); printf("------------------------------------------------------------------/n"); printf("|0.exit|1.create|2.show|3.insert|4.update|5.delete|6.empty|7.drop|/n"); printf("------------------------------------------------------------------/n"); scanf("%d",&choice); switch(choice) { case 0: break; case 1: printf("we will create a table for you,please input the name of your table:/n"); scanf("%s",pTable_name); printf("please input the number of column:/n"); scanf("%d",&column_num); printf("please input column_name column_type:/n"); for(i = 0; i < column_num; i++) { scanf("%s %s",column[i].column_name,column[i].column_type); } db_create(db,table_name,column_num,column); break; case 2: db_show(db,table_name); break; case 3: db_insert(db,column); break; case 4: db_update(db,column); break; case 5: db_delete(db,table_name); break; case 6: db_empty(db,table_name); break; case 7: db_drop(db,table_name); break; default: printf("your choice is not exist!/n"); // printf("please input a number again:/n"); // scanf("%d",&choice); exit(0); } } return 0; } void db_create(sqlite3 * db,COLUMN column[]) { int ret,i; char * errmsg = NULL; char sql[200]; char * psql; psql = sql; strcpy(psql,"create table "); strcat(psql,pTable_name); strcat(psql,"("); strcat(psql,column[0].column_name); strcat(psql," "); strcat(psql,column[0].column_type); strcat(psql," primary key autoincrement"); strcat(psql,","); for(i = 1; i < column_num-1; i++) { strcat(psql,column[i].column_name); strcat(psql," "); strcat(psql,column[i].column_type); strcat(psql,"); } strcat(psql,column[column_num-1].column_name); strcat(psql,column[column_num-1].column_type); strcat(psql,");"); printf("/nsqlite > %s/n/n",psql); ret = sqlite3_exec(db,psql,NULL,&errmsg); if(ret != sqlITE_OK) { printf("error:%d:%s/n",ret,errmsg); exit(1); } return; } int rscallback(void * p,char ** column_name) { int i; *(int *)p = 0; for(i = 0; i < column; i++) { printf("%s > %s/t/t",column_name[i],column_val[i]); } printf("/n"); return 0; } void db_show(sqlite3 * db,char * pTable_name) { int ret = 0; char * errmsg = NULL; int empty = 1; char sql[200]; char * psql; printf("/n"); psql = sql; strcpy(psql,"select * from "); strcat(psql,pTable_name); strcat(psql,";"); ret = sqlite3_exec(db,rscallback,&empty,errmsg); exit(1); } if(empty) { printf("the table is empty!/n"); } printf("/n"); return; } void db_insert(sqlite3 * db,COLUMN column[]) { int ret = 0; char * errmsg = NULL; char sql[200]; char * psql; char tmp[20]; int i; psql = sql; strcpy(psql,"insert into "); strcat(psql,"("); for(i = 1; i < column_num-1; i++) { strcat(psql,"); strcat(psql," "); } strcat(psql,") "); strcat(psql,"values("); for(i = 1; i < column_num-1; i++) { printf("please input %s/n",column[i].column_name); if(strcmp(column[i].column_type,"text") == 0) { strcat(psql,"/'"); memset(tmp,sizeof(tmp)); scanf("%s",tmp); strcat(psql,"/'"); strcat(psql,"); } else { memset(tmp,"); strcat(psql," "); } } printf("please input %s/n",column[column_num-1].column_name); if(strcmp(column[column_num-1].column_type,"text") == 0) { scanf("%s",tmp); strcat(psql,"/'"); strcat(psql,");"); } else { scanf("%s",");"); } printf("/nsqlite > %s/n/n",psql); ret = sqlite3_exec(db,errmsg); exit(1); } return; } void db_update(sqlite3 * db,COLUMN column[]) { int ret = 0; char * errmsg = NULL; char sql[200]; char * psql; char new_val[20]; char new_id[3]; int i; psql = sql; strcpy(psql,"update "); strcat(psql," set "); for(i = 1; i < column_num-1; i++) { strcat(psql," = "); if(strcmp(column[i].column_type,"text") ==0) { strcat(psql,"/'"); memset(new_val,sizeof(new_val)); printf("please input a new %s/n",column[i].column_name); scanf("%s",new_val); strcat(psql,"); } else { memset(new_val,"); } } strcat(psql,column[i].column_name); strcat(psql," = "); if(strcmp(column[column_num-1].column_type,"text") ==0) { strcat(psql,"/'"); memset(new_val,sizeof(new_val)); printf("please input a new %s/n",column[i].column_name); scanf("%s",new_val); strcat(psql," "); } else { memset(new_val," "); } strcat(psql,"where id = "); printf("please input the id that you want to change its value:/n"); scanf("%s",new_id); strcat(psql,";"); printf("sqlite > %s/n/n",errmsg); exit(1); } return; } void db_delete(sqlite3 * db,char * pTable_name) { int ret = 0; char * errmsg = NULL; char sql[200]; char * psql; char tmp_name[20]; char tmp_id[3]; psql = sql; strcpy(psql,"delete from "); strcat(psql," where id = "); printf("please input a id that you want to delete:/n"); scanf("%s",tmp_id); strcat(psql,";"); printf("/nsqlite > %s/n/n",errmsg); exit(1); } return; } void db_empty(sqlite3 * db,char * pTable_name) { int ret = 0; char * errmsg = NULL; char sql[200]; char * psql; psql = sql; strcpy(psql,psql); ret = sqlite3_exec(db,errmsg); exit(1); } return; } void db_drop(sqlite3 * db,"drop table "); strcat(psql,errmsg); exit(1); } return; }