Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
Interfacing applications to a MySQL database engine

At the core of MySQL is "mysqld", the database engine daemon which handles connections and requests via a TCP port - typically post no. 3306. Provided with the MySQL distribution is the "mysql" program, a small client which allows for manual examination, amendment and administration of the database, but which is not a tool for daily production work.

Typically, your MySQL database will be accessed from applications written in other programming languages, such as PHP or Perl; this module goes on to cover (in varying depths) how a number of such languages interface to MySQL.

In order to interface an SQL based database (such as MySQL) to a programming language (such as Perl), you need to have some prior understanding of both SQL and the programming language that you'll be using, and this prior knowledge is assumed in this module.

INTERFACING MYSQL TO PERL

The Perl DBI module (available from the CPAN) provides a database independent access tool for Perl to a wide range of SQL based databases, including commercial products such as Oracle, Sybase, Informix, and open source databases such as mSQL, PostGRESql and MySQL.

DBI is just an access tool - it wouldn't be practical to support and maintain a single package with support for such a wide range of databases. So additionally you need from the CPAN the appropriate DBD module.

 DBI Data Base Independent
+ DBD Data Base Dependant

In your Perl program, you'll
 use DBI;
to draw in the DBI module, then when you run a connect command, Perl will also draw in the appropriate DBD module(s) too.


Here's a sample Perl program that selects a specific table from a database, and displays its contents:

#!/usr/bin/perl

# Perl program to display a table contents from a MySQL database

use DBI;

$db_handle = DBI -> connect('DBI:mysql:entertainment', 'root', $ARGV[0])
                or die ("connection: $DBI::errstr\n");

$getkey = $db_handle -> prepare("SELECT * FROM dvd");
$getkey -> execute;

while (@row = $getkey->fetchrow) {
        print (join (" | ",@row),"\n");
        }

Let's see the table "dvd" from the "entertainment" database displayed through the mysql utility, then through the Perl program.

First, through mysql:

$ mysql -uroot -pabc123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 3.23.22-beta

Type 'help' for help.

mysql> use entertainment;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_entertainment |
+-------------------------+
| dvd |
+-------------------------+
1 row in set (0.01 sec)

mysql> select * from dvd;
+----+-------------------+--------+------+-----------+---------------------------------------+------------+----------------+----------+--------------+
| id | title | length | cert | videotype | lang | bought | changed | timeseen | company |
+----+-------------------+--------+------+-----------+---------------------------------------+------------+----------------+----------+--------------+
| 1 | Dinosaur | 79.5 | PG | PAL | English | 2001-03-15 | 20010701133113 | 1 | NULL |
| 2 | Chicken Run | 81 | U | PAL | English | 2001-02-07 | 20010701133113 | 1 | NULL |
| 3 | Groundhog Day | 97 | PG | PAL | English,French,German,Italian,Spanish | 2000-12-08 | 20010701133113 | 1 | NULL |
| 4 | Erin Brockovich | 126 | 15 | NULL | English,German | NULL | 20010701133113 | 1 | NULL |
| 5 | The Piano | 115 | 15 | NULL | NULL | NULL | 20010701133113 | 1 | NULL |
| 6 | The Color Purple | 148 | 15 | NULL | NULL | NULL | 20010701133113 | 1 | NULL |
| 7 | Billy Elliott | 106 | 15 | NULL | English | NULL | 20010701133113 | 1 | NULL |
| 8 | Patch Adams | 111 | 15 | NULL | English | NULL | 20010701133113 | 1 | NULL |
| 9 | Toy Story 2 | 89 | U | NULL | English | NULL | 20010701133113 | 1 | NULL |
| 10 | The Perfect Storm | 125 | 12 | NULL | English | NULL | 20010701135612 | 0 | Warner Bros. |
+----+-------------------+--------+------+-----------+---------------------------------------+------------+----------------+----------+--------------+
10 rows in set (0.00 sec)

mysql> exit Bye $

And through the Perl program:

