/* 项目名称:用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, 0,sizeof(str));
*
*
*
*
* 张双喜
* v1.0
* 2010.9.29 14:39
*
* update 1
*
* 1、增加了一个测试表test_table,解决了,每次对操作数据库必须首先建立一个表的bug
* 2、增加了确认退出功能
*
* 张双喜
* v2.0
* 2010.9.30 9:40
*
* */
#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 * create_flag);
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, char * pTable_name, int column_num, COLUMN column[], int * test_flag, int * create_flag);
void db_update(sqlite3 * db, char * pTable_name, int column_Num, COLUMN column[], int * test_flag, int * create_flag);
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 * test_flag, int * create_flag);
void db_test(sqlite3 * db, char * pTable_name, int ** test_flag);
void db_flag(sqlite3 * db, int * create_flag, int * test_flag, char * pTable_name);
void db_reset_table_name(sqlite3 * db, char * pTable_name);
int main(int argc, char* argv[])
{
sqlite3 * db;
int empty = 1;
int ret = 0;
int create_flag = 1;
int test_flag = 1;
int test_flag_create = 1;
char c = 'n';
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;
strcpy(pTable_name, "test_table");
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:
printf("you choise leave, y or n?/n");
setbuf(stdin, NULL);
scanf("%c", &c);
setbuf(stdin, NULL);
if(c == 'y')
{
if(test_flag == 0)
{
db_drop(db, "test_table", &test_flag, &create_flag);
}
printf("goodbye!/n");
sqlite3_close(db);
return 0;
}
else
{
choice = -1;
}
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, &create_flag);
break;
case 2:
db_flag(db, &create_flag, &test_flag, table_name);
db_show(db, table_name);
break;
case 3:
db_flag(db, &create_flag, &test_flag, table_name);
db_insert(db, table_name, column_num, column, &test_flag, &create_flag);
break;
case 4:
db_flag(db, &create_flag, &test_flag, table_name);
db_update(db, table_name, column_num, column, &test_flag, &create_flag);
break;
case 5:
db_flag(db, &create_flag, &test_flag, table_name);
db_delete(db, table_name);
break;
case 6:
db_flag(db, &create_flag, &test_flag, table_name);
db_empty(db, table_name);
break;
case 7:
db_flag(db, &create_flag, &test_flag, table_name);
db_drop(db, table_name, &test_flag, &create_flag);
break;
default:
printf("your choice is not exist!/n");
break;
}
}
sqlite3_close(db);
return 0;
}
void db_flag(sqlite3 * db, int * create_flag, int * test_flag, char * pTable_name)
{
if(create_flag)
{
if(*test_flag)
{
db_test(db, pTable_name, &test_flag);
}
}
return;
}
void db_test(sqlite3 * db, char * pTable_name, int ** test_flag)
{
int ret;
char * errmsg;
**test_flag = 0;
printf("because you have not create a table,so we create a test_table table for you!/n");
ret = sqlite3_exec(db, "create table test_table (id integer primary key autoincrement, name text, sex text, age integer);", NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, errmsg);
exit(1);
}
ret = sqlite3_exec(db, "insert into test_table (name, sex, age) values ('zsx', 'm', 23);",NULL ,NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, errmsg);
exit(1);
}
return;
}
void db_create(sqlite3 * db, char * pTable_name, int column_num, COLUMN column[], int * create_flag)
{
int ret, i;
char * errmsg = NULL;
char sql[200];
char * pSql;
*create_flag = 0;
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, " ");
strcat(pSql, column[column_num-1].column_type);
strcat(pSql, ");");
printf("/nsqlite > %s/n/n",pSql);
ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, errmsg);
exit(1);
}
return;
}
int rscallback(void * p, int column, char ** column_val,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;
pSql = sql;
strcpy(pSql, "select * from ");
strcat(pSql, pTable_name);
strcat(pSql, ";");
printf("/nsqlite > %s/n/n", pSql);
ret = sqlite3_exec(db, pSql, rscallback, &empty, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, errmsg);
exit(1);
}
if(empty)
{
printf("the table is empty!/n");
}
printf("/n");
return;
}
void db_insert(sqlite3 * db, char * pTable_name, int column_num, COLUMN column[], int * test_flag, int * create_flag)
{
int ret = 0;
char * errmsg = NULL;
char sql[200];
char * pSql;
char tmp[20];
int i;
char test_val[100];
pSql = sql;
strcpy(pSql, "insert into ");
strcat(pSql, pTable_name);
strcat(pSql, "(");
if((*test_flag == 0) && (*create_flag))
{
strcat(pSql, " name, ");
strcat(pSql, "sex, ");
strcat(pSql, "age ) ");
strcat(pSql, "values (");
printf("please input name:/n");
scanf("%s", test_val);
strcat(pSql, " /'");
strcat(pSql, test_val);
strcat(pSql, "/', ");
printf("please input sex:/n");
scanf("%s", test_val);
strcat(pSql, "/'");
strcat(pSql, test_val);
strcat(pSql,"/', ");
printf("please input age:/n");
scanf("%s",test_val);
strcat(pSql,test_val);
strcat(pSql, ");");
}
if(*create_flag == 0)
{
for(i = 1; i < column_num-1; i++)
{
strcat(pSql, column[i].column_name);
strcat(pSql, ", ");
}
strcat(pSql, column[column_num-1].column_name);
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, 0, sizeof(tmp));
scanf("%s",tmp);
strcat(pSql, tmp);
strcat(pSql,"/'");
strcat(pSql, ",");
}
else
{
memset(tmp,0,sizeof(tmp));
scanf("%s",tmp);
strcat(pSql, tmp);
strcat(pSql,",");
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, tmp);
strcat(pSql, "/'");
strcat(pSql, ");");
}
else
{
scanf("%s",tmp);
strcat(pSql, tmp);
strcat(pSql, ");");
}
}
printf("/nsqlite > %s/n/n", pSql);
ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, errmsg);
exit(1);
}
return;
}
void db_update(sqlite3 * db, char * pTable_name, int column_num, COLUMN column[], int * test_flag, int * create_flag)
{
int ret = 0;
char * errmsg = NULL;
char sql[200];
char * pSql;
char new_val[20];
char new_id[3];
int i;
char test_val[100];
pSql = sql;
strcpy(pSql, "update ");
strcat(pSql, pTable_name);
strcat(pSql, " set ");
if((*test_flag == 0) && (*create_flag))
{
strcat(pSql, "name = ");
printf("please input a new name:/n");
scanf("%s",test_val);
strcat(pSql,"/'");
strcat(pSql,test_val);
strcat(pSql, "/', sex = ");
printf("please input a new sex: /n");
scanf("%s", test_val);
strcat(pSql, "/'");
strcat(pSql, test_val);
strcat(pSql, "/', age = ");
printf("please input a new age:/n");
scanf("%s",test_val);
strcat(pSql,test_val);
strcat(pSql, " where id = ");
printf("please input a id that you want to change:/n");
scanf("%s",test_val);
strcat(pSql, test_val);
strcat(pSql, ";");
}
if(*create_flag == 0)
{
for(i = 1; i < column_num-1; i++)
{
strcat(pSql, column[i].column_name);
strcat(pSql, " = ");
if(strcmp(column[i].column_type, "text") ==0)
{
strcat(pSql, "/'");
memset(new_val, 0, sizeof(new_val));
printf("please input a new %s/n", column[i].column_name);
scanf("%s",new_val);
strcat(pSql, new_val);
strcat(pSql, "/'");
strcat(pSql, " , ");
}
else
{
memset(new_val, 0, sizeof(new_val));
printf("please input a new %s/n", column[i].column_name);
scanf("%s",new_val);
strcat(pSql, new_val);
strcat(pSql, " , ");
}
}
strcat(pSql, column[i].column_name);
strcat(pSql, " = ");
if(strcmp(column[column_num-1].column_type, "text") ==0)
{
strcat(pSql, "/'");
memset(new_val, 0, sizeof(new_val));
printf("please input a new %s/n", column[i].column_name);
scanf("%s",new_val);
strcat(pSql, new_val);
strcat(pSql, "/'");
strcat(pSql, " ");
}
else
{
memset(new_val, 0, sizeof(new_val));
printf("please input a new %s/n", column[i].column_name);
scanf("%s",new_val);
strcat(pSql, new_val);
strcat(pSql, " ");
}
strcat(pSql, "where id = ");
printf("please input the id that you want to change its value:/n");
scanf("%s",new_id);
strcat(pSql, new_id);
strcat(pSql, ";");
}
printf("sqlite > %s/n/n",pSql);
ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, 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, pTable_name);
strcat(pSql, " where id = ");
printf("please input a id that you want to delete:/n");
scanf("%s",tmp_id);
strcat(pSql, tmp_id);
strcat(pSql, ";");
printf("/nsqlite > %s/n/n",pSql);
ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, 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, "delete from ");
strcat(pSql, pTable_name);
strcat(pSql,";");
printf("/nsqlite > %s/n/n",pSql);
ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, errmsg);
exit(1);
}
return;
}
void db_drop(sqlite3 * db, char * pTable_name, int * test_flag, int * create_flag)
{
int ret = 0;
char * errmsg = NULL;
char sql[200];
char * pSql;
pSql = sql;
strcpy(pSql, "drop table ");
strcat(pSql, pTable_name);
strcat(pSql,";");
if(*test_flag)
{
printf("/nsqlite > %s/n/n",pSql);
}
if(*create_flag == 0)
{
printf("/nsqlite > %s/n/n",pSql);
}
ret = sqlite3_exec(db, pSql, NULL, NULL, &errmsg);
if(ret != SQLITE_OK)
{
printf("error:%d:%s/n", ret, errmsg);
exit(1);
}
return;
}
用c语言动态操作sqilite3数据库v2.0
最新推荐文章于 2024-06-26 21:56:48 发布