Connecting to the MySQL Server, Selecting a Database, and Disconnecting

2.2.1 Problem

You need to establish a connection to the server to access a database, and to shut down the connection when you're done.

2.2.2 Solution

Each API provides functions for connecting and disconnecting. The connection routines require that you provide parameters specifying the MySQL user account you want to use. You can also specify a database to use. Some APIs allow this at connection time; others require a separate call after connecting.

2.2.3 Discussion

The programs in this section show how to perform three fundamental operations that are common to the vast majority of MySQL programs:

• Establishing a connection to the MySQL server.

  Every program that uses MySQL does this, no matter which API you use. The details on specifying connection parameters vary between APIs, and some APIs provide more flexibility than others. However, there are many common elements. For example, you must specify the host where the server is running, as well as the name and password for the MySQL account that you're using to access the server.

• Selecting a database.

  Most MySQL programs select a database, either when they connect to the server or immediately thereafter.

• Disconnecting from the server

  Each API provides a means of shutting down an open connection. It's best to close the connection as soon as you're done with the server so that it can free up any resources that are allocated to servicing the connection. Otherwise, if your program performs additional computations after accessing the server, the connection will be held open longer than necessary. It's also preferable to close the connection explicitly. If a program simply terminates without closing the connection, the MySQL server eventually notices, but shutting down the connection explicitly allows the server to perform an orderly close on its end immediately.

Our example programs for each API in this section show how to connect to the server, select the cookbook database, and disconnect. However, on occasion you might want to write a MySQL program that doesn't select a database. This would be the case if you plan to issue a query that doesn't require a default database, such as SHOW VARIABLES or SHOW DATABASES. Or perhaps you're writing an interactive program that connects to the server and allows the user to specify the database after the connection has been made. To cover such situations, the discussion for each API also indicates how to connect without selecting any database.

2.2.4 Perl

To write MySQL scripts in Perl, you should have the DBI module installed, as well as the MySQL-specific DBI driver module, DBD::mysql. Appendix A contains information on getting these if they're not already installed. There is an older interface for Perl named MysqlPerl, but it's obsolete and is not covered here.

Here is a simple Perl script that connects to the cookbook database, then disconnects:

#! /usr/bin/perl -w # connect.pl - connect to the MySQL server use strict; use DBI; my $dsn = "DBI:mysql:host=localhost;database=cookbook"; my $dbh = DBI->connect ($dsn, "cbuser", "cbpass") or die "Cannot connect to server "; print "Connected "; $dbh->disconnect ( ); print "Disconnected "; exit (0);

To try the script, create a file named connect.pl that contains the preceding code. To run connect.pl under Unix, you may need to change the pathname on the first line if your Perl program is located somewhere other than /usr/bin/perl. Then make the script executable with chmod +x, and invoke it as follows:

% chmod +x connect.pl % ./connect.pl Connected Disconnected

Under Windows, chmod will not be necessary; you run connect.pl like this:

C:> perl connect.pl Connected Disconnected

If you have a filename association set up that allows .pl files to be executed directly from the command line, you need not invoke Perl explicitly:

C:> connect.pl Connected Disconnected

For more information on running programs that you've written yourself, see the sidebar "Using Executable Programs" in Recipe 1.33.

The -w option turns on warning mode so that Perl produces warnings for any questionable constructs. Our example script has no such constructs, but it's a good idea to get in the habit of using -w; as you modify your scripts during the development process, you'll often find that Perl has useful comments to make about them.

The use strict line turns on strict variable checking and causes Perl to complain about any variables that are used without having been declared first. This is a sensible precaution because it helps find errors that might otherwise go undetected. The use DBI statement tells Perl that the program needs to use the DBI module. It's unnecessary to load the MySQL driver module (DBD::mysql) explicitly, because DBI will do that itself when the script connects to the database server.

The next two lines establish the connection to MySQL by setting up a data source name (DSN) and calling the DBI connect( ) method. The arguments to connect( ) are the DSN, the MySQL username, the password, and any connection attributes you want to specify. The DSN is required. The other arguments are optional, although usually it's necessary to supply a name and password to get very far.

The DSN specifies which database driver to use and other options indicating where to connect. For MySQL programs, the DSN has the format DBI:mysql:options. The three components of which have the following meanings:

• The first component is always DBI. It's not case sensitive; dbi or Dbi would do just as well.
• The second component tells DBI which database driver to use. For MySQL, the name must be mysql and it is case sensitive. You can't use MySQL, MYSQL, or any other variation.
• The third component, if present, is a semicolon-separated list of name=value pairs specifying additional connection options. The order of any options you provide doesn't matter. For our purposes here, the two most relevant options are host and database. They specify the hostname where the MySQL server is running and the database you want to use. Note that the second colon in the DSN is not optional, even if you don't specify any options.

Given this information, the DSN for connecting to the cookbook database on the local host localhost looks like this:

DBI:mysql:host=localhost;database=cookbook

If you leave out the host option, its default value is localhost. Thus, these two DSNs are equivalent:

DBI:mysql:host=localhost;database=cookbook DBI:mysql:database=cookbook

If you omit the database option, no database is selected when you connect.

The second and third arguments of the connect( ) call are your MySQL username and password. You can also provide a fourth argument following the password to specify attributes that control DBI's behavior when errors occur. By default, DBI prints error messages when errors occur but does not terminate your script. That's why connect.pl checks whether connect( ) returns undef to indicate failure:

my $dbh = DBI->connect ($dsn, "cbuser", "cbpass") or die "Cannot connect to server ";

Other error-handling strategies are possible. For example, you can tell DBI to terminate the script automatically when an error occurs in a DBI call by disabling the PrintError attribute and enabling RaiseError instead. Then you don't have to check for errors yourself:

my $dbh = DBI->connect ($dsn, $user_name, $password, {PrintError => 0, RaiseError => 1});

Error handling is discussed further in Recipe 2.3.

Assuming that connect( ) succeeds, it returns a database handle that contains information about the state of the connection. (In DBI parlance, references to objects are called "handles.") Later we'll see other handles, such as statement handles that are associated with particular queries. DBI scripts in this book conventionally use $dbh and $sth to signify database and statement handles.

2.2.4.1 Additional connection parameters

For connections to localhost, you can provide a mysql_socket option in the DSN to specify the path to the Unix domain socket:

my $dsn = "DBI:mysql:host=localhost;mysql_socket=/var/tmp/mysql.sock" . ";database=cookbook";

The mysql_socket option is available as of MySQL 3.21.15.

For non-localhost connections, you can provide a port option to specify the port number:

my $dsn = "DBI:mysql:host=mysql.snake.net;port=3307;database=cookbook";

2.2.5 PHP

To write PHP scripts that use MySQL, your PHP interpreter must have MySQL support compiled in. If it doesn't, your scripts will terminate with an error message like this:

Fatal error: Call to undefined function: mysql_connect( )

Should that occur, check the instructions included with your PHP distribution to see how to enable MySQL support.

PHP scripts usually are written for use with a web server. I'll assume that if you're going to use PHP that way here, you can simply drop PHP scripts into your server's document tree, request them from your browser, and they will execute. For example, if you run Apache as the web server on the host http://apache.snake.net/ and you install a PHP script myscript.php at the top level of the Apache document tree, you should be able to access the script by requesting this URL:

http://apache.snake.net/myscript.php

This book uses the .php extension (suffix) for PHP script filenames. If you use a different extension, such as .php3 or .phtml, you'll need to change the script names or else reconfigure your web server to recognize the .php extension. Otherwise, when you request a PHP script from your browser, the literal text of the script will appear in your browser window. You don't want this to happen, particularly if the script contains the username and password you use for connecting to MySQL. (For additional information about configuring Apache for use with PHP, see Recipe 16.3.)

PHP scripts often are written as a mixture of HTML and PHP code, with the PHP code embedded between the special and ?> tags. Here is a simple example:

A simple page

For brevity, when I show PHP examples consisting entirely of code, typically I'll omit the enclosing and ?> tags. Examples that switch between HTML and PHP code include the tags.

