SQLite tutorial (13): C language programming example code (1), sqlite programming example

SQLite tutorial (13): C language programming example code (1), sqlite programming example

1. Get the table Schema information:

1) dynamically create a table.

2) obtain the table field information, such as the number of fields and the type of each field, based on the API provided by sqlite3.

3). Delete the table.

See the following code and key notes:
Copy codeThe Code is as follows:
# Include <sqlite3.h>
# Include <string>

Using namespace std;

Void doTest ()
{
Sqlite3 * conn = NULL;
// 1. Open the 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 a data table. If creation fails, use sqlite3_finalize to release the sqlite3_stmt object to prevent memory leakage.
If (sqlite3_prepare_v2 (conn, createTableSQL, len, & stmt, NULL )! = SQLITE_ OK ){
If (stmt)
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 3. Run the sqlite3_step command to create a table. For DDL and DML statements, sqlite3_step executes the correct return value.
// Only SQLITE_DONE is supported. For SELECT queries, if data is returned, SQLITE_ROW is returned. If data arrives at the end of the result set, SQLITE_ROW is returned.
// SQLITE_DONE.
If (sqlite3_step (stmt )! = SQLITE_DONE ){
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 4. Release the resource used to create the TABLE statement object.
Sqlite3_finalize (stmt );
Printf ("Succeed to create test table now. \ n ");
// 5. Construct the sqlite3_stmt object for querying 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. Obtain the number of fields in the result set based on the select statement object.
Int fieldCount = sqlite3_column_count (stmt2 );
Printf ("The column count is % d. \ n", fieldCount );
// 7. traverse the meta information of each field in the result set and obtain the declared type.
For (int I = 0; I <fieldCount; ++ I ){
// Because no data exists in the Table at this time, the Data Type in SQLite itself is dynamic, so when there is no data
// It cannot be obtained through the sqlite3_column_type function. At this time, sqlite3_column_type will only return SQLITE_NULL,
// The specific type can be returned only when data exists. Therefore, the sqlite3_column_decltype function is used to obtain the table sound.
// The Declaration type given in explicit time.
String stype = sqlite3_column_decltype (stmt2, I );
Stype = strlwr (char *) stype. c_str ());
// For the following parsing rules, see the "data type --> 1. Rule for determining field affinity" section in this series. The link is as follows:
// Http://www.bkjia.com/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. To facilitate the next test run, we need to delete the data table created by the function.
// 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 result is:
// 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) INSERT test data using the INSERT statement.
3). Delete the test table.
See the following code and key notes:

Copy codeThe Code is as follows:
# Include <sqlite3.h>
# Include <string>
# Include <stdio. h>

Using namespace std;

Void doTest ()
{
Sqlite3 * conn = NULL;
// 1. Open the 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 a data table. If creation fails, use sqlite3_finalize to release the sqlite3_stmt object to prevent memory leakage.
If (sqlite3_prepare_v2 (conn, createTableSQL, len, & stmt, NULL )! = SQLITE_ OK ){
If (stmt)
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 3. Run the sqlite3_step command to create a table. For DDL and DML statements, sqlite3_step executes the correct return value.
// Only SQLITE_DONE is supported. For SELECT queries, if data is returned, SQLITE_ROW is returned. If data arrives at the end of the result set, SQLITE_ROW is returned.
// SQLITE_DONE.
If (sqlite3_step (stmt )! = SQLITE_DONE ){
Sqlite3_finalize (stmt );
Sqlite3_close (conn );
Return;
}
// 4. Release the resource used to create the TABLE statement object.
Sqlite3_finalize (stmt );
Printf ("Succeed to create test table now. \ n ");

Int insertCount = 10;
// 5. Construct the sqlite3_stmt object for data insertion.
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. To facilitate the next test run, we need to delete the data table created by the function.
// 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 result is 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.