Computing: Free Pascal Programming

Writing data to a MySQL database from a Lazarus/Free Pascal application.


In my tutorial Connecting to a MySQL database, I describe how to connect to a MySQL 5.7 database with a Lazarus/Free Pascal application, using a TMySQL57Connection, or alternatively, a TODBCConection. The tutorial Reading data from a MySQL database explains, how to proceed to read (select) data from a MySQL database from within a Lazarus/Free Pascal application. In this tutorial, I try to show the basics, concerning inserting rows into the database, deleting rows from the database and updating actual database content. Use the following link, if you want to download the source code of the example program used with the 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.
As in the tutorials before, I use the "world" database, installed as sample together with MySQL Workbench (cf. my article concerning the installation of MySQL on MS Windows). As tables in real life databases normally not only have primary keys, but also foreign keys (in order to guarantee data integrity), I modified the structure of the "world" database by adding a foreign key constraint to the "city" table. This may be done by executing the following SQL statement (here in phpMyAdmin):
ALTER TABLE city
    ADD CONSTRAINT city_ibfk FOREIGN KEY (CountryCode)
        REFERENCES country (Code)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT;
This will make sure, that it's not possible to insert a city for a country, that doesn't exist in the "country" table (as well as not deleting a country, without first deleting all its cities in the "city" table). Note, by the way, that the database allows to enter duplicate city names for a same country, what corresponds to reality and may even apply within the same district.
Adding a foreign key constraint to the 'world.city' table
Sample database application.
The application, build in this tutorial, should be able to perform the following database queries:
  • Displaying information concerning a given city (SELECT).
  • Removing a given city from the database (DELETE).
  • Changing the population of a given city (UPDATE).
  • Adding a new city to the database (INSERT).
Create a Lazarus application project with the form shown on the screenshot below and save it, for example, as MySQL4.lpi. The database query to be performed is selected by checking the corresponding radio button; all queries are executed by clicking the "Query" push button. "Clear" resets all edit fields to blank.
Creating sample Lazarus database project
Connecting to and deconnecting from the database.
The code for connecting to MySQL (part of the TfMySQL4.FormCreate method) and to disconnect from MySQL (part of the TfMySQL4.btExitClick method) is exactly the same as for the application, used with the Reading data from a MySQL database tutorial. Please, note, that with MySQL 8.0 (and higher), it may be that the two UTF-8 related queries must not be used anymore!
Executing the database query, depending on user selection.
All queries are executed, when the user pushes the "Query" button; what action is performed depending on the radio button checked. The TfMySQL4.btQueryClick method, containing the corresponding code, is rather long, as I perform several validity checks on the data entered by the user. Examples: the user has always to enter a country and a city; to update the city population, the new value of the population has to be given. I'll show here only a part of the code; for details, please download the sample application source. The application uses two error related variables, "MySQLError" (containing the error message returned by the MySQL server) and "DataError" (user data entry error, that is such, that it is not possible to correctly execute the query); these error messages will be displayed in the message edit field, similar as in my other MySQL sample programs. If the data, entered by the user, is invalid, but even so allows to execute the query, a warning message is displayed and data is adjusted by the program. For example, deleting a city, with both its ID and name given, makes no sense and the application will use the ID, ignoring the name. Another example is the insertion of a new city with a city ID given (cf. below).
If the data entered is valid, the method calls one of the procedures, performing the select, delete, update or insert. If there is an error message issued by the MySQL server, it is displayed, otherwise, the application checks, if the operation was successful or did fail. A case of failure is for example, when the user tries to update the population of a city, that does not exist. If all is ok, the city information on the form is updated. For a detailed description of the code, checking for the success/failure of the query, please, jump to Checking, if queries were executed correctly.
Reading the city information from the database.
Task to do in the MySQLSelect procedure: For a given country and city, find the district and the city population. The SQL statement created depends on the fields to be used for the selection (country code or name, city code or name). The MySQL related code is very similar to the one of the MySQL3.lpi project (for details, in particular why the necessity to use the table name with several fields in the SQL statement, and by this, using aliases, cf. Reading data from a MySQL database). In fact, it's a simplification of this code, as here, the procedure only reads one record from the database. Done so for simplicity reasons, this isn't, of course, not correct! There may be countries with several cities of the same name and with this simplified code, some of them will never been read. For example, if you search for Arlington in USA, the city displayed will be Arlington in Texas; Arlington in Virginia will never be shown (unless you use the city ID, of course). To notify the user about this possible issue, a warning message is displayed, if she does a select with the city given with its name. Beside the city information, the procedure also returns the MySQL error message (there will for example be a MySQL error, if the user enters a non-numeric value as city ID) and the number of rows returned; in fact this last one will be either 1 or 0, 0 meaning that the user tried to get information concerning a city, that does not exist in the database.
Removing a city from the database.
Task to do in the MySQLDelete procedure: Remove a given city of a given country from the database. The sample application allows to give the city either by its ID or its name (showing, that the fields specified in the WHERE clause of a DELETE statement must not necessarily be parts of a primary key). As, for a given country, there may be several cities with the same name, this wouldn't be a good idea in a real world application (even though the confirmation dialog should prevent the possible issue)! Issue, that consists in the fact, that with the possibility of duplicate city names for the given country, all these cities would be removed!
There are 3 steps to do, for executing a SQL DELETE query:
  1. Assigning the SQL text (standard SQL DELETE statement) to the SQL.Text property of the TSQLQuery object.
  2. Calling the ExecSQL method of the TSQLQuery object (this executes the DELETE query).
  3. Calling the Commit method of the TSQLTransaction object (this commits the changes to the database).
