SQLite introduction and some common examples

1: common interfaces

I personally prefer SQLite, which is the most convenient to use. The only preparation work is to download the K source, and the author is very enthusiastic and has a question and answer.

The following describes how to use SQLite to create a database and then query the content. Two important structs and five main functions:

Sqlite3 * PDB, database handle, similar to file handle

Sqlite3_stmt * stmt, which is equivalent to the command object of ODBC, used to save compiled SQL statements

 

Sqlite3_open () to open the database

Sqlite3_exec (): executes non-query SQL statements.

Sqlite3_prepare (): This function is used to prepare SQL statements, execute select statements, or use parameter BIND (encapsulate sqlite3_exec ).

Sqlite3_step (): After sqlite3_prepare is called, use this function to move in the record set.

Sqlite3_close () to close database files

 

There are also a series of functions used to obtain data from record set fields, such

Sqlite3_column_text (), which is text-type data.

Sqlite3_column_blob (), fetch blob-type data

Sqlite3_column_int (), which is an int-type data.

...

2: SQLite data type Introduction

Before performing Database SQL operations, you must first note that the data type of SQLite is different from that of other databases. The data types supported by SQLite have their own characteristics, this feature is sometimes considered a potential drawback, but it is not within our scope of discussion.
Most databases have strict restrictions on data types. When creating a table, each column must have a data type, only data that meets the data type can be saved in this column. In SQLite 2. in column X, the attribute of data type only belongs to the data source and is not related to the column where the data is stored. That is to say, the data type is not limited by the data column (with an exception: integer primary key, this column can only store integer data ).
However, when SQLite enters version 3.0, it seems that there is a new answer to this question. SQLite developers began to restrict this kind of untyped use. In version 3.0, each column has its own type. when the data is stored in this column, the database tries to convert the data type to this type and store the converted data type. Of course, if the conversion is considered unfeasible, SQLite will still store the data, just like in his previous version.
For example, if you try to insert a string into an integer column, SQLite checks whether the string has the characteristics of integer data. If so, it can be recognized by the database, the string is converted to an integer and then saved. If not, it is stored as an integer.

In general, all data in SQLite 3.0 has one of the following data types:
Null: The value is null.
Integer: A signed integer, which is stored as 1, 2, 3, 4, 6, or 8 bytes in size.
Real (real): A floating point number, which is stored in the 8-byte index format.
Text: String, stored in database encoding (UTF-8, UTF-16BE, or UTF-16-LE ).
BLOB: BLOB data is stored as input without any conversion.

PS: In relational databases, clob and blob types are used to store enlarged objects. Bolb indicates a Binary Large Object. This type of data is used to save images, images, videos, and so on. Clob indicates a large character object, which can store a large amount of character-based data.

Corresponding, for data columns, there are also the following data types:

Text
Numeric
Integer
Real
None
The attribute of the data column is used to determine the conversion direction of the inserted data:

Text converts data to text. The corresponding data type is null, text or blob.
Numeric converts data to numbers. The corresponding data type may be all five types of data, when you try to save the text, it will convert it to an integer or floating point type (depending on the specific value). If the conversion is not feasible, the storage of the text type will be retained. null or Blob will not change.
Integer converts data to an integer, similar to numeric. The difference is that floating-point numbers without floating-point signs will be converted to an integer to save
Real converts data to the floating point type, similar to numeric. The difference is that integers are converted to floating point to save
Null does not convert any data column type
 

The instance code is as follows,

The attachment project can be compiled directly. The Blob data type is used in this example.

# Include "sqlite3.h" // you can use the SQLite interface to contain a header file.

# Include "stdlib. H"

# Include "stdio. H"

# Include "string. H"

 

# Pragma comment (Lib, "SQLite. lib") // I compiled SQLite into a static lib file.

 

Void createdb ();

Void querydb ();

 

Int main ()

{

Createdb ();

Querydb ();

 

Return 0;

}

 

Void createdb ()

{

Int ret;

Sqlite3 * PDB = 0;

Sqlite3_stmt * stmt = 0;

Char * error = 0;

Char * SQL = "insert into Table1 values ('value11',: AAA )";

Int index;

Static void * value = "asdfadsfasdfjasdfjaksdfaskjdfakdsfaksfja ";

 

Ret = sqlite3_open ("db1.sdb", & PDB); // open the database, just like opening a text file

If (Ret! = Sqlite_ OK)

Return;

Ret = sqlite3_exec (PDB, "create table Table1 (col1 char (20), col2 BLOB)", 0, 0, & error );

If (Ret! = Sqlite_ OK)

Return;

 

Ret = sqlite3_prepare (PDB, SQL, strlen (SQL), & stmt, & error );

If (Ret! = Sqlite_ OK)

Return;

 

Index = sqlite3_bind_parameter_index (stmt, ": AAA ");

 

Ret = sqlite3_bind_blob (stmt, index, value, strlen (value), sqlite_static );

If (Ret! = Sqlite_ OK)

Return;

 

Ret = sqlite3_step (stmt );

If (Ret! = Sqlite_done)

Return;

 

Sqlite3_close (PDB );

}

 

