SQLite Tutorial (13): C language Programming Instance code (1) _sqlite

First, get schema information for the table:

1. Create a table dynamically.

2. According to the API provided by Sqlite3, get information about the table field, such as the number of fields and the type of each field.

3). Deletes the table.

See the following code and key comments:

Copy Code code as follows:

#include <sqlite3.h>
#include <string>

using namespace Std;

void Dotest ()
{
sqlite3* conn = NULL;
1. Open Database
int result = Sqlite3_open ("d:/mytest.db", &conn);
If (result!= Sqlite_ok) {
Sqlite3_close (conn);
Return
}
Const char* Createtablesql =
"CREATE TABLE testtable (int_col int, float_col real, String_col TEXT)";
sqlite3_stmt* stmt = NULL;
int len = strlen (createtablesql);
2. Prepare to create the data table, and if the 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
}
3. Executes the statement that creates the table through the Sqlite3_step command. For DDL and DML statements, Sqlite3_step performs the correct return value
Only Sqlite_done, in the case of a select query, returns if there is data returned Sqlite_row, and when the end of the result set is reached
Sqlite_done.
if (Sqlite3_step (stmt)!= Sqlite_done) {
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
Return
}
4. Frees the resources that create a table statement object.
Sqlite3_finalize (stmt);
printf ("Succeed to create Test table now.\n");
5. Sqlite3_stmt object that constructs the query table data.
Const char* 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
}
6. Gets the number of fields in the result set based on the object of the SELECT statement.
int fieldcount = Sqlite3_column_count (STMT2);
printf ("The column count is%d.\n", fieldcount);
7. Traverses each field meta information in the result set and gets the type of its declaration.
for (int i = 0; i < FieldCount; ++i) {
Because there is no data in the table, and then there is the data type in the SQLite itself is dynamic, so when there is no data
Cannot get through the Sqlite3_column_type function, this time Sqlite3_column_type will only return sqlite_null,
It is not possible to return a specific type until there is data, so the Sqlite3_column_decltype function is used to get the table sound
The type of declaration given at the time of Ming.
String stype = Sqlite3_column_decltype (stmt2,i);
Stype = STRLWR ((char*) stype.c_str ());
The following parsing rules see the series "Data type-->1." The rules for determining field affinity are linked as follows:
Http://www.jb51.net/article/65424.htm
if (Stype.find ("int")!= String::npos) {
printf ("The type of%dth column is integer.\n", i);
else if (Stype.find ("char")!= String::npos
|| Stype.find ("text")!= String::npos) {
printf ("The type of%dth column is text.\n", i);
else if (Stype.find ("real")!= String::npos
|| Stype.find ("Floa")!= String::npos
|| Stype.find ("Doub")!= String::npos) {
printf ("The type of%dth column is double.\n", i);
}
}
Sqlite3_finalize (STMT2);
8. In order to facilitate the next test run, we need to remove the data table created by the function, otherwise it will not be available at the next run
Create the table because it already exists.
Const char* 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
}
if (Sqlite3_step (stmt3) = = Sqlite_done) {
printf ("The test table has been dropped.\n");
}
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
}

int main ()
{
Dotest ();
return 0;
}
The output results are:
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.
The type of 2th column is TEXT.
The test table has been dropped.

II. General Data insertion:

1). Create a test data table.
2. Inserts the test data through the INSERT statement.
3. Delete the test table.
See the following code and key comments:

Copy Code code as follows:

#include <sqlite3.h>
#include <string>
#include <stdio.h>

using namespace Std;

void Dotest ()
{
sqlite3* conn = NULL;
1. Open Database
int result = Sqlite3_open ("d:/mytest.db", &conn);
If (result!= Sqlite_ok) {
Sqlite3_close (conn);
Return
}
Const char* Createtablesql =
"CREATE TABLE testtable (int_col int, float_col real, String_col TEXT)";
sqlite3_stmt* stmt = NULL;
int len = strlen (createtablesql);
2. Prepare to create the data table, and if the 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
}
3. Executes the statement that creates the table through the Sqlite3_step command. For DDL and DML statements, Sqlite3_step performs the correct return value
Only Sqlite_done, in the case of a select query, returns if there is data returned Sqlite_row, and when the end of the result set is reached
Sqlite_done.
if (Sqlite3_step (stmt)!= Sqlite_done) {
Sqlite3_finalize (stmt);
Sqlite3_close (conn);
Return
}
4. Frees the resources that create a table statement object.
Sqlite3_finalize (stmt);
printf ("Succeed to create Test table now.\n");

int insertcount = 10;
5. Builds the Sqlite3_stmt object that inserts the data.
Const char* Insertsql = "INSERT into TestTable VALUES (%d,%f, '%s ')";
Const char* TestString = "This is a test.";
Char sql[1024];
sqlite3_stmt* stmt2 = NULL;
for (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);
Return
}
if (Sqlite3_step (STMT2)!= Sqlite_done) {
Sqlite3_finalize (STMT2);
Sqlite3_close (conn);
Return
}
printf ("Insert succeed.\n");
}
Sqlite3_finalize (STMT2);
6. In order to facilitate the next test run, we need to remove the data table created by the function, otherwise it will not be available at the next run
Create the table because it already exists.
Const char* 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
}
if (Sqlite3_step (stmt3) = = Sqlite_done) {
printf ("The test table has been dropped.\n");
}
Sqlite3_finalize (STMT3);
Sqlite3_close (conn);
}

int main ()
{
Dotest ();
return 0;
}
The 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.
Insert succeed.
The test table has been dropped.