How To Use MySQL With Delphi

part 1

About the author:

My name is Masoud Kalali ,I'm 20 years old and going To university to become a computer software engineer

You can reach me via: masoud001ir@yahoo.com

, its now (when I'm writing this tutorial I'm in holyday And university will start in about 2 short weeks so I Should prepare myself for boring mathematic and

Such other boring lessons) I'm going to year 3 of my University education and I learned nothing extra in last two years in university. I hope I learn advanced topic in the next two years.

I start learning computer when I was 15 and it happened by a 80186 series computer

First programming language which I learned was Quick basic  and after that I start working on Pascal, C++ , C--, assembly, visual basic J and finally I dropt on Delphi Its really good programming language ever I have seen.

  

During several mount which I worked on huge database applications all ways I was thinking about a free and powerful database system for my latter work

After that projects which I did by Interbase® I decide to work on ThinkSQL which is an RDBMS engine or MySQL.

After an small comparing I select MySQL database, so I start working and this tutorial is the result of my work.

in this tutorial I used several names of several companies which you can see some of them below.

ThinkSQL developed by greg gaughan.If you like you can find ThinkSQL in www.thinkSQL.co.uk.

MySQL is a trademark of MySQL AB.

Windows is a trademark of Microsoft®.

Delphi is a trademark of Borland ®

At first you should know what is MySQL and Where you can get this

MySQL (TM) is a very fast, multi-threaded, multi-user, and robust SQL (Structured Query Language) database server. MySQL is intended for mission-critical, heavy load production systems as well as for embedding into mass-deployed software. MySQL is a trademark of MySQL AB.

For using this tutorial you need some stuff that you can get them all from respective vendor. (Almost all of them are free). you should install them all before you start working by this tutorial

First you need Delphi 6 (I did this pages by Enterprise edition but probably you can use professional or personal edition) for more information about Delphi refer to www.Borland.com

Then you need My SQL that you can get from www.MySQL.com

And also Myodbc which is available at WWW.MySQL.com

And finally you need to have Mycc which is available at www.MySQL.com

 

For this tutorial you need to make tutorial database which contain 4 different table

I did this one by means of Mycc.

At first you should register a server, to register a server follow the instruction mentioned below

 

1-Run Mycc

2-By default register server button is the first button on the left side of toolbar.

3-click on register server button and fill the form as I did. (figure Mycc1).

 

 

Note: if you r using a network joined computer you should fill the host name field by your computer name or IP

Now push the test button if you did all jobs without mistake you will get no message else

You will get an error message which means you didn’t fill it right

 

Warning: Database name is mini_employee which defined as a normal database. I set admin as user and password is 123.

 

So if you get error message check for following items

1-check if MySQL server is running (By default after you install MySQL it will run automatically)

2-check if you fill host name as mentioned?

3-check if you fill server name as you should do?

After you make testing sequence error free you can click add button to add the server to your server list.

After you register the server as mentioned you will see ‘Test server for my tutorial ‘ in the MySQL servers tree view

There is a nice icon for servers in this application, isn’t it?

To connect to the server double click on the Test server  for my tutorial or click on connect button from toolbar

Now you are connected to a fresh MySQL server.

                   figure:Mycc1

 

To create database :

1-right clicks on databases and choose new database

2-fill database name by mini_employee and then choose okay.

Congratulation you made your database for this tutorial.

 

Okay now we assume you register your server and create your database  its now I’ll teach you how to make table.

There is some differences between MySQL and Interbase you can learn the MySQL scripting in the MySQL manual wich is downloadable from http://www.MySQL.com

To make tables

1-double click on databases to make it open

2-double click on the mini_employee database.[you can click on connect button from tool bar instead]

Now you can see the tables item below of mini_employee

Creating tables is too easy by using Mycc

Just right click on the tables and select create new table

Now fill the field names and field type for each table as I mentioned in the tables

After you fill all field of each table click the save button and type table name for that as I write under each table.

For example I’ll fill one field of employee

1-type “emp_no” (without double quote) for field name.

2-select smallint from the combo box at the end of cell.

3-fill the length edit box by 6.

4-fill the default value by 0.

 

