Using a MySQL database in a Lazarus program

Version 1





Top

Introduction

A Lazarus program can modify, interrogate and view the contents of an existing MySQL database.

This tutorial is a step-by-step guide. However, it is worth looking at the layout of the Form and the source code now. They are at the end of this document.

I am working with Lazarus 0.9.28.2 beta in MS Windows.

You will connect using components from the SQLdb component palette. This comes with Lazarus. This palette has a specific MySQL component which we will use.





Top

Preparations

Create a folder called MySQLConnect. To save a Lazarus application into the folder

Lazarus is ready.

If you need to get MySQL then see Appendix A.

You need to create a MySQL database beforehand. My database is called A.db. I created it using the XAMPP lite portable webserver. See Appendix B.

You need to start the MySQL server. This is easy with XAMPP lite. See Appendix C

You need the file libMySQL.dll for MySQL 5.0 in the same folder as your Lazarus project. See Appendix D.

Everything is now in place.


Summary
You need

Top

Putting the Lazarus components on the form

In Lazarus, you need to find the SQLdb component palette at the end of the row of tabs holding components. On the right of SQLdb component palette you will find a component called TMySQL50Connection.

The TSQLTransaction component is at the other end of the SQLdb component palette.

Drop a TSQLTransaction component underneath the TMySQL50Connection component on the form using the same method as before. This component passes Database transactions to the Connection component.

The TSQLQuery component is in the SQLdb component palette near the TSQLTransaction component.

Drop a TSQLQuery underneath the TSQLTransaction component. The TSQLQuery component stores the results from any query that you have run on the database. It also passes any changes to its contents to the TMySQL50Connection component.

The TDatasource component is in the Data MySQL component palette.

Drop a TDatasource component on the form underneath the TSQLQuery1 component. This takes data from the TSQLQuery component and feeds it to the database-aware components that you will shortly put on your form.

The TDBGrid component is in the DB controls component palette.

Drop a DBGrid component onto your form under the TDatasource component. This is a visible component so you must drag it to a reasonable size on your form. It is a database-aware component and it displays whatever it is fed from the datasource.

The TButton component is in the Standard component palette.

Summary
Components on the form

Top

Connecting

You now set up the connection between the program and the database. When you click the button ‘Connect’ then a connection to a.db, (the MySQL database) is made and the data from your database will appear in the DBgrid.

Here is how:

Double-click the button. The source editor appears. In the Button1click procedure add the following code:

procedure TForm1.Button1Click(Sender: TObject);
begin

MySQL50Connection1.DatabaseName:='LazConnect';
MySQL50Connection1.UserName:='Admin';
MySQL50Connection1.Transaction:=SQLTransaction1;

SQLTransaction1.Database:=MySQL50Connection1;

SQLQuery1.Database:=MySQL50Connection1;
SQLQuery1.Transaction:=SQLTransaction1;
SQLQuery1.UsePrimaryKeyAsKey:=False;
SQLQuery1.SQL.Text:='SELECT * FROM Student';

Datasource1.dataset:=SQLQuery1;
DBGrid1.DataSource:=DataSource1;

end;

This code

You can see that it prepares a route from the database to the DBGrid. It also prepares (but does not execute) a query to get all the data from the database using

SELECT * FROM Student There should be no errors. You should see the same as before.

If the program doesn’t run or there is an error when you click the button then you must stop and check everything. It is pointless to proceed since this problem is going to block you every time.

Add a label to Form1. The TLabel component is in the 'Standard' component palette. This label will change to show you are connected when you connect.

To do this, add the 5 lines that are highlighted just before the end;

Datasource1.dataset:=SQLQuery1;
DBGrid1.DataSource:=DataSource1;

MySQL50Connection1.Open;
If MySQL50Connection1.Connected then
begin

Label1.caption:='connected -great';

end;

end;

If the label doesn’t change then you must stop and check everything. It is pointless to proceed since there is no connection.

Next add the one line of code immediately after the code you just wrote and just before the end of the procedure:

SQLQuery1.open;

This single line executes the query that was prepared earlier. The table of results is stored in SQLQuery1

‘Select * from Student’

is passed to the TMySQL50Connection and then to the database and the result is passed back through the chain to the TSQLQuery, the datasource and into the DBGrid.

The label should say 'Connected to MySQL database - great' The DBGrid should fill up with the data from your database.

If it does then that is great. You can go on. If it doesn’t then you must stop and check everything. It is pointless to proceed since there is no connection.

Summary
Components Procedures

Top

SELECTing data

You can interrogate the data in the database using SELECT.