Note, that for a deletion (as for an update or an insertion, and in contrary to a selection), there is no call to the TSQLQuery "Open" and "Close" methods; these are only used, when data records (table rows or other data values) are returned from the database server. Important to remember: The changes in the (physical) database are only made after the transaction has been committed!. The ExecSQL method does execute the query, but only to a local (virtual) dataset. Thus if the query is successful, the city will be removed from this dataset (or, if there was a MySQL error, the "ESQLDatabaseError" error condition will be thrown). Even, if you do not do the commit, selecting this city will result in a "city not found" condition, but if you check the table content (for example in phpMyAdmin), the city will still be there! Until the transaction is committed!
{ SQL DELETE: Remove given city from the "world.city" database }
procedure MySQLDelete(Transaction: TSQLTransaction; Query: TSQLQuery; CountryCode, CityID, CityName: string; out MySQLError: string);
var
  Sql: string;
begin
  Sql := 'DELETE FROM city ';
  Sql += 'WHERE CountryCode = "' + CountryCode + '" ';
  if CityID <> '' then
    Sql += 'AND ID = ' + CityID + ' '
  else
    Sql += 'AND city.Name = "' + CityName + '"';
  try
    Query.SQL.Text := Sql;
    Query.ExecSQL;
    Transaction.Commit;
  except
    on E: ESQLDatabaseError do
      MySQLError := E.Message;
  end;
end;
The screenshots below show the trial to remove a city, that does not exist, the country code for Germany being DEU and not GER (left), and a city deletion, that was successful (right). For further details, please, have a look at Checking, if queries were executed correctly.
Lazarus database application: Deleting a city from the database - City not found
Lazarus database application: Deleting a city from the database - Query successful
Changing the population of a city.
Task to do in the MySQLUpdate procedure: Change the population of a given city of a given country to the (new) value entered by the user. As for the deletion, the application allows to give the city either by its ID or its name (what could create lots of mess in a real world situation). The steps to do, for executing a SQL UPDATE query are similar to those for a deletion:
  1. Assigning the SQL text (standard SQL UPDATE statement) to the SQL.Text property of the TSQLQuery object.
  2. Calling the ExecSQL method of the TSQLQuery object (this executes the UPDATE query).
  3. Calling the Commit method of the TSQLTransaction object (this commits the changes to the database).
And remember: The population update in the database are only made after the transaction has been committed!!
{ SQL UPDATE: Change population of given city in the "world.city" database }
procedure MySQLUpdate(Transaction: TSQLTransaction; Query: TSQLQuery; CountryCode, CityID, CityName: string; NewPopulation: Integer; out MySQLError: string);
var
  Sql: string;
begin
  Sql := 'UPDATE city ';
  Sql += 'SET Population = ' + IntToStr(NewPopulation) + ' ';
  Sql += 'WHERE CountryCode = "' + CountryCode + '" ';
  if CityID <> '' then
    Sql += 'AND ID = ' + CityID + ' '
  else
    Sql += 'AND city.Name = "' + CityName + '"';
  try
    Query.SQL.Text := Sql;
    Query.ExecSQL;
    Transaction.Commit;
  except
    on E: ESQLDatabaseError do
      MySQLError := E.Message;
  end;
