Reading data from a MySQL database using Free Pascal

Reading data from a MySQL database with a Lazarus/Free Pascal application.

In my tutorial Connecting to a MySQL database, I described how to connect to a MySQL 5.7 database with a Lazarus/Free Pascal application, using a TMySQL57Connection, or alternatively, a TODBCConection. The little program, shown there, just connected to the "world" database, installed as sample together with MySQL Workbench (cf. my article concerning the installation of MySQL on MS Windows) and read and displayed the number of cities in the city table. In this tutorial, I explain, how to proceed if you do some "serious" reading (selection) from a MySQL database, i.e. how to access the different fields of the different records returned by the MySQL server. I also describe, what you have to do in order to correctly handle UTF-8 data. Click the following link to download the Lazarus/Free Pascal source code of the application used in this tutorial. Note: If you use MySQL 8.0 with a recent version of Lazarus, you may prefer to download the source code, using a TMySQL80Connection.

Create a Lazarus application project with the form shown on the screenshot below and save it, for example, as MySQL3.lpi. The aim of the application is to display, for a user selected country, all cities with a population greater than 1 million.

Connecting to and deconnecting from the database.

The connection to MySQL is done, using a TMySQLConnection object (cf. my tutorial, mentioned above). The code, except for 2 special queries, explained further down in the text, is the same as in MySQL1.lpi, but in this program it is part of the TfMySQL3.FormCreate method, what means that the application tries to connect to the server as soon as it starts up. The disconnection is done when the "Exit" button is pushed.

procedure TfMySQL3.FormCreate(Sender: TObject);
begin
if dbMySQLConnection.Connected then
dbMySQLConnection.Close;
// Set the connection parameters
dbMySQLConnection.HostName := 'localhost';
dbMySQLConnection.UserName := 'nemo';
dbMySQLConnection.Password := 'nemo';
dbMySQLConnection.DatabaseName := 'world';
// Connect to the "world" database
try
dbMySQLConnection.Open;
edMess.Color := clDefault;
edMess.Text := 'Connected to MySQL database "world"';
// Mandatory queries when using UTF-8 data! (at least for MySQL 5.7 and prior)!
// It is possible that for MySQL 8.0 (and higher) these queries must NOT be executed anymore
// If you get "garbage" with non-ANSI characters, try commenting the following 2 lines
dbSQLQuery.SQL.Text := 'SET CHARACTER SET "utf8"'; dbSQLQuery.ExecSQL;
dbSQLQuery.SQL.Text := 'SET NAMES "utf8"'; dbSQLQuery.ExecSQL;
except
on E: ESQLDatabaseError do begin
edMess.Color := clRed;
edMess.Text := 'Connection to MySQL database "world" FAILED!';
end;
end;
end;

procedure TfMySQL3.btExitClick(Sender: TObject);
begin
if dbMySQLConnection.Connected then
dbMySQLConnection.Close;
Close;
end;

Reading (selecting) MySQL data.

All database querying is done, using properties and methods of the TSQLQuery object. In particular, dbSQLQuery.SQL.Text is used to define the SQL SELECT statement. The selection itself is very similar to an ordinary file read:

Action to perform	Procedure (file read)	Method (database select)
Opening the file/starting the query	RESET filehandle	dbSQLQuery.Open
Iteration until all data processed	while not EOF(filehandle) do	while not dbSQLQuery.EOF do
Reading the actual file/database record	Read(record-variable); pointer automatically set to next record	dbSQLQuery.First resp. dbSQLQuery.Next
Accessing the data (record fields)	fields of the record-variable	dbSQLQuery.Fields[index] or dbSQLQuery.FieldByName(field-name)
Closing the file/ending the query	CLOSE(filehandle)	dbSQLQuery.Close

The major difference between file reading and database selection is, that with the first one, you use the same procedure for all records ("Read", placed within the "while" loop), whereas with the second one, you use two separate methods: one to select the first record and one to select all subsequent records (the "First" method being executed before entering the "while" loop and the "Next" method being within the "while" loop).

To access the record fields, the MySQL1 application uses dbSQLQuery.Fields[index], where the index, starting from 0, points to the subsequent fields of the record, in the order as they have been specified in the SELECT statement. The dbSQLQuery.FieldByName(field-name) method, used in MySQL3, allows to access the record's fields, independently of their "position", by specifying the field's name. This name corresponds to the name of the column name in the returned dataset, that is normally the same as the column name in the database table. If you are familiar with SQL, you know that the column names in the returned dataset may be freely chosen by the programmer, using AS; if you do so, it's this alias name, that has to be used with the dbSQLQuery.FieldByName method (that's logical, isn't it?). For both methods, you have to use a qualifier such as AsInteger, AsString with their properties, when assigning these to Free Pascal variables.

The selection of the cities with a population greater than 1 million is done, when the user has filled in a country code or name and pushed the "Query" button. Here the code of the corresponding TfMySQL3.btQueryClick method.

