SQLite provides a number of C function interfaces that you can use to manipulate the database. By using these interfaces, passing some standard SQL statements (with the char * type) to the SQLite function, SQLite will manipulate the database for you. SQLite, like MS Access, is a file-type database, which means that a database is a file in which many tables can be built, indexes, triggers, and so on, but it actually gets a file. Backing up this file backs up the entire database. SQLite does not require any database engine, which means that if you need SQLite to save some user data, you don't even need to install the database.
The basics of database operations are described below.
1. Basic Flow
(1) Key data structure:
The most commonly used in SQLite is the sqlite3 * type. Starting with the database, SQLite will be ready for this type of memory until the database is closed, the entire process needs to use this type. When the database is open, this type of variable represents the database you want to manipulate. Here is a detailed description.
(2) Open the database:
int Sqlite3_open (filename, sqlite3 * *); Use this function to start the database operation. You need to pass in two parameters, one is the database file name, such as: ... \\test\\testDatabase.db.
The filename does not need to exist, and if the file does not exist, SQLite will automatically establish it. If it exists, try to open it as a database file. where the sqlite3 * * parameter is the key data structure mentioned earlier. The bottom detail of this structure is how you don't turn it off.
The function return value indicates whether the operation is correct, and if it is SQLITE_OK, it indicates that the operation is normal. The associated return value SQLite defines some macros. The meaning of these macros can refer to the Sqlite3.h file. There is a detailed definition inside.
(3) Close the database:
int Sqlite3_close (sqlite3 *); If you start a database with Sqlite3_open, don't forget to close the database with this function at the end.
SQLite Database Operations Example
Copy Code code as follows:
#include "./sqlite3.h"
int main (int, char**)
{
Sqlite3 * db = NULL; Declaring SQLite key structure pointers
int result;
Need to pass in the pointer to db this pointer,
Because the Sqlite3_open function allocates memory for this pointer, let the DB pointer point to this memory area
result = Sqlite3_open (".. \\test\\testDatabase.db ", &db);/Open Database
If (Result!= SQLITE_OK)
{
return-1; Database open failed
}
Database Operation code
...-
Database Open successfully
Sqlite3_close (DB); Close Database
return 0;
}
This is a database operation process.
2. SQL statement operations (how to execute standard SQL syntax with SQLite)
(1) Execute SQL statements: int sqlite3_exec (sqlite3*, const char *sql, sqlite3_callback, void *, char **errmsg); This is the function that executes an SQL statement.
Parameter description:
The 1th argument no longer says, is the pointer to the previous open function. Said is the key data structure.
The 2nd Argument const char *SQL is an SQL statement that ends with a.
The 3rd parameter sqlite3_callback is the callback, and when the statement is executed, Sqlite3 will invoke the function you provided.
The 4th parameter void * Is the pointer you provide, you can pass any pointer parameter here, this parameter will eventually be passed to the callback function, if you do not need to pass the pointer to the callback function, you can fill in null. Wait, let's look at the callback function, and the use of this parameter.
The 5th parameter char * * ERRMSG is an error message. Note that the pointer is a pointer. There are a lot of fixed error messages inside the sqlite3. After executing the sqlite3_exec, you can refer to the pointer (direct printf ("%s\n", errmsg) to get a string of information that tells you where the error is. The Sqlite3_exec function points to the error message by modifying the pointer to the pointer you pass in, so that the sqlite3_exec function can get a specific error from the char*.
Description: Normally, both the sqlite3_callback and the void below it can be filled with NULL. Filling in null means you don't need a callback. For example, you do insert operation, do delete operation, there is no need to use callback. When you make a SELECT, you use a callback, because sqlite3 the data, and you have to tell you what data was found by the callback.
(2) Exec's callback: typedef int (*sqlite3_callback) (void*,int,char**, char**); Your callback function must be defined as the type of the above function.
SQLite Database Operation Example:
Copy Code code as follows:
Sqlite3 callback function
SQLite call this callback once every record is found
Para is the void parameter you passed in the sqlite3_exec.
With the para parameter, you can pass in some special pointers (such as class pointers, structure pointers), and then cast them to the corresponding type in this area.
(This is a void* type that must be cast to your type to be available). And then manipulate the data
N_column is the number of fields in this record (that is, how many columns this record has)
char * * Column_value is a key value, the detected data is stored here, is actually a 1-d array (do not think it is a 2-dimensional array),
Each element is a char * value and is a field content (represented by a string, ending with a)
char * * column_name is corresponding to Column_value, which indicates the field name of the field
int loadmyinfo (void * para, int n_column, char * * column_value, char * * column_name)
{
Here, I don't use the para parameter. Ignore its existence.
int i;
printf ("record contains%d fields \ n", n_column);
for (i = 0; i < n_column i + +)
{
printf ("Field name:%s?> field value:%s\n", Column_name[i], column_value[i]);
}
printf ("\ n");
return 0;
}
int main (int, char * *)
{
Sqlite3 * DB;
int result;
char * errmsg = NULL;
result = Sqlite3_open (".. \\test\\testDatabase.db ", &db);
If (Result!= SQLITE_OK)
{
return-1; Database open failed
}
Database Operation code
Create a test table named Mytable_1 with 2 fields: ID and name. Where the ID is an automatically incremented type,
Insert later, you can not specify this field, it will increase itself starting from 0
result = sqlite3_exec (db, "CREATE TABLE Mytable_1 (ID integer primary key autoincrement, name nvarchar)", NULL, NUL L, errmsg);
If (Result!= SQLITE_OK)
{
printf ("CREATE table failed, error code:%d, error reason:%s\n", result, errmsg);
}
Insert Some Records
result = sqlite3_exec (db, insert into Mytable_1 (name) VALUES (' Walk '), 0, 0, errmsg);
If (Result!= SQLITE_OK)
{
printf ("Insert record failed, error code:%d, error reason:%s\n", result, errmsg);
}
result = sqlite3_exec (db, insert into Mytable_1 (name) VALUES (' cycling '), 0, 0, errmsg);
If (Result!= SQLITE_OK)
{
printf ("Insert record failed, error code:%d, error reason:%s\n", result, errmsg);
}
result = sqlite3_exec (db, insert into Mytable_1 (name) VALUES (' Ride car '), 0, 0, errmsg);
If (Result!= SQLITE_OK)
{
printf ("Insert record failed, error code:%d, error reason:%s\n", result, errmsg);
}
result = sqlite3_exec (db, ' select * from Mytable_1 ', Loadmyinfo, NULL, errmsg);/Start Query Database Sqlite3_close (db ); Close Database
return 0;
}
Through the example above, you should know how to open a database, how to do database basic operations.
(3) querying the database without using a callback
Sqlite3_exec uses callbacks to perform a select operation. There is also a way to query directly without requiring a callback. However, I personally feel that the callback is good, because the code can be more neat, but with the callback is cumbersome, you have to declare a function, if this function is a class member function, you have to declare it as static (C + + member function actually hides a parameter: this,c++ When invoking a member function of a class, it is implied that the class pointer is passed in as the first parameter of the function. As a result, this causes a SQLite callback function to be a parameter that does not match the previous argument. Only when declaring a member function static does it have no superfluous implied this parameter. Although the callback appears to be neatly coded, you sometimes want a select query that is not callback. This can be done through the sqlite3_get_table function.
int sqlite3_get_table (sqlite3*, const char *sql, char ***resultp, int *nrow, int *ncolumn, char **errmsg);
Parameter description:
The 1th argument no longer says more, look at the previous example.
The 2nd parameter is the SQL statement, which is the same as the SQL in Sqlite3_exec. is a very ordinary char * string at the end of a.
The 3rd parameter is the query result, which is still a one-dimensional array (do not assume that it is a two-dimensional array, let alone a three-dimensional array). It has a memory layout: The first row is the field name, followed by the value of each field. Here's a case.
The 4th parameter is how many records are queried (that is, how many rows are detected).
The 5th argument is how many fields (how many columns).
The 6th parameter is the error message, as in the previous, there is not much to say.
SQLite Database Operation Example:
Copy Code code as follows:
int main (int, char * *)
{
SQLITE3* DB;
int result;
char* errmsg = NULL;
Char **dbresult; is char * * type, two * number
int Nrow, Ncolumn;
int I, J;
int index;
result = Sqlite3_open (".. \\test\\testDatabase.db ", &db);
If (Result!= SQLITE_OK)
{
return-1; Database open failed
}
Database Operation code
Suppose the Mytable_1 table has been created before
Start query, the incoming dbresult is already char * *, and here Add a & address character, passed into the char * * *
result = sqlite3_get_table (db, "select * from Mytable_1", &dbresult, &nrow, &ncolumn, &errmsg);
if (SQLITE_OK = result)//query success
{
index = Ncolumn; I said earlier dbresult the first row of data is the field name, starting with the Ncolumn index is the real data
printf ("%d records found \ \ n", nrow);
for (i = 0; i < nrow; i++)
{
printf ("%d Records \ n", i+1);
for (j = 0; J < Ncolumn; J + +)
{
printf ("Field name:%sß> field value:%s\n", Dbresult[j], Dbresult [index]);
Dbresult field values are contiguous, from the No. 0 index to the nColumn-1 index are field names
Starting with the Ncolumn index, followed by the field values,
It represents a two-dimensional table (the traditional row and column notation) in a flat form
++index;
}
printf ("\ n");
}
}
Here, regardless of whether the database query is successful, release the char** query results, using the features provided by SQLite to release
Sqlite3_free_table (Dbresult);
Sqlite3_close (db);//Close Database
return 0;
}
By this example, Sqlite3 's common usage has been introduced. With the above method, the majority of database requirements can be fully met.
3. Transaction processing
SQLite is supported for transaction processing. If you know that you want to delete a lot of data synchronously, do not imitate them into a unified transaction. Usually once sqlite3_exec is a transaction, if you want to delete 10,000 data, SQLite did 10,000 times: Start a new transaction-> delete a data-> submit a transaction-> start a new transaction. The process. This operation is very slow. Because time is spent on starting a transaction, committing a transaction. You can make these similar operations into a transaction so that if the operation is wrong, the transaction can be rolled back. The operation of a transaction does not have a special interface function, it is a normal SQL statement:
were as follows:
Copy Code code as follows:
int result;
result = sqlite3_exec (db, "BEGIN Transaction", 0, 0, &zerrormsg); Start a transaction
result = sqlite3_exec (db, "Commit Transaction", 0, 0, &zerrormsg); Commit a transaction
result = sqlite3_exec (db, "ROLLBACK TRANSACTION", 0, 0, &zerrormsg); Rolling back a transaction
