Delphi and SQLite

file:0 Objective

The purpose of this paper is to use journal method to record some bits of learning Delphi accessing the embedded database in SQLite. Welcome all of you to study together and criticize.

File:1 preparatory work Part1

Delphi version: Delphi2007 for Win32 Update3. Any installed version can be.
SQLite DLL Version: 3.5.3. Currently the latest version of the SQLite engine. [http://www.sqlite.org/]
SQLite for Delphi:simple SQLite 3.0 for Delphi. The current release date is August 2007, which supports the SQLite DLL version 3.4.2. After a simple test, 3.5.3 is also possible. Bring your own demo. [http://www.itwriting.com/sqlitesimple.php]

File:2 preparatory work Part2

Create a new Form Application project and set the saved folder. Copy the Sqlite3.pas,sqlite3table.pas,sqlite3udf.pas from simple SQLite 3.0 for Delphi to the folder where the project is located. and add these three files to the project. Copy the Sqlite.dll to the folder where you compiled the EXE file. This depends on the individual's setting.

File:3 Preliminary test

Reference the Sqlitetable3.pas unit.
Create a button (TButton) called Btnversion on the form. Write the following code in the Click event of the Btnversion.
Procedure Tfrmabout.btnversionclick (Sender:tobject);
Var
Sqlitedb:tsqlitedatabase;
Begin
Sqlitedb:=tsqlitedatabase.create (");
ShowMessage (' Sqlite DLL version: ' +sqlitedb.version);
Sqlitedb.free;
End
Compile run, if successful, will show the version number of the current SQLite DLL, mine is 3.5.3.

File:4 Simple Working principle description

In the simple sqlite3.0 for Delphi several files, mainly uses two files. Sqlite3.pas,sqlite3table.pas, respectively.
Sqlite3.pas implements an external definition of the Sqlite.dll interface.
Sqlite3table.pas a simple access function encapsulation.
In Delphi, various accesses to the SQLite database are implemented through Sqlite3table.pas.
Regarding Sqlite3udf.pas, according to the author's description, mainly used with the creation of user-defined functions, the specific function is not tested.

File:5 reading data

Let's say we have a SQLite database file called Database.db, which is the directory where the generated EXE files are compiled. There's a form called countries.
The table creation statement is as follows.
CREATE TABLE "Countries" (
Name VARCHAR not NULL PRIMARY KEY UNIQUE,
Capital VARCHAR is not NULL,
Area INTEGER is not NULL,
Pop INTEGER not NULL,
PCI INTEGER not NULL
);
How do 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[' area ');
Display control 4.text:=sqlitetb.fieldasstring (sqlitetb.fieldindex[' pops ');
Display control 5.text:=sqlitetb.fieldasstring (sqlitetb.fieldindex[' PCI ');
Sqlitetb.free;
Sqlitedb.free;
End
The Tsqlitetable class has two methods, namely next and previous, which are used to move data cursors backwards and forwards. With these two methods, we can read arbitrary data contents of the table. For example, select * from countries the where area >8000000 data.

File:6 Writing data

We can read the data and we can write the data. How do we do that? Or 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 Update method of the data can be implemented in this way. As you can see here, the character class data can be tagged with double quotation marks instead of the usual single quotation marks of SQL Server.

File:7 character encoding

Friends who have accessed MySQL database data should remember the nightmare of Chinese data access experience. The database uses one kind of code, the program is another kind of code, causes the Chinese data to become garbled.
In SQLite, UTF-8 access is used in the database, and the data that Delphi takes out is ASCII encoded. In other words, we need to do the encoding conversion while accessing.
There are two methods, Utf8decode (), Utf8encode (). When we read data from the database, we use Utf8decode (); When we write data to the database, we use Utf8encode ();
For example:
Displays the 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 we have a better way, we can only use ...

File8:blob blob blob

At some point, we will need to store and read images, videos, audio and other information to the database, for example: sexy photos of ex-girlfriends. There is a data type called blob in SQLite that can meet our requirements. How to access and read it?
The following is an example of accessing a JPEG image in a photolib table in the DATABASE.DB database:
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 Timage control display:
Var
Ms:tmemorystream;
Pic:tjpegimage;
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 (' This record does not have ex-girlfriend photo data. ');
Exit
End
Ms. Position: = 0;
PIC: = tjpegimage.create;
Pic. Loadfromstream (MS);
Self. Image2.Picture.Graphic: = PIC;
Ms.free;
Pic. Free;
End

Delphi and SQLite