To use MySQL in a PHP script, you connect to the MySQL server and select a database in two steps, by calling the mysql_connect( ) and mysql_select_db( ) functions. Our first PHP script, connect.php, shows how this works:

# connect.php - connect to the MySQL server if (!($conn_id = @mysql_connect ("localhost", "cbuser", "cbpass"))) die ("Cannot connect to server "); print ("Connected "); if (!@mysql_select_db ("cookbook", $conn_id)) die ("Cannot select database "); mysql_close ($conn_id); print ("Disconnected ");

mysql_connect( ) takes three arguments: the host where the MySQL server is running, and the name and password of the MySQL account you want to use. If the connection attempt succeeds, mysql_connect( ) returns a connection identifier that can be passed to other MySQL-related functions later. PHP scripts in this book conventionally use $conn_id to signify connection identifiers.

If the connection attempt fails, mysql_connect( ) prints a warning and returns FALSE. (The script prevents any such warning by putting @ (the warning-suppression operator) in front of the function name so it can print its own message instead.)

mysql_select_db( ) takes the database name and an optional connection identifier as arguments. If you omit the second argument, the function assumes it should use the current connection (that is, the one most recently opened). The script just shown calls mysql_select_db( ) immediately after it connects, so the following calls are equivalent:

if (!@mysql_select_db ("cookbook", $conn_id)) die ("Cannot select database "); if (!@mysql_select_db ("cookbook")) die ("Cannot select database ");

If mysql_select_db( ) selects the database successfully, it returns TRUE. Otherwise, it prints a warning and returns FALSE. (Again, as with the mysql_connect( ) call, the script uses the @ operator to suppress the warning.) If you don't want to select any database, just omit the call to mysql_select_db( ).

To try the connect.php script, copy it to your web server's document tree and request it from your browser. Alternatively, if you have a standalone version of the PHP interpreter that can be run from the command line, you can try the script without a web server or browser:

% php -q connect.php Connected Disconnected

PHP actually provides two functions for connecting to the MySQL server. The script connect.php uses mysql_connect( ), but you can use mysql_pconnect( ) instead if you want to establish a persistent connection that doesn't close when the script terminates. This allows the connection to be reused by subsequent PHP scripts run by the web server, thus avoiding the overhead of setting up a new connection. However, MySQL is so efficient at opening connections that you might not notice much difference between the two functions. Also, you should consider that use of mysql_pconnect( ) sometimes results in too many connections being left open. A symptom of this is that the MySQL server stops accepting new connections because so many persistent connections have been opened by web server processes. Using mysql_connect( ) rather than mysql_pconnect( ) may help to avoid this problem.

2.2.5.1 Additional connection parameters

For connections to localhost, you can specify a pathname for the Unix domain socket by adding :/path/to/socket to the hostname in the connect call:

$hostname = "localhost:/var/tmp/mysql.sock"; if (!($conn_id = @mysql_connect ($hostname, "cbuser", "cbpass"))) die ("Cannot connect to server ");

For non-localhost, connections, you can specify a port number by adding :port_num to the hostname:

$hostname = "mysql.snake.net:3307"; if (!($conn_id = @mysql_connect ($hostname, "cbuser", "cbpass"))) die ("Cannot connect to server ");

The socket pathname option is available as of PHP 3.0.B4. The port number option is available as of PHP 3.0.10.

In PHP 4, you can use the PHP initialization file to specify a default hostname, username, password, socket path, or port number by setting the values of the mysql.default_host, mysql.default_user, mysql.default_password, mysql.default_socket, or mysql.default_port configuration directives.

2.2.6 Python

To write MySQL programs in Python, you need the MySQLdb module that provides MySQL connectivity for Python's DB-API interface. If you don't have this module, see Appendix A for instructions. DB-API, like Perl's DBI module, provides a relatively database-independent way to access database servers, and supplants earlier Python DBMS-access modules that each had their own interfaces and calling conventions. This book doesn't cover the older, obsolete MySQL Python interface.

Python avoids the use of functions that return a special value to indicate the occurrence of an error. In other words, you typically don't write code like this:

