Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to easily operate sqlite3. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Download SQLite3

We download the sqlite source code package and only need sqlite3.c and sqlite.h in it.

The simplest table creation operation

# include

# include "sqlite3.h"

Int main (int argc,char * argv []) {

Const char * sql_create_table= "create table t (id int primary key,msg varchar (128))"

Char * errmsg = 0

Int ret = 0

Sqlite3 * db = 0

Ret = sqlite3_open (". / sqlite3-demo.db", & db)

If (ret! = SQLITE_OK) {

Fprintf (stderr, "Cannot open db:% s\ n", sqlite3_errmsg (db))

Return 1

}

Printf ("Open database\ n")

Ret = sqlite3_exec (db,sql_create_table,NULL,NULL,&errmsg)

If (ret! = SQLITE_OK) {

Fprintf (stderr, "create table fail:% s\ n", errmsg)

}

Sqlite3_free (errmsg)

Sqlite3_close (db)

Printf ("Close database\ n")

Return 0

}

In this operation, we do the following:

Open the database

Execute SQL statement

Shut down the database

Of course, there will be some state judgment and memory pointer release and so on.

The API that opens the database is as follows:

Int sqlite3_open (

Const char * filename, / * Database filename (UTF-8) * /

Sqlite3 * * ppDb / * OUT: SQLite db handle * /

);

A very complex sqlite3 data structure will be introduced here. I will know more about this according to my needs.

Open the database in addition to this form of accident, there are sqlite3_open, sqlite3_open16, sqlite3_open_v2 several forms, basically similar.

Most sql operations can be done through sqlite3_exec, which is in the form of API:

Int sqlite3_exec (

Sqlite3*, / * An open database * /

Const char * sql, / * SQL to be evaluated * /

Int (* callback) (void*,int,char**,char**), / * Callback function * /

Void *, / * 1st argument to callback * /

Char * * errmsg / * Error msg written here * /

);

The meaning of each parameter is:

Sqlite3 describes the database handle

SQL statement to be executed by sql

Callback callback function

Void * the first argument of the callback function

Errmsg error message, if there is no SQL problem, the value is NULL

The callback function is a more complex function. Its prototype looks like this:

Int callback (void * params,int column_size,char * column_value,char * * column_name) {

The meaning of each parameter is as follows:

Params is the fourth parameter passed in by sqlite3_exec

Column_size is the number of result fields

Column_value is an one-character array pointer that returns a record

Column_name is the name of the result field

Callback is usually used in select operations, especially when dealing with the number of records per row. The callback function is called for each row of the returned result. If the callback function returns non-0, sqlite3_exec returns SQLITE_ABORT, and subsequent callback functions will not be executed, and unexecuted subqueries will not continue.

For operations that do not require callback functions, such as update, delete, insert, etc., the third and fourth parameters of sqlite3_exec can be passed in 0 or NULL.

Usually, sqlite3_exec returns the result of SQLITE_OK=0. For non-zero results, you can get the corresponding error description through errmsg.

Compile under Windows:

D:\ home\ dev\ c > cl / nologo / TC sqlite3-demo.c sqlite3.c

Compile under GCC:

$gcc-o sqlite3-demo.bin sqlite3-demo.c sqlite3.c delete table operation

To prevent junk data, we delete the table operation when loading the database.

For a simple delete operation, you can simply use sqlite3_exec. There is no need for the callback function and its parameters. Of course, you can watch whether the result returned by sqlite3_exec is the value of SQLITE_OK.

Const char * sql_drop_table= "drop table if exists t"

Const char * sql_create_table= "create table t (id int primary key,msg varchar (128))"

Sqlite3_exec (db,sql_drop_table,0,0,&errmsg)

Sqlite3_exec (db,sql_create_table,0,0,&errmsg)

Insert data

Insert the first piece of data

Ret = sqlite3_exec (db, "insert into t (id,msg) values", NULL,NULL,&errmsg)

Printf ("Insert a record% s\ n", ret = = SQLITE_OK? "OK": "FAIL")

The returned value ret is SQLITE_OK, which means the operation is successful.

Insert multiple pieces of data and delete the data

Ret = sqlite3_exec (db, "insert into t (id,msg) values", NULL,NULL,&errmsg)

Printf ("Insert a record% s\ n", ret = = SQLITE_OK? "OK": "FAIL")

Ret = sqlite3_exec (db, "insert into t (id,msg) values (2) IMXYLZ')", NULL,NULL,&errmsg)

Printf ("Insert a record% s\ n", ret = = SQLITE_OK? "OK": "FAIL")

Ret = sqlite3_exec (db, "delete from t where id < 3", NULL,NULL,&errmsg)

Printf ("Delete records:% s\ n", ret = = SQLITE_OK? "OK": "FAIL")

Insert multiple pieces of data and simply use sqlite3_exec for SQL execution. Of course, here is the complete SQL string.

Precompilation operation

Int I = 0

Sqlite3_stmt * stmt

Char ca [255]

/ / prepare statement

Sqlite3_prepare_v2 (db, "insert into t (id,msg) values (?,?)",-1)

For (iTunes 10)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.