SQLdb Tutorial1

From Lazarus wiki
Revision as of 18:51, 15 November 2012 by BigChimp (talk | contribs) (Added salary column used in sqldb tutorial3)
Jump to navigationJump to search

Template:SQLdb Tutorial

Introduction

This tutorial shows you how to program databases using practical example code based on the SQLdb Package. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he should read relevant books/documentation. For this tutorial I use Firebird with the example database employee.fdb. Other databases can also be used; some adjustments will need to be made which are mentioned in the text.

While this tutorial may seem long, it mostly is just a lot of text that explains why you should type what you type. As you can see at the end, the amount of actual code you will need for a working application is not that great. More experienced developers will hopefully be able to glance through the instructions and quickly understand what's going on. Also, you can stop at the end of the Basic example chapter and have a working program.

This tutorial is based on a German tutorial by Swen, but it is extended, especially after the Basic example. Swen wants the German version to remain as-is. If this is a problem, we can rename this version and base a new German translation on that.

From Swen: thanks to Joost and Michael. Without their help this tutorial probably never would have come about.

Requirements

If possible you should use a recent Lazarus version (with FPC 2.2.2 or newer) for this tutorial. If the SQLdb package isn't already installed, you should do it now (Package -> Install/Uninstall packages ... -> SQLDBLaz 1.0.1).

Furthermore you need an SQL/relational database, such as Firebird (if possible version 2.0 or newer). It's easiest if you use standard settings (e.g user name SYSDBA and password masterkey), and that you have the employee sample database installed.

You can use another database (e.g. MySQL, PostgreSQL, Oracle, SQLite or another database using ODBC): you'd have to have the proper database/table structure (see below), and use the relevant TSQLConnector descendant (also indicated below). If your database has significant differences regarding transactions etc. please make a note in the relevant section. Notes on SQLite have been started.

Firebird database libraries on Windows

Finally, on Windows you will need to have the Firebird client DLLs present, optionally in your system directory, but preferably in your project directory (for design time support in the IDE) and in the output directory where the executable is (to run the compiled program). One easy way to get the client DLLs is: download Firebird Embedded 2.5 from [1] Extract these files to your application directory:

fbembed.dll
firebird.msg
ib_util.dll
icudt30.dll
icuin30.dll
icuuc30.dll
Microsoft.VC80.CRT.manifest
msvcp80.dll
msvcr80.dll

Rename fbembed.dll to fbclient.dll (the name for a regular, client-server Firebird client). The embedded Firebird DLL can also act as a regular Firebird client.

Make sure the employee.fdb database is in your project directory.

Finally, compile your project (even if it empty) once to create the output directory, and copy the dlls, as well as the employee.fdb database, into that directory.

Firebird database libraries on other systems

On Linux/OSX, you will also need the Firebird client shared libraries. On Linux you can use your distribution's method of getting programs to get the Firebird client packages, e.g. on Debian: <bash> aptitude install libfbclient2 </bash>

No Firebird or employee.fdb installed?

If you don't have the employee sample database installed or are using a different database, here is a minimal version of the table we'll be using:

CREATE TABLE CUSTOMER
(
  CUST_NO INTEGER NOT NULL,
  CUSTOMER VARCHAR(25) NOT NULL,
  CITY VARCHAR(25),
  COUNTRY VARCHAR(15),
  SALARY NUMERIC(10,2),
  CONSTRAINT INTEG_60 PRIMARY KEY (CUST_NO)
);

Some data so you can at least show something:

INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('1', 'Michael Design', 'San Diego', 'USA', 36000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2', 'VC Technologies', 'Dallas', 'USA', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('3', 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA', 48000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('4', 'Felipe Bank', 'Manchester', 'England', 53000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('5', 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong', 35000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('6', 'Van der Voort Int.', 'Ottawa', 'Canada', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('7', 'Mrs. Mauvais', 'Pebble Beach', 'USA', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('8', 'Asinine Vacation Rentals', 'Lihue', 'USA', 37000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('9', 'Fax', 'Turtle Island', 'Fiji', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('10', 'FPC Corporation', 'Tokyo', 'Japan', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('11', 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland', 80000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('12', '3D-Pad Corp.', 'Paris', 'France', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('13', 'Swen Export, Ltd.', 'Milan', 'Italy', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('14', 'Graeme Consulting', 'Brussels', 'Belgium', 40000);
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('15', 'Klenin Inc.', 'Den Haag', 'Netherlands', 60000);

Please create the database, table and insert the data in your database environment.

SQLite

If you are using SQLite, you can create the database mentioned above in your project directory by running the sqlite executable:

sqlite employee.sqlite

Now copy and paste the above CREATE TABLE and INSERT statements. To test if the right data is present, enter this query:

select * from customer;

End your session with .quit A file called employee.sqlite should now be created in your project directory.

Make sure the required sqlite dll/so is installed - e.g. on Windows, sqlite3.dll should be present in your project directory.

Compile your project (even if it is empty) once to create the output directory, and (on Windows) copy the dll, as well as the employee.sqlite database, into that directory.

PostgreSQL

This section assumes you're using a Linux server and the shell; comparable steps can be done using Windows and GUI tools such as pgadmin Log in to your server and switch to the postgres account:

su - postgres -c psql # immediately start up psql SQL interpreter

Create a user for the database and the tables:

CREATE USER employee WITH PASSWORD 'hellopassword'; -- of course, adjust password to taste
-- something like  'CREATE ROLE' should appear indicating success.
-- to later change the password you can use something like
-- alter user employee with password '<newpasswordhere>';
-- We're going to let the password never expire; if you want more security, you can leave this step out:
ALTER USER employee VALID UNTIL 'infinity'; --password never expires
-- Now we're tightening it up a bit again:
-- Don't allow user to create a database or create other users:
ALTER USER employee NOCREATEDB NOCREATEUSER; --restrict object creation
-- something like 'ALTER ROLE' should appear indicating success.
-- Create our database:
CREATE DATABASE employee;
-- something like CREATE DATABASE should appear indicating success.
-- Assign all privileges on database employee to user employee:
GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- allow user full permissions to database
-- something like GRANT should appear indicating success.
-- We create the table using a serial datatype - aka autonumber/autoincrement:
CREATE TABLE customer
(
  cust_no serial NOT NULL,
  customer character varying(25) NOT NULL,
  city character varying(25),
  country character varying(15),
  CONSTRAINT integ_60 PRIMARY KEY (cust_no )
);
-- Now copy and paste the above INSERT statements. To test if the right data is present, enter this query: 
SELECT * FROM customer;
--Exit out of psql:
\q

Now you should be on a shell logged in as the postgres user.

If your server is on another machine than your development machine, make sure you allow network access to the database. See your postgresql documentation for details, but something like this should work:

# please adjust nano (e.g. use vim,emacs,joe...) and the postgres version number depending on situation
nano /etc/postgresql/8.4/main/pg_hba.conf

Verify if there is a line like - NOTE: replace 192.168.0.1 with your own LAN ip address range

#allow access from local network using md5 hashed passwords: host all all 192.168.0.1/24 md5

or more restrictive:

# only allow network access to the employee database by the employee user host employee employee 192.168.0.1/24 md5

If there isn't such a line, add the line at the end, save and close your editor. See PostgreSQL documentation for more details.

Reload PostgreSQL settings:

 
psql

then

 
SELECT pg_reload_conf(); --reload settings...
-- ...and exit back to shell:
\q

Test logging in to PostgreSQL.

Note: by default PostgreSQL tries an ident/unix domain socket login which doesn't allow passwords. So we specify a host to force TCP/IP login:

psql -h 127.0.0.1 -d employee -U employee -W #Log in via tcp/ip. enter your db password

Make sure the required sqlite dll/so is installed - e.g. on Windows, sqlite3.dll should be present in your project directory.

Compile your project (even if it is empty) once to create the output directory, and (on Windows) copy the dllsinto that directory.

Basic example

Project and components

First you should create a new Lazarus project.

To get access to our database we need one TIBConnection, one TSQLTransaction and one TSQLQuery component from the 'SQLdb' tab in the component palette.

TIBConnection is an Interbase/Firebird specific connection component. If you are using a different database, substitute the proper component from the 'SQLDB' tab, e.g. a TSQLite3Connection for an SQLite database, PQConnection for a PostgreSQL database. Discussion of setting up any database access libraries is out of scope for this tutorial; see e.g. Databases for that.

Click on the TIBConnection (or equivalent connection component) on your form, and in the Object Inspector, change the name to DBConnection. This will simplify the rest of the tutorial when using different databases. It's also generally a good idea to name your components for something useful in your program (e.g. MainframeDBConnection) so you know what it stands for.

The other two components, TSQLTransaction and TSQLQuery, can be used for all databases that are supported by SQLdb.

To display the data, we use a TDBGrid component, which can be found on the 'Data Controls' tab. To connect this component to the database components we need a TDatasource component from the 'Data Access' tab.

Now we have all database components needed for the first example. You can enlarge the TDBGrid to have enough space to display all data.

Link the components

Next we need to connect our components. A very simple way is to use the object inspector, but you can also do this in your source code.

Change the Transaction property of DBConnection to 'SQLTransaction1'. This causes the Database property of SQLTransaction1 to automatically change to 'DBConnection'.

Then, change the Database property of SQLQuery1 to 'DBConnection'. Lazarus automatically adds the value for the 'Transaction' property.

Next, change the Dataset property of Datasource1 to 'SQLQuery1'.

Finally we change the Datasource property of DBGrid1 to 'Datasource1'.

We now end up with a connection that links its default transaction to a transaction component. The transaction component links its database property to the connection object. These two components are enough to connect and execute instructions, but not enough to show queries. For this, SQLQuery component is used, which points to the database (and links to its default transaction). With the SQLQuery, we'll later on retrieve data and post it back to the database.

Finally, the datasource component, which is linked to the query component, is a sort of place holder. It keeps track of where in the query dataset we are and the GUI components are linked to that so they all show the same record.

If this is gibberish to you, don't despair: with just some more work, we'll be able to show our first data.

Connecting to the database

How can we now show the data from our database on the screen? First we need to tell DBConnection where the employee.fdb database is located (usually in the .../examples/empbuild/ subdirectory of your Firebird installation). Again you have the choice: you can use the object inspector to assign the path or do it directly in your source code.

We choose to use the object inspector: set the DBConnection 'HostName' property to the Firebird server name or IP adddress. Use localhost if a Firebird server is running on your development machine; use a blank value if you use the embedded Firebird client. Change the DatabaseName property of DBConnection to the path to the employee.fdb file on the database server (e.g. C:\Program Files\Firebird\Firebird_2_0\examples\empbuild\EMPLOYEE.FDB on a Windows machine).

Before the database server grants access to the data, it will check authorisation via username and password. A serious database application will ask the user for both values when the application is started, and send these to the server when connecting. A possible way of doing this is shown in SQLDB Tutorial3.

However, for now, to simplify matters, we use the object inspector again to hard code these. Change the 'UserName' property to 'SYSDBA' and 'Password' to 'masterkey' (of course, adjust if your database installation has a different username/password).

Now check if all settings so far are correct: set the 'Connected' property to 'True'. If the database path isn't correct or if username or password are wrong, you will get an error message. If the connection was successful, you should cut it now (set 'Connected' to 'False').

PostgreSQL

The situation with PostgreSQL is very similar to that on Firebird. The database name does not have a path - you just specify a name part (e.g. 'employee'). PostgreSQL has no embedded mode, so you need to fill out the HostName property for the connection test to work.

SQLite

For SQLite, you can leave the 'HostName', 'UserName', and 'Password' properties empty. Set the 'DatabaseName' to the name of your SQLite file, e.g. employee.sqlite. Note: sqlite will create the database specified if it doesn't exist, so be careful here.

You should now have something like the following screenshot - todo: this screenshot is actually further along, we only have a button now:

Form and components set up

Choosing what data to show

Although the connection was successful, no data was displayed. The reason is simple. We haven't told the database server which data to return: the employee.fdb database contains several tables, and we haven't told Firebird the table we want to see. If you don't know the structure of a database, you can use tools like FlameRobin, to display the contents. Lazarus also provides such a tool - the DataDesktop. You can find it in the /tools/lazdatadesktop/ subdirectory of Lazarus. Save our project and then open the project lazdatadesktop.lpi and compile it.

The DataDesktop in action

Back to our example.

We want to display all data from the table 'CUSTOMER'. The SQL instruction for that is:

select * from CUSTOMER

We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this would look like:

SQLQuery1.SQL.Text := 'select * from CUSTOMER';

The SQL instruction must be enclosed by single quotes. You also have the ability to assign the content of another component (e.g. Edit1.Text). This is not always a good idea; see Secure programming (a more advanced text) for details on SQL injection.

Let's add a TButton from the 'Standard' tab on the form. When the user clicks on the button, data retrieval should start. We will need some code for this. Double click on Button1. Lazarus then creates the skeleton of the necessary procedure. In our source code we should find the following lines:

procedure TForm1.Button1Click(Sender: TObject);
begin

end;

Between begin and end we must enter the instructions needed to display the data.... obviuosly that will be something to do with SQLQuery1..

The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close the component first:

SQLQuery1.Close;

Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands:

SQLQuery1.SQL.Text := 'select * from CUSTOMER';

Now we need to establish the connection to the database, activate the transaction and open the query:

DBConnection.Connected := True;
SQLTransaction1.Active := True;
SQLQuery1.Open;

You can omit the first two instructions, because they are done automatically by the third instruction. If you compile the project at this point, you could already see the data from the 'CUSTOMER' table.

However, a serious application must make sure that all open database connections are properly closed when not needed anymore. Otherwise the secondary effects would not be foreseeable. So, we use the OnClose event of our form (create it with a double click in the object inspector):

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin

end;

To close the connection we use the reverse order compared to our opening code:

SQLQuery1.Close;
SQLTransaction1.Active := False;
DBConnection.Connected := False;

Summary

Up to now we have learned how to connect to a database using the SQLdb package and how to display the contents of a table on the screen. If you want to add more functionality such as editing, please continue with SQLdb Tutorial2

If you followed the previous steps, then your code should look like:

unit Unit1; 

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
  Graphics, Dialogs, DBGrids, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    DBConnection: TIBConnection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure Button1Click(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
  private
    { private declarations }
  public
    { public declarations }
  end; 

var
  Form1: TForm1; 

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.Close;
  SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
  DBConnection.Connected:= True;
  SQLTransaction1.Active:= True;
  SQLQuery1.Open;
end;

procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
  SQLQuery1.Close;
  SQLTransaction1.Active:= False;
  DBConnection.Connected:= False;
end;

end.


See also