end;
The screenshots below show the pop-up of the warning, when tying to update the population of a city, given by name (left), and the failure message, because the city name is misspelled (right). For further details, please, have a look at Checking, if queries were executed correctly.
Lazarus database application: Updating a city's population - Asking for user confirmation
Lazarus database application: Updating a city's population - City not found
Adding a new city to the datbase.
MySQL (and I suppose other relational database systems, too) allows to create tables without primary keys. What is somewhat nonsense and should never be done. Primary keys are the guarantee that each record in the table has a unique identifier and accessing the record using its primary key will ensure to always get the record you effectively want. The tables in the "world" database do have primary keys: "Code" for the "country" table and "ID" for the "city" table. Related tables should also have foreign keys, to guarantee data integrity; that's why I added the foreign key constraint for the "CountryCode" field in the "city" table. As we have seen above, it is not mandatory to use primary keys in the WHERE clause of deletions and updates. However doing without them, may be risky, as there is the possibility, that there is more than one table row affected, in our case, that several cities are deleted or their population changed. For database insertions, the situation is different: If the table has a PK, the value of the primary key field(s) is mandatory for adding new records to a table. Thus, normally, you have to specify the PK values in your INSERT statement. Normally, but there is one case, where it's just the contrary and you must not specify the PK field: If the PK is an auto-increment field, it's the database server, that chooses and adds the primary key. This actually is the case in the "world.city" table: ID is an integer value, automatically incremented by MySQL, each time a new city is inserted. That's what I meant, when I said above, that the application issues a warning, if the user specifies a city ID when adding a new city: Not the value, entered by the user, but the one chosen by MySQL, will be used as ID for the new city.
Task to do in the MySQLInsert procedure: Add a new city of a given (existing!) country to the database. The steps to do, for executing a SQL INSERT query are similar to those for a deletion or an update:
  1. Assigning the SQL text (standard SQL INSERT or REPLACE statement) to the SQL.Text property of the TSQLQuery object.
  2. Calling the ExecSQL method of the TSQLQuery object (this executes the INSERT/REPLACE query).
  3. Calling the Commit method of the TSQLTransaction object (this commits the changes to the database).
And remember: The new city will be added to the (physical) database only after the transaction has been committed!!
{ SQL INSERT: Add new city to the "world.city" database }
procedure MySQLInsert(Transaction: TSQLTransaction; Query: TSQLQuery; CountryCode, CityName, District: string; Population: Integer; out MySQLError: string);
var
  Sql: string;
begin
  Sql := 'INSERT INTO city (Name, CountryCode, District, Population) ';
  Sql += 'VALUES ("' + CityName + '", "' + CountryCode + '", "' + District + '", ' + IntToStr(Population) + ')';
  try
    Query.SQL.Text := Sql;
    Query.ExecSQL;
    Transaction.Commit;
  except
    on E: ESQLDatabaseError do
      MySQLError := E.Message;
  end;
end;
The screenshots below show the trial to add a city for a country, that does not exist in the database, showing the MySQL error related to the foreign key constraint (left) and a selection, after the new city has been inserted, showing a city ID of 4085, automatically added by MySQL for this auto-increment primary key (right).
Lazarus database application: Adding a new city to the database - Foreign key constraint failure
Lazarus database application: Displaying new city information - City ID automatically added by MySQL
Checking, if queries were executed correctly.
The big plus of working with the SQL.Text and ExecSQL properties of TSQLQuery objects is that they make it very easy to insert, update or delete database records. In fact, nothing else but writing a standard SQL statement, assigning it to SQL.Text and executing the query. The disadvantage of this simple way to proceed is the fact, that you haven't any indication, if the query succeeded or failed (at least I don't see, how you could check this), if the modifications to the database were actually done or not. The only thing, you can catch, are MySQL errors; if there aren't any, the query was executed, but this does not mean, that what you wanted to do, was effectively done.
You can write your own code for checking if the query succeeded or failed. In the following paragraphs, I show, how and what I tested, after executing an insertion, deletion or update in the "world.city" table (for selections, just count the number of rows returned; if it is zero, the city you searched for, does not exist in the database).
Checking, if DELETE was executed correctly.
The simplest way, that I found to do this, is to read the count of records in the city table, before and after the deletion. If the new number of rows is less (1 less for deletions using the primary key city.ID), the deletion was effectively done, otherwise it was a failure and this failure must (at least I think?) be because the city, you specified, does not exist. The row-count of the "city" table may be read by executing a SELECT with the following SQL statement:
SELECT COUNT(*) AS CityCount FROM city
Here the code, that I use in the MySQL4.lpi sample project to check for the success of a deletion (CountBefore and CountAfter being the return values of the row count queries before resp. after doing the deletion):
// DELETE query: Actual number of rows must be less than before the insertion
if rbDelete.Checked then begin
  if CountAfter < CountBefore then begin
    edMess.Color := clLime;
    edMess.Text := 'City successfully deleted!';
  end
  else begin
    // If no deletion was done, must be because the city to be deleted was not found...
    edMess.Color := clYellow;
    edMess.Text := 'Deletion of city failed (because city did not exist in database)!';
  end;
