Usage of SQLite

Document directory

• 2.2.1. Create a database
• 2.2.2. Create a table
• 2.2.4. concurrency of Data Operations
• 2.3.1. cppsqlite3table
• 2.3.2. cppsqlite3query
• 2.3.3. cppsqlite3buffer
• 2.3.4. cppsqlite3exception
• 2.3.5. cppsqlitebinary
• 2.3.6. cppsqlite3statement

1. SQLite Introduction

SQLite is a lightweight database and an acid-compliant associated database management system. It is designed to be embedded and has been used in many embedded products, it occupies very low resources. In embedded devices, it may only need several hundred KB of memory.

SQLite data is stored in a single file, so you can think of a file as a database. It is easy to use and requires no driver installation. Your application can read data only by relying on the SQLite dynamic link library. It also supports common SQL syntax for addition, deletion, modification, and query. It is lightweight and does not rely heavily on data operations.

2. SQLite uses 2.1. Environment Configuration

To use SQLite in a project, you must first configure the project environment. All the files required for SQLite development are stored in a common folder, as shown in the list:

Copy it to the project directory and set it in the project properties. Take vs2005 as an example:

1) set the additional directory:

To facilitate management, the SQLite header files are all under the common folder. To facilitate calling in the code, you can directly set the additional directory so that you can directly reference the file name, instead of adding a path before the file name.

2) set additional dependencies:

3) Add the SQLite file to the project.

2.2. Basic operations 2.2.1. database creation

Const char * gszfile = "C :\\ test. DB"; // database path

Cppsqlitedb;

Remove (gszfile); // clear the file in the current path

DB. Open (gszfile); // if the database exists, open the database. If the database does not exist, create a database.

2.2.2. Create a table

Before creating a table, check whether the table exists to avoid errors:

DB. tableexists ("EMP"); // if true is returned, the table already exists; if false, the table does not exist.

Create a table without the same name

Db.exe cdml ("create table emp (empno int, empname char (20 ));");

After creation, you can also use dB. tableexists ("EMP") to determine whether the creation is successful.

2.2.3. Execution of basic SQL statements

Generally, we use execdml in SQLite to execute the SQL statement:

Db.exe cdml ("Update EMP set numcils = 10 where dayno = 1 ;");

When performing operations related to updating data, such as adding, deleting, modifying, and so on, it is best to put the operation in the transaction. One is to effectively lock the Usage Status of the database, and the other is to perform rollback operations.

For example:

Db.exe cdml ("begin transaction ;");

Db.exe cdml ("Update EMP set numcils = 10 where dayno = 1 ;");

Db.exe cdml ("Commit transaction ;");

2.2.4. concurrency of Data Operations

SQLite allows multiple users or threads to perform query operations at the same time, but only one user can perform data update operations in a certain period of time. Therefore, in order not to rule out that the database is occupied by other users or threads during database operations, we use try... Catch... To avoid errors caused by database occupation.

Try

{

Int nrows = db.exe cdml ("Update EMP set numcils = 100 Where dayno = 1 ;");

Cout <"main thread: Updated" <nrows <"rows" <Endl;

}

Catch (cppsqlite3exception & E)

{

Cout <"main thread:" <E. errorcode () <":" <E. errormessage () <Endl;

}

2.3. Main categories 2.3.1. cppsqlite3table

Cppsqlite3table is provided by SQLite to store a complete table structure. We can also read the content in this table through some methods and functions in cppsqlite3table.

Cppsqlite3table T = dB. gettable ("select * from EMP ;");

T. numfields (); // returns the total number of columns in the table.

T. fieldname (rows); // return the column name of the nth column in the table.

T. numrows (); // returns the total number of rows in the table.

T. setrow (I); // The row where I is located.

T. fieldisnull (field); // determines whether the column number is null. Field indicates the column number of the row.

T. fieldvalue (field); // read the field column data in row I.

Example:

Cppsqlite3table T = dB. gettable ("select * from EMP order by 1 ;");

For (outputs = 0; counts <t. numfields (); counts ++)

{

Cout <t. fieldname (rule) <"| ";

}

Cout <Endl;

For (int row = 0; row <t. numrows (); row ++)

{

T. setrow (ROW );

For (INT fields = 0; counts <t. numfields (); counts ++)

{

If (! T. fieldisnull (partition ))

Cout <t. fieldvalue (timeout) <"| ";

Else

Cout <"null" <"| ";

}

Cout <Endl;

}

2.3.2. cppsqlite3query

Cppsqlite3query is used in SQLite to store the returned results of the query.

Cppsqlite3query q = db.exe cquery ("select * from EMP order by 1 ;");

Q. numfields (); // The total number of columns returned for this dataset

Q. fieldname (partition); // return the column name of the nth column.

Q. fielddecltype (delimiter); // return the Data Type of the nth partition column.

Q. fieldvalue (0); // return the data in column 0th of a row

Q. nextrow (); // point to the next row of the current Dataset

Q. EOF (); // determines whether the current row is empty.

Example:

Cppsqlite3query q = db.exe cquery ("select * from EMP order by 1 ;");

For (counts = 0; counts <q. numfields (); counts ++)

{

Cout <q. fieldname (dimensions) <"(" <q. fielddecltype (dimensions) <") | ";

}

