Difference between revisions of "SQLdb Tutorial1"

From Lazarus wiki
Jump to navigationJump to search
m (→‎No Firebird or employee.fdb installed?: Change cust no to match postgresql serial datatype)
(added note to SQLite section about (MEMO) text fields)
 
(40 intermediate revisions by 11 users not shown)
Line 1: Line 1:
{{SQLdb Tutorial}}
+
{{SQLdb Tutorial1}}
  
 +
{{Infobox databases}}
 
== Introduction ==
 
== Introduction ==
  
Line 15: Line 16:
 
== Requirements ==
 
== 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).
+
This tutorial is written for use with recent Lazarus versions (Laz 1.0); it should also work on older versions Lazarus 0.9.30.
  
Furthermore you need an SQL/relational database, such as [http://sourceforge.net/project/showfiles.php?group_id=9028 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.
+
Please see [[SQLdb_Tutorial0]], which will walk you through making sure you have the right sample database set up.
 
 
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
 
[http://www.firebirdsql.org/en/firebird-2-5/]
 
Extract these files to your application directory:
 
<syntaxhighlight lang="bash">
 
fbembed.dll
 
firebird.msg
 
ib_util.dll
 
icudt30.dll
 
icuin30.dll
 
icuuc30.dll
 
Microsoft.VC80.CRT.manifest
 
msvcp80.dll
 
msvcr80.dll
 
</syntaxhighlight>
 
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:
 
<syntaxhighlight lang="sql">
 
CREATE TABLE CUSTOMER
 
(
 
  CUST_NO INTEGER NOT NULL,
 
  CUSTOMER VARCHAR(25) NOT NULL,
 
  CITY VARCHAR(25),
 
  COUNTRY VARCHAR(15),
 
  CONSTRAINT INTEG_60 PRIMARY KEY (CUST_NO)
 
);
 
</syntaxhighlight>
 
 
 
Some data so you can at least show something:
 
<syntaxhighlight lang="sql">
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('1', 'Michael Design', 'San Diego', 'USA');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2', 'VC Technologies', 'Dallas', 'USA');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('3', 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('4', 'Felipe Bank', 'Manchester', 'England');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('5', 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('6', 'Van der Voort Int.', 'Ottawa', 'Canada');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('7', 'Mrs. Mauvais', 'Pebble Beach', 'USA');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('8', 'Asinine Vacation Rentals', 'Lihue', 'USA');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('9', 'Fax', 'Turtle Island', 'Fiji');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('10', 'FPC Corporation', 'Tokyo', 'Japan');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('11', 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('12', '3D-Pad Corp.', 'Paris', 'France');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('13', 'Swen Export, Ltd.', 'Milan', 'Italy');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('14', 'Graeme Consulting', 'Brussels', 'Belgium');
 
INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('15', 'Klenin Inc.', 'Den Haag', 'Netherlands');
 
</syntaxhighlight>
 
 
 
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:
 
<syntaxhighlight lang="bash">
 
sqlite employee.sqlite
 
</syntaxhighlight>
 
Now copy and paste the above CREATE TABLE and INSERT statements.
 
To test if the right data is present, enter this query:
 
<syntaxhighlight lang="SQL">
 
select * from customer;
 
</syntaxhighlight>
 
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:
 
<syntaxhighlight lang=bash>
 
su - postgres -c psql # immediately start up psql SQL interpreter
 
</syntaxhighlight>
 
 
 
Create a user for the database and the tables:
 
<syntaxhighlight lang=SQL>
 
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 funambol to user employee:
 
GRANT ALL PRIVILEGES ON DATABASE employee TO employee; -- allow user full permissions to database
 
-- something like GRANT should appear indicating success.
 
-- 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;
 
--Exit out of psql:
 
\q
 
</syntaxhighlight>
 
 
 
''BigChimp September 2012: we need an autoincrement/autonumber field/trigger for customer number otherwise we run in trouble with inserting data later''
 
 
 
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:
 
<syntaxhighlight lang=bash>
 
# 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
 
</syntaxhighlight>
 
 
 
Verify if there is a line like - NOTE: replace 192.168.0.1 with your own LAN ip address range
 
 
 
<nowiki>
 
#allow access from local network using md5 hashed passwords:
 
 
 
host    all        all        192.168.0.1/24      md5
 
</nowiki>
 
 
 
or more restrictive:
 
 
 
<nowiki>
 
# only allow network access to the employee database by the employee user
 
 
 
host    employee        employee        192.168.0.1/24      md5
 
</nowiki>
 
 
 
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:
 
<syntaxhighlight lang=bash>
 
psql
 
</syntaxhighlight>
 
then
 
<syntaxhighlight lang=bash>
 
SELECT pg_reload_conf(); --reload settings...
 
-- ...and exit back to shell:
 
\q
 
</syntaxhighlight>
 
 
 
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:
 
<syntaxhighlight lang=bash>
 
psql -h 127.0.0.1 -d employee -U employee -W #Log in via tcp/ip. enter your db password
 
</syntaxhighlight>
 
 
 
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 ==
 
== Basic example ==
Line 192: Line 25:
 
First you should create a new Lazarus project.
 
First you should create a new Lazarus project.
  
To get access to our database we need one ''TIBConnection'', one ''TSQLTransaction'' and one ''[[Working With TSQLQuery|TSQLQuery]]'' component from the 'SQLdb' tab in the component palette.
+
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.
+
[[File:sqldbcomponents.png‎]]
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.
+
[[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, [[TPQConnection]] 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.
 
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.
+
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.
+
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.
 
Now we have all database components needed for the first example. You can enlarge the TDBGrid to have enough space to display all data.
Line 217: Line 52:
 
Finally we change the ''Datasource'' property of DBGrid1 to 'Datasource1'.
 
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.
+
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, an 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.
 
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.
Line 225: Line 60:
 
=== Connecting to the database ===
 
=== Connecting to the database ===
 
How can we now show the data from our database on the screen?
 
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.
+
 
 +
First we need to tell DBConnection where the employee.fdb database is located. Locations of that db differ depending on operating system, it could be something like:
 +
* .../examples/empbuild/ subdirectory of your Firebird installation on Linux
 +
* C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB on a Windows machine
 +
* Using Firebird embedded, the file should be in your project directory
 +
 
 +
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.
 
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).
+
Change the ''DatabaseName'' property of DBConnection to the path to the employee.fdb file on the database server. If you use embedded Firebird, the path part should be empty and you should just specify the filename.
  
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.
+
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).  
 
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).  
Line 244: Line 85:
 
You should now have something like the following screenshot - todo: this screenshot is actually further along, we only have a button now:
 
You should now have something like the following screenshot - todo: this screenshot is actually further along, we only have a button now:
 
[[Image:SQLTut1ComponentsSetUp.png|framed|center|Form and components set up]]
 
[[Image:SQLTut1ComponentsSetUp.png|framed|center|Form and components set up]]
 +
 +
Change the property ''Options'' → ''dgDisplayMemoText'' of the DBGrid to '(True)'. Otherwise you will see (MEMO) in all the data fields.
  
 
=== Choosing what data to show ===
 
=== 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 [http://www.flamerobin.org/index.php 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.  
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 [http://www.flamerobin.org/index.php 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.  
 
  
 
[[Image:DataDictonary1.png|framed|center|The DataDesktop in action]]
 
[[Image:DataDictonary1.png|framed|center|The DataDesktop in action]]
Line 259: Line 101:
  
 
We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this would look like:
 
We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this would look like:
<syntaxhighlight>
+
 
 +
<syntaxhighlight lang=pascal>
 
SQLQuery1.SQL.Text := 'select * from CUSTOMER';
 
SQLQuery1.SQL.Text := 'select * from CUSTOMER';
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 
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.
 
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.  
+
Let's add a ''TButton'' from the 'Standard' tab on the form. When the user clicks on the button, data retrieval should start. Change its ''Caption'' property from "Button1" to Show data".
 +
 
 
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:
 
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:
  
<syntaxhighlight>
+
<syntaxhighlight lang=pascal>
 
procedure TForm1.Button1Click(Sender: TObject);
 
procedure TForm1.Button1Click(Sender: TObject);
 
begin
 
begin
Line 274: Line 119:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Between ''begin'' and ''end'' we must enter the instructions needed to display the data.... obviuosly that will be something to do with SQLQuery1..
+
Between ''begin'' and ''end'' we must enter the instructions needed to display the data.... obviously 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:
 
The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close the component first:
<syntaxhighlight>
+
 
 +
<syntaxhighlight lang=pascal>
 
SQLQuery1.Close;
 
SQLQuery1.Close;
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 
Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands:
 
Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands:
<syntaxhighlight>
+
 
 +
<syntaxhighlight lang=pascal>
 
SQLQuery1.SQL.Text := 'select * from CUSTOMER';
 
SQLQuery1.SQL.Text := 'select * from CUSTOMER';
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
Now we need to establish the connection to the database, activate the transaction and open the query:
 
Now we need to establish the connection to the database, activate the transaction and open the query:
<syntaxhighlight>
+
 
 +
<syntaxhighlight lang=pascal>
 
DBConnection.Connected := True;
 
DBConnection.Connected := True;
 
SQLTransaction1.Active := True;
 
SQLTransaction1.Active := True;
Line 295: Line 144:
 
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):
 
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):
  
<syntaxhighlight>
+
<syntaxhighlight lang=pascal>
 
procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
 
procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
 
begin
 
begin
Line 303: Line 152:
  
 
To close the connection we use the reverse order compared to our opening code:
 
To close the connection we use the reverse order compared to our opening code:
<syntaxhighlight>
+
 
 +
<syntaxhighlight lang=pascal>
 
SQLQuery1.Close;
 
SQLQuery1.Close;
 
SQLTransaction1.Active := False;
 
SQLTransaction1.Active := False;
Line 309: Line 159:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=== Summary ===
+
If you set DBConnection.Connected to False, the Transaction and the Query is automatically closed and you can omit closing them manually.
Up to now we have learned how to connect to a (Firebird) database using the SQLdb package and how to display the contents of a table on the screen.
+
 
 +
== 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:
 
If you followed the previous steps, then your code should look like:
<syntaxhighlight>
+
 
 +
<syntaxhighlight lang=pascal>
 
unit Unit1;  
 
unit Unit1;  
  
Line 355: Line 209:
 
begin
 
begin
 
   SQLQuery1.Close;
 
   SQLQuery1.Close;
  SQLQuery1.SQL.Clear;
 
 
   SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
 
   SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
 
   DBConnection.Connected:= True;
 
   DBConnection.Connected:= True;
Line 371: Line 224:
 
end.                                                 
 
end.                                                 
 
</syntaxhighlight>
 
</syntaxhighlight>
 
== Enhancement of the basic example ==
 
 
While this application works, we can add some refinements.
 
 
=== Dynamic database connection ===
 
 
Up to now, we used a fixed database server name, database location, username and password for simplicity.
 
As mentioned, "real" applications normally let users specify their own username and password.
 
 
Let's change the form so we can specify them: add two TEdits from the standard menu. Set their name properties to Username and Password.
 
Set the Password's PasswordChar property to * (the asterisk) for some security against people looking over your shoulder.
 
 
If you want to make it easier (and less secure, of course) to connect, you can set the UserName Text property to a valid database user, such as SYSDBA. You could even set the Password Text property to a default value like masterkey, easy for testing on developer machines if security doesn't matter...
 
Cosmetically, adding some labels so people know what they're supposed to type is useful.
 
 
Also, to make it easier to connect to any employee sample database on any Firebird/Interbase server, we add two textboxes for server name and database path. Add another two TEdits, and name them ServerName and DatabaseName.
 
If you want, you can set the 'Text' property to default sensible values for your situation, e.g. localhost and C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB
 
Labels to explain what users need to enter would help here, too.
 
 
For clarity, we're going to remove the connection info from our designtime components: on the DBConnection component, remove all text from the UserName, Password, DatabaseName and HostName properties.
 
 
Now, finally, we need to tell our database connection component how to connect. This should normally only be necessary only at the beginning of an application run. In our case the existing 'Button1' code is a good way to set up the connection:
 
Add code until you get:
 
<syntaxhighlight>
 
procedure TForm1.Button1Click(Sender: TObject);
 
begin
 
  SQLQuery1.Close;
 
  //Connection settings for Firebird/Interbase database
 
  //only needed when we have not yet connected:
 
  if DBConnection.Connected = false then
 
  begin
 
    DBConnection.HostName := ServerName.Text;
 
    DBConnection.DatabaseName := DatabaseName.Text;
 
    DBConnection.Username := UserName.Text;
 
    DBConnection.Password := Password.Text;
 
    // Now we've set up our connection, visually show that
 
    // changes are not possibly any more
 
    ServerName.ReadOnly:=true;
 
    DatabaseName.ReadOnly:=true;
 
    UserName.ReadOnly:=true;
 
    Password.ReadOnly:=true;
 
  end; 
 
  SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
 
  DBConnection.Connected:= True;
 
  SQLTransaction1.Active:= True;
 
  SQLQuery1.Open;
 
end;
 
</syntaxhighlight>
 
Now run and test if you can connect.
 
 
==== SQLite, other databases ====
 
Adjust the Text property in the DatabaseName TEdit as needed; e.g. employee.sqlite for SQLite.
 
For sqlite, specifying HostName, Username and Password doesn't make sense, so you can omit these TEdits. Obviously, leave out/comment out assigning the corresponding values to DBConnection in the code above.
 
For Firebird embedded, please hardcode the Username to SYSDBA; specifying this when sqlite is used won't hurt.
 
 
The code will look something like:
 
<syntaxhighlight>
 
procedure TForm1.Button1Click(Sender: TObject);
 
begin
 
  SQLQuery1.Close;
 
  //Connection settings for embedded databases
 
  //only needed when we have not yet connected:
 
  if DBConnection.Connected = false then
 
  begin
 
    DBConnection.DatabaseName := DatabaseName.Text;
 
DBConnection.UserName := 'SYSDBA'; //Firebird embedded needs this; doesn't harm if using SQLite
 
    // Now we've set up our connection, visually show that
 
    // changes are not possibly any more
 
    DatabaseName.ReadOnly:=true;
 
  end; 
 
  SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
 
  DBConnection.Connected:= True;
 
  SQLTransaction1.Active:= True;
 
  SQLQuery1.Open;
 
end;
 
</syntaxhighlight>
 
 
=== Filtering data ===
 
 
Often, tables contain a huge amount of data that the user doesn't want to see (and that might take a long time to query from the database and travel over the network). Let's assume that only the customers from the USA should be displayed. Therefore the SQL instruction in 'SQLQuery1' would look like:
 
<syntaxhighlight lang="SQL">
 
select * from CUSTOMER where COUNTRY = 'USA'
 
</syntaxhighlight>
 
... which would translate to something like this in our code:
 
<syntaxhighlight>
 
SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = 'USA'';
 
</syntaxhighlight>
 
There are two reasons why we will not use this instruction for our example application:
 
 
First there is a problem with the usage of the single quote. The compiler would interpret the quote before USA as a closing quote (the first quote is before the select from...) and so the SQL instruction would become invalid. Solution: double the inside quotes: <syntaxhighlight>SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = ''USA''';</syntaxhighlight>
 
 
The second, more important reason is the fact, that we probably don't know what constraints the user will want to filter on. We don't want to limit the flexibility of the user.
 
 
To get this flexibility, first we change our SQL query statement and replace 'USA' by a placeholder (a parameter in SQL speak): change the Button1click procedure and replace
 
<syntaxhighlight>
 
SQLQuery1.SQL.Text := 'select * from CUSTOMER';
 
</syntaxhighlight>
 
with
 
<syntaxhighlight>
 
SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';
 
</syntaxhighlight>
 
 
The SQL parameter is marked by the leading colon. To allow the user to enter a value for the filter, we place a ''TEdit'' component on our form. Delete the value of its 'Text' property.
 
 
We can now take the text entered in the TEdit and fill the SQL COUNTRY parameter by using the 'Params' property of TSQLQuery. Add this below the previous statement:
 
<syntaxhighlight>
 
SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
 
</syntaxhighlight>
 
The parameter can be specified by its position or name. Using the name should improve the readability of the source code, and obviously helps if you insert more parameters in the middle of existing parameters.
 
 
We use .AsString to assign a string value to the parameter; there are equivalent property assignments for integer parameters, boolean parameters etc.
 
 
The code up to now forces us to use a filter. If a user specifies an empty value in the edit box, no record will be displayed. This is probably not what we want. Let's test for an empty value and build our query accordingly. We should end up with a procedure like this:
 
 
<syntaxhighlight>
 
procedure TForm1.Button1Click(Sender: TObject);
 
begin
 
  SQLQuery1.Close;
 
  //Connection settings for Firebird/Interbase database
 
  //only needed when we have not yet connected:
 
  if DBConnection.Connected = false then
 
  begin
 
    DBConnection.HostName := ServerName.Text;
 
    DBConnection.DatabaseName := DatabaseName.Text;
 
    DBConnection.Username := UserName.Text;
 
    DBConnection.Password := Password.Text;
 
    // Now we've set up our connection, visually show that
 
    // changes are not possibly any more
 
    ServerName.ReadOnly:=true;
 
    DatabaseName.ReadOnly:=true;
 
    UserName.ReadOnly:=true;
 
    Password.ReadOnly:=true;
 
  end;
 
  // Show all records, or filter if user specified a filter criterium
 
  if Edit1.Text='' then
 
    SQLQuery1.SQL.Text := 'select * from CUSTOMER'
 
  else
 
  begin
 
    SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
 
    SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
 
  end;
 
  DBConnection.Connected:= True;
 
  SQLTransaction1.Active:= True;
 
  SQLQuery1.Open;
 
end;                                           
 
</syntaxhighlight>
 
Now you can play around a bit with filtering using Edit1. If you enter a country that's not present in the database, an empty grid is shown.
 
 
=== Error handling ===
 
The application should run, but sometimes problems can occur.
 
Databases, even embedded databases can crash (e.g. when the database server crashes, the disk is full, or just due to a bug), leaving the application hanging.
 
 
Access to a database (any external process, really) should therefore '''always''' be integrated in a try ... except and/or try ... finally construct. This ensures that database errors are handled and the user isn't left out in the cold. A rudimental routine for our example application could look like this:
 
<syntaxhighlight>
 
begin
 
  try
 
    SQLQuery1.Close;
 
    ...
 
    SQLQuery1.Open;
 
  except
 
    //We could use EDatabaseError which is a general database error, but we're dealing with Firebird/Interbase, so:
 
    on E: EIBDatabaseError do
 
    begin
 
      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
 
      Edit1.Text:='';
 
    end;
 
  end;
 
end;               
 
</syntaxhighlight>
 
 
==== SQLite, PostgreSQL, other databases ====
 
You can either use the more generic EDatabaseError, or - if available - your own specialized databaseerror, if you need more details.
 
E.g. SQLite and the PostgreSQL driver in FPC 2.6.1 and lower doesn't have a specialized E*DatabaseError; you'd have to use EDatabaseError. PostgreSQL on FPC trunk (development version) has EPQDatabaseError.
 
 
== Editing data using the grid ==
 
 
=== Editing ===
 
Up to now, if you tried to edit data in the grid, the changes would not be saved. This is because the ''SQLQuery1'' is not instructed to send the changes to the database transaction at the right moment.
 
We need to fix this, and then commit the transaction in the database, so all changes get written. For this, you would use code like this:
 
<syntaxhighlight>
 
SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
 
SQLTransaction1.Commit; //... and commit them using the transaction.
 
//SQLTransaction1.Active now is false
 
</syntaxhighlight>
 
We want to make sure any edits (inserts, updates, deletes) are written to the database:
 
* when the users changes the filtering criteria and presses the button to query the database
 
* when the form is closed
 
 
It makes sense to make a separate procedure for this that is called in those two instances.
 
Go to the code, and add an empty line here:
 
<syntaxhighlight>
 
  TForm1 = class(TForm)
 
    Button1: TButton;
 
    Datasource1: TDatasource;
 
    DBGrid1: TDBGrid;
 
    Edit1: TEdit;
 
    DBConnection: TIBConnection;
 
    SQLQuery1: TSQLQuery;
 
    SQLTransaction1: TSQLTransaction;
 
*****insert the empty line here****
 
    procedure Button1click(Sender: TObject);
 
    procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
 
  private
 
</syntaxhighlight>
 
then type
 
<syntaxhighlight>
 
    procedure SaveChanges;
 
</syntaxhighlight>
 
press shift-ctrl-c (default combination) to let code completion automatically create the corresponding procedure body.
 
 
We need to add error handling and check that the transaction is active - remember, this code also gets called when pressing the button the first time, when the transaction is not active yet. We get:
 
<syntaxhighlight>
 
procedure Tform1.Savechanges;
 
// Saves edits done by user, if any.
 
begin
 
  try
 
    if SQLTransaction1.Active=true then
 
    // Only if we are within a started transaction;
 
    // otherwise you get "Operation cannot be performed on an inactive dataset"
 
    begin
 
      SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
 
      SQLTransaction1.Commit; //... and commit them using the transaction.
 
      //SQLTransaction1.Active now is false
 
    end;
 
  except
 
  on E: EIBDatabaseError do
 
    begin
 
      MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
 
        E.Message, mtError, [mbOK], 0);
 
      Edit1.Text := '';
 
    end;
 
  end;
 
end;
 
</syntaxhighlight>
 
 
Now we need to call this procedure at the appropriate moments:
 
<syntaxhighlight>
 
procedure Tform1.Button1click(Sender: TObject);
 
begin
 
  SaveChanges; //Saves changes and commits transaction
 
  try
 
    SQLQuery1.Close;
 
....
 
</syntaxhighlight>
 
and
 
<syntaxhighlight>
 
procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
 
begin
 
  SaveChanges; //Saves changes and commits transaction
 
  SQLQuery1.Close;
 
....
 
</syntaxhighlight>
 
 
Now test and see if edits made in the dbgrid are saved to the database.
 
 
=== Hiding primary key column ===
 
Often, you don't want your users to see autonumber/generated primary keys as they are only meant to maintain referential integrity. If users do see them, they might want to try the edit the numbers, get upset that the numbers change, that there are gaps in the numbers, etc.
 
 
In our example, CUST_NO is the primary key, with content auto-generated by Firebird using triggers and a sequence/generator. This means that you can insert a new record without specifying the CUST_NO; Firebird will create one automatically.
 
 
We could simply change our SQLQuery1.SQL.Text property to not include CUST_NO, but this would lead to problems when editing data - a primary key is needed in those circumstances for uniquely identifying the row/record in question.
 
 
Therefore, let's use a trick to query for all columns/fields in the table, but keep the grid from showing the first field, CUST_NO: in the Button1Click procedure, add code so it looks like:
 
<syntaxhighlight>
 
procedure Tform1.Button1click(Sender: TObject);
 
begin
 
...
 
    SQLQuery1.Open;
 
    // Hide the primary key column which is the first column in our queries.
 
    // We can only do this once the DBGrid has created the columns
 
    DBGrid1.Columns[0].Visible:=false;
 
</syntaxhighlight>
 
Recompile, and check to see if the primary key column is really hidden.
 
 
==== SQLite, other databases ====
 
* Other databases: a lot of other databases use an 'autonumber' or 'autoinc' type of field to provide auto-generated field content. Try changing your table definition and see if it works.
 
* Sqlite: the example above works for SQLite as is because we're using an integer primary key. See the [http://www.sqlite.org/autoinc.html documentation] for details.
 
 
=== Inserting new data ===
 
If you insert new rows/records without any CUST_NO information you may have noticed that you get an error message: <tt>Field CUST_NO is required, but not supplied</tt>. This also happens if you hid the CUST_NO column, as in the previous section.
 
 
The reason: Lazarus thinks that CUST_NO is required. That's not so strange, because it is a primary key and the underlying table definition in the database does say it is required.
 
 
If we can instruct Lazarus that this field is not actually required, we can pass empty values (=NULL values) to the database.
 
Fortunately, a query's field object has a ''Required'' property that does exactly that.
 
 
Change the code to something like:
 
<syntaxhighlight>
 
    SQLQuery1.Open;
 
    {
 
    Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, e.g.:
 
    Field CUST_NO is required, but not supplied
 
    We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
 
    when inserting new records.
 
    }
 
    SQLQuery1.FieldByName('CUST_NO').Required:=false;
 
    // Hide the primary key column which is the first column in our queries.
 
    // We can only do this once the DBGrid has created the columns
 
    DBGrid1.Columns[0].Visible:=false;
 
</syntaxhighlight>
 
 
=== Deleting data ===
 
Up to now, I haven't found a way to delete records/rows in the grid using the keyboard.
 
However, you can let your users use the mouse to do this. You don't even need to code a single line for this functionality...
 
 
On the 'Data Controls' tab, select a ''TDBNavigator'' component and drop it on the form, above the grid.
 
 
To indicate what the navigator should be linked to, set its ''DataSource'' property to your existing datasource ('DataSource1') using the Object Inspector.
 
Now you can use the button on the ''DBNavigator'' to delete records, but also insert them, and move around the records. Also, when editing cells/fields, you can use the ''Cancel'' button to cancel your edits.
 
 
=== Summary ===
 
If you followed along up to now, you can retrieve data from the database, filter it, and edit and delete data in the grid. Your code should look something like this:
 
<syntaxhighlight>
 
unit sqldbtutorial1unit;
 
 
{$mode objfpc}{$H+}
 
 
interface
 
 
uses
 
  Classes, SysUtils, IBConnection, sqldb, DB, FileUtil, Forms, Controls,
 
  Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls;
 
 
type
 
 
  { TForm1 }
 
 
  TForm1 = class(TForm)
 
    Button1: TButton;
 
    DatabaseName: TEdit;
 
    Datasource1: TDatasource;
 
    DBGrid1: TDBGrid;
 
    Dbnavigator1: Tdbnavigator;
 
    Edit1: TEdit;
 
    Label2: Tlabel;
 
    Label3: Tlabel;
 
    Label4: Tlabel;
 
    Label5: Tlabel;
 
    Password: TEdit;
 
    UserName: TEdit;
 
    ServerName: TEdit;
 
    DBConnection: TIBConnection;
 
    Label1: TLabel;
 
    SQLQuery1: TSQLQuery;
 
    SQLTransaction1: TSQLTransaction;
 
    procedure SaveChanges;
 
    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.Savechanges;
 
// Saves edits done by user, if any.
 
begin
 
  try
 
    if SQLTransaction1.Active=true then
 
    // Only if we are within a started transaction
 
    // otherwise you get "Operation cannot be performed on an inactive dataset"
 
    begin
 
      SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
 
      SQLTransaction1.Commit; //... and commit them using the transaction.
 
      //SQLTransaction1.Active now is false
 
    end;
 
  except
 
  on E: EIBDatabaseError do
 
    begin
 
      MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
 
        E.Message, mtError, [mbOK], 0);
 
      Edit1.Text := '';
 
    end;
 
  end;
 
end;
 
 
 
procedure Tform1.Button1click(Sender: TObject);
 
begin
 
  SaveChanges; //Saves changes and commits transaction
 
  try
 
    SQLQuery1.Close;
 
    //Connection settings for Firebird/Interbase database
 
    //only needed when we have not yet connected:
 
    if DBConnection.Connected = false then
 
    begin
 
      DBConnection.HostName := ServerName.Text;
 
      DBConnection.DatabaseName := DatabaseName.Text;
 
      DBConnection.Username := UserName.Text;
 
      DBConnection.Password := Password.Text;
 
      // Now we've set up our connection, visually show that
 
      // changes are not possibly any more
 
      ServerName.ReadOnly:=true;
 
      DatabaseName.ReadOnly:=true;
 
      UserName.ReadOnly:=true;
 
      Password.ReadOnly:=true;
 
    end;
 
    // Show all records, or filter if user specified a filter criterium
 
    if Edit1.Text='' then
 
      SQLQuery1.SQL.Text := 'select * from CUSTOMER'
 
    else
 
    begin
 
      SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
 
      SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
 
    end;
 
    DBConnection.Connected := True;
 
    SQLTransaction1.Active := True; //Starts a new transaction
 
    SQLQuery1.Open;
 
    {
 
    Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, i.e. error message:
 
    "Field CUST_NO is required, but not supplied"
 
    We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
 
    when inserting new records.
 
    }
 
    SQLQuery1.FieldByName('CUST_NO').Required:=false;
 
    {
 
    Hide the primary key column which is the first column in our queries.
 
    We can only do this once the DBGrid has created the columns
 
    }
 
    DBGrid1.Columns[0].Visible:=false;
 
  except
 
    //EDatabaseError is a general error, but we're dealing with Firebird/Interbase, so:
 
    on E: EIBDatabaseError do
 
    begin
 
      MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
 
        E.Message, mtError, [mbOK], 0);
 
      Edit1.Text := '';
 
    end;
 
  end;
 
end;
 
 
procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
 
begin
 
  SaveChanges; //Saves changes and commits transaction
 
  SQLQuery1.Close;
 
  SQLTransaction1.Active := False;
 
  DBConnection.Connected := False;
 
end;
 
 
end.
 
</syntaxhighlight>
 
 
== Embedded database without code changes ==
 
 
=== Firebird on Windows ===
 
A bonus for Firebird users on Windows: if you have been following this tutorial (even if you only did the basic example), you renamed the <tt>fbembed.dll</tt> embedded Firebird library to <tt>fbclient.dll</tt>. With this, Lazarus could connect to regular Firebird servers (either on another machine or on your local machine).
 
However, you can also copy the <tt>employee.fdb</tt> database to your application directory, run the application, clear the ''Server name'' TEdit and use Firebird embedded to directly connect to the database file, without any servers set up.
 
 
This is great if you want to deploy database applications to end users, but don't want the hassle of installing servers (checking if a server is already installed, if it's the right version, having users check firewalls, etc).
 
 
See [[Firebird embedded]] for more details.
 
 
September 2011: in recent development (SVN) versions of FreePascal, FPC tries to first load <tt>fbembed.dll</tt>, so you need not rename <tt>fbclient.dll</tt> anymore for this to work.
 
 
=== Firebird on Linux/OSX/Unix ===
 
There must be a way to get this to work on Linux/OSX. See [[Firebird in action]] for hints and links. Updates to the wiki are welcome.
 
 
=== SQLite ===
 
SQLite certainly offers embedded functionality - it does not allow a client/server setup on the other hand.
 
By following the tutorial above, you can see that switching between databases (e.g. SQLite and Firebird) is not so much work at all.
 
 
=== Other databases ===
 
Your database might offer similar functionality. Updates of this wiki for other database systems are welcome.
 
  
  
 
== See also ==
 
== See also ==
 +
* [[SQLdb Tutorial0]]: Instructions for setting up sample tables/sample data for the tutorial series.
 +
* [[SQLdb Tutorial2]]: Second part of the DB tutorial series, showing editing, inserting etc.
 +
* [[SQLdb Tutorial3]]: Third part of the DB tutorial series, showing how to program for multiple databases and use a login form
 +
* [[SQLdb Tutorial4]]: Fourth part of the DB tutorial series, showing how to use data modules
 
* [[Lazarus Database Overview]]: Information about the databases that Lazarus supports. Links to database-specific notes.
 
* [[Lazarus Database Overview]]: Information about the databases that Lazarus supports. Links to database-specific notes.
 
* [[SQLdb Package]]: information about the SQLdb package
 
* [[SQLdb Package]]: information about the SQLdb package
Line 852: Line 236:
 
* [[SqlDBHowto]]: information about using the SQLdb package
 
* [[SqlDBHowto]]: information about using the SQLdb package
 
* [[Working With TSQLQuery]]: information about TSQLQuery
 
* [[Working With TSQLQuery]]: information about TSQLQuery
 
[[Category:Databases]]
 
[[Category:Tutorials]]
 

Latest revision as of 05:40, 1 November 2022

Deutsch (de) English (en) español (es) français (fr) 日本語 (ja) 中文(中国大陆)‎ (zh_CN)

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

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

This tutorial is written for use with recent Lazarus versions (Laz 1.0); it should also work on older versions Lazarus 0.9.30.

Please see SQLdb_Tutorial0, which will walk you through making sure you have the right sample database set up.

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:

sqldbcomponents.png

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, TPQConnection 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, an 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. Locations of that db differ depending on operating system, it could be something like:

  • .../examples/empbuild/ subdirectory of your Firebird installation on Linux
  • C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB on a Windows machine
  • Using Firebird embedded, the file should be in your project directory

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. If you use embedded Firebird, the path part should be empty and you should just specify the filename.

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

Change the property OptionsdgDisplayMemoText of the DBGrid to '(True)'. Otherwise you will see (MEMO) in all the data fields.

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. Change its Caption property from "Button1" to Show data".

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

If you set DBConnection.Connected to False, the Transaction and the Query is automatically closed and you can omit closing them manually.

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