发布网友 发布时间:2022-04-20 14:58
共3个回答
懂视网 时间:2022-04-30 14:39
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
”);
return -1;
}
printf(“Connection successful
”);
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
”);
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 ”,sqlite3_column_name(stmt,col),val);
}
printf(“
”);
}
else if(retval == SQLITE_DONE)
{
// All rows finished
printf(“All rows fetched
”);
break;
}
else
{
// Some error encountered
printf(“Some error encountered
”);
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
参考:
SQLite/嵌入式数据库
标签:
热心网友 时间:2022-04-30 11:47
SQlite就是一个精简版的SQL库,可以不用SQL Server服务后台支持的情况下,实现选择、删除等SQL命令,主要用在独立的C/C++/Java客户端程序里。热心网友 时间:2022-04-30 13:05
http://ke.baidu.com/view/19310.htm