Cout <Endl;

While (! Q. EOF ())

{

Cout <q. fieldvalue (0) <"| ";

Cout <q. fieldvalue (1) <"|" <Endl;

Q. nextrow ();

}

2.3.3. cppsqlite3buffer

Cppsqlite3buffer is a string type provided by SQLite that can contain special characters. For example, in the SQL statement in our previous program, the SQL statement involves single quotation marks:

Cstring STR;

Str. append ("select * from table where uid = '"

Str. append (m_id );

Str. append ("'");

Sometimes it seems inconvenient, but in SQLite, we can use this type through it:

Cppsqlite3buffer bufsql;

Bufsql. Format ("insert into EMP (empno, empname) values (% Q, % Q);", no, name );

Db.exe cdml (bufsql );

2.3.4. cppsqlite3exception

Cppsqlite3exception is a class provided by SQLite to capture error messages. It can capture the exception errors that occur when we use SQLite.

For example:

Try

{

......

}

Catch (cppsqlite3exception & E)

{

Cout <E. errorcode () <":" <E. errormessage () <Endl;

}

2.3.5. cppsqlitebinary

Cppsqlite3binary can convert some data containing space characters and carriage return characters into binary data and encoding for saving and reading to ensure the integrity of this data.

You can use the setencoded () and setbinary () Methods to convert data.

Use the getencoded () and getbinary () methods to obtain data.

Getbinarylength () is used to obtain the data length.

Example:

Unsigned char bin [256];

Cppsqlite3binary blob;

For (I = 0; I <sizeof bin; I ++)

{

Bin [I] = I;

}

Blob. setbinary (bin, sizeof bin );

Bufsql. Format ("insert into bindata values ('testing', % Q);", blob. getencoded ());

Db.exe cdml (bufsql );

Cout <"stored binary length:" <sizeof bin <Endl;

Q = db.exe cquery ("Select data from bindata where DESC = 'testing ';");

If (! Q. EOF ())

{

Blob. setencoded (unsigned char *) Q. fieldvalue ("data "));

Cout <"retrieved binary length:" <blob. getbinarylength () <Endl;

}

Q. Finalize ();

2.3.6. cppsqlite3statement

If you want to insert Multiple SQL statements, but these SQL statements only insert different data in the same format, you can use cppsqlite3statement to save the template, then, you only need to assign different values to the template and execute the template.

For example:

Db.exe cdml ("begin transaction ;");

Cppsqlite3statement stmt = dB. compilestatement ("insert into EMP values (?, ?); ");

For (I = 0; I <nrowstocreate; I ++)

{

Char Buf [16];

Sprintf (BUF, "empname % 06d", I );

Stmt. BIND (1, I );

Stmt. BIND (2, Buf );

Stmt.exe cdml ();

Stmt. Reset ();

}

Db.exe cdml ("Commit transaction ;");

2.4. Precautions

① When using the SQLite embedded database, you must first judge whether the file storing the SQLite database exists and then perform the operation. Similarly, when operating the tables in the SQLite database, you must first check whether the table exists.

DB. Open (gszfile); // if the database exists, open the database. If the database does not exist, create a database.

DB. tableexists ("EMP"); // if true is returned, the table already exists; if false, the table does not exist.

② Any SQL statement that updates data must be put in the transaction for operations during execution. Transactions are conducive to data rollback and exclusive operations on the database. The SQLite database allows multiple users to perform read operations at the same time, but only one user can update the database at a certain time and multiple users can perform read operations.

Db.exe cdml ("begin transaction ;");

Db.exe cdml ("Update EMP set numcils = 10 where dayno = 1 ;");

Db.exe cdml ("Commit transaction ;");

③ All database update operations must use try... Catch... Capture and process exceptions to obtain unknown exceptions.

Try

{

Int nrows = db.exe cdml ("Update EMP set numcils = 100 Where dayno = 1 ;");

Cout <"main thread: Updated" <nrows <"rows" <Endl;

}

Catch (cppsqlite3exception & E)

{

Cout <"main thread:" <E. errorcode () <":" <E. errormessage () <Endl;

}

④ When Using SQLite, cppsqlite3table should be used as the container for data storage when reading tables. Cppsqlite3query is used as the container to store data when reading only some fields of a table.

⑤ To assemble an SQL statement sentence, it is strictly prohibited to assemble strings such as cstring. The SQL statement should be assembled using the cppsqlite3buffer provided by SQLite.

Cppsqlite3buffer bufsql;

Bufsql. Format ("insert into EMP (empno, empname) values (% Q, % Q);", no, name );

Db.exe cdml (bufsql );

⑥ When a SQL statement uses the same table and field except for different data content, cppsqlite3statement is used to save the template, then, only the template is operated, which saves the Compilation Time of many SQL statements and improves the efficiency.

Db.exe cdml ("begin transaction ;");

Cppsqlite3statement stmt = dB. compilestatement ("insert into EMP values (?, ?); ");

For (I = 0; I <nrowstocreate; I ++)

{

Char Buf [16];

Sprintf (BUF, "empname % 06d", I );

Stmt. BIND (1, I );

Stmt. BIND (2, Buf );

Stmt.exe cdml ();

Stmt. Reset ();

}

Db.exe cdml ("Commit transaction ;");