$ ./sql1.pl abc123
1 | Dinosaur | 79.5 | PG | PAL | English | 2001-03-15 | 20010701133113 | 1 |
2 | Chicken Run | 81 | U | PAL | English | 2001-02-07 | 20010701133113 | 1 |
3 | Groundhog Day | 97 | PG | PAL | English,French,German,Italian,Spanish | 2000-12-08 | 20010701133113 | 1 |
4 | Erin Brockovich | 126 | 15 | | English,German | | 20010701133113 | 1 |
5 | The Piano | 115 | 15 | | | | 20010701133113 | 1 |
6 | The Color Purple | 148 | 15 | | | | 20010701133113 | 1 |
7 | Billy Elliott | 106 | 15 | | English | | 20010701133113 | 1 |
8 | Patch Adams | 111 | 15 | | English | | 20010701133113 | 1 |
9 | Toy Story 2 | 89 | U | | English | | 20010701133113 | 1 |
10 | The Perfect Storm | 125 | 12 | | English | | 20010701135612 | 0 | Warner Bros.
$

You'll notice that we still require the user of the Perl program to type in the database password, which we collect from the command line within the SQL connection. Technically, it would be quite possible to build the password into the Perl program, but then
 - anyone who got a copy of the script would learn the password
and - a password change would result in a script change which are major security and maintenance issues.

THE PERL PROGRAM IN DETAIL

 $db_handle = DBI -> connect('DBI:mysql:entertainment', 'root', $ARGV[0])
                or die ("connection: $DBI::errstr\n");

The connection is made though the connect method in the DBI module, and the connect method returns an object reference. By returning an object reference in this way, Perl allows you to connect to more than one database engine at the same time within the same Perl application, which makes Perl an ideal tool for transferring information between different database products.

Parameters to connect are:

 The database to which to connect, in the format
  DBI:[databasetype]:[databasename]:[hostcomputer}:[portnumber]
 The latter two parameters default to localhost and 3306.

 The SQL account name (not the system account name)

 The SQL account password

Optionally, a reference to a hash of additional parameters may also be given if you want to modify the default behaviour of the DBD.

If connect fails, it will return a null string - a false value which we've trapped in our example using a lazy "or" operator. If we want to know why the failure occurred, we can look within the DBI module [package] at the errstr variable which contains the descriptive text.

Having connected to the "entertainment" database, we want to print out the entire contents of the "dvd" table. The SQL for this is
 SELECT * FROM dvd
and so we embed this command into our Perl:

 $getkey = $db_handle -> prepare("SELECT * FROM dvd");

The prepare method, though, doesn't actually do the work - it simply tells the DBD module that we're going to run an command, and it returns a further reference to us - this time a reference to the command. There are two reasons why it doesn't just run the SQL command:

1. It might be that we want to pass in other SQL as a part of the same command (for example, "WHERE ....")

2. The result set returned might be vast, and if that's the case we'll want to get it back record by record rather than setting up any huge lists or hashes in our Perl.

Once we've completed our SQL instruction, we tell the DBD module to actually execute it on the Database:

 $getkey -> execute;

and then we fetch back the result set in a loop - the fetchrow method iterates through the result set, returning a list of the fields in each line in turn (and a null when the response has been exhausted) which for the purpose of our example we simple turn into a series of "|" delimited lines.

 while (@row = $getkey->fetchrow) {
         print (join (" | ",@row),"\n");
         }

There - job done!

A FURTHER EXAMPLE

This example includes SQL commands to create and drop a table, and to select specific records too.

The program:

#!/usr/bin/perl

# Example of handling

use DBI;

"@ARGV" or die ("Usage: $0 [populate|delete|whereclause] password\n");

$db_handle = DBI -> connect('DBI:mysql:test:localhost:3306',
                'root', $ARGV[-1] , {RaiseError => 1})
                or die ("connection: $DBI::errstr\n");
pop @ARGV;

$field_defs = <<"FDS";
code CHAR(20),
latitude FLOAT,
longitude FLOAT,
description CHAR(40),
journeyflags CHAR(6)
FDS

# Special cases

($ARGV[0] =~ /^populate$/i) and populate() and exit;
($ARGV[0] =~ /^delete$/i) and remove() and exit;

