C language programming based on SQLite Database

I. SQLite operations
SQLite provides some C function interfaces that you can use to operate databases. By using these interfaces, you can pass some standard SQL statements (in char * type) to the SQLite function, and SQLite will operate the database for you.
SQLite and MS access is the same as a file database, that is to say, a database is a file, this database can create a lot of tables, you can create indexes, triggers, etc.,, what it actually gets is a file. Back up this file to back up the entire database.
SQLite does not need any database engine, which means that if you need SQLite to save some user data, you don't even need to install the database (if you have to install sqlserver to run a small software, it's too dark ).
The following describes basic database operations.
1. Basic Process
(1) Key Data Structure
The most common type in SQLite is sqlite3. Since the database is opened, SQLite needs to prepare memory for this type until the database is closed. This type is used throughout the process. When the database is opened, this type of variable represents the database you want to operate on. The following is a detailed introduction.
(2) Open the database
Int sqlite3_open (file name, sqlite3 **);
Use this function to start database operations.
You need to input two parameters. One is the database file name, for example, C: \ dongchunguang_database.db.
The file name does not need to exist. If the file does not exist, SQLite will automatically create it. If it exists, try to open it as a database file.
The sqlite3 ** parameter is the key data structure mentioned above. Do not close the underlying details of this structure.
The Return Value of the function indicates whether the operation is correct. If it is sqlite_ OK, the operation is normal. Related return values SQLite defines some macros. For more information about these macros, see the sqlite3.h file. There are detailed definitions in it (by the way, sqlite3 code annotation rate claims to be very high, but it is indeed very high. As long as you can read English, SQLite can help you learn a lot ).
The following describes how to shut down the database and give a reference code.
(3) shut down the database
Int sqlite3_close (sqlite3 *);
If you have enabled a database with sqlite3_open, do not forget to use this function to close the database.
The following is a simple code:
Extern "C"
# Include "./sqlite3.h"
};
Int main (INT, char **)
Sqlite3 * DB = NULL; // declare SQLite key structure pointer
Int result;
// Open the database
// You need to pass in the pointer of the database, because the sqlite3_open function needs to allocate memory for this pointer, And the DB pointer must point to this memory Zone
Result = sqlite3_open ("c: \ dcg_database.db", & dB );
If (result! = Sqlite_ OK)
{
// Failed to open the database
Return-1;
// Database operation code
//...
// The database is successfully opened.
// Close the database
Sqlite3_close (db );
Return 0;
This is a database operation process.
2. SQL statement operations
Some commands in SQLite are different from those in MySQL. For example, you can use. Help to view the commands starting with a good command.
However, the standard SQL syntax SQLite is supported.
(1) Execute SQL statements
Int sqlite3_exec (sqlite3 *, const char * SQL, sqlite3_callback, void *, char ** errmsg );
This is the function used to execute an SQL statement.
The first parameter is a pointer obtained by the open function. It is the key data structure.
The 2nd const char * SQL is an SQL statement ending with \ 0.
The 3rd sqlite3_callback parameter is a callback. After this statement is executed, sqlite3 will call the function you provided. (What is a callback function? Learn from other materials)
The third 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 enter null. Let's take a look at the method of the callback function and the use of this parameter.
The first parameter char ** errmsg is the error message. Note the pointer. Sqlite3 contains many fixed error messages. After sqlite3_exec is executed, you can view this pointer (directly printf ("% s \ n", errmsg) When execution fails to get a string, this string of Information tells you where the error is. The sqlite3_exec function points the pointer you provided to the error message by modifying the pointer you passed in, so that the sqlite3_exec function can use this char * to get a specific error message.
Note: Normally, either sqlite3_callback or void * after it can be null. If this parameter is set to null, callback is not required. For example, if you perform insert and delete operations, there is no need to use callback. When you do select, you need to use a callback, because sqlite3 checks the data and uses a callback to tell you what data has been found.
(2) exec callback
Typedef int (* sqlite3_callback) (void *, Int, char **, char **);
Your callback function must be defined as the type of the above function. The following is a simple example:
// Sqlite3 callback function
// Each time SQLite finds a record, this callback is called once.
Int loadmyinfo (void * para, int n_column, char ** column_value, char ** column_name)
// Para is the void * parameter you pass in sqlite3_exec
// Using the Para parameter, you can pass in some special pointers (such as class pointers and structure pointers), and then forcibly convert them to the corresponding type (here it is the void * type, must be forcibly converted to your type ). Then operate on the data
// N_column indicates the number of fields in the record (that is, the number of columns in the record)
// Char ** column_value is a key value. The retrieved data is saved here. It is actually a one-dimensional array (not a two-dimensional array ), each element is a char * value and a field content (expressed as a string and ended with \ 0)
// Char ** column_name corresponds to column_value, indicating the field name of this field
// Here, I do not use the Para parameter. Ignore its existence.
Int I;
Printf ("The record contains % d fields \ n", n_column );
For (I = 0; I <n_column; I ++)
Printf ("field name: % s region> 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 ("c: \ dcg_database.db", & dB );
If (result! = Sqlite_ OK)
{
// Failed to open the database
Return-1;
// Database operation code
// Create a test table named mytable_1 with two fields: ID and name. ID is an automatically added type. This field can not be specified during future insert operations. It will increase from 0.
Result = sqlite3_exec (dB, "create table mytable_1 (ID integer primary key autoincrement, name nvarchar (32)", null, null, errmsg );
If (result! = Sqlite_ OK)
Printf ("failed to create table, error code: % d, error cause: % 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 cause: % s \ n", result, errmsg );
Result = sqlite3_exec (dB, "insert into mytable_1 (name) values ('bike jacking')", 0, 0, errmsg );
If (result! = Sqlite_ OK)
Printf ("Insert record failed, error code: % d, error cause: % s \ n", result, errmsg );
Result = sqlite3_exec (dB, "insert into mytable_1 (name) values ('chelay')", 0, 0, errmsg );
If (result! = Sqlite_ OK)
Printf ("Insert record failed, error code: % d, error cause: % s \ n", result, errmsg );
// Start querying the database
Result = sqlite3_exec (dB, "select * From mytable_1", loadmyinfo, null, errmsg );
// Close the database
Sqlite3_close (db );
Return 0;
The following example shows how to open a database and perform basic database operations.
With this knowledge, you can basically cope with a lot of database operations.
(3) do not use callback to query the database
The sqlite3_exec described above uses a callback to execute the Select Operation. There is also a way to directly query without callback. However, I personally think the callback is good, because the code can be more neat, but it is very troublesome to use the callback. You must declare a function. If this function is a class member function, you have to declare it static (ask why? This is the basis of C ++. The C ++ member function actually hides a parameter: This, when C ++ calls a class member function, the class pointer is implicitly passed as the first parameter of the function. The result is inconsistent with the parameters of the SQLite callback function described above. Only when the member function is declared as static does it have unnecessary implicit this parameter ).
Although the callback code looks neat, sometimes you still want select queries that are not called back. 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 );
The 1st parameters are no longer mentioned. refer to the previous example.
The 2nd parameters are SQL statements, which are the same as SQL statements in sqlite3_exec. It is a common char * string ending with \ 0.
The first parameter is the query result, and it is still a one-dimensional array (do not think it is a two-dimensional array, or a three-dimensional array ). Its memory layout is: the first line is the field name, followed by the value of each field. The following is an example.
The 4th parameter indicates how many records are queried (that is, how many rows are queried ).
The number of fields (columns) of the 5th parameter ).
The first parameter is the error message, which is the same as above.
The following is a simple example:
Int main (INT, char **)
Sqlite3 * dB;
Int result;
Char * errmsg = NULL;
Char ** dbresult; // It is of the char ** type and has two * numbers.
Int nrow, ncolumn;
Int I, J;
Int index;
Result = sqlite3_open ("c: \ dcg_database.db", & dB );
If (result! = Sqlite_ OK)
{
// Failed to open the database
Return-1;
}
// Database operation code
// Assume that the mytable_1 table has been created.
// Start the query. The passed dbresult is already char **. Here, an & get address character is added, and the passed result is Char ***
Result = sqlite3_get_table (dB, "select * From mytable_1", & dbresult, & nrow, & ncolumn, & errmsg );
If (sqlite_ OK = Result)
{
// Query successful
Index = ncolumn; // the data in the first row before dbresult is the field name, which is the real data starting from the ncolumn index.
Printf ("% d records \ n", nrow );
For (I = 0; I <nrow; I ++)
{
Printf ("Number % d records \ n", I + 1 );
For (j = 0; j <ncolumn; j ++)
{
Printf ("field name: % s success> Field Value: % s \ n", dbresult [J], dbresult [Index]);
+ Index; // The Field Value of dbresult is continuous. From The 0th index to the ncolumn-1 index, the field name starts from the ncolumn index, followed by the field value, it uses a flat form to represent a two-dimensional table (traditional row-column representation ).
}
Printf ("------- \ n ");
}
}
// Here, the char query result is released no matter whether the database query is successful or not, and the function provided by SQLite is used for release.
Sqlite3_free_table (dbresult );
// Close the database
Sqlite3_close (db );
Return 0;
Until this example, the common usage of sqlite3 has been described.
With the above method, coupled with SQL statements, can fully meet the needs of the vast majority of databases.
However, in one case, the preceding method cannot be used: insert and Select Binary are required. The preceding method cannot be used to process binary data.
Three-Action binary
I made up a fruit program to insert the fruit image into the database and read it. Of course, it is still a fruit. (Not provided here)
SQLite requires an auxiliary data type to operate binary data: sqlite3_stmt *.
This data type records an "SQL statement ". Why do I use double quotation marks for "SQL statements? Because you can regard the content represented by sqlite3_stmt * as an SQL statement, but in fact it is not a well-known SQL statement. It is an internal data structure that has resolved SQL statements and marked records by SQLite itself.
Because this structure has been parsed, you can insert binary data into this statement. Of course, inserting binary data into the sqlite3_stmt structure cannot directly use memcpy or use a + number like STD: string. The function provided by SQLite must be used for insertion.
(1) Write binary data
The following describes the steps for writing binary data.
To insert binary data, make sure that the field type of this table is blob. I suppose there is such a table:
Create Table tbl_2 (ID integer, file_content BLOB)
First declare
Sqlite3_stmt * Stat;
Then, resolve an SQL statement to the stat structure:
Sqlite3_prepare (dB, "insert into tbl_2 (ID, file_content) values (10 ,? ) ",-1, & stat, 0 );
The above function completes SQL statement parsing. The first parameter, like the previous one, is a sqlite3 * type variable, and the second parameter is an SQL statement.
What is special about this SQL statement is that there is? . In the sqlite3_prepare function ,? Number indicates an undefined value, which is inserted only when its value is equal.
The third parameter is-1, which indicates the length of the preceding SQL statement. If it is less than 0, SQLite automatically calculates its length (the SQL statement is treated as a string ending with \ 0 ).
The fourth parameter is the pointer of sqlite3_stmt. The parsed SQL statement is placed in this structure.
I do not know what the fifth parameter is. It can be set to 0.
If this function is successfully executed (sqlite_ OK is returned and stat is not null), you can start to insert binary data.
Sqlite3_bind_blob (stat, 1, pdata, (INT) (length_of_data_in_bytes), null); // pdata is the data buffer, length_of_data_in_bytes is the data size, in bytes
This function has a total of five parameters.
1st parameters: the sqlite3_stmt * type variable obtained by prepare.
2nd parameters :? Number index. In the preceding prepare SQL statement, is there? Number. If there are multiple? How can I insert a number? The method is to change the bind_blob function's 2nd parameters. I write 1 for this parameter, indicating that the value inserted here should replace the first? (Here, the index starts counting from 1, not from 0 ). If you have multiple? Number, write multiple bind_blob statements, and change their 2nd parameters to replace them with different? . If yes? No. SQLite is null.
3rd parameters: Start pointer of binary data.
4th parameters: the length of binary data, in bytes.
5th parameters: it is a callback function that tells SQLite to call this function after processing data to analyze your data. I have not used this parameter, so I cannot understand it deeply. However, it is generally null, and the memory to be released is released using code.
After the BIND is complete, the binary data enters your "SQL statement. Now you can save it to the database:
Int result = sqlite3_step (STAT );
The SQL statement indicated by stat is written to the database.
Finally, construct sqlite3_stmt to release:
Sqlite3_finalize (STAT); // analyze the content just allocated
(2) read binary data
The following describes how to read binary data.
As before, declare the sqlite3_stmt * type variable first:
Sqlite3_stmt * Stat;
Then, resolve an SQL statement to the stat structure:
Sqlite3_prepare (dB, "select * From tbl_2",-1, & stat, 0 );
After the prepare is successful (the returned value is sqlite_ OK), query the data.
Int result = sqlite3_step (STAT );
The return value of this statement is sqlite_row, indicating success (not sqlite_ OK ).
You can run the sqlite3_step function cyclically to query a record at a time. If the returned value is not sqlite_row, the query is complete.
Then obtain the value of the first field: ID. ID is an integer. Use the following statement to obtain its value:
Int id = sqlite3_column_int (stat, 0); // The first field content is obtained. The value starts from 0 because the ID field of my table is the first field, so here I enter 0
Next we will get the value of file_content, because file_content is binary, so I need to get its pointer and its length:
Const void * pfilecontent = sqlite3_column_blob (stat, 1 );
Int Len = sqlite3_column_bytes (stat, 1 );
In this way, the binary value is obtained.
After saving the content of pfilecontent, do not forget to release the sqlite3_stmt structure:
Sqlite3_finalize (STAT); // analyze the content just allocated
(3) Reuse the sqlite3_stmt Structure
If you need to repeat the sqlite3_stmt structure parsed by sqlite3_prepare, you need to use the function: sqlite3_reset.
Result = sqlite3_reset (STAT );
In this way, the stat structure becomes the status when sqlite3_prepare is completed, and you can re-bind the content for it.
4. Transaction Processing
SQLite supports transaction processing. If you know that you want to delete a lot of data synchronously, do not make them into a unified transaction.
A sqlite3_exec is usually a transaction. If you want to delete 10 thousand pieces of data, SQLite performs 10 thousand times: start a new transaction-> delete a piece of data-> commit a transaction-> Start a new transaction->... . This operation is very slow. Because time is spent on starting and committing transactions.
You can make these similar operations into a transaction, so that if the operation is incorrect, you can roll back the transaction.
Transactions do not have special interface functions. They are just a common SQL statement:
They are 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); // submit the transaction
Result = sqlite3_exec (dB, "rollback transaction", 0, 0, & zerrormsg); // roll back the transaction
For the code, refer to the following article. I have compiled a program on this basis. The problem is that after we use fseek and ftell to find the length of the file, remember to use seek_set to set the position pointer to the beginning of the file. Never forget this. Code Conversion here
1. First include the relevant header file:
# Include "sqlite3.h"
# Pragma comment (Lib, "sqlite3.lib ")
2. Create a database:
Sqlite3 * dB;
Sqlite3_stmt * Stat;
Char * zerrmsg = 0;
Sqlite3_open ("F: \ c ++ \ SQLite \ bin. DB", & dB );
If (DB = NULL)
{
Return;
}
Sqlite3_exec (dB, "create table image (filename varchar (128) Unique, img blob);", & zerrmsg );
Sqlite3_close (db );
3. Insert images into the database:
Sqlite3 * dB;
Sqlite3_stmt * Stat;
Char * zerrmsg = 0;
File * fp = NULL;
Long filesize = 0;
Char * ffile = NULL;
Char * Buf = NULL;
Sqlite3_open ("F: \ c ++ \ SQLite \ bin. DB", & dB );
If (DB = NULL)
{
Return;
}
Fp = fopen ("F: \ c ++ \ SQLite \ 131.jpg"," rb ");
If (FP! = NULL)
{
Fseek (FP, 0, seek_end );
Filesize = ftell (FP );
Fseek (FP, 0, seek_set );
Ffile = new char [filesize];
Size_t SZ = fread (ffile, sizeof (char), filesize, FP );
Fclose (FP );
}
// Sqlite3_exec (dB, "create table image (filename varchar (128) Unique, img blob);", & zerrmsg );
Sqlite3_prepare (dB, "insert into image values ('girl.jpg ',?) ",-1, & stat, 0 );
Sqlite3_bind_blob (stat, 1, ffile, filesize, null );
Sqlite3_step (STAT );
Delete [] ffile;
Sqlite3_finalize (STAT );
Sqlite3_close (db );
4. Export images:
Sqlite3 * dB;
Sqlite3_stmt * stat2;
Char * zerrmsg = 0;
Long filesize = 0;
Int RC;
Char * Buf = NULL;
Sqlite3_open ("F: \ c ++ \ SQLite \ bin. DB", & dB );
If (DB = NULL)
{
Return;
}
Sqlite3_prepare (dB, "select * from image",-1, & stat2, 0 );
Sqlite3_step (stat2 );
// While (rc = sqlite_row)
File * fp2;
Fp2 = fopen ("F: \ c ++ \ SQLite \ show.jpg", "WB ");
Const void * imgdata = sqlite3_column_blob (stat2, 1 );
Int size = sqlite3_column_bytes (stat2, 1 );
Size_t ret = fwrite (imgdata, sizeof (char), size, fp2 );
Fclose (fp2 );
Sqlite3_finalize (stat2 );
Sqlite3_close (db );
5. Update the image:
Sqlite3 * dB;
Sqlite3_stmt * Stat;
Char * zerrmsg = 0;
File * fp = NULL;
Long filesize = 0;
Char * ffile = NULL;
Char * Buf = NULL;
Sqlite3_open ("F: \ c ++ \ SQLite \ bin. DB", & dB );
If (DB = NULL)
{
Return;
}
Fp = fopen ("F: \ c ++ \ SQLite \ 124.jpg"," rb ");
If (FP! = NULL)
{
Fseek (FP, 0, seek_end );
Filesize = ftell (FP );
Fseek (FP, 0, seek_set );
Ffile = new char [filesize];
Size_t SZ = fread (ffile, sizeof (char), filesize, FP );
Fclose (FP );
}
Sqlite3_prepare (dB, "Update image set IMG =? Where filename='girl.jpg '",-1, & stat, 0 );
Sqlite3_bind_blob (stat, 1, ffile, filesize, null );
Sqlite3_step (STAT );
Delete [] ffile;
Sqlite3_finalize (STAT );
Sqlite3_close (db );