Sqlite3 database operations

Database Operations

We use sqlite3 database software in this project.
You can use sqlite3 to create databases, create tables, insert records, query records, update records, and close databases to store data in the database.
Open the database and create the table 1.1 SQLite * dB. Define a variable for SQLite *.

Sqlite3_open ("./link. DB", & dB );

Open a database named Link. DB in the current directory. If not, create a database named Link. DB in the current directory.

1.2 SQL = "CREATE TABLE weblink (ID integer primary key, domain text, page text, fromdomain text, status integer );"

Create a weblink table in an opened database. The table attributes are as follows:
Id integer primary key: ID (which is included in the token)
Domain text: Domain Name
Page text: subpage
Fromdomain text: Source Domain Name
Status INTEGER: Status flag
The domain name and the sub-page are obtained from the web page of the source domain name.
Eg: www.sina.com/news.html.
Domain Name: www.sina.com
Webpage: news.html

1.3 sqlite3_exec (dB, SQL, null );

The function that executes an SQL statement.
Function prototype: int sqlite3_exec (sqlite3 *, const char * SQL, sqlite3_callback, void *, char ** errmsg)
The first parameter is the 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 4th void parameter * is the pointer you provide. You can pass any pointer parameter here, this parameter will be passed to the callback function. If you do not need to pass a 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. 5th parameters 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: Generally, either sqlite3_callback or void * can be entered.
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.

Sqlite3_exec callback function

Int record_callback (void * para, int n_column, char ** column_value, char ** column_name)

A ). para is the void * parameter you pass in sqlite3_exec. Through the Para parameter, you can pass in some special pointers (such as class pointers and structure pointers ), then, it is forcibly converted to the corresponding type (void * type here, which must be converted to your type only ). Then operate on the Data B ). n_column is the number of fields in this record (that is, the number of columns in this record or the number of Field names in this record) Char ** column_value is a key value, and the data found is saved here, it is actually a one-dimensional array (do not think it is a two-dimensional array), each element is a char * value, is a field content (represented by a string, ended with \ 0) c ). char
** Column_name corresponds to column_value, indicating the field name of this field

Insert record
SQL = sqlite3_mprintf ("insert into weblink (domain, page, fromdomain, status)" "values (% Q, % d)", domain, page, fromdomain, status );

Insert into: insert command
Weblink (domain, page, fromdomain, status): name of the table to be operated and the field name corresponding to the data to be inserted
Values (% Q, % d): format of the data to be inserted
Domain, page, fromdomain, status: type parameter name passed through the function (data to be inserted)

Query records
SQL = sqlite3_mprintf ("select * From weblink where page = % Q and domain = % Q", page, domain );

Select * From weblink query command (query all records of the entire table)
Select * From weblink where page = % Q and domain = % Q: query command (using the subpage name and domain name as the keyword)
Page, domain: type parameter name passed through the function (data to be queried)

Update record
Sqlite3_mprintf ("Update weblink set status = % d Where id = % d", status, ID );

Update weblink set status = % d Where id = % d: update command (key to status value and ID)
Status, ID: type parameter name passed in through the function (data to be updated)

Retrieve records
SQL = sqlite3_mprintf ("select * From weblink where status = 0 order by ID ASC limit 0, 1 ");

Select * From weblink where status = 0 order by idasc limit 0, 1: query command (query records whose status is 0 in ascending order)
Order by idasc limit 0, 1 in ascending order

Close Database
Int sqlite3_close (db)

If you have enabled a database with sqlite3_open, do not forget to use this function to close the database.

7. Common sqlite3 operation commands

A). sqlite3 link. DB open the database
B). view the table name in tables.
C). view the table structure in schema.
D). Select * from (Table Name); view table records
E). Select count (*) from (Table Name); view the total record data of the table
F). Help to view help
G). Q: log out of the database.