Simple Address Book Program (console) implemented based on sqlite3)

1. sqlite3 Installation

1.1. Download The sqlite3 source code
Www.sqlite3.org
Download sqlite-autoconf-3070701.tar.gz

1.2. Decompress
Decompress the downloaded sqlite-autoconf-3070701.tar.gz to get the sqlite-autoconf-3070701 folder

1.3. Compile the source code (refer to the install file in the decompressed folder)
$ Cd sqlite-autoconf-3070701 # enter the folder
$./Configure
$ Make
$ Sudo make install # Be sure to have the root permission.

1.4. view the installation status
In/usr/local/include: sqlite3.h sqlite3ext. h
In/usr/local/bin: sqlite3
Under/usr/local/lib: pkgconfig (folder) libsqlite3.a libsqlite3.la
Libsqlite3.so libsqlite3.so. 0 libsqlite3.so. 0.8.6

2. sqlite3 c Programming Environment (Codeblocks)

2.1. Create a c project
Open codeblocks, File --> new --> project --> console application --> c

2.2 import to Warehouse
Project --> build options --> linker setting
Click add under linker library and select the/usr/local/lib/libsqlite3.so file.

2.3. Add a. h file
Start with the Program # include <sqlite3.h>

2.4. Now the environment has been set up.
In the program, you can use the C language interface of sqlite3.
For example, sqlite3_open ()

Sqlite3_close ()

3. Address Book Program

A simple Address Book Program is mainly used to train hands and be familiar with Slqite3.

Database: contact. db, which has only one info table. It stores the contact information (name, age, relationship, and mobile phone number)

There are many bugs in the program, but they cover the common operations of sqlite3 databases: creating databases, creating tables, adding, deleting, modifying, and querying.

