SQLite/嵌入式数据库

Include stdio.h, sqlite3.h and stdlib.h , stdlib.h is for malloc and sqlite3.h contains the standard function declarations needed for the required functionality.

// The number of queries to be handled,size of each query and pointer
int q_cnt = 5,q_size = 150,ind = 0;
char **queries = malloc(sizeof(char) * q_cnt * q_size);

q_cnt stored the number of queries we may want to do, q_size stores the max size of a SQL query, ind is the index.

**queries is a double array or matrix which stores the multiple queries. The total amount of storage to be allocated is sizeof(char) * q_cnt * q_size

// A prepered statement for fetching tables
sqlite3_stmt *stmt;

// Create a handle for database connection, create a pointer to sqlite3
sqlite3 *handle;

// try to create the database. If it doesnt exist, it would be created
// pass a pointer to the pointer to sqlite3, in short sqlite3**
retval = sqlite3_open(“sampledb.sqlite3″,&handle);
// If connection failed, handle returns NULL
if(retval)
{
printf(“Database connection failed\n”);
return -1;
}
printf(“Connection successful\n”);

We need to create a pointer to sqlite3 and sqlite3_stmt structures. sqlite3 is the structure which is to hold the database connection handle. sqlite3_stmt is just like a cursor to a database.

sqlite3_open function needs the address of the sqlite3 database instance on the disk. The second parameter is the pointer to the pointer to sqlite3 structure. One mistake which I stumbled upon was to create a sqlite3 ** handle and then pass it to this function. The correct way is to create a sqlite3* handle and then pass the pointer to it using the & operator

// Create the SQL query for creating a table
char create_table[100] = “CREATE TABLE IF NOT EXISTS users (uname TEXT PRIMARY KEY,pass TEXT NOT NULL,activated INTEGER)”;

// Execute the query for creating the table
retval = sqlite3_exec(handle,create_table,0,0,0);

// Insert first row and second row
queries[ind++] = “INSERT INTO users VALUES(‘manish’,‘manish’,1)”;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);
queries[ind++] = “INSERT INTO users VALUES(‘mehul’,‘pulsar’,0)”;
retval = sqlite3_exec(handle,queries[ind-1],0,0,0);

Create a table if it does not exist and then insert two rows. Note that sqlite3 does not support inserting two rows in one single query. Maybe I need to confirm this fact again, but I never worked for me ever.

// select those rows from the table
queries[ind++] = “SELECT * from users”;
retval = sqlite3_prepare_v2(handle,queries[ind-1],-1,&stmt,0);
if(retval)
{
printf(“Selecting data from DB Failed\n”);
return -1;
}

// Read the number of rows fetched
int cols = sqlite3_column_count(stmt);

Create a prepared statement for fetching data from the database usingsqlite3_prepare_v2 function call. The first parameter is the database handle itself which is a sqlite3* pointer. The second parameter is the SQL statement which needs to be executed. The third parameter tells upto how long the second parameter to be read. Pass -1 to make it read till line terminator. Fourth statement is the pointer to pointer to prepared statement structure. Take care of the pointer concept as I told about sqlite3 structure. The fifth parameter is filled with the unused portion of the query. Have a look at the official documentation.

sqlite3_column_count function gets the number of columns for the result fetched.

while(1)
{
// fetch a row’s status
retval = sqlite3_step(stmt);

if(retval == SQLITE_ROW)
{
// SQLITE_ROW means fetched a row

// sqlite3_column_text returns a const void* , typecast it to const char*
for(int col=0 ; col {
const char *val = (const char*)sqlite3_column_text(stmt,col);
printf(“%s = %s\t”,sqlite3_column_name(stmt,col),val);
}
printf(“\n”);
}
else if(retval == SQLITE_DONE)
{
// All rows finished
printf(“All rows fetched\n”);
break;
}
else
{
// Some error encountered
printf(“Some error encountered\n”);
return -1;
}
}

We have put this code in infinite while loop as we are not sure how much rows it contains. Usually, the table returns n+1 rows, where 1 extra row is for telling that all rows have been fetched. sqlite3_step returns the status which is actually an enumeration. Check all the results contants here. Two most used are SQLITE_DONE, SQLITE_ROW. The former tells that all the rows have been fetched, now the user can come out of this loop and continue. SQLITE_ROW tells that a valid row has been fetched.

// Close the handle to free memory
sqlite3_close(handle);
return 0;
}

sqlite3_close simply closes the database connection.

Save the code in a file named, say dataman.c , compile it using the command

$ gcc dataman.c -o dataman -l sqlite –std=c99

You obviously need to have sqlite development headers installed for compiling the same. The name of the package on Ubuntu is libsqlite3-dev

Official SQLite Documentation

参考：

1. 快速入门 http://www.sqlite.org/sqlite.html
2. “SQLite header and source version mismatch” http://jianshusoft.blog.51cto.com/2380869/824575
3. c语言的较为详细的例子 http://milky.manishsinha.net/2009/03/30/sqlite-with-c/
4. 
   

SQLite/嵌入式数据库

标签：