Friday, August 03, 2007

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
           <!--[if !vml]--><!--[endif]-->
  You can reach me via:
, 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.
<!--[if !vml]--><!--[endif]-->
                   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:
<!--[if !supportMisalignedColumns]--> <!--[endif]-->
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
<!--[if !vml]--><!--[endif]-->
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)
 
<!--[if !vml]--><!--[endif]-->
 
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
<!--[if !vml]--><!--[endif]-->
 
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.                           
<!--[if !vml]--><!--[endif]-->
Figure:details_frm
 
<!--[if !vml]--><!--[endif]--> 
 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.


Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.

0 Comments: