Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
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
  [104] - ()
  [644] - ()
  [663] - ()
  [723] - ()
  [1381] - ()
  [1450] - ()
  [1518] - ()
  [1561] - ()
  [1885] - ()
  [2263] - ()
  [2381] - ()
  [2745] - ()
  [2790] - ()
  [3035] - ()
  [3099] - ()
  [3447] - ()
  [3455] - ()
  [4436] - ()

Java - JDBC - Relational Database Access
  [515] - ()
  [2154] - ()
  [2861] - ()
  [3046] - ()

Additional Python Facilities
  [183] - ()
  [208] - ()
  [239] - ()
  [463] - ()
  [663] - ()
  [672] - ()
  [753] - ()
  [901] - ()
  [1043] - ()
  [1136] - ()
  [1149] - ()
  [1305] - ()
  [1336] - ()
  [1337] - ()
  [1876] - ()
  [2407] - ()
  [2435] - ()
  [2462] - ()
  [2655] - ()
  [2721] - ()
  [2745] - ()
  [2746] - ()
  [2764] - ()
  [2765] - ()
  [2786] - ()
  [2790] - ()
  [3089] - ()
  [3442] - ()
  [3469] - ()
  [4085] - ()
  [4211] - ()
  [4298] - ()
  [4439] - ()
  [4451] - ()
  [4536] - ()
  [4593] - ()
  [4709] - ()

Using MySQL Databases in PHP Pages
  [104] - ()
  [515] - ()
  [572] - ()
  [581] - ()
  [647] - ()
  [666] - ()
  [723] - ()
  [915] - ()
  [937] - ()
  [947] - ()
  [1010] - ()
  [1561] - ()
  [1983] - ()
  [2071] - ()
  [2259] - ()
  [2320] - ()
  [2432] - ()
  [2447] - ()
  [2561] - ()
  [2628] - ()
  [3035] - ()
  [3455] - ()
  [4378] - ()
  [4483] - ()

Using SQL Databases from Perl
  [515] - ()
  [644] - ()
  [975] - ()
  [1224] - ()
  [1885] - ()
  [1904] - ()
  [2381] - ()
  [2561] - ()
  [3099] - ()

Ruby GUIs, XML, SQL Database Connectivity
  [1890] - ()
  [2714] - ()
  [4006] - ()
  [4679] - ()

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., 2024: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • 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