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, please go to http://www.blogger.com/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:
Now, create the customers table using this command:
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:
The program
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, please go to http://www.blogger.com/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.dbWith 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,After creating the table, return to the shell:
...> firstname varchar(100),
...> lastname varchar(100),
...> borndate date);
sqlite> .quitYou 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;
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.
