DELPHI XE5 and SQLite

The most recent use of Delphi Project is to use DELPHI2009, in order to access the local database convenient, using Access database, do not need to install drivers, (Windows comes with), but the performance of the Access database is really bad, through ADO connection, INSERT 1000 data averaged over 1.5 seconds.

Before facing a new project, the local database is continued with ADO, or other, becomes a problem.

The recent more popular local database is SQLite, want to test the performance of SQLite, and the degree of convenience.

1.

DELPHI and SQLite in degrees Niang up the most is through the ASQLITE3DB components to connect.

Http://www.2cto.com/kf/201010/76781.html

This set of components Tsqlitedatabase class and Tsqlitetable class, through a few DLLs to access SQLite data, the study found that Tsqlitetable class and Delphi native Tdataset class is completely irrelevant, is too primitive, cannot be connected to a Delphi native database component and can be cumbersome to use.

Inadvertently found a good sqlite tool:

D:\TEST\delphixe5Test\sqllite\ Database Tools \personal 3

2.

It has been mentioned that you can use ADO to connect via ODBC,

http://blog.csdn.net/zyq5945/article/details/6457741

Download the ODBC driver online, see: D:\TEST\delphixe5Test\sqllitetest\odbc, after configuration,

Procedure Tform1.button3click (Sender:tobject);
Var
I:integer;
B:long;
Begin
Adoconnection2.begintrans;
B: = GetTickCount ();
For I: = 0 To
Begin
Adoquery2. Sql. Text: = ' INSERT into a (a) values (' Middle ' + inttostr (i) + ') ';
Adoquery2.execsql;
End
Adoconnection2.committrans;
Edit1.text: = IntToStr (GetTickCount ()-B);

End

Procedure Tform1.formcreate (Sender:tobject);
Begin
Adoconnection2.connectionstring: = ' provider=msdasql.1; Persist Security Info=false; Extended properties= "Driver={sqlite3 ODBC Driver};D atabase=c:\users\hl\desktop\aa.db; Nowchar=1, "';
Adoconnection2.open ();
End

The effect can also be, the average insert 1000 in about 0.5 seconds, than access increased by more than three times times.

However, the problem with ODBC is that ODBC drivers must be installed, that the Internet does not find how to directly hit the driver in my own program installation package, and there may be some machines are not loaded with ODBC caused the inability to connect to the local database problem, and finally decided to abandon the ODBC connection.

3.

Someone mentioned can be connected through the dbexpress, tossing a half-day, has been prompted not to find Sqlite3.dll, find a long time did not find a solution, give up.

4.

Being desperate, seeing http://redboy136.blog.163.com/blog/static/10718843220139206951165/

Mention of the new features in Delphi XE5 adds FIREDAC support for SQLite. Start studying FIREDAC.

Online on the introduction of FIREDAC, fortunately found a Delphi help file.

Test:

D:\TEST\delphixe5Test\sqllitetest\FireDAC

Procedure Tform1.button1click (Sender:tobject);
Begin
FDConnection1.Params.Clear;
FDCONNECTION1.PARAMS.ADD (' database= ' + extractfilepath (application.exename) + ' aa.db ');
FDCONNECTION1.PARAMS.ADD (' Driverid=sqlite ');
fdconnection1.connected: = True;
End

Procedure Tform1.button2click (Sender:tobject);
Var
I:integer;
B:long;
Begin
Fdconnection1.starttransaction;
Try
B: = GetTickCount ();
For I: = 0 To
Begin
FDQuery2.SQL.Text: = ' INSERT into a (a) values (' ' + inttostr (i) + ') ';
Fdquery2.execsql;
End
Fdconnection1.commit;
Except
Fdconnection1.rollback;
Raise
End
Edit1.text: = IntToStr (GetTickCount ()-B);
End

INSERT 1000 is less than 90 milliseconds and requires no driver installation. It's perfect.