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, 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.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;

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.