Monday, November 12, 2007

 

Using Sqlite3 with Fcl-Db


In this tutorial, I'll try to explain how to access Sqlite3 databases using
FreePascal and Fcl-Db. Fcl-Db is the standard way of accessing databases using FreePascal, but you can find some other methods to connect to them, such as LibSql and Zeos.

The first step is to install Sqlite versión 3.x.x and its development library (in Ubuntu is libsqlite3-dev), please go to www.sqlite.org to download a stable version and install in your computer. I'll don't explain how to install it, you are a programmer, you should know how to install a program.

Creating the "tutorial" database

Creating a database using the command line SQLite Client is as easy as:
>sqlite3 tutorial.db
With that command, you created a file named "tutorial.db" and started the command line SQLite Client. Note I'm using sqlite3, not sqlite, the older is a client for 2.8.xx version.

Now, create the customers table using this command:
sqlite> create table customers(id integer not null primary key,
...> firstname varchar(100),
...> lastname varchar(100),
...> borndate date);
After creating the table, return to the shell:
sqlite> .quit
You can do a 'ls' in Linux to check if the file tutorial.db exists.

Sample program

The following sample program assumes you have created the tutorial.db database in the same directory as the compiled program, just type it in any text editor, then save as sqlitetest.pp and call FreePascal compiler from the command line using this command:

fpc -Sd -B -CX -Xs sqlitetest.pp

The program

program SqliteTest;

uses
db, sqlite3ds, SysUtils;

{$mode objfpc}

var
dsTutorial: TSQLite3Dataset;
mSql: string;
(* TFields declarations
You can use Field[n].AsString but its slower *)

mId: TIntegerField;
mFirstName: TStringField;
mLastName: TStringField;
mBornDate: TDateTimeField;

begin
dsTutorial := TSqlite3Dataset.Create(nil);

try
with dsTutorial do
begin
FileName := 'tutorial.db';
TableName := 'customers';
PrimaryKey := 'Id';

(* Define the Insert skleton -uptate and delete works the same way-*)
mSql := 'insert into customers(firstname, lastname, borndate) ' +
'values(''%s'', ''%s'', %f)';

(* Non transactional method *)
(* Insert first customer *)
Sql := Format(mSql, ['Leonardo', 'Ramé', Now]);
ExecSql;

(* Insert second customer *)
Sql := Format(mSql, ['Michael', 'Stratten', Now]);
ExecSql;

(* Transactional method *)
(* I don't really know why I must populate the TFields using a Select,
if you know an elegant way to accomplish this, please tell me. *)
Sql := 'select Id, FirstName, LastName, BornDate from customers limit 1';
Open;

(* Assign TFields *)
mId := TIntegerField(Fields[0]);
mFirstName := TStringField(Fields[1]);
mLastName := TStringField(Fields[2]);
mBornDate := TDateTimeField(Fields[3]);

(* Append, Edit or Insert for the first field *);
Append;
mFirstName.Value := 'Juan';
mLastName.Value := 'Pérez';
mBornDate.Value := Now;
Post;

(* Append, Edit or Insert for the second field *);
Insert;
mFirstName.Value := 'Johan';
mLastName.Value := 'Arndth';
mBornDate.Value := Now;
Post;

(* This commits the data to the db. *)
ApplyUpdates;

(* Now, select all fields *)
Close;
Sql := 'select Id, FirstName, LastName, BornDate from customers';
Open;

(* To go to the first record of the DataSet, use First.
This isn't usefull here since Open points to the first record,
but you'll need in your projects so I keep it in the example. *)
First;
while not Eof do
begin
mId := TIntegerField(Fields[0]);
mFirstName := TStringField(Fields[1]);
mLastName := TStringField(Fields[2]);
mBornDate := TDateTimeField(Fields[3]);

writeln (IntToStr(mId.Value) + ' - ' + mFirstName.Value + ', ' +
mLastName.Value + ' - ' + DateToStr(mBornDate.Value));
(* Move to the next record *)
Next;
end;
end;
finally
dsTutorial.Free;
end;

end.

Comments:
Extracted from the fillds.pp example of FCL-DB (Sqlite):

//Calling Open with an empty SQL, is the same of setting SQL to 'SELECT * from [TableName]';

Then, you must replace the first select with:

SQL := ''; // To be sure SQL is empty
Open;

That's it.
 
thankyou for the help :)
 
hey, it's helpful...
 
Keep up the good work.
 
Thank you. Finally got fpc / Lazarus to work with sqlite3.

Great combo for database development!
 
how does security work (sql injections) with fcl db?
 
I don't think this framework includes sql injection protection.

This must be handled by the programmer by their own.
 
Merci beaucoup !
je vais pouvoir utiliser SQLITE avec fpc

 
Post a Comment

Links to this post:

Create a Link



<< Home

This page is powered by Blogger. Isn't yours?