Computing: Free Pascal Programming

Programming Home   Home   Contact

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 database with a Lazarus/Free Pascal application, using a TMySQLConnection, or alternatively, a TODBCConection. The little program, shown there, just connected to the "world" database, installed as sample together with MySQL Workbench (cf. my artcile concerning MySQL) 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.
Create a Lazarus application project with the form shown on the screeenshot 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.
Simple Lazarus project
Connecting to and deconnecting from the database.
The connection to MySQL is done, using a TMySQLConnection object (cf. my tutorial, mentionned 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 deconnection 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!
        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 the two procedures is that with files, you read a record and the record pointer is automatically advanced to the next record, whereas with databases, you position the pointer manually and then use a separate method to read the record.
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 thay 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 (what'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.
If your data contains non-ASCII characters (charcters 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 you have whatever to do; including correct reading of UTF-8 coded (text) files. To avoid possible problems, use the "string" type (and not the "UFF8String" type). This is not true, when reading data from a database. In this case, you have to tell the database server to send all data with UTF-8 encoding. This is done by 2 special queries:
    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.
MySQL query without UTF-8 support
 
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 "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 MySQL3 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 as we select two columns named "Name", there would normally be 2 fieldnames named "Name". That is not a problem as such, 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" 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...
MySQL query with ambiguous column name