# Other cases - enquiry

$whereclause = (@ARGV > 0)?"WHERE @ARGV":"";

display($whereclause);

##########################################################

sub populate {

 # create and fill the database table

 open (D,"distances") or die ("No distances text file available\n");
 $db_handle -> do("CREATE TABLE dist ($field_defs)");

 while (<D>) {
  ($code,$lat,$long,@txt) = split;
  if ($txt[-1] =~ /[-+*0%]/) {
   $flags = pop @txt;
  } else {
   $flags = "";
  }
  $recin = '"'.join('", "',$code,$lat,$long,"@txt",$flags).'"';
  $db_handle -> do("INSERT INTO dist VALUES( $recin )");
  }
 print "Table populated from text file\n";
 1;
}

##########################################################

sub remove {
 # Wow - this is dangerous .....
 $db_handle -> do ("DROP TABLE dist");
 print "Table DROPPED\n";
 1;
}

##########################################################

sub display {
 $what = $_[0];
 print "Listing records $what\n";
 $getkey = $db_handle -> prepare("SELECT * FROM dist $what");
 $getkey -> execute;

 while (@row = $getkey->fetchrow) {
         print "@row\n";
         }

}

And some examples of the program in operation:


$ sql2.pl
Usage: ./sql2.pl [populate|delete|whereclause] password
$ sql2.pl abc123
Listing records
DBD::mysql::st execute failed: Table 'test.dist' doesn't exist at ./sql2.pl line 71.
$ sql2.pl populate abc123
Table populated from text file
$ sql2.pl code = \"LL\" abc123
Listing records WHERE code = "LL"
LL 3.8 53.3 North Wales 0
$ sql2.pl abc123
Listing records
AB 3 57.8 Aberdeen +
AL 0.4 51.7 St. Albans -
B 2 52.5 Birmingham -
BA 2.4 51.4 Bath
BB 2.6 53.8 Blackburn 0
(etc)
Wexford 6.6 52.4 County *0%
Carlow 6.9 52.6 County *0%
Waterford 7.5 52.2 County *0%
Kilkenny 7.2 52.5 County *0%
Cork 8.9 52.2 County *+%
Kerry 9.8 52.3 County *+%
(etc)
Norway 10.5 59.8 Oslo *+!
Sweden 18 59 Stockholm *+%
Austria 18 47 Vienna *+%
Italy 14 42 Rome *+%
Italia 14 42 Roma *+%
$ sql2.pl delete abc123
Table DROPPED
$ sql2.pl abc123
Listing records
DBD::mysql::st execute failed: Table 'test.dist' doesn't exist at ./sql2.pl line 71.
$

For further details of interfacing Perl to any relational database, see "Programming the Perl DBI" by Alligator Descartes and Tim Bunce, published by O'Reilly.

INTERFACING MYSQL TO PHP

PHP is an HTML embedded scripting language which has rocked in popularity recently. It includes functions to make direct calls to the MySQL database engine, and like MySQL it's open source. This means that it's a "natural" to use MySQL with PHP if you're going to be using a browser (i.e. a web front end) to access your relational database. So much is MySQL used in association with PHP that a number of books cover both subjects (plus the integration of the two); at the time of writing, we already have four such volumes in our library.

Here's an example of a PHP script which reports on the contents of a mySQL database within a web page

<head>
<title>Report contents of table</title>
</head>
<body bgcolor=white>
This page reports contents of an SQL table ....<BR>
<font color=red size=5>
<?php
$dbid = mysql_connect('localhost','root','abc123');
mysql_select_db("test",$dbid);

$query = "SELECT * FROM demo";
$result = mysql_query($query,$dbid);
print ("<TABLE BORDER=1>");
while ($row = mysql_fetch_row($result)) {
 print ("<tr><td>$row[0]</td><td>$row[1]</td></tr>");
 }
print ("</table>");
?>
</font><P>
OK. Done!
</body>

INTERFACING MYSQL TO JAVA