procedure TfMySQL3.btQueryClick(Sender: TObject);
var
Population, Count: Integer;
Query, CountryCode, CountryName: string;
begin
if dbMySQLConnection.Connected then begin
if edCountry.Text <> '' then begin
sgCities.Clean(0, 1, 2, 40, []);
// Create query, depending on country code or name entered
Query := 'SELECT country.Name AS CountryName, city.Name AS CityName, District, city.Population AS CityPopulation FROM country, city ';
Query += 'WHERE Code = CountryCode ';
if Length(edCountry.Text) = 3 then begin
// Considering 3 letters as country code
CountryCode := edCountry.Text;
Query += 'AND CountryCode = "' + CountryCode + '" ';
end
else begin
// Consider other entries as country name
CountryName := edCountry.Text;
Query += 'AND country.Name = "' + CountryName + '" ';
end;
Query += 'AND city.Population > 1000000 ';
Query += 'ORDER BY city.Population DESC';
// Query the database
dbSQLQuery.SQL.Text := Query; Count := 0;
try
dbSQLQuery.Open;
dbSQLQuery.First;
while not dbSQLQuery.EOF do begin
Inc(Count);
CountryName := dbSQLQuery.FieldByName('CountryName').AsString;
sgCities.Cells[0, Count] := dbSQLQuery.FieldByName('CityName').AsString;
sgCities.Cells[1, Count] := dbSQLQuery.FieldByName('District').AsString;
Population := dbSQLQuery.FieldByName('CityPopulation').AsInteger;
sgCities.Cells[2, Count] := FloatToStrF(Population, ffNumber, 0, 0);
if Population < 10000000 then
sgCities.Cells[2, Count] := ' ' + sgCities.Cells[2, Count];
dbSQLQuery.Next;
end;
dbSQLQuery.Close;
// Include the contry name in the string-grid label
laCountry.Caption := 'Cities';
if CountryName <> '' then
laCountry.Caption := laCountry.Caption + ' in ' + CountryName;
laCountry.Caption := laCountry.Caption + ' with more than 1 million inhabitants:';
// Display count of cities
if Count = 0 then
edMess.Text := 'No cities found in database!'
else
edMess.Text := 'Number of cities in database = ' + IntToStr(Count);
edMess.Color := clDefault;
except
on E: ESQLDatabaseError do begin
edMess.Text := E.Message;
edMess.Color := clRed;
end;
end;
end;
end;
end;

Handling UTF-8 data read from MySQL.

Please, note that this paragraph may be outdated! It is possible that with MySQL 8.0 (and higher), the queries described here must not be used anymore (handling of non ANSI-characters working correctly by default).

If your data contains non-ANSI characters (characters with accents or umlauts, or characters of a non Latin alphabet), you should use a UTF-8 collation sequence when creating your database and tables. Note, that most of these are case-independent, thus querying for "usa" or "mexico" returns the same dataset than querying for "USA" resp. "Mexico". Free Pascal normally handles UTF-8 without that you have whatever to do, including correct reading of UTF-8 coded (text) files. To avoid possible problems, always use the "string" type (and not the "UFF8String" type). When reading data from a database, however, things are different: you have to tell the database server to send all data with UTF-8 encoding. This is done by 2 special queries (the second one telling the server, that table and other names should be considered being UTF8):
SET CHARACTER SET 'utf8'
SET NAMES 'utf8'
To commit these queries assign them to dbSQLQuery.SQL.Text and then use the dbSQLQuery.ExecSQL method.

The screenshots below show the output of the MySQL3 application: on the left without the 2 "UTF-8 queries", on the right as it should be.

Lazarus database application: MySQL query without UTF-8 support

Lazarus database application: MySQL query with UTF-8 support

A little excursion concerning database column names.

I think that with the explanations given in the text, you shouldn't have problems to understand the program code and you should be able to write more complex applications, reading data from a MySQL (or other) database. However, you may have wondered about the SQL statements themselves. Why these names containing a dot? Why the usage of aliases (AS)? Necessities in relationship with the "world" database tables' field names!

A given column "belongs" to a given table, and not directly to a given database. This means, that, if column names of a table must have unique names, this is not true for columns (related or not) of two different tables. Thus, in the "world" database, there are columns named "Name" and "Population" as well in the "country" as in the "city" table. This naming liberty may seem a nice thing, but it easily risks to cause problems. In fact, if you make a selection, involving two tables and reference a column name, existing in both of them, how should MySQL know, which data you mean? It can't know it, and such a reference results in an "ambiguous column" error message, as in the screenshot below, where the MySQL3 application was run with the code Query += 'AND Population > 1000000 '; instead of Query += 'AND city.Population > 1000000 ';. To avoid such ambiguities, you have to explicitly specify the table name, e.g. "country.Name" for the "Name" column in the "country" table. So far, so good. MySQL now knows which data you want to select, but are you sure, that your variables will contain what you expected? The dbSQLQuery.FieldByName method uses the column names of the returned data set. Such names are always simple names, i.e. not including a table name. And if we selected two columns named "Name", there would normally be 2 field names named "Name". That is not a problem for MySQL, dbSQLQuery.FieldByName('Name') would simply return the value of the first field named "Name" it finds, when looking up the record fields in the order of how the table columns were specified in the SELECT statement. What in our case would have as result to get all cities of Mexico called Mexico! Changing the order of country.Name and city.Name in the SELECT statement would of course solve this problem. But, such ambiguities are just good to mess up things – thus, simple and efficient work-around: the usage of aliases for the column names gives us a dataset whose records have unique field names, as it should be.

Personally, I'm a bit surprised, that a sample database, officially delivered with MySQL, contains non-unique column names and I think that the good practice is to make sure, that all names in a given database are unique. The easiest way to do this, is to use a prefix for each table, e.g. "co_" for the "country" table and "ci_" for the "city" table; with column names such "co_Name" or "ci_Population" you would immediately know what's the data in the column about and all possible ambiguity problems would be avoided...

Lazarus database application: MySQL query with ambiguous column name

Note: The tutorial examples have been build and tested on Windows 10, using Lazarus 1.8.4 64bit (with FPC 3.0.4); the database server used has been MySQL 5.7.22 64bit. If you experience problems with newer versions, the actual version (January 2021) considerations at the tutorial start page may be helpful.

Using MySQL databases with Lazarus/Free Pascal | 1. Connecting to a MySQL database | 3. Writing data to a MySQL database

If you find this text helpful, please, support me and this website by signing my guestbook.

Computing: Free Pascal Programming

Reading data from a MySQL database with a Lazarus/Free Pascal application.