Void querydb ()

{

Int ret;

Sqlite3 * PDB = 0;

Sqlite3_stmt * pstmt = 0;

Char * error = 0;

Char * SQL = "select * From Table1 ";

Int Len;

Int I;

Char * Name;

Void * value;

 

Ret = sqlite3_open ("db1.sdb", & PDB );

If (Ret! = Sqlite_ OK)

Return;

 

Ret = sqlite3_prepare (PDB, SQL, strlen (SQL), & pstmt, & error );

If (Ret! = Sqlite_ OK)

Return;

 

While (1)

{

Ret = sqlite3_step (pstmt );

If (Ret! = Sqlite_row)

Break;

 

Name = sqlite3_column_text (pstmt, 0 );

Value = sqlite3_column_blob (pstmt, 1 );

Len = sqlite3_column_bytes (pstmt, 1 );

}

}

 

 

Example 2: How to Use APIs to operate BLOB fields in SQLite?

 

In actual programming and development, we often need to process the storage of large-capacity binary data, such as slices or music. For these binary data (BLOB fields), we cannot simply insert or query the data as if we were dealing with common text. Therefore, SQLite provides a set of functions to process this blob field type. The following code demonstrates how to use these API functions.

 

First, we need to create a database:

Sqlite3_exec (dB, "create table list (fliename varchar (128) Unique, fzip BLOB);", 0, 0, & zerrmsg );

 

// Since mmmm.rar is a binary file, do you need to use the insert statement first? No.

Sqlite3_prepare (dB, "insert into list values ('mmmm.rar ',?); ",-1, & stat, 0 );

File * FP;

Long filesize = 0;

Char * ffile;

 

Fp = fopen ("mmmm.rar", "rb ");

 

If (FP! = NULL)

{

// Calculate the file size

Fseek (FP, 0, seek_end );

Filesize = ftell (FP );

Fseek (FP, 0, seek_set );

 

// Read the file

Ffile = new char [filesize + 1];

Size_t SZ = fread (ffile, sizeof (char), filesize + 1, FP );

Fclose (FP );

}

 

// Bind the file data to the insert statement and replace "?" Part

Sqlite3_bind_blob (stat, 1, ffile, filesize, null );

// Execute the bound SQL statement

Sqlite3_step (STAT );

At this time, the database already has a piece of data containing the Blob field. Next we will read this data:

// Select the data.

Sqlite3_prepare (dB, "select * From list;",-1, & stat, 0 );

Sqlite3_step (STAT );

// Obtain the Blob field in the record

Const void * test = sqlite3_column_blob (stat, 1 );

// Obtain the data length in the field

Int size = sqlite3_column_bytes (stat, 1 );

// Copy this field

Sprintf (buffer2, "% s", test );

At this time, buffer2 can be written to the file, so that BLOB data processing is complete.

 

Example 3: using blob to store and retrieve images in SQLite

 

# Include <iostream>
# Include <string>
# Include <sqlite3.h>

Using namespace STD;

Int main ()
{
Sqlite3 * dB;
Sqlite3_stmt * Stat;
Char * zerrmsg = 0;

Char buffer2 [1024] = "0 ";

Sqlite3_open ("./metainfo. DB", & dB );
Int result;

If (result)
{
Cout <"Open the database SQLite. DB failed" <Endl;
}

Else
Cout <"Open the database SQLite. DB sucessfully" <Endl;

Sqlite3_exec (dB, "create table list (fliename varchar (128) Unique, fzip BLOB);", 0, 0, & zerrmsg );
Sqlite3_prepare (dB, "insert into list values ('./data/2.bmp ',?); ",-1, & stat, 0 );
 
File * FP;
Long filesize = 0;
Char * ffile;

Fp = fopen ("./data/2.bmp", "rb ");

If (FP! = NULL)
{
Fseek (FP, 0, seek_end );
Filesize = ftell (FP );
Fseek (FP, 0, seek_set );

Ffile = new char [filesize + 1];
Size_t SZ = fread (ffile, sizeof (char), filesize + 1, FP );

Fclose (FP );
}

Sqlite3_bind_blob (stat, 1, ffile, filesize, null );
Sqlite3_step (STAT );

Sqlite3_prepare (dB, "select * From list;",-1, & stat, 0 );
Sqlite3_step (STAT );

Const void * test = sqlite3_column_blob (stat, 1 );
Int size = sqlite3_column_bytes (stat, 1 );

Sprintf (buffer2, "% s", test );

File * fp2;

Fp2 = fopen ("outfile.png", "WB ");

If (fp2! = NULL)
{
Size_t ret = fwrite (test, sizeof (char), size, fp2 );
Fclose (fp2 );
}

Delete (ffile );
Sqlite3_finalize (STAT );
Sqlite3_close (db );
Return 0;
}