Add a new button (Button2) and in the Object Inspector change the button’s caption to ‘SELECT’. Also add an editbox (Edit2) onto the form so that the user can enter their SELECT command. It needs to be fairly wide.

Add the procedure below

procedure TForm1.Button2Click(Sender: TObject);
begin

SQLQuery1.Close;
SQLQuery1.SQL.text:=edit1.text;
SQLQuery1.Open;

end;

Closing SQLQuery1 clears the contents in preparartion for a new query. Then the new query is loaded. Then the new query is executed and the table of results made available in SQLQuery1.

The list of first names only should appear in the DBGrid.
Summary
Components Procedures

Top

INSERTing data directly

There are two ways of inserting data into the database. Both work and both are good. The first method uses a transaction

Add a new button (Button3) and in the Object Inspector change its caption to ‘Insert’. Also add an editbox (Edit2) so that the user can enter their INSERT command. It needs to be fairly wide.

Double-click on the button and add this source code:

procedure TForm1.Button3Click(Sender: TObject);
begin

SQLTransaction1.commit;
SQLTransaction1.StartTransaction;
MySQL50Connection.ExecuteDirect(edit2.text);
SQLTransaction1.commit;

end;

The first line completes any transactions that may be outstanding from procedures that we have yet to write. The next three lines simply perform the transaction. This procedure changes the database directly according to the INSERT command in the edit box.

So if you write

INSERT INTO Student VALUES (23, ‘Fred’,’Jones’);

then this will enter Fred Jones as student 23 into the database.

Summary
Components Procedures

Top

INSERTing data with TSQLQuery

This is the second way. This method uses SQLQuery and is very similar to SELECT

Double-click on the button3 and add this source code:

procedure TForm1.Button3Click(Sender: TObject);
begin

//Not now needed - we will use SQLQuery1 instead //SQLTransaction1.commit;
//SQLTransaction1.StartTransaction;
//SQLite3Connection.ExecuteDirect(edit2.text);
//SQLTransaction1.commit;

SQLQuery1.Close;
SQLQuery1.SQL.text:=edit1.text;
SQLQuery1.ExecSQL;

end;

Using // comments out a line in Lazarus. The technique above is a neat way of making code invisible to the compiler but retaining it for reference or later use.

This is very similar to the SELECT code. The rule is -

Now if you write

INSERT INTO Student VALUES (24, ‘Stefani’,’Germanotta’);

then this will enter 'Stefani Germanotta' as student 24 in the database.

Summary
Components Procedures

Top

Viewing the database in a memo box

You can see the whole database in a memobox. The memobox can’t be edited and it doesn’t change with each new SELECT because it is not a database-aware component. It’s still nice to see the database contents initially (and it’s easy!).

Add a new button (Button4) and in the Object Inspector change its caption to ‘Show Whole Database’. Add a memo box from the Standard component palette.

procedure TForm1.Button4Click(Sender: TObject);
begin

SQLQuery1.Close
SQLQuery1.SQL.Text:='Select * FROM Student';
SQLQuery1.Open;
while not SQLQuery1.Eof do
begin

memo1.lines.add(
' ID: ' + SQLQuery1.FieldByName('StudentID').AsString +
' First name: ' + SQLQuery1.FieldByName('First').AsString +
' Second name: ' + SQLQuery1.FieldByName('Second').AsString) ;
SQLQuery1.Next;

end;

end;

The first 3 lines execute the query

SELECT * FROM Student;

so the whole table is copied to SQLQuery1 automatically. It then appears in the DBGrid because DBGrid is database-aware. This is just as before.

The while block analyses the database table row-by-row. For each row it dumps a string into the memo box. The string is created by concatenating 4 smaller strings, two of which are simple labels and two of which are the entries in that row of the database.

The database should appear in the DBGrid and the memo box.

Summary
Components Procedures

Top

DELETE data

The method for deleting data is easy. Remember that the code for INSERT isn't just for INSERT but for any SQL command that does not return a result from the database. DELETE is just such a command. Therefore the code for DELETE is identical to the code for INSERT. Therefore you can enter a DELETE command in the INSERT edit box and it will work Remember to then run 'SELECT * FROM Student' in order to see the effect.

The SQL command

DELETE FROM Student WHERE StudentID=23

will delete student 23 Fred Jones from the database

.

To test this out write

To get a 'good' DELETE button you would first have the code for INSERT and then have the code for opening the query 'SELECT * FROM Student' after it. Then the result will then appear instantaneous.



Top

Appendix A

Getting MySQL with XAMPP lite