if (func1 ( ) == some_bad_value or func2 () == another_bad_value): print "An error occurred" else: print "No error occurred"

Instead, put the statements you want to execute in a try block. Errors cause exceptions to be raised that you can catch with an except block containing the error handling code:

try: func1 ( ) func2 ( ) except: print "An error occurred"

Exceptions that occur at the top level of a script (that is, outside of any try block) are caught by the default exception handler, which prints a stack trace and exits.

To use the DB-API interface, import the database driver module you want to use (which is MySQLdb for MySQL programs). Then create a database connection object by calling the driver's connect( ) method. This object provides access to other DB-API methods, such as the close( ) method that severs the connection to the database server. Here is a short Python program, connect.py, that illustrates these operations:

#! /usr/bin/python # connect.py - connect to the MySQL server import sys import MySQLdb try: conn = MySQLdb.connect (db = "cookbook", host = "localhost", user = "cbuser", passwd = "cbpass") print "Connected" except: print "Cannot connect to server" sys.exit (1) conn.close ( ) print "Disconnected" sys.exit (0)

The import lines give the script access to the sys module (needed for the sys.exit( ) function) and to the MySQLdb module. Then the script attempts to establish a connection to the MySQL server by calling connect( ) to obtain a connection object, conn. Python scripts in this book conventionally use conn to signify connection objects.

If the connection cannot be established, an exception occurs and the script prints an error message. Otherwise, it closes the connection by using the close( ) method.

Because the arguments to connect( ) are named, their order does not matter. If you omit the host argument from the connect( ) call, its default value is localhost. If you leave out the db argument or pass a db value of "" (the empty string), no database is selected. If you pass a value of None, however, the call will fail.

To try the script, create a file called connect.py containing the code just shown. Under Unix, you may need to change the path to Python on the first line of the script if your Python interpreter is located somewhere other than /usr/bin/python. Then make the script executable with chmod +x and run it:

% chmod +x connect.py % ./connect.py Connected Disconnected

Under Windows, run the script like this:

C:> python connect.py Connected Disconnected

If you have a filename association set up that allows .py files to be executed directly from the command line, you need not invoke Python explicitly:

C:> connect.py Connected Disconnected

2.2.6.1 Additional connection parameters

For connections to localhost, you can provide a unix_socket parameter to specify the path to the Unix domain socket:

conn = MySQLdb.connect (db = "cookbook", host = "localhost", unix_sock = "/var/tmp/mysql.sock", user = "cbuser", passwd = "cbpass")

For non-localhost connections, you can provide a port parameter to specify the port number:

conn = MySQLdb.connect (db = "cookbook", host = "mysql.snake.net", port = 3307, user = "cbuser", passwd = "cbpass")

2.2.7 Java

Database programs in Java are written using the JDBC interface, in conjunction with a driver for the particular database engine you wish to access. This makes the JDBC architecture similar to that used by the Perl DBI and Python DB-API modules: a generic interface used in conjunction with database-specific drivers. Java itself is similar to Python in that you don't test specific function calls for return values that indicate an error. Instead, you provide handlers that are called when exceptions are thrown.

Java programming requires a software development kit (SDK). See the sidebar, Installing a Java SDK for instructions on installing one if you need it. To write MySQL-based Java programs, you'll also need a MySQL-specific JDBC driver. Several are listed in Appendix A. I use the MySQL Connector/J driver because it is free and is actively maintained; use one of the other drivers if you prefer. (MySQL Connector/J is the successor to MM.MySQL, and if you already have MM.MySQL installed, you can use it instead by making a simple change: whenever you see org.gjt.mm.mysql in Java code, replace it with com.mysql.jdbc.)

The following Java program, Connect.java, illustrates how to connect to and disconnect from the MySQL server:

// Connect.java - connect to the MySQL server import java.sql.*; public class Connect { public static void main (String[ ] args) { Connection conn = null; String url = "jdbc:mysql://localhost/cookbook"; String userName = "cbuser"; String password = "cbpass"; try { Class.forName ("com.mysql.jdbc.Driver").newInstance ( ); conn = DriverManager.getConnection (url, userName, password); System.out.println ("Connected"); } catch (Exception e) { System.err.println ("Cannot connect to server"); } finally { if (conn != null) { try { conn.close ( ); System.out.println ("Disconnected"); } catch (Exception e) { /* ignore close errors */ } } } } }