end
Checking, if INSERT was executed correctly.
I checked the success of the insertion of a new city the same way as for deletions. If the number of rows after the insertion is greater (1 greater here, as there is always one single record inserted), the insertion was effectively done. I did this test (displaying an "unknown database error" message), even though I think, that, in fact, there isn't any need to check for the success of an insertion. Because, either it works, or, it doesn't and in this later case, it's because of a database error and the ESQLDatabaseError will be thrown.
Checking, if UPDATE was executed correctly.
With updates, the number of rows in the "city" table remains the same, thus, the way to proceed, used for deletions and insertions, is not applicable here. The simplest way, that I found, to check the success of the population update in the "city" table, is a re-selection of the city, that had to be updated. If the SELECT query doesn't return any rows, this city does not exist in the table, and, the update must have failed, because the city to update hasn't been found. I added a further test, checking if the population in the re-selected record is equal to the new population value entered by the user and used in the UPDATE query. As with insertions, this test isn't required, I think: if the city actually exists and if there wasn't any MySQL error, the update was done and the population must have been set to the new value. Here the code, that I use in the MySQL4.lpi sample project to check for the success of an update:
// UPDATE query: Number of rows does not change. Perform a SELECT on the city, that should have been updated, to check operation success
if rbUpdate.Checked then begin
  MySQLSelect(dbSQLQuery, CountryCode, CountryName, CityCode, CityName, District, Population, Count, MySQLError);
  if Count = 0 then begin
    // If there isn't any database row returned, it's that this city does not exist
    // Thus, the city couldn't be found during the update and the update was a failure
    edMess.Color := clYellow;
    edMess.Text := 'Update of city population failed (because city did not exist in database)!';
  end
  else if Population = StrToInt(edPopulationNew.Text) then begin
    // If the actual city population was set to the new value (the one entered by the user), the update succeeded
    edMess.Color := clLime;
    edMess.Text := 'City population successfully updated!';
    edPopulation.Text := FloatToStrF(Population, ffNumber, 0, 0);
  end
  else begin
    // Normally, this will never be the case. Either the update was done, or there was something, resulting in a MySQL error...
    edMess.Color := clRed;
    edMess.Text := 'Unknown database error! Update of city population failed!';
  end;
end;
Other ways to code the modification of database content.
As I said at the beginning of this tutorial, I intend to show here the basics, concerning the modification of database content from within a Lazarus/Free Pascal application. The usage of the SQL.Text and ExecSQL properties of TSQLQuery objects make database insert, update and delete tasks very easy to code, not lots more than writing a standard SQL statement. But, there are other, more advanced ways. TSQLQuery objects are descendants of TDataSet objects and by this, may be treated as datasets with the possibility to take advantage of all properties and methods of TDataSet objects. In particular (in relationship with checking the success of a query), thinking of the UpdateStatus property, that may take values like "usUnmodified", "usModified", "usInserted" or "usDeleted". That's exactly what we would have needed, isn't it? The disadvantage of proceeding this way is that it's not thus easy as using SQL.Text and ExecSQL and that additional care is needed, when implementing the application. I never tried this; to preview for (a near) future...
Other, more sophisticated, components are TDBGrid objects. These objects may be connected to TSQLQuery objects, with the help of a TDataSource object. This is done by setting the Dataset property of the data source to the TSQLQuery object and the Datasource property of TDBGrid object to the TDataSource object. Doing so will allow to perform a database selection and automatically fill-in a string-grid with the data, returned by the server. But, not only this: It also makes possible to directly update the database, corresponding to the changes made in the grid. More than useful in real world applications, especially if a big number of updates have to be done, it seems obvious, that using these objects requires more skills and care than the basic approach, described in this tutorial. Maybe, one day, a tutorial concerning TDBGrid...
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       |       2. Reading data from a MySQL database
 
If you find this text helpful, please, support me and this website by signing my guestbook.