C + + operations SQLite Concise Tutorials _c language

SQLite is a lightweight local file database and is a relational database management system that complies with acid. Its design goal is embedded, and has been used in many embedded products, it's powerful, fast, it occupies a very low resource, in embedded devices, may only need hundreds of K of memory is enough. It can support the mainstream operating system such as Windows/linux/unix, and can be combined with many program languages.

Data types of SQLite

Before the database operation, there is a problem to be explained, that is, SQLite data types, and other databases, SQLite supported data types have his own characteristics: typelessness (no type). SQLite is no type, which means that you can save any type of data to any column in any table you want to save, regardless of the type of data that the column declares.

Most databases have strict restrictions on data types, and each column must have a data type when it is created, and only data that conforms to that data type can be stored in this column. In SQLite 2.X, the data type attribute belongs only to the data native, not to the column in which the data is stored, that is, the type of the data is not limited by the data column (with one exception: integer PRIMARY KEY, which can only contain integer data).

But when SQLite entered the 3.0 version, the question seemed to have a new answer, SQLite's developers are starting to limit this type of usage, in the 3.0 release, each column begins to have its own type, and when the data is stored in the column, the database attempts to convert the type of data to that type, and then the converted Class Type storage. Of course, if the conversion is not considered feasible, SQLite will still store the data, just like his predecessor.

For instance, if you attempt to insert a string into a column of an integer type, SQLite checks whether the string has the characteristics of integer data, if any, and can be recognized by the database, the string is converted to an integral type and then saved, or as a string if it is not.
Admittedly sqlite allows data types to be ignored, but it is still recommended that you specify the data type in your CREATE TABLE statement. Because data types are useful for communicating with other programmers or when you are ready to change your database engine. SQLite supports common data types, such as:

1.NULL, value is NULL
2.INTEGER, the value is a signed shaping, based on the size of the value of 1,2,3,4,6 or 8 bytes stored
3.REAL, values are floating-point values, stored in 8-byte IEEE floating-point numbers
4.TEXT, value is a text string, using database encoding (UTF-8,UTF-16BE or Utf-16le)
5.BLOB, just a block of data, completely in accordance with the input storage (that is, no change)

SQLite operator Interface

2 Important structures of SQLite:

Sqlite3 *pdb, database handle, similar to file handler file
Sqlite3_stmt *stmt, a command object that is equivalent to ODBC, for saving compiled SQL statements

The 5 main functions of SQLite:

Sqlite3_open (), opening database
Sqlite3_exec (), executing a non-queried SQL statement
Sqlite3_prepare (), prepares the SQL statement, executes the SELECT statement, or uses parameter bind, which encapsulates the sqlite3_exec.
Sqlite3_step (), after calling Sqlite3_prepare, uses this function to move in the recordset.
Sqlite3_close (), closing the database file

There is also a series of functions that are used to get data from the recordset field, such as:

Sqlite3_column_text (), take the text type of data
Sqlite3_column_blob (), fetching BLOB type data
Sqlite3_column_int (), take int type of data

C + + preparation before use

Download SQLite, respectively from Http://www.sqlite.org/2013/sqlite-amalgamation-3071700.zip, http://www.sqlite.org/2013/ Sqlite-dll-win32-x86-3071700.zip Downloads SQLite source files and library files.

However, Sqlite-dll-win32-x86-3071700.zip does not provide a SQLite lib file and needs to compile the build itself. Extract Sqlite-dll-win32-x86-3071700.zip to sqlite-dll-win32-x86-3071700 directory, then the VS installation directory under VC LIB.EXE, LINK. EXE, Mspdb80.dll (here is VS2008) copy to sqlite-dll-win32-x86-3071700, execute lib.exe/def:sqlite3. Def/machine:ix86 can get SQLITE3.LIB files.

C + + Access SQLite

C + + code for SQLite operations is as follows:

Copy Code code as follows:

#include "stdafx.h"
#include <string.h>
using namespace Std;

#include "Sqlite3.h"
#pragma comment (lib, "SQLITE3.") LIB ")

static int selectcallback (void *notused, int argc, char **argv, char **azcolname)
{
for (int i = 0; i < argc; i++)
{
printf ("%s =%s", Azcolname[i], (Argv[i]? Argv[i]: "NULL"));
if (i!= argc-1)
{
printf (",");
}
}
printf ("\ n");
return 0;
}

int _tmain (int argc, _tchar* argv[])
{
Sqlite3 * PDB;
char* errmsg;

Open the SQLite database
int res = Sqlite3_open ("sql.db", &pdb);
if (res!= SQLITE_OK) {
printf ("Can ' t Open database:%s\n", Sqlite3_errmsg (PDB));
Sqlite3_close (PDB);
return-1;
}

Create a table
String strsql= "CREATE TABLE test (ID int, name text);";
res = sqlite3_exec (PDB, Strsql.c_str (), 0, 0, &errmsg);
if (res!= SQLITE_OK)
{
printf ("Create table error:%s\n", errmsg);
return-1;
}

Inserting data
res = sqlite3_exec (PDB, "BEGIN Transaction;", 0,0, &errmsg);
for (int i= 1; i < ++i)
{
Char sql[512];
sprintf_s (SQL, "INSERT into test values (%d,%s);", (i+10), "\" Test name\ "");

res = sqlite3_exec (PDB, sql,0,0, &errmsg);
if (res!= SQLITE_OK)
{
printf ("Insert Error:%s\n", errmsg);
return-1;
}
}
res = sqlite3_exec (PDB, "Commit transaction;", 0,0, &errmsg);

Querying data
Strsql= "SELECT * from Test;";
res = sqlite3_exec (PDB, Strsql.c_str (), Selectcallback, 0, &errmsg);
if (res!= SQLITE_OK)
{
printf ("Select Error:%s\n", errmsg);
return-1;
}

Close Database
Sqlite3_close (PDB);

return 0;
}