Java interfaces to databases through JDBC (Java Database Connectivity). Your Java Runtime Environment should include the java.sql package by default, which is basically a driver manager class and does not include any drivers for any specific databases. You then source appropriate drivers from elsewhere; there's a list of suitable drivers on Sun's site:
 http://industry.java.sun.com/products/jdbc/drivers
which currently lists 155 drivers, including several for MySQL.

In addition, you could interface MySQL to Java using the JDBC to ODBC bridge but ... don't; you'll be adding in an extra layer of conversions, and the drivers listed on the site above are all "type 4" drivers which means that they're written in native Java code.

Here's a complete working example, using drivers sourced via this web site:

public class jdbc1 {

public static void main(String [] args) {

 java.sql.Connection conn = null;

 System.out.println("SQL Test");

 try {
  Class.forName("org.gjt.mm.mysql.Driver").newInstance();
  conn = java.sql.DriverManager.getConnection(
   "jdbc:mysql://bhajee/test?user=jtest&password=");

 }
 catch (Exception e) {
  System.out.println(e);
  System.exit(0);
  }

 System.out.println("Connection established");

 try {
  java.sql.Statement s = conn.createStatement();
  java.sql.ResultSet r = s.executeQuery ("SELECT code, latitude, longitude FROM dist");
  while(r.next()) {
   System.out.println (
    r.getString("code") + " " +
    r.getString("latitude") + " " +
    r.getString("longitude") );
   }
 }
 catch (Exception e) {
  System.out.println(e);
  System.exit(0);
  }
  
 

 }

}

And in operation:

$ java jdbc1
SQL Test
Connection established
java.sql.SQLException: General error: Table 'test.dist' doesn't exist
$ sql2.pl populate abc123
Table populated from text file
$ java jdbc1
SQL Test
Connection established
AB 3 57.8
AL 0.4 51.7
B 2 52.5
BA 2.4 51.4
BB 2.6 53.8
BD 1.9 53.8
BH 2.9 50.8
BL 2.5 53.6
(etc)
Portugal -8.5 37
Denmark 14 55.5
Germany 9 50
Norway 10.5 59.8
Sweden 18 59
Austria 18 47
Italy 14 42
Italia 14 42
$

INTERFACING MYSQL TO TCL/TK

A Tcl/Tk 8.0 interface to MySQL is available from
 http://www.xdobry.de/mysqltcl

Download from that site ... you get full instructions, plus test programs. The following code snippet connects (as user root, no password, localhost) to a MySQL database that's already been created and populated, and reads back all records in the Student table in the uni database.

load ./.libs/libmysqltcl.so

set handle [mysqlconnect -user root]

set rows [mysqlsel $handle {select * from uni.Student}]
for {set x 0} {$x<$rows} {incr x} {
    set res [mysqlnext $handle]
    set nr [lindex $res 0]
    set name [lindex $res 1]
    set sem [lindex $res 2]
}

mysqlclose


See also Training Module covering this material

Please note that articles in this section of our web site were current and correct to the best of our ability when published, but by the nature of our business may go out of date quite quickly. The quoting of a price, contract term or any other information in this area of our website is NOT an offer to supply now on those terms - please check back via our main web site

Related Material

Interfacing Applications to MySQL Databases
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3447] Needle in a haystack - finding the web server overload - (2011-09-18)
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2381] Checking the database connection manually - (2009-08-28)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1450] Easy selection of multiple SQL conditions from PHP - (2007-11-30)
  [1381] Using a MySQL database to control mod_rewrite via PHP - (2007-10-06)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [663] Python to MySQL - (2006-03-31)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

Java - JDBC - Relational Database Access
  [3046] Java Beans, tag libraries and JSPs - what and why. - (2010-11-13)
  [2861] MySQL and Java - connectivity past, present, and future thoughts - (2010-07-09)
  [2154] Bean Classes in Java and Java Database Connections - (2009-05-02)
  [515] MySQL - an FAQ - (2005-12-03)

