20.2 THE MySQL DATABASE MANAGER

Our SQL examples in the next section and our Java and C++ programs later in the chapter use the MySQL database management system. MySQL, one of the most popular open source^[2] SQL-based systems for relational databases, can be downloaded from http://www.mysql.com

The MySQL database management system consists primarily of a MySQL server that can be accessed by a MySQL client for creating and using databases.^[3] MySQL also comes with a "terminal monitor"interactive program mysqlthat can be used for executing command-line SQL statements. This program can also be used to run SQL statements in a batch mode in which you place multiple statements in a file and then tell mysql to execute the contents of the file.

The rest of this section introduces some of the basic terminology of communicating with a database, our interest being specifically in communicating with MySQL databases. We will define and provide examples for the terms Driver Manager, bridge driver, and database URL.We will use Java-related examples for the terms, but the terms have the same meanings when C++ classes from Mysql++ are used for accessing a database.

As mentioned already, one communicates with a database through a database driver. It is the driver's job to figure out how to reach into the row—column representations of the tables of the database and to retrieve or modify the information at prescribed locations. There are a number of drivers available for communicating with a MySQL database. A commonly used driver by Java programs is the open-sourceMM. MySQL driver.^[4] In the same vein, other database systems have their own drivers. Many of these database systems, such as Access, dBase, DB2, Excel, Text, and so on, can be accessed with the ODBC (for Open DataBase Connectivity) driver that also understands SQL. While each of these database systems would have its own driver module, an ODBC driver would know how to "talk" to the product-specific drivers. A Java database program can communicate with all ODBC-accessible databases by using the JDBC-ODBC bridge driver.

Platforms that support database programming also usually provide a driver manager that knows about the various types of drivers commonly used today. For example, the DriverManagerclass in Java.sql will load in all the drivers referenced in the "jdbc.drivers" system property that can be included in a file of pathname .hotJava/properties at the top level of your home directory. One also has the option of loading into a JDBC program a specific driver by an invocation such as the following which works for the MM. MySQL driver:

       Class.forName( "org.gjt.mm.mysql.Driver").newInstance();

where the static method Class.forNamereturns the Class object associated with the class of name org.gjt.mm.mysql.Driver.

A driver manager can also help establish a connection with a database. Using the example the JDBC class DriverManager again, its method getConnectionreturns an object of type Connection defined in the Java.sql package. The argument to the getConnection method is a specially formatted string that for MySQL is the name of the database. For example, if we want a Java program to make a connection with the MySQL database test that comes with MySQL installation, we'd need to make the calls

    String url = "jdbc:mysql:///test";    Connection con = DriverManager.getConnection( url );

The string "jdbc:mysql:///test" is called a database URL (as opposed to the internet URL). If, on the other hand, we wanted a Java program to talk to an ODBC database, we could say

     String url ="jdbc:odbc:myDatabaseName";     Connection con = DriverManager.getConnection( url );

If you are trying to reach a remote database over the internet, the database URL string may have to include the port number and other information, besides, of course, the internet address of the machine hosting the database.

^[2]Open source means that the source code for the software is available free and can be extended/modified without penalty to suit a particular need. Open source software is commonly made available under the GNU General Public License (GPL). This license, available from http://www.gnu.org, spells out what you can and cannot do with such software.

^[3]If you have installed MySQL with default options on a personal Linux machine and you are just now becoming familiar with it, for the kinds of practice programs we will be discussing in this chapter you can start up the server daemon as root by invoking

             safe_mysqld -Sg &

where safe_mysqld is a wrapper around the daemon executable mysqld that automatically invokes the proper options to use—unless they are overridden by command line options. The command line option Sg—which stands for "skip grant tables"—starts up the server without grant tables, giving all users full access to all tables. With default installation on a Linux machine, the database tables would ordinarily be stored in the directory /var/lib/mysql. The command

            mysqld --help

shows all the options with which the daemon server program can be run. The following command when entered as root shuts down the server on a Linux machine

            mysqladmin -u root shutdown

With default options, the server daemon will ordinarily monitor port 3306 for incoming connections.

^[4]MM.MySQL is available free from http://mmmysql.sourceforge.net/