SQLite dB example

The previous term was used as a project (Embedded Linux). Because a large amount of data needs to be stored, it takes up to 30 days. It was originally intended to be saved to the file and re-parsed every time the application was started. However, when the data volume is large, efficiency problems may occur. In the end, I gave up my intention to use files and decided to use databases to access data.

There are also a lot of databases in Linux, there are open source, there are also charges. For us, we must use open source databases. I have used Berkely dB before, but need licience. Therefore, the SQLite dB is selected based on the efficiency performance and size constraints. We think of it as "SQLite is an excellent open-source data project that is completely free ".

The following is a simple example:
Int main (INT argc, char ** argv)
{
Sqlite3 * dB;
Sqlite3_stmt * stmt;
Const char * ztail;

// Open the database
Int r = sqlite3_open ("mysqlite. DB", & dB)
If (r ){
Printf ("% s", sqlite3_errmsg (db ));
}

// Create a table
Sqlite3_prepare (dB,
"Create table players (ID integer primary key, Name text, age interer );",
-1, & stmt, & ztail );
Sqlite3_step (stmt );
Sqlite3_finalize (stmt );

// Insert data
Sqlite3_prepare (dB,
"Insert into players (name, num) values (?,?); ",
-1, & stmt, & ztail );

Char STR [] = "Kevin ";
Int n = 23;
Sqlite3_bind_text (stmt, 1, STR,-1, sqlite_static );
Sqlite3_bind_int (stmt, 2, N );
R = sqlite3_step (stmt );
If (R! = Sqlite_done ){
Printf ("% s", sqlite3_errmsg (db ));
}
Sqlite3_reset (stmt );

// Insert the second data
Char str2 [] = "Jack ";
Int n2 = 16;
Sqlite3_bind_text (stmt, 1, str2,-1, sqlite_static );
Sqlite3_bind_int (stmt, 2, N2 );
R = sqltie3_step (stmt );
If (R! = Sqlite_done ){
Printf ("% s", sqlite3_errmsg (db ));
}
Sqltie3_finalize (stmt );

// Query all data
Sqlite3_prepare (dB,
"Select ID, name, num from players order by num ;",
-1, & stmt, & ztail );
R = sqlite3_step (stmt );
Int number;
Int ID;
Const unsigned char * Name;
While (r = sqlite_row ){
Id = sqlite3_column_int (stmt, 0 );
Name = sqlite3_column_text (stmt, 1 );
Number = sqlite3_column_int (stmt, 2 );
Printf ("ID: % d name: % s age: % d/N", ID, name, number );
Sqlite3_step (stmt );
}
Sqlite3_finalize (stmt );

// Close the database
Sqlite3_close (db );
Return 0;
}
Add the header file # include <stdio. h> # icnlude <sqlite3.h> to compile and run gcc-O sample. C./sample
The result is as follows:
ID: 1 Name: Kevin age: 23
ID: 2 Name: Jack age: 16
Briefly describe the process of executing SQL statements in the SQLite Database

** Call sqlite3_prepare () to compile the SQL statement into a struct (sqlite3_stmt) in SQLite. The struct contains information about the SQL statement to be executed.
** If you need to input parameters, use '? 'As a placeholder, and then call the sqlite3_bind_xxx () function to pass in the corresponding parameters.
** When sqlite3_step () is called, the SQL statement is actually executed. note that the return values of this function. sqlite_done and sqlite_row indicate that the execution is successful. The difference is that sqlite_done indicates that no query results are returned, and SQL statements such as update and insert return sqlite_done, the SELECT query statement returns sqlite_row when the query result is not empty and sqlite_done when the query result is empty.
** Each time you call sqlite3_step (), only one row of data is returned, and the sqlite3_column_xxx () function is used to retrieve the data. to retrieve all the data, you must call sqlite3_step () repeatedly (). (Note: When the BIND parameter is set, the index of the parameter list starts from 1, and when the data is retrieved, the index of the column starts from 0 ).
** After the SQL statement is used up, you must call sqlite3_finalize () to release the memory occupied by stmt. The memory is allocated when sqlite3_prepare.
** To reuse an SQL statement, you can call sqlite3_reset () to identify the bound parameters.

Of course, you can also encapsulate existing libsqlite based on your own needs to make the operation more convenient. Advanced features are still learning... In addition, if you want to use SQLite dB, you 'd better read the SQL syntax operation first.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/mawl2002/archive/2007/11/23/1899678.aspx