Note Allow null means that if the field can left empty in data entry ? If you don’t check allow null check box and put the field empty in data entry time you will get a database level exception

 

Now create tables as u see in the tables below.

(My best wishes are with you to create tables successfully J  )

 

 

Tables are:

1- Employee:

 

DDL for employee is

 

CREATE TABLE `employee` (

  `emp_no` smallint(6) NOT NULL default '0',

  `first_name` varchar(15) default NULL,

  `last_name` varchar(20) default NULL,

  `phone` varchar(15) default NULL,

  `dept_no` smallint(6) default NULL,

  `jobe_code` tinyint(4) default NULL,

  `job_grade` tinyint(4) default NULL,

  `salary` int(11) default NULL,

  `hire_date` date default NULL,

  `Full_name` tinyint(4) default NULL

) TYPE=MyISAM

 

You should fill the create table form like this:

 

Field name	Field kind
emp_no	smallint(6) NOT NULL default '0'
first_name	varchar(15) default NULL
last_name	varchar(20) default NULL
Phone	varchar(15) default NULL
dept_no	smallint(6) default NULL
jobe_code	tinyint(4) default NULL
job_grade	tinyint(4) default NULL
salary	int(11) default NULL
hire_date	date default NULL
Full_name	tinyint(4) default NULL

    

  

2- department:

 

DDL for department  is

 

CREATE TABLE `department` (

  `dep_no` smallint(4) default NULL,

  `department` varchar(30) default NULL,

    `manager_no` smallint(6) default NULL,

  `location` varchar(30) default NULL,

  `phone` varchar(10) default NULL

) TYPE=MyISAM

 

You should fill the create table form like this:

Field name	Field kind
Dep_no	smallint(4) default NULL
Department	varchar(30) default NULL
Manager_no	smallint(6) default NULL
Location	varchar(30) default NULL
phone	varchar(10) default NULL

 

3-jobs:

 

DDL for job is

 

CREATE TABLE `jobs` (

  `jobe_id` smallint(6) default NULL,

  `job_grade` smallint(6) default NULL,

  `job_title` varchar(10) default NULL

) TYPE=MyISAM

 

You should fill the create table form like this:

Field name		Field kind
	jobe_id	smallint(6) default NULL
	job_grade	smallint(6) default NULL
	job_title	varchar(10) default NULL

 

4-projects:

 

DDL for projects is

 

CREATE TABLE `projects` (

  `emp_no` smallint(4) default NULL,

  `proj_name` varchar(15) default NULL,

  `team_leader` smallint(6) default NULL,

  `product` set('software','hardware','n/a','other') default 'software'

) TYPE=MyISAM

 

 

 

 

You should fill the create table form like this:

 

Field name	Field kind
Emp_no	smallint(4) default NULL
Proj_name	varchar(15) default NULL
Team_leader	smallint(6) default NULL
Product	set('software','hardware','n/a','other') default 'software'

 

After you make database and tables you need to make an odbc connection to your database

For this item you need to install Myodbc which is downloadable from www.MySQL.com

Its know we assume you install the driver now I’ll tell you how to make odbc connection for your database

1-Goto control panel

Note: if you are in win2k or winxp  goto Administrative Tools then select

2-open data sources (odbc)

Now you will see a window like this

now you should make a new connection by clicking on add button

its now select  MySQL odbc 3.51 driver from the listbox (its probably at the end of list)

 

 

after you select the item and click on the finish button you need to fill some field of data to make your connection:

do it as I did

 

Warning: Beware that the password field is 123 .

Now push the Test Data Source button if you did all job as yu should you will face with a successfully connected to … MessageJ.

Congratulation you did the first phase of learning MySQL J

If you get any fail message test if MySQL service is running?

if you fill database name correctly?

If you fill password and user name correctly?

Okay we assume you connected to database successfully

Now you should copy some files from MySQL dir\ lib\opt  to your windows directory\system\

Note: If you r using any NT edition of Microsoft windows you should copy the files into Windows directory\system32 \

 

 

 

It’s now our programming by Delphi started

1-Start Delphi

 

