Delphi Database Programming with MySQL - Creating a phone book application



Part I - Getting ready
  1. Introduction
  2. Setting up MySQL
  3. Setting MicroOLAP DAC for MySQL component
Part II - Let's get started
  1. Delphi and DAC for MySQL database components
  2. Designing the Delphi forms
  3. More features of microOLAP's DAC for MySQL

 

Part III - The end

  1. Final words
  2. Acknowledgement
  3. References and links

Part I - Getting ready

1. 1 - Introduction

In this artcile you will learn how to create database programs using Delphi and MySQL. You are required to have basic Delphi and SQL programming skills only. In the next section we will get to installing MySQL and download the MySQL DAC Components. By the end of this article you will have learned how to write your own database programs using Delphi and MySQL.

1. 2 - Setting up MySQL

MySQL is a free DBMS and can be downloaded from MySQL.com website. You need to get the Windows version for this article.
After downloading it, you need to set it up, select the administrator password.
Second thing to be done is to create the database and the needed tables.

  1. Start -> Programs -> MySQL -> Start Command Line MySQL Client
  2. You need to enter the password that you used during the setup phase
  3. Create a new database named "phonebook":
    create database phonebook;
  4. Select that database:
    use phonebook;
  5. And finally create the table we are going to use:
    CREATE TABLE contacts (id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
name VARCHAR (200), 
email VARCHAR (200), 
phone VARCHAR (50), 
comments TEXT, 
picture MEDIUMBLOB, 
PRIMARY KEY(id));
    Notice that we created an ID as a primary key, basic text fields for the name/address/email/etc... and a blob field to store the contact's picture.
  6. If all was okay then you should see no errors and your screen would like something like this:

1. 3 - Setting microOLAP DAC for MySQL component

There are more than a way to use MySQL from inside Delphi, some of these ways are:

  1. Port the MySQL C header files into Delphi and use LibMySql.dll
  2. Use MySQL's ODBC drivers and then use Delphi's ADO components
  3. Use a third party component that makes accessing MySQL the least of your worries

The first method is not really feasible and makes your programming hard, especially if you are going to make use of data aware components. The second method is acceptable but it requires many prerequisits on the client's machine and may or may not exploit the full features of MySQL. We are going to explore the 3rd solution using microOLAP's excellent MySQL component called DAC for MySQL (Direct Access Component).

Before proceeding, please download and install the component from: http://www.microolap.com/products/connectivity/mysqldac/

From the microOLAP website, here are ten top reasons to use the DAC component:

Now that you have everything ready, let us get started!

Part II - Let's get started

 

2. 1 - Delphi and DAC for MySQL database components

Before introducing the components, let me make sure that you are aware of the following concepts needed to access databases from Delphi:

Putting it all together, we first need a database connection and a table object. Then if we want to represent the table visually, we need to insert a data source and link it with the table object (dataset), and have the data source linked to a dbgrid component.
The following screenshot demonstrates what we need:

  1. We select DAC for MySQL components from this newly installed package
  2. The MySQL database connection (needs: server name, username/password, and database name).
  3. A logical MySQL table representation object (needs: The table name)
  4. A datasource which has its dataset linked to (3)
  5. A database string grid which has its datasource linked to (4)

Let us configure the mySQLDatabase1 component together:

  1. Database address
  2. username / password
  3. database name
  4. and finally set it to Connected (not before)

2. 2 - Designing the Delphi forms

We will design our application using one form, however all the screens will be inside tab pages. If we use this method we won't have to use a data module unit (which is used to centralize database connection and other objects).

The "List all" page

For that we need a TmySQLTable component linked to the mySQLDatabase1 and its table name set to "contacts", then we need a data set linked to the TMySQLTable DataSet and finally a DBGrid linked to the DataSet. Make sure you set the DataSet active.

Now you may adjust the DBGrid and select the fields you want to have displayed. To do that, double click on the DBGrid and right-click/Add all fields. After adding all the fields, you may now edit each field and delete it if you don't want it displayed.

For example:


Nothing additional is needed to be done in that page. The DBGrid will enumerate all the elements using DAC's dataset.

The "Manage" page

