Free Pascal

SQLite Database Tutorial: GuitarClub, part 1

Structure of the database and the table

Let's start with a simplified version, that contains only one table.

The database GuitarClub.db contains a table named Members, with the details of each club member. Its columns (aka "fields") are defined as follows:

Field Name Type Index
Lastname VARCHAR(20)  
Firstname VARCHAR(20)  
ID VARCHAR(4) Primary key

Note: for SQLite, the type VARCHAR is the same as the type TEXT. It is only important for some components of Lazarus. We'll come back to this later on.

GuitarClub project with 1 table

For this project, create a folder FPlaz\SQLite01, download and unzip to this folder.

  1. Start Lazarus and open project SQLite01.
  2. Compile and run the application (press F9).

    Below is a snapshot of the application at runtime:

  3. Play a while with the application: navigate through the grid by using the DBNavigator, modify records, add records, delete records.
  4. Stop the program.

When you look at the source code, it is surprisingly short:

procedure TForm1.FormShow(Sender: TObject);
  SQLTransaction1.Active := True;

However, most of the functionality was set up in the Object InSpector.
Let's have a look at the components at design time:

Name Properties Value Notes
SQLite3Connection1 DatabaseName guitarclub.db  
Connected False
Transaction SQLTransaction1
queryMembers Active False

Property SQL defines which rows and columns are shown in the grid.
Database SQLite3Connection1
Options [sqoKeepOpenOnCommit,
SQL select * from members order by
  upper(lastname), upper(firstname)
Transaction SQLTransaction1
UpdateMode upWhereAll
SQLTransaction1 Database SQLite3Connection1  
dsMembers DataSet queryMembers  
gridMembers Columns Lastname:
   Fieldname: Lastname; Width: 120
   Fieldname: Firstname; Width: 120
   Fieldname: ID; Width: 50
DataSource dsMembers
DBNavigator1 DataSource dsMembers