The best way to get MySQL is to have it as part of the portable webserver XAMPP lite. This is an easy download.



Top

Appendix B

Creating a simple MySQL database called A.db

You need to create an MySQL database. My database is called A.db, and it has one table called Student, with three columns - StudentID(number), First(text) and Second(text). There are three rows in the table.

To produce the database

-- SQL for the Student table

CREATE TABLE Student(
StudentID INTEGER PRIMARY KEY NOT NULL,
First VARCHAR(20),
Second VARHAR(20));

-- Some Student Values

INSERT INTO Student VALUES (1, ‘David’, ‘Beckham’);
INSERT INTO Student VALUES (2, ‘William’, ‘Shakespeare’);
INSERT INTO Student VALUES (3, ‘Reginald’, ‘Dwight’);

-- End of SQL

Then -

The Student table should have been imported along with the three records. Check they are there by browsing them.



Top

Appendix C

Getting libMySQL.dll

The libMySQL.dll file MUST be for MySQL5.0 NOT MySQL5.1.

Unfortunately, XAMPPlite now comes with MySQL5.1.
Therefore you can not copy the file of the same name from XAMPPlite. Nonetheless, the 5.0 file can be obtained from the Internet but it is not easy to find.

You can try a search. MySQL is open-source and so there should not be a problem with copyright.

This is how I did it. Visit the MySQL archive, download the whole of MYSQL 5.0 and install it on your C drive. Then look in the folder C:/Program Files/MySQL/MySQL 5.0/bin you will find libMySQL.dll. Copy and paste it in to your folder.



Top

Appendix D

Starting MySQL in XAMPP lite

You need to start the MySQL folder before you can connect

Find the XAMPPlite folder.

They should both then say ‘running’. MySQL has started. You can make a connection.

The link between Lazarus and A is made.



Top

Screenshot of the form

Screenshot of Form

Top

Source Code

unit Unit1;

{$mode objfpc}{$H+}

interface

uses

Classes, SysUtils, odbcconn, sqldb, db, FileUtil, LResources, Forms, Controls,
Graphics, Dialogs, DBGrids, StdCtrls;

type

{ TForm1 }
TForm1 = class(TForm)

Button1: TButton;
Button2: TButton;
Button3: TButton;
Button4: TButton;
Datasource1: TDatasource;
DBGrid1: TDBGrid;
Edit1: TEdit;
Edit2: TEdit;
Label1: TLabel;
Memo1: TMemo;
MySQL50Connection1: TMySQL50Connection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);

private
{ private declarations }
public
{ public declarations }
end;

var

Form1: TForm1;

implementation

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin

MySQL50Connection1.DatabaseName:='LazConnect';
MySQL50Connection1.UserName:='Admin';
MySQL50Connection1.Transaction:=SQLTransaction1;

SQLTransaction1.Database:=MySQL50Connection1;

SQLQuery1.Database:=MySQL50Connection1;
SQLQuery1.Transaction:=SQLTransaction1;
SQLQuery1.SQL.Text:='SELECT * FROM Student';

Datasource1.dataset:=SQLQuery1;
DBGrid1.DataSource:=DataSource1;

MySQL50Connection1.Open;
If MySQL50Connection1.Connected then
begin

Label1.caption:='connected -great';

end;
SQLQuery1.open;

end;

procedure TForm1.Button2Click(Sender: TObject);
begin

SQLQuery1.Close;
SQLQuery1.SQL.text:=edit1.text;
SQLQuery1.Open;

end;

procedure TForm1.Button3Click(Sender: TObject);
begin

//Not now needed - we will use SQLQuery1 instead //SQLTransaction1.commit;
//SQLTransaction1.StartTransaction;
//MySQL50Connection1.ExecuteDirect(edit2.text);
//SQLTransaction1.commit;

SQLQuery1.Close;
SQLQuery1.SQL.text:=edit1.text;
SQLQuery1.ExecSQL;

end;

procedure TForm1.Button4Click(Sender: TObject);
begin

SQLQuery1.Close
SQLQuery1.SQL.Text:='Select * FROM Student';
SQLQuery1.Open;
while not SQLQuery1.Eof do
begin

memo1.lines.add(
' ID: ' + SQLQuery1.FieldByName('StudentID').AsString +
' First name: ' + SQLQuery1.FieldByName('First').AsString +
' Second name: ' + SQLQuery1.FieldByName('Second').AsString) ;
SQLQuery1.Next;

end;

end;

initialization
{$I unit1.lrs}

end.

Valid XHTML 1.0 Strict