Delphi and SQLite

From: http://zhyhero.googlepages.com/heartset

 

 

 

File: 0 Preface

The purpose of this article is to use the stream accounting method to record some details about how Delphi accesses the embedded database SQLite. You are welcome to join us in learning and correcting your criticism.

File: 1 Preparation 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. The latest version is released on 27 August 2007 and supports SQLite dll version 3.4.2. After a simple test, 3.5.3 is also possible. Built-in demo. Http://www.itwriting.com/sqlitesimple.php

File: 2 Preparations Part2

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. This depends on personal settings.

File: 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 );
VaR
Sqlitedb: tsqlitedatabase;
Begin
Sqlitedb: = tsqlitedatabase. Create ('');
Showmessage ('sqlite dll version: '+ sqlitedb. version );
Sqlitedb. Free;
End;
After compilation and running, the current SQLite dll version is displayed, Which is 3.5.3.

File: 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.

File: 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.

File: 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.

File: 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 ......

File8: 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:
VaR
Sqlitedb: tsqlitedatabase;
FS: tfilestream;
Begin
Sqlitedb: = 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:
VaR
MS: tmemorystream;
PIC: Tsung image;
Sqlitedb: tsqlitedatabase;
Sqlitetb: tsqlitetable;
Begin
Sqlitedb: = tsqlitedatabase. Create ('database. db ');
Sqlitetb: = sqlitedb. gettable ('select * From photolib where id = 1 ');
MS: = sqlitetb. fieldasblob (sqlitetb. fieldindex ['photo']);
If (MS = nil) then
Begin
Showmessage ('the record does not contain 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;