The import java.sql.* statement references the classes and interfaces that provide access to the data types you use to manage different aspects of your interaction with the database server. These are required for all JDBC programs.

Connecting to the server is a two-step process. First, register the database driver with JDBC by calling Class.forName( ). Then call DriverManager.getConnection( ) to initiate the connection and obtain a Connection object that maintains information about the state of the connection. Java programs in this book conventionally use conn to signify connection objects.

Use com.mysql.jdbc.Driver for the name of the MySQL Connector/J JDBC driver. If you use a different driver, check its documentation and use the name specified there. DriverManager.getConnection( ) takes three arguments: a URL describing where to connect and the database to use, the MySQL username, and the password. The format of the URL string is as follows:

jdbc:driver://host_name/db_name

This format follows the usual Java convention that the URL for connecting to a network resource begins with a protocol designator. For JDBC programs, the protocol is jdbc, and you'll also need a subprotocol designator that specifies the driver name (mysql, for MySQL programs). Many parts of the connection URL are optional, but the leading protocol and subprotocol designators are not. If you omit host_name, the default host value is localhost. If you omit the database name, no database is selected when you connect. However, you should not omit any of the slashes in any case. For example, to connect to the local host without selecting a database name, the URL is:

jdbc:mysql:///

To try out the program, you should compile it and execute it. The class statement indicates the program's name, which in this case is Connect. The name of the file containing the program should match this name and include a .java extension, so the filename for the example program is Connect.java.[1] Compile the program using javac:

[1] If you make a copy of Connect.java to use as the basis for a new program, you'll need to change the class name in the class statement to match the name of your new file.

% javac Connect.java

If you prefer a different Java compiler, just substitute its name in compilation commands. For example, if you'd rather use Jikes, compile the file like this instead:

% jikes Connect.java

javac (or jikes, or whatever) generates compiled byte code to produce a class file named Connect.class. Use the java program to run the class file (note that you specify the name of the class file without the .class extension):

% java Connect Connected Disconnected

You may need to set your CLASSPATH environment variable before the example program will compile and run. The value of CLASSPATH should include at least your current directory (.) and the path to the MySQL Connector/J JDBC driver. On my system, that driver is located in /usr/local/lib/java/lib/mysql-connector-java-bin.jar, so for tcsh or csh, I'd set CLASSPATH like this:

setenv CLASSPATH .:/usr/local/lib/java/lib/mysql-connector-java-bin.jar

For shells such as sh, bash, and ksh, I'd set it like this:

export CLASSPATH=.:/usr/local/lib/java/lib/mysql-connector-java-bin.jar

Under Windows, I'd set CLASSPATH as follows if the driver is in the D:Javalib directory:

CLASSPATH=.;D:Javalibmysql-connector-java-bin.jar

You may also need to add other class directories or libraries to your CLASSPATH setting; the specifics depend on how your system is set up.

Some JDBC drivers (MySQL Connector/J among them) allow you to specify the username and password as parameters at the end of the URL. In this case, you omit the second and third arguments of the getConnection( ) call. Using that URL style, the code that establishes the connection in the example program could have been written like this:

// connect using username and password included in URL Connection conn = null; String url = "jdbc:mysql://localhost/cookbook?user=cbuser&password=cbpass"; try { Class.forName ("com.mysql.jdbc.Driver").newInstance ( ); conn = DriverManager.getConnection (url); System.out.println ("Connected"); }

The character that separates the user and password parameters should be &, not ;.

2.2.7.1 Additional connection parameters

For non-localhost connections, specify an explicit port number by adding :port_num to the hostname in the connection URL:

String url = "jdbc:mysql://mysql.snake.net:3307/cookbook";

For connections to localhost, there is no option for specifying the Unix domain socket pathname, at least not for MySQL Connector/J. Other MySQL JDBC drivers may allow for this; check their documentation.