Let us create Edit Boxes for each of the DB fields. First we need another TmySQLTable, then we double click on it and we right-click and add all fields. Now that the fields are selected, we drag and drop them into the form. Automatically Delphi will create for us appropriate Edit and Memo boxes.


Notice now we have all those controls and they are data-aware, that means whenever we change them, the changes are reflected on the database.

Now that we want to have a picture for each contact, we are going to drop into our form a TDBImage control (wuth datafield='picture' and datasource='dsManage'). Then whenever the user double clicks on that control, we will want to show the "Open Dialog" and insert the picture into the blob.

procedure TForm1.DBImage1DblClick(Sender: TObject);
var
   BlobField1: TBlobField;
begin
  // allow picture edition only in edit or insert mode!
   if not (
            (dsManage.State = dsEdit) or (dsManage.State = dsInsert)
         )
  then
     Exit;

   BlobField1 := TBLOBField(DBImage1.Field);
   BlobField1.Transliterate := false;
   if dlgOpen1.Execute then
   begin
      BlobField1.LoadFromFile(dlgOpen1.FileName);
      dsManage.DataSet.Post;
   end;
end;

And to finalize and make things simple, we will add a TDBNavigator to the manager form so that we can easily insert/edit or delete records.
At the end, our manager form will look something like that:


The "Search" page
Now that we have finished most of the tab pages using the excellent help of microOLAP's DAC, we will now show you how to create queries using your code.
We will illustrate how to use DAC's TmySQLQuery and how to setup prepared statements. Before we begin make sure you insert a new TmySQLQuery named "querySearch" and set its database value correctly, also insert a TDataSource and bind it with the search result DBGrid.

Let us take the example of finding by name, the prepared statement should be something like:

SELECT 
	name, email, phone 
FROM 
	contacts 
WHERE
	name = :name

Notice how we use the ":name" which designates a variable to be replaced when the statement is prepared.
To continue, let us pass this query to the component and pass the name parameter then execute the statement as: 
  querySearch.SQL.Text := 'SELECT name, phone, email FROM contacts WHERE name=:name';
  querySearch.Params.ParamByName('name').AsString := txtQuery.Text;
  querySearch.Open;
  lblResults.Caption := Format('%d record(s) found!', [querySearch.RecordCount]);

Now that you know how we can put the parameter of a prepared statement, let us look on how we can write the search form based on the selection of the user.



The search code could be something like that:

procedure TForm1.Button1Click(Sender: TObject);
begin
  if (rbEverything.Checked) then
  begin
    querySearch.SQL.Text :='SELECT name, phone, email FROM contacts';
  end
  else if (rbByName.Checked) then
  begin
    querySearch.SQL.Text := 'SELECT name, phone, email FROM contacts WHERE name=:name';
    querySearch.Params.ParamByName('name').AsString := txtQuery.Text;
  end
  else if rbByEmail.Checked then
  begin
    querySearch.SQL.Text := 'SELECT name, phone, email FROM contacts WHERE email=:email';
    querySearch.Params.ParamByName('email').AsString := txtQuery.Text;
  end
  else if rbByPhone.Checked then
  begin
    querySearch.SQL.Text := 'SELECT name, phone, email FROM contacts WHERE phone=:phone';
    querySearch.Params.ParamByName('phone').AsString := txtQuery.Text;
  end;
  querySearch.Open;
  lblResults.Caption := Format('%d record(s) found!', [querySearch.RecordCount]);
end;

2. 3 - More features of microOLAP's DAC for MySQL

The article barely demonstrated one of the many facilities provided by microOLAP's DAC.
It is worth mentioning the following additional components:

Part III - The End

 

3. 1 - Final words

I hope you have found benefits in this introductory article. MySQL is an excellent DBMS, but using it with Delphi was a hassle, but after finding DAC for MySQL, programming MySQL was so simple and transparent. Hope you find DAC for MySQL helpful too.

3. 2 - Acknowledgement

I would like to thank all the people who contributed to the Delphi community through free components, newsgroups contributions, articles and tutorials.
Many thanks to microOLAP for "DAC for MySQL" component which is really simple to use but very powerful.
microOLAP also encourages users to earn free copies of the component through writing a tutorial, I won my copy, so can you! Spread the word.

3. 3 - References and links

Download the project files here. Some websites that can be of help to you:

Home