The data access component which we use are BDE component tab which contain several component (listed below) but we will not use them all in this part of my  tutorial probably you will learn more

 About them in next part tutorial, we will just use

1-Table 2- Query 3- Database

 

2-Open a new project

3-add a datamodule to the new project and change name property to employee_DtMdl.

4-add a form to your project and change the name to details_frm.

5-add employee_DtMdl to use cluse of the form and frame ;)

6-rename the form1 to master_frm

7-save the (u can choose your desire name for the project and units ;))

Drop the component which listed below on the datamodule which you add to new project

 

Component	Name	Related properties
Database	Mini_emp_dtb	AliasName = mini_employee DatabaseName= mini_employee Name=mini_employee_db Donot change the driver name property Left it blank Params:   USERNAME  Admin PASSWORD Sysdba
Query	Employee_qry	Database=mini_emp_dtb
Table	projects _tlb	Databasename= Mini_emp_dtb TableName =projects Mastersource= Employee_dtsr Masterfield=emp_no Indexfieldname= employee_no
Table	Jobs_tlb	Databasename= Mini_emp_dtb TableName =jobs Mastersource= Employee_dtsr Masterfield=jobe_code Indexfieldname=jobe_id
Table	department_tlb	Databasename= Mini_emp_dtb TableName =employee_proj Mastersource= Employee_dtsr Masterfield=dept_no Indexfieldname=dep_no
Datasource	projects _dtsr	Dataset= projects _tlb
Datasource	Jobs_dtsr	Dataset= Jobs_tlb
Datasource	department_dtsr	Dataset=  department_tlb
Datasource	Employee_dtsr	Dataset=employee_qry

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now we did anything require for our data access part.

Lets do some data control job and finish our first MySQL learning paper

Drop the components listed below on the form and make changes with their properties as listed in the

Table

 

Component	Name	Properties
DbGride	Employee_grd	Datasource= employee_DtMdl. employee_qry
Dbgride	Projects_grd	Datasource= employee_DtMdl. projects _tlb
Statusbar	Main_stbar	Add 1 new panel and make it a text panel
Button	Exit_btn	Caption= cancel
button	Clear_btn	Caption=Clear
Bottom	Execute_btn	Caption = Execute
Memo	SQL_mem	Lines:select * from employee where emp_no = 2
Label	No matter	Caption=Enter your SQL command here
Label	No matter	Caption=Result of your SQL command
Label	No matter	Caption=Related project for selected employee

 

We need frame to show some other details about our database so we need another frame or form (we add frame because it dosnt need as resources as form need)

 

Component	Name	Properties
Dbgrid	Jobs_grd	Datasource= employee_DtMdl. Jobs_dtsr
Dbgrid	Dep_grid	Datasource= employee_DtMdl.department_drst
Label	No matter	Caption= Selected employee job details
Label	Nomatter	Caption=selected employee department details
Button	Close_btn	Cancel=true

 

Now we can start coding in delph to make our test application for MySQL accessing J

We will made a master details application to show some preadvanced topic of database programming

And a SQL input box will help us to execute our query by typing and clicking on execute button.J

So design form and frame as you can see below on figures: detail_frm and main_frm

After you designed the interface you need to write some code to make application working

So we will write code for the execute button to make our query execute what we will write

In the SQL_mem .

 

Note: MySQL language has some difference with ISQL (Interbase SQL) PL SQL (Oracle SQL) and T-SQL (Ms SQL server SQL). So you need to have a look at MySQL manual which is available in www.MySQL.com

 

I have write several SQL sample wich you can see in the zip file distributed with this tutorial

We will write one of them together.                           

Figure:details_frm

 

 

 Figure:master_frm

 

 

 

Now make following changes in the components events

 

For details_frm:

 

procedure Tdetails_frm.Button1Click(Sender: TObject);

begin

self.Close;

end;

 

for master_frm:

procedure Tmaster_frm.Button3Click(Sender: TObject);

begin

application.Terminate;

end;

 

procedure Tmaster_frm.Button1Click(Sender: TObject);

begin

SQL_mem.Lines.Clear;

end;

 

procedure Tmaster_frm.Button2Click(Sender: TObject);

begin

employee_dtmdl.employee_qry.close;