/***************************************: Contact_using_sqlite3.c * function: A simple Address Book Program, mainly used to practice sqlite3 usage * Description: Use sqlite3 to create a database contact, where there is a table of info (name varchar (10 ), age smallint, relation varchar (10), phone varchar (11) includes the following functions: 1. view Address Book 2. add contact 3. delete contact 4. modify contact information 5. find the contact * Author: JarvisChu * Time: 2011-8-22 * revision:, complete modification and deletion; ************************************ ***********************************/# Include <stdio. h> # include <stdlib. h> # include <string. h> # include <sqlite3.h> sqlite3 * db = NULL; // the database // show the menu on the screen, and return user's choiceint showMenu () {int choice = 0; while (1) {printf ("************************************ * **************** \ n "); printf ("Welcome \ n"); printf ("1. display All 2. add Contact \ n "); printf (" 3. delete Contac T 4. alter Contact \ n "); printf (" 5. find Contact \ n "); printf (" Your choice is: "); scanf (" % d ", & choice ); if (choice = 1 | choice = 2 | \ choice = 3 | choice = 4 | \ choice = 5) {return choice ;} else {printf ("Wrong! Again! \ N ") ;}} return 0 ;}// show all records in dbvoid displayAll () {int I = 0; int j = 0; int index = 0; int ret = 0; int row = 0; int column = 0; char * SQL = NULL; char ** resultSet = NULL; // store the query result SQL = (char *) malloc (sizeof (char) * 20); strcpy (SQL, "select * from info;"); ret = sqlite3_get_table (db, SQL, & resultSet, & row, & column, 0); if (ret! = SQLITE_ OK) {fprintf (stderr, "select records err \ n");} printf ("There are % d Contact: \ n", row); index = 0; for (I = 0; I <= row; I ++) {for (j = 0; j <column; j ++) {printf ("%-11 s ", resultSet [index ++]);} printf ("\ n");} sqlite3_free_table (resultSet); free (SQL);} // add contactvoid addContact () {int ret = 0; char * name = NULL; int age = 0; char * relation = NULL; char * phone = NULL; char * SQL = NULL; name = (char *) mal Loc (sizeof (char) * 10); relation = (char *) malloc (sizeof (char) * 10); phone = (char *) malloc (sizeof (char) * 12); SQL = (char *) malloc (sizeof (char) * 64); printf ("input (name age relation phone ):"); scanf ("% s % d % s", name, & age, relation, phone); // printf ("% s, % d, % s, % s \ n ", name, age, relation, phone); sprintf (SQL," insert into info values ('% s', % d,' % s ', '% s'); ", name, age, relation, phone); // printf (" % s \ n ", SQL); ret = s Qlite3_exec (db, SQL, 0, 0); if (ret! = SQLITE_ OK) {printf ("failed! \ N ");} else {printf (" OK! \ N ");} free (name); free (relation); free (phone); free (SQL);} // find Contactvoid findContact () {int I, j, index; int ret = 0; int row = 0; int column = 0; char * name = NULL; char * SQL = NULL; char ** resultSet = NULL; name = (char *) malloc (sizeof (char) * 10); printf ("Input the name you want to find:"); scanf ("% s", name ); SQL = (char *) malloc (sizeof (char) * 64); sprintf (SQL, "select * from info where name = '% s' ", Name); ret = sqlite3_get_table (db, SQL, & resultSet, & row, & column, 0); if (ret! = SQLITE_ OK) {fprintf (stderr, "select err: % s \ n", sqlite3_errmsg (db); return;} index = 0; if (row> 0) {for (I = 0; I <= row; I ++) {for (j = 0; j <column; j ++) {printf ("%-11 s", resultSet [index ++]);} printf ("\ n") ;}} else {printf ("no such person! \ N ") ;}/// alertContact () void alterContact () {// first, find the contact info to be altered. int I, j, index; int ret = 0; int row = 0; int column = 0; int age = 0; char * name = NULL; char * relation = NULL; char * phone = NULL; char * SQL = NULL; char ** resultSet = NULL; name = (char *) malloc (sizeof (char) * 10 ); printf ("Input the name you want to alter:"); scanf ("% s", name); SQL = (char *) malloc (sizeof (char) * 1 28); sprintf (SQL, "select * from info where name = '% S'", name); ret = sqlite3_get_table (db, SQL, & resultSet, & row, & column, 0); if (ret! = SQLITE_ OK) {fprintf (stderr, "select err: % s \ n", sqlite3_errmsg (db); return;} index = 0; if (row> 0) {for (I = 0; I <= row; I ++) {for (j = 0; j <column; j ++) {printf ("%-11 s", resultSet [index ++]);} printf ("\ n");} sqlite3_free_table (resultSet); // exist, then alter relation = (char *) malloc (sizeof (char) * 10); phone = (char *) malloc (sizeof (char) * 12 ); printf ("input the new data (age relation phone):"); scanf ("% d % s ", & Age, relation, phone); // printf ("% d, % s, % s \ n", name, age, relation, phone); sprintf (SQL, "update info set age = % d, relation = '% s', phone =' % s' where name = '% s';", age, relation, phone, name); // printf ("% s \ n", SQL); ret = sqlite3_exec (db, SQL, 0, 0); if (ret! = SQLITE_ OK) {printf ("failed! \ N ");} else {printf (" OK! \ N ") ;}free (relation); free (phone) ;}else {printf (" no such person! \ N ");} free (SQL); free (name);} // delete Contactvoid deleteContact () {int ret = 0; char * name = NULL; char * SQL = NULL; name = (char *) malloc (sizeof (char) * 10); SQL = (char *) malloc (sizeof (char) * 64 ); printf ("Input the name of contact you want to delete:"); scanf ("% s", name); sprintf (SQL, "delete from info where name = '% s';", name); // to be simple, there will be no warning if the contact does not exist ret = Sqlite3_exec (db, SQL, 0, 0); if (ret! = SQLITE_ OK) {printf ("delete err: % s", sqlite3_errmsg (db);} else {printf ("OK! ");} Free (name); free (SQL);} int main () {int ret = 0; int choice = 0; int ch = 0; char * errmsg = NULL; char * SQL = NULL; // open the db if exist or create it ret = sqlite3_open ("contact. db ", & db); if (ret) {fprintf (stderr," Cannot open database: % s \ n ", sqlite3_errmsg (db); sqlite3_close (db ); exit (1);} else {printf ("Open database successfully... \ n ");} // create the table info if not exists SQL = (char *) m Alloc (sizeof (char) * 128); // strcpy (SQL,); // printf ("Copy SQL successfully \ n"); ret = sqlite3_exec (db, "create table if not exists info (\ name varchar (10) primary key, \ age smallint, \ relation varchar (10), \ phone varchar (11);", 0, 0, & errmsg); if (ret! = SQLITE_ OK) {// printf ("Create table error \ n"); fprintf (stderr, "Create table err: % s \ n", sqlite3_errmsg (db ));} // printf ("Create table successfully \ n"); // insert some initial records, // it will cause a err if not the frist time, but that does not matter strcpy (SQL, "insert into info values ('zhu', 22, 'I', '123');"); ret = sqlite3_exec (db, SQL, 0, 0, & errmsg); if (ret! = SQLITE_ OK) {fprintf (stderr, "Insert record err: % s \ n", sqlite3_errmsg (db);} free (SQL ); // printf ("Insert record successfully \ n"); // main menu while (1) {choice = showMenu (); // show the menu and get the user's choose switch (choice) {case 1: displayAll (); // show all records in db break; case 2: addContact (); break; case 3: deleteContact (); break; case 4: alterContact (); break; case 5: findContact (); B Reak; default: break;} // if back to main menu or not printf ("\ nBack to Menu 1 (yes)/0 (no )? "); Scanf (" % d ", & ch); if (ch = 0) {break;} // printf (" \ 33 [2J "); system ("clear");} sqlite3_close (db); return 0 ;}