Objective:
The aim of this write-up is to show how easy it is to create table in sqlite table in C. The example below show how you can create/ open a DB file, create multiple table, Insert & retrieve records.
Getting Started:
Libraies you might require
libslqite3-development package
In ubuntu you could install it with the help of apt-get
CMD: sudo apt-get install libsqlite3-dev
To view the Db file you can use sqlitebrowser
CMD: sudo apt-get install sqlitebrowser
Quick Summary:
a. Line 49 Show how to open/create a DB file
b. Line 65, 76 create new table UserMenu & SettingsMenu
c. Line 80-84 , 86-90 Show how to insert into the database.
d. Line 104-133 : Sow how to reteve the data
f. Line 136: Finaly, show how to close the DB.
Source
1 /******************************************************************************
2 * To Compile: *
3 * !gcc -o sql_multitable_sample sql_multitable_sample.c -Wall -W *
4 * -O2 -Wl,-R/usr/local/lib -lsqlite3 *
5 * Create multiple tables in a file and adding new rows to the table. *
6 * Added callback function for the sql exec function to handle the query *
7 * results. *
8 ******************************************************************************/
9
10
11 #include<stdio.h>
12 #include<sqlite3.h>
13 #include<stdlib.h>
14
15 /* Callback called when the query is exceuted */
16 static int callback(void *NotUsed, int argc, char **argv, char **azColName)
17 {
18 printf ("\n ******** Inside Callback\n");
19 int i;
20 int rowpr=argc-1;
21 NotUsed=0;
22 printf("\n %s ",__FUNCTION__);
23 for(i=0; i<rowpr; i++)
24 printf("%s ",azColName[i]);
25
26 printf("%s\n",azColName[rowpr]);
27
28
29
30 for(i=0; i<rowpr; i++){
31 printf("%s ", argv[i] ? argv[i] : "NULL");
32
33 }
34 printf("%s\n", argv[rowpr] ? argv[rowpr] : "NULL");
35
36 return 0;
37 }
38
39 int main()
40 {
41 //return value
42 int retval;
43
44 int q_cnt = 10,q_size = 150,ind = 0;
45 char **queries = malloc(sizeof(char) * q_cnt * q_size);
46 // Creating statment & opening new DB
47 sqlite3_stmt *stmt;
48 sqlite3 *handle;
49 retval = sqlite3_open("FirstDB.sqlite3",&handle);
50 if(retval)
51 {
52 // If connection failed, handle returns NULL
53 printf("Database connection failed\n");
54 return -1;
55 }
56 printf("Connection successful\n");
57
58 // creating a multilple tables
59 char create_table[] = "CREATE TABLE IF NOT EXISTS UsersMenu (uname TEXT PRIMARY KEY,pass TEXT NOT NULL,activated INTEGER)";
60 char create_table1[] = "CREATE TABLE IF NOT EXISTS SettingsMenu (app_id TEXT PRIMARY KEY,gconf_path TEXT NOT NULL,activated INTEGER)";
61
62
63 char *errmsg = NULL;
64 // Execute the query
65 retval = sqlite3_exec(handle,create_table1,callback,handle,&errmsg);
66 if(retval != SQLITE_OK)
67 {
68 if(errmsg != NULL)
69 {
70 printf("\n@@Exec err:%s\n",errmsg);
71 sqlite3_free(errmsg);
72 }
73
74 }
75 printf ("\n---> create_table1 retvalue %d %s \n",retval, sqlite3_errmsg(handle));
76 retval = sqlite3_exec(handle,create_table,callback,0,0);
77 retval = 0;
78 printf ("\n ---> create_table retvalue %d %s \n",retval, sqlite3_errmsg(handle) );
79
80 // now Insert rows into table UsersMenu
81 queries[ind++] = "INSERT INTO UsersMenu VALUES('Speaker','Creative',1)";
82 retval = sqlite3_exec(handle,queries[ind-1],callback,0,0);
83 queries[ind++] = "INSERT INTO UsersMenu VALUES('Mouse','Logitech',0)";
84 retval = sqlite3_exec(handle,queries[ind-1],callback,0,0);
85
86 // now Insert rows into table SettingsMenu
87 queries[ind++] = "INSERT INTO SettingsMenu VALUES('media_player','$HOME/media',1)";
88 retval = sqlite3_exec(handle,queries[ind-1],callback,0,0);
89 queries[ind++] = "INSERT INTO SettingsiMenu VALUES('picture_viewer','$HOME/pic',0)";
90 retval = sqlite3_exec(handle,queries[ind-1],callback,0,0);
91
92 // select query from the table
93 queries[ind++] = "SELECT * from UsersMenu";
94 retval = sqlite3_prepare_v2(handle,queries[ind-1],-1,&stmt,0);
95 if(retval)
96 {
97 printf("Selecting data from DB Failed\n");
98 return -1;
99 }
100
101 // Read the number of rows fetched
102 int cols = sqlite3_column_count(stmt);
103 int col =0;
104 while(1)
105 {
106 // fetch a row's status
107 retval = sqlite3_step(stmt);
108
109 if(retval == SQLITE_ROW)
110 {
111 // SQLITE_ROW means fetched a row
112
113 // sqlite3_column_text returns a const void* , typecast it to const char*
114 for( col=0 ; col<cols;col++)
115 {
116 const char *val = (const char*)sqlite3_column_text(stmt,col);
117 printf("%s = %s\t",sqlite3_column_name(stmt,col),val);
118 }
119 printf("\n");
120 }
121 else if(retval == SQLITE_DONE)
122 {
123 // All rows finished
124 printf("All rows fetched\n");
125 break;
126 }
127 else
128 {
129 // Some error encountered
130 printf("Some error encountered\n");
131 return -1;
132 }
133 }
134
135 // Close the handle to free memory
136 sqlite3_close(handle);
137 return 0;
138 }
How to build & run
Issue the command build the source
CMD: gcc -o sql_multitable_sample sql_multitable_sample.c -Wall -W -O2 -Wl,-R/usr/local/lib -lsqlite3
To run excecute ./sql_multitable_sample.
Output: A DB file FirstDB.sqlite3 will be create in the current directory.
Now, view the DB file with SqliteBrowser
Issue CMD: sqlitebrowser FirstDB.sqlite3
Enjoy playing around with Sqlite … it really nice!!
Sreeni's Tech Blog 