SQLite Learning Manual (example code < one >)

First, get the schema information of the table:

1). Dynamically create a table.
2). Based on the API provided by Sqlite3, get information about table fields, such as the number of field and the type of each field.
3). Delete the table.
See the following code and key notes:
1 #include <sqlite3.h>
2 #include <string>
3
4 using namespace Std;
5
6 void Dotest ()
7 {
8 sqlite3* conn = NULL;
9//1. Open Database
Ten int result = Sqlite3_open ("d:/mytest.db", &conn);
One-if (result! = SQLITE_OK) {
Sqlite3_close (conn);
return;
14}
Const char* Createtablesql =
"CREATE TABLE testtable (int_col int, float_col REAL, String_col TEXT)";
sqlite3_stmt* stmt = NULL;
int len = strlen (createtablesql);
19//2. Prepare to create a data table, and if creation fails, you need to release the Sqlite3_stmt object with sqlite3_finalize to prevent memory leaks.
if (SQLITE3_PREPARE_V2 (conn,createtablesql,len,&stmt,null)! = SQLITE_OK) {
if (stmt)
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
return;
25}
26//3. Executes the statement that creates the table by using the Sqlite3_step command. For DDL and DML statements, Sqlite3_step performs the correct return value
27//Only Sqlite_done, for select queries, if there is data returned Sqlite_row, returns when the end of the result set is reached
//sqlite_done.
if (Sqlite3_step (stmt)! = Sqlite_done) {
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
return;
33}
34//4. Frees the resource that created the table statement object.
Sqlite3_finalize (stmt);
printf ("Succeed to create Test table now.\n");
37//5. Constructs a Sqlite3_stmt object that queries the table data.
char* Const SELECTSQL = "SELECT * from testtable WHERE 1 = 0";
sqlite3_stmt* stmt2 = NULL;
if (Sqlite3_prepare_v2 (Conn,selectsql,strlen (selectsql), &stmt2,null)! = SQLITE_OK) {
if (STMT2)
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
return;
45}
46//6. Gets the number of fields in the result set, based on the object of the SELECT statement.
FieldCount int =Sqlite3_column_count(STMT2);
-printf ("The column count is%d.\n", fieldcount);
49//7. Iterate through each field meta information in the result set and get the type of its declaration.
(int i = 0; i < FieldCount; ++i) {
51//Because there is no data in the table at this time, then there is the data type in SQLite itself is dynamic, so when there is no data
52//Can not be obtained through the Sqlite3_column_type function, at this time Sqlite3_column_type will only return sqlite_null,
53//The specific type is not returned until the data is available, so the Sqlite3_column_decltype function is used to get the table sound
54//Mingshi the type of claim given.
The string stype = Sqlite3_column_decltype (stmt2,i);
Stype = STRLWR ((char*) stype.c_str ());
57//The following parsing rules are shown in the series "Data type-->1." The rules that determine the affinity of a field are linked as follows:
//http://www.cnblogs.com/stephen-liu74/archive/2012/01/18/2325258.html
if (Stype.find ("int") = String::npos) {
printf ("The type of%dth column is integer.\n", i);
Stype.find} else if ("char")! = String::npos
62 | | Stype.find ("text")! = String::npos) {
("The type of%dth column is text.\n", i);
Stype.find} else if ("real")! = String::npos
65 | | Stype.find ("Floa")! = String::npos
66 | | Stype.find ("Doub")! = String::npos) {
In printf ("The type of%dth column is double.\n", i);
68}
69}
Sqlite3_finalize (STMT2);
71//8. To facilitate the next test run, we need to delete the data table created by the function, otherwise it will not be available at the next run
72//create the table because it already exists.
The char* const DROPSQL = "DROP TABLE testtable";
sqlite3_stmt* stmt3 = NULL;
SQLITE3_PREPARE_V2 (Conn,dropsql,strlen (dropsql), &stmt3,null)! = SQLITE_OK) {
if (STMT3)
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
return;
80}
Bayi if (sqlite3_step (stmt3) = = Sqlite_done) {
been printf ("The test table has a dropped.\n");
83}
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
86}
87
A. Int. int main ()
89 {
Dotest ();
return 0;
92}
93//Output result is:
94//succeed to create test table now.
//the column Count is 3.
//the type of 0th column is INTEGER.
//the type of 1th column is DOUBLE.
98//the type of 2th column is TEXT.
The//the test table has been dropped.

II. General Data insertion:

1). Create a test data table.
2). Insert the test data through the INSERT statement.
3). Delete the test table.
See the following code and key notes:
1 #include <sqlite3.h>
2 #include <string>
3 #include <stdio.h>
4
5 using namespace Std;
6
7 void Dotest ()
8 {
9 sqlite3* conn = NULL;
10//1. Open Database
one int result = Sqlite3_open ("d:/mytest.db", &conn);
if (Result! = SQLITE_OK) {
Sqlite3_close (conn);
return;
15}
Const char* Createtablesql =
"CREATE TABLE testtable (int_col int, float_col REAL, String_col TEXT)";
sqlite3_stmt* stmt = NULL;
int len = strlen (createtablesql);
20//2. Prepare to create a data table, and if creation fails, you need to release the Sqlite3_stmt object with sqlite3_finalize to prevent memory leaks.
if (SQLITE3_PREPARE_V2 (conn,createtablesql,len,&stmt,null)! = SQLITE_OK) {
if (stmt)
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
return;
26}
27//3. Executes the statement that creates the table by using the Sqlite3_step command. For DDL and DML statements, Sqlite3_step performs the correct return value
28//Only Sqlite_done, for select queries, if there is data returned Sqlite_row, returns when the end of the result set is reached
//sqlite_done.
if (Sqlite3_step (stmt)! = Sqlite_done) {
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
return;
34}
35//4. Frees the resource that created the table statement object.
Sqlite3_finalize (stmt);
Notoginseng printf ("Succeed to create Test table now.\n");
38
Insertcount int = 10;
40//5. Constructs a Sqlite3_stmt object that inserts data.
char* Const INSERTSQL = "INSERT into TestTable VALUES (%d,%f, '%s ')";
The const char* teststring = "This is a test.";
(sql[1024 char);
sqlite3_stmt* stmt2 = NULL;
(int i = 0; i < Insertcount; ++i) {
sprintf (Sql,insertsql,i,i * 1.0,teststring);
if (SQLITE3_PREPARE_V2 (Conn,sql,strlen (SQL), &stmt2,null)! = SQLITE_OK) {
if (STMT2)
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
The return;
52}
if (Sqlite3_step (stmt2)! = Sqlite_done) {
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
The return;
57}
printf ("Insert succeed.\n");
59}
Sqlite3_finalize (STMT2);
61//6. To facilitate the next test run, we need to delete the data table created by the function, otherwise it will not be available at the next run
62//Create the table because it already exists.
char* Const DROPSQL = "DROP TABLE testtable";
sqlite3_stmt* stmt3 = NULL;
if (Sqlite3_prepare_v2 (Conn,dropsql,strlen (dropsql), &stmt3,null)! = SQLITE_OK) {
if (STMT3)
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
return;
70}
if (Sqlite3_step (stmt3) = = Sqlite_done) {
been printf ("The test table has a dropped.\n");
73}
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
76}
77
int main ()
79 {
Dotest ();
Bayi return 0;
82}
83//Output results are as follows:
//succeed to create test table now.
//insert succeed.
//insert succeed.
//insert succeed.
//insert succeed.
//insert succeed.
//insert succeed.
//insert succeed.
//insert succeed.
//insert succeed.
94//insert succeed.
//the test table has been dropped.