Additional Python Facilities
  [4211] Handling JSON in Python (and a csv, marshall and pickle comparison) - (2013-11-16)
  [4085] JSON from Python - first principles, easy example - (2013-05-13)
  [3469] Teaching dilemma - old tricks and techniques, or recent enhancements? - (2011-10-08)
  [3442] A demonstration of how many Python facilities work together - (2011-09-16)
  [3089] Python regular expressions - repeating, splitting, lookahead and lookbehind - (2010-12-17)
  [2790] Joining a MySQL table from within a Python program - (2010-06-02)
  [2786] Factory methods and SqLite in use in a Python teaching example - (2010-05-29)
  [2765] Running operating system commands from your Python program - (2010-05-14)
  [2764] Python decorators - your own, staticmethod and classmethod - (2010-05-14)
  [2746] Model - View - Controller demo, Sqlite - Python 3 - Qt4 - (2010-04-29)
  [2745] Connecting Python to sqlite and MySQL databases - (2010-04-28)
  [2721] Regular Expressions in Python - (2010-04-14)
  [2655] Python - what is going on around me? - (2010-02-28)
  [2462] Python - how it saves on compile time - (2009-10-20)
  [2435] Serialization - storing and reloading objects - (2009-10-04)
  [2407] Testing code in Python - doctest, unittest and others - (2009-09-16)
  [1876] Python Regular Expressions - (2008-11-08)
  [1337] A series of tyre damages - (2007-09-08)
  [1336] Ignore case in Regular Expression - (2007-09-08)
  [1305] Regular expressions made easy - building from components - (2007-08-16)
  [1149] Turning objects into something you can store - Pickling (Python) - (2007-04-15)
  [1136] Buffering output - why it is done and issues raised in Tcl, Perl, Python and PHP - (2007-04-06)
  [1043] Sending an email from Python - (2007-01-18)
  [901] Python - listing out the contents of all variables - (2006-10-21)
  [753] Python 3000 - the next generation - (2006-06-09)
  [672] Keeping your regular expressions simple - (2006-04-05)
  [663] Python to MySQL - (2006-03-31)
  [463] Splitting the difference - (2005-10-13)
  [239] What and why for the epoch - (2005-03-08)
  [208] Examples - Gadfly, NI Number, and Tcl to C interface - (2005-02-10)
  [183] The elegance of Python - (2005-01-19)

Using MySQL Databases in PHP Pages
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2628] An example of an injection attack using Javascript - (2010-02-08)
  [2561] The future of MySQL - (2010-01-03)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [947] What is an SQL injection attack? - (2006-11-27)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [915] Paging through hundreds of entries - (2006-11-05)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [666] Database design - get it right from first principles - (2006-04-02)
  [647] Checking for MySQL errors - (2006-03-15)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [515] MySQL - an FAQ - (2005-12-03)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

Using SQL Databases from Perl
  [3099] Perl - database access - DBD, DBI and DBIx modules - (2010-12-22)
  [2561] The future of MySQL - (2010-01-03)
  [2381] Checking the database connection manually - (2009-08-28)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1885] Hiding a MySQL database behind a web page - (2008-11-15)
  [1224] Object Relation Mapping (ORM) - (2007-06-09)
  [975] Answering ALL the delegate's Perl questions - (2006-12-09)
  [644] Using a MySQL database from Perl - (2006-03-13)
  [515] MySQL - an FAQ - (2005-12-03)

Ruby GUIs, XML, SQL Database Connectivity
  [4006] Ruby / SQLite3 example program, showing JOIN v LEFT JOIN - (2013-02-16)
  [2714] A simple example - XML from a Ruby program - (2010-04-10)
  [1890] MySQL database from Ruby - an example - (2008-11-16)

resource index - MySQL
Solutions centre home page

You'll find shorter technical items at The Horse's Mouth and delegate's questions answered at the Opentalk forum.

At Well House Consultants, we provide training courses on subjects such as Ruby, Lua, Perl, Python, Linux, C, C++, Tcl/Tk, Tomcat, PHP and MySQL. We're asked (and answer) many questions, and answers to those which are of general interest are published in this area of our site.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/solutions/mysql-in ... ngine.html • PAGE BUILT: Wed Mar 28 07:47:11 2012 • BUILD SYSTEM: wizard