employee_dtmdl.employee_qry.SQL:=SQL_mem.Lines;

try

employee_dtmdl.employee_qry.Open;

details_frm.Show;

except

application.MessageBox ('There is an error in your type SQL command',' Error');

end;

 

 

Now save the project and run it J.

Amazing hum? You did your first MySQL database application using odbc driver J

Congratulation.

 

Let’s practice some SQL command making, for our skill improvement.

 

To make SQL command in Mycc you need to be connected to a database

So connect to database which you made for your tutorial and then double click on one of tables

A new window will appear.

On the tool bar you can see a speed button with SQL caption when you click on it you will see an SQL pan on the form, now you can type any SQL command you like here .

After you did you can run the query by clicking on the (almost latest one in the right)

Also if you mean a special SQL command (delete, insert, select, update, append and make tables) you can have a template of these by going to: main menu/query/query type and select what you want there.

Also if you like to have DDL of any table you can click on the RUN button

To view the DDL of any table (DDL=Data Definition Language) you may use this instruction:

1-connect to a server.

2-connect to your target database.

3-from toolbars click on the tools button.

4-select shows create from drop down menu of tools button.

5-select what table(s) you like to have DDL for it.

 

 

 

SELECT `employee`.`emp_no`,

       `employee`.`first_name`,

       `employee`.`last_name`,

       `employee`.`phone`,

       `employee`.`dept_no`,

       `employee`.`jobe_code`,

       `employee`.`salary`,

       `employee`.`hire_date`,

       `projects`.`employee_no`,

       `projects`.`proj_name`,

       `projects`.`team_leader`,

       `projects`.`product`,

       `jobs`.`jobe_id`,

       `jobs`.`job_grade`,

       `jobs`.`job_title`,

       `department`.`dep_no`,

       `department`.`department`,

       `department`.`head_dep`,

       `department`.`manager_no`,

       `department`.`location`,

       `department`.`phone`

FROM `department`

   INNER JOIN `employee` ON (`department`.`dep_no` = `employee`.`dept_no`)

   INNER JOIN `projects` ON (`employee`.`emp_no` = `projects`.`employee_no`)

   INNER JOIN `jobs` ON (`employee`.`jobe_code` = `jobs`.`jobe_id`)

 

As you can see there is no need for double quote in the name of field and name of table

Instead of double quote we use single quote (‘) and in the join section no difference is between

Interase SQL and MySQL so you can change your Interbase SQL to MySQL  as easy as you can think.

 

 

SELECT `projects`.`employee_no`,

       `projects`.`proj_name`,

       `projects`.`team_leader`,

       `projects`.`product`,

       `department`.`dep_no`,

       `department`.`department`,

       `department`.`head_dep`,

       `department`.`manager_no`,

       `department`.`location`,

       `department`.`phone`,

       `employee`.`emp_no`,

       `employee`.`first_name`,

       `employee`.`last_name`,

       `employee`.`phone`,

       `employee`.`dept_no`,

       `employee`.`jobe_code`,

       `employee`.`salary`,

       `employee`.`hire_date`,

       `jobs`.`jobe_id`,

       `jobs`.`job_grade`,

       `jobs`.`job_title`

FROM `jobs`

   INNER JOIN `employee` ON (`jobs`.`jobe_id` = `employee`.`jobe_code`)

   INNER JOIN `projects` ON (`employee`.`emp_no` = `projects`.`employee_no`)

   INNER JOIN `department` ON (`employee`.`dept_no` = `department`.`dep_no`)

ORDER BY `projects`.`employee_no`, `department`.`dep_no`, `jobs`.`jobe_id`

 

As you can see no difference except using single quote instead of double quote happened in sorting Statement in MySQL and Interbase SQL.

 

Source code of the project and also database for this tutorial is available on a zip file you can download it by clicking on the http://m-kalali.united.net.kg/MySQL_tut_p1.zip

 

It was a brief tutorial to use MySQL and Delphi for Standalone application

In the next part of my tutorial you will learn how to use MySQL for advanced topic like CGI programming and C/S architecture, I hope I find a time to prepare it for you to learn more and more about Delphi programming.