Dynamic sqilite3 database operations using C language

/* Project name: use C language to dynamically operate the sqilite3 Database
* Project member: Zhang Shuangxi
* Compiling environment: gcc
* Project functions:
* 1. dynamically create a table
* 2. dynamically implement basic table operations (add, delete, modify, and query)
* 3. Clear table data and Delete tables (additional functions)
* Project summary:
* 1. technologies involved:
* 1. Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite_callback, void *, char **);
* To achieve dynamic database operations, you must dynamically construct SQL statements. You can use the strcat function to construct SQL statements and splice them until the SQL statements are fully constructed.
* 2. To implement the effect of simple menu options, you must separate the functions that implement each sub-function. In the switch, select the call
* 3,
* 2. unexpected gains:
* 1. If you want to enter a string with spaces, you cannot use the scanf function. Therefore, scanf cannot achieve the expected results because the scanf function has spaces, line breaks, and carriage returns.
* To solve this problem, you can use the gets function. However, gets is a dangerous function and is not recommended in C language. You must clear the buffer before using the gets function. Otherwise. Will appear
* Unexpected input results, which is caused by the previous uncleared buffer.
* 2. How to clear the buffer in Linux C:
* Personal opinion:
* After testing, Linux C cannot clear the buffer using fflush (stdin). After fflush (stdin) is used, the gets function is used. Gets will still read from the buffer,
* The buffer zone is not cleared by fflush (stdin;
* After finding information on the Internet, I learned that setbuf (stdin, null) is a function. After testing, the buffer zone is successfully cleared.
* In short, the function for clearing the buffer in Linux C is setbuf (stdin, null );
* 3. Programming Problems and Solutions:
* 1. correct usage of update database:
* Update table_name set name = 'new _ name', sex = 'new _ sex' where id = id_val; // correct
* In the above sentence, it cannot be replaced with and,
* That is:
* Update table_name set name = 'new _ name' and sex = 'new _ sex' where id = id_val; // the error message returned.
* 2. String problems:
* Char * P = "Hello world! "; // This string is read-only and cannot be modified or rewritten. It cannot be strcat (P," Jack ");
* Char STR [] = "Hello world! "// This allows you to perform arbitrary operations on strings through pointers or without pointers.
* Char * P;
* P = STR;
* 3. Zero string cleaning:
* Char STR [10];
* Memset (STR, 0, sizeof (STR ));
* Zhang Shuangxi
* V1.0
* 2010.9.29
* Update 1
* 1. added a test table test_table to solve the problem. Each operation on the database must first create a table bug.
* 2. added the exit confirmation function.
*
* Zhang Shuangxi
* V2.0
* 2010.9.30
*
**/

# Include <stdio. h> # include <stdlib. h> # include <string. h> # include "sqlite3.h"/* This struct is convenient for table data operations */typedef struct table_column {char column_name [100]; // The column name char column_type [200]; // column data type} column; void db_create (sqlite3 * dB, char * ptable_name, int column_num, 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 [], int * test_flag, int * create_flag ); void db_update (sqlite3 * dB, char * ptable_name, int column_num, 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 * ptabl E_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; ch Ar 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); D B _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 Ta Ble 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 [], 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, colum[ column_num-1]. column_name); strcat (Psql, ""); strcat (Psql, colum[ column_num-1]. column_type); strcat (Psql, ");"); printf ("/nsq Lite> % s/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", 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]; c Har * Psql; Psql = SQL; strcpy (Psql, "select * from"); strcat (Psql, ptable_name); strcat (Psql ,";"); printf ("/nsqlite> % s/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 [], int * test_flag, int * create_flag) {int ret = 0; char * errmsg = NULL; char SQL [1, 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, colum[ 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 (colum[ 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 ", 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 [], 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]. col Umn_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 (P SQL, "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", 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 [1, 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); Str CAT (Psql, tmp_id); strcat (Psql, ";"); printf ("/nsqlite> % s/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 [1, 200]; char * Psql; Psql = SQL; strcpy (Psql, "delete from "); strcat (Psql, ptable_name); strcat (Psql, ";"); printf ("/nsqlite> % s/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", Psql);} If (* create_flag = 0) {printf ("/nsqlite> % S/n ", Psql);} ret = sqlite3_exec (dB, Psql, null, null, & errmsg); If (Ret! = Sqlite_ OK) {printf ("error: % d: % s/n", RET, errmsg); exit (1) ;}return ;}