Sqlite and delphi

Previously, a WebService Service was compiled in the company. Only by calling the service can we continue the subsequent operations. In the WebService, we saved the cookie and processed the session, at that time, the session was stored through hash, but later in the concurrency test, it was found that there was a hash which could not be found, after checking the information on the Internet, I realized that it was because of the processing of webmoudle that multiple hash files were created, and the redmost error object was found. Later I stored the hash file as a unique object, this problem has been solved. However, after being released in IIS recently, the number of IIS processes has been adjusted to 10 to improve efficiency, and a similar situation has occurred, now, the values maintained in hash are truly unique, so we want to use SQLite for processing.

A Brief Introduction to SQLite:

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. It supports mainstream operating systems such as Windows, Linux, and UNIX, and can be combined with many programming languages, such as TCL, C #, PHP, Java, and ODBC interfaces, similar to MySQL and PostgreSQL, the two world-renowned open-source database management systems, the processing speed is faster than that of them.

Unlike the common client-server example, the SQLite engine is not an independent process for communications between programs, but a major part of the connection to programs. Therefore, the main communication protocol is direct API calls in programming languages. This plays a positive role in total consumption, latency, and overall simplicity. The entire database (definition, table, index, and data itself) is stored in a single file on the host. Its simple design is completed by locking the entire data file at the beginning of a transaction.

SQLite Data Type

First, you will get into a noun that surprised you: typelessness (no type). Right! SQLite is non-typed. This means that you can save any type of data to any column in any table you want to save, regardless of the Data Type declared in this column

For SQLite, it is completely valid for fields not to specify the type. For example:

Create Table ex1 (A, B, C );

Although SQLite allows data types to be ignored, we recommend that you specify the data type in your create table statement. this is because the data type provides a prompt or help when you communicate with other programmers or when you are about to replace your database engine. SQLite supports common data types, such:

Create Table ex2 (A varchar (10), B nvarchar (15), c text, d integer, e float, f Boolean, G clob, H blob, I timestamp, j numeric (10, 5), K varying character (24), L National varying character (16 ));

In some cases, the SQLite field is not non-typed, that is, when the field type is Integer primary key.

Although SQLite is small, the supported SQL statements are not inferior to those of other open-source databases. The supported SQL statements include:

Attach Database

Begin transaction

Comment

Commit transaction

Copy

Create Index

CREATE TABLE

CREATE TRIGGER

CREATE VIEW

DELETE

DETACH DATABASE

DROP INDEX

DROP TABLE

DROP TRIGGER

DROP VIEW

END TRANSACTION

EXPLAIN

Expression

INSERT

On conflict clause

PRAGMA

REPLACE

ROLLBACK TRANSACTION

SELECT

UPDATE.

How does delphi use sqlite? See the following simple example:

1. Preparations part1

Delphi version: delphi2007 for win32 update3. Any version can be installed.

Sqlite dll version: 3.5.3. The latest version of the sqlite engine. [Http://www.sqlite.org/]

Sqlite for delphi: simple sqlite 3.0 for delphi.

2. Preparation

Create a new form application project and set the saved folder. Copy sqlite3.pas, sqlite3table. Pas, and sqlite3udf. Pas from simple SQLite 3.0 for Delphi to the project folder. Add these three files to the project. Copy SQLite. DLL to the folder where the EXE file is compiled and generated.
3. Preliminary Test

REFERENCE The sqlitetable3.pas unit.

Create a button called btnversion on the form (tbutton ). Write the following code in the btnversion click event.

procedure TfrmAbout.btnVersionClick(Sender: TObject);varSqliteDB:TSQLiteDatabase;beginSqliteDB:=TSQLiteDatabase.Create('');showmessage('Sqlite dll version:'+SqliteDb.version);SqliteDB.Free;end;

After compilation and running, the current SQLite dll version is displayed.

4. Simple working principle description

Two files are mainly used in simple sqlite3.0 for Delphi. Sqlite3.pas and sqlite3table. Pas.

Sqlite3.pas implements external definitions of SQLite. dll interfaces.

Sqlite3table. Pas encapsulate simple access functions.

In Delphi, sqlite3table. PAS is used to implement various accesses to the SQLite database.

Sqlite3udf. PAS is mainly used to create user-defined functions according to the author's description. The specific functions are not tested.

5. Read data

Suppose we have an sqlite database file named database. db, in the directory where the generated exe file is located. There is a table called countries.

The table creation statement is as follows.

CREATE TABLE "Countries" (Name VARCHAR NOT NULL PRIMARY KEY UNIQUE, Capital VARCHAR NOT NULL, Area INTEGER NOT NULL, Pop INTEGER NOT NULL, PCI INTEGER NOT NULL );

How can we access the first piece of data.

Var
SqliteDB: TSQLiteDatabase;
SqliteTB: TSQLiteTable;
Begin
SqliteDB: = TSQLiteDatabase. Create ('database. db ');
SqliteTB: = SqliteDB. GetTable ('select * from countries ');
Display Control 1. text: = SqliteTB. FieldAsString (SqliteTB. FieldIndex ['name']);
Display Control 2. text: = SqliteTB. FieldAsString (SqliteTB. FieldIndex ['capital ']);
Display Control 3. text: = SqliteTB. FieldAsString (SqliteTB. FieldIndex ['region']);
Display Control 4. text: = SqliteTB. FieldAsString (SqliteTB. FieldIndex ['pop']);
Display Control 5. text: = SqliteTB. FieldAsString (SqliteTB. FieldIndex ['pci ']);
SqliteTB. free;
SqliteDB. free;
End;

The TSQLiteTable class has two methods: Next and Previous, which are used to move data cursor backward and forward. With these two methods, we can read any data in the table. For example, select * from countries where area> 8000000 data.

6. Write Data

We can read data to write data. How can we achieve this? Take the Countries table as an example.

Var
SqliteDB: TSQLiteDatabase;
Begin
SqliteDB: = TSQLiteDatabase. Create ('database. db ');
SqliteDB. ExecSQL ('insert Into Countries (Name, Capital, Area, Pop, PCI) values ("China", "Beijing", 9600000,1500000000, 6000 )');
SqliteDB. Free;
End;

Similarly, the data update method can also be implemented using this method. We can see that character data can be marked with double quotation marks instead of the single quotation marks of sqlserver.
7. character encoding

Those who have accessed MySql database data should remember the nightmare Chinese Data Access experience. The database uses one encoding and the program is another encoding, which leads to garbled Chinese data.

In sqlite, the database uses UTF-8 access, the data retrieved by DELPHI is ASCII code. That is to say, we need to perform encoding conversion while accessing.

There are two methods: utf8decode () and utf8encode (). When reading data from the database, we use utf8decode (); when writing data to the database, we use utf8encode ();

For example:

Display Control. text: = utf8decode (sltb. fieldAsString (sltb. fieldIndex ['name']); SqliteDB. execSQL (utf8encode ('insert Into Countries (Name, Capital, Area, Pop, PCI) values ("China", "Beijing", 9600000,1500000000, 6000 )'));

Before there is a better method, we can only use this ......

8. Blob

In some cases, we need to store and read image, video, audio, and other information data to the database, such as the sexiness photos of ex-girlfriends. Sqlite has a data type called Blob, which can meet our requirements. How can I access and read data?

The following uses a JPEG image stored in the PhotoLib table in the database. db database as an example:

Create table "PhotoLib" (Id Integer not null primary key unique, Photo BLOB); write: varSqliteDB: TSQLiteDatabase; FS: TFileStream; beginSqliteDB: = TSQLiteDatabase. create ('database. db'); FS: = TFileStream.Create('test.jpeg ', fmOpenRead); SqliteDB. updateBlob ('Update PhotoLib set Photo =? Where id = 1', FS); FS. free; SqliteDB. free; end; read to the TImage control display: varMS: TMemoryStream; PIC: t1_image; SqliteDB: TSQLiteDatabase; SqliteTB: TSQLIteTable; beginSqliteDB: = TSQLiteDatabase. create ('database. db'); SqliteTB: = SqliteDB. getTable ('select * From PhotoLib Where Id = 1'); MS: = SqliteTB. fieldAsBlob (SqliteTB. fieldIndex ['photo ']); if (MS = nil) thenbeginshowmessage ('this record has no ex-girlfriend photo data. '); exit; end; MS. position: = 0; PIC: = tsf-image. create; PIC. loadFromStream (MS); self. image2.Picture. graphic: = PIC; MS. free; PIC. free; end;