Recipe 2.1. Connecting, Selecting a Database, and Disconnecting
ProblemYou need to establish a connection to the server to access a database, and to shut down the connection when you're done. SolutionEach API provides routines for connecting and disconnecting. The connection routines require that you provide parameters specifying the hostname that is running the MySQL server and the MySQL account that you want to use. You can also select a default database. DiscussionThe programs in this section show how to perform three fundamental operations that are common to the vast majority of MySQL programs:
The example programs for each API in this section show how to connect to the server, select the cookbook database, and disconnect. On occasion you might want to write a MySQL program that doesn't select a database. This could be the case if you plan to issue a statement that doesn't require a default database, such as SHOW VARIABLES or SELECT VERSION( ). Or perhaps you're writing an interactive program that connects to the server and enables 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 default database. PerlTo write MySQL scripts in Perl, you should have the DBI module installed, as well as the MySQL-specific driver module, DBD::mysql. Appendix A contains information on getting these modules if they're not already installed. Here is a simple Perl script that connects to the cookbook database and then disconnects: #!/usr/bin/perl # connect.pl - connect to the MySQL server use strict; use warnings; use DBI; my $dsn = "DBI:mysql:host=localhost;database=cookbook"; my $dbh = DBI->connect ($dsn, "cbuser", "cbpass") or die "Cannot connect to server\n"; print "Connected\n"; $dbh->disconnect (); print "Disconnected\n"; To try the script, create a file named connect.pl that contains the preceding code and run it from the command line. (Under Unix, you may need to change the path on the first line of the script if your Perl program is located somewhere other than /usr/bin/perl.) You should see the program print two lines of output indicating that it connected and disconnected successfully: % connect.pl Connected Disconnected If you need background on running Perl programs, see Appendix B. 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 warnings line 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 enabling warnings to catch problems that occur during the script development process. use warnings is similar to specifying the Perl -w command-line option, but provides more control over which warnings you want to see. (Execute a perldoc warnings command for more information.) The use DBI statement tells Perl that the program needs to load the DBI module. It's unnecessary to load the MySQL driver module (DBD::mysql) explicitly, because DBI does 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 and 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 username and password. 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 second colon in the DSN is not optional, even if you specify no options. The three DSN components have the following meanings:
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, the connect( ) operation selects no default database. 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. With no attributes, DBI by default 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\n"; 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 (although you also lose the ability to decide how your program will recover from errors): my $dbh = DBI->connect ($dsn, $user_name, $password, {PrintError => 0, RaiseError => 1}); Section 2.2 discusses error handling further. Another common attribute is AutoCommit, which sets the connection's auto-commit mode for transactions. In MySQL, this is enabled by default for new connections, but we'll set it from this point on to make the initial connection state explicit: my $dbh = DBI->connect ($dsn, $user_name, $password, {PrintError => 0, RaiseError => 1, AutoCommit => 1}); As shown, the fourth argument to connect( ) is a reference to a hash of connection attribute name/value pairs. An alternative way of writing this code is as follows: my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1); my $dbh = DBI->connect ($dsn, $user_name, $password, \%conn_attrs); Use whichever style you prefer. The scripts in this book use the %conn_attr hash to make the connect( ) call simpler to read. 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, which are associated with particular statements. Perl DBI scripts in this book conventionally use $dbh and $sth to signify database and statement handles. Additional connection parameters. For localhost connections, 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;database=cookbook" . ";mysql_socket=/var/tmp/mysql.sock"; For non-localhost (TCP/IP) connections, you can provide a port option to specify the port number: my $dsn = "DBI:mysql:host=mysql.example.com;database=cookbook" . ";port=3307"; RubyTo write MySQL scripts in Ruby, you should have the DBI module installed, as well as the MySQL-specific driver module. Both are included in the Ruby DBI distribution. Appendix A contains information on getting Ruby DBI if it's not already installed. Here is a simple Ruby script that connects to the cookbook database and then disconnects: #!/usr/bin/ruby -w # connect.rb - connect to the MySQL server require "dbi" begin dsn = "DBI:Mysql:host=localhost;database=cookbook" dbh = DBI.connect(dsn, "cbuser", "cbpass") puts "Connected" rescue puts "Cannot connect to server" exit(1) end dbh.disconnect puts "Disconnected" To try the script, create a file named connect.rb that contains the preceding code. (Under Unix, you may need to change the path on the first line of the script if your Ruby program is located somewhere other than /usr/bin/ruby.) You should see the program print two lines of output indicating that it connected and disconnected successfully: % connect.rb Connected Disconnected If you need background on running Ruby programs, see Appendix B. The -w option turns on warning mode so that Ruby 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 to catch problems that occur during the script development process. The require statement tells Ruby that the program needs to load the DBI module. It's unnecessary to load the MySQL driver module explicitly, because DBI does that itself when the script connects to the database server. The connection is established by passing a data source name and the MySQL username and password to the connect( ) method. The DSN is required. The other arguments are optional, although usually it's necessary to supply a username and password. The DSN specifies which database driver to use and other options that indicate where to connect. For MySQL programs, the DSN typically has one of these formats: DBI:Mysql:db_name:host_name DBI:Mysql:name=value;name=value ... The DSN components have the following meanings:
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, the connect( ) operation selects no default database. Assuming that connect( ) succeeds, it returns a database handle that contains information about the state of the connection. Ruby DBI scripts in this book conventionally use dbh to signify a database handle. If the connect( ) method fails, there is no special return value to check for. Ruby programs raise exceptions when problems occur. To handle errors, put the statements that might fail inside a begin block, and use a rescue clause that contains the error-handling code. Exceptions that occur at the top level of a script (that is, outside of any begin block) are caught by the default exception handler, which prints a stack trace and exits. Additional connection parameters. For localhost connections, you can provide a socket option in the DSN to specify the path to the Unix domain socket: dsn = "DBI:Mysql:host=localhost;database=cookbook" + ";socket=/var/tmp/mysql.sock" For non-localhost (TCP/IP) connections, you can provide a port option to specify the port number: dsn = "DBI:Mysql:host=mysql.example.com;database=cookbook" + ";port=3307" PHPTo write PHP scripts that use MySQL, your PHP interpreter must have MySQL support compiled in. If it doesn't, your scripts will be unable to connect to your MySQL server. Should that occur, check the instructions included with your PHP distribution to see how to enable MySQL support. PHP actually has two extensions that enable the use of MySQL. The first, mysql, is the original MySQL extension. It provides a set of functions that have names beginning with mysql_. The second, mysqli, or "MySQL improved," provides functions with names that begin with mysqli_. For purposes of this book, you can use either extension, although I recommend mysqli. In any case, PHP scripts in this book won't use either extension directly. Instead, they use the DB module from the PHP Extension and Add-on Repository (PEAR) . The PEAR DB module provides an interface to whichever underlying MySQL extension that you decide to use. This means that in addition to whichever PHP MySQL extension you choose, it's also necessary to have PEAR installed. Appendix A contains information on getting PEAR if it's not already installed. 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 copy 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 localhost 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://localhost/myscript.php This book uses the .php extension (suffix) for PHP script filenames, so your web server must be configured to recognize the .php extension. Otherwise, when you request a PHP script from your browser, the server will simply send the literal text of the script and that is what you'll see in your browser window. You don't want this to happen, particularly if the script contains the username and password that you use for connecting to MySQL. For information about configuring Apache for use with PHP, see Section 17.2. PHP scripts often are written as a mixture of HTML and PHP code, with the PHP code embedded between the special <?php and ?> tags. Here is a simple example: <html> <head><title>A simple page</title></head> <body> <p> <?php print ("I am PHP code, hear me roar!\n"); ?> </p> </body> </html> PHP can be configured to recognize "short" tags as well, which are written as <? and ?>. This book does not assume that you have short tags enabled, so none of the PHP scripts shown here use them. Here is a simple PHP script that connects to the cookbook database and then disconnects: <?php # connect.php - connect to the MySQL server require_once "DB.php"; $dsn = "mysqli://cbuser:cbpass@localhost/cookbook"; $conn =& DB::connect ($dsn); if (PEAR::isError ($conn)) die ("Cannot connect to server\n"); print ("Connected\n"); $conn->disconnect (); print ("Disconnected\n"); ?> For brevity, when I show examples consisting entirely of PHP code, typically I'll omit the enclosing <?php and ?> tags. (Thus, if you see no tags in a PHP example, assume that <?php and ?> surround the entire block of code that is shown.) Examples that switch between HTML and PHP code do include the tags, to make it clear what is PHP code and what is not. The require_once statement accesses the DB.php file that is required to use the PEAR DB module. require_once is just one of several PHP file-inclusion statements:
$dsn is the data source name that indicates how to connect to the database server. Its general syntax is as follows: phptype://user_name:password@host_name/db_name The phptype value is the PHP driver type. For MySQL, it should be either mysql or mysqli to indicate which MySQL extension to use. You can choose either one, as long as your PHP interpreter has the chosen extension compiled in. The PEAR DB connect( ) method uses the DSN to connect to MySQL. If the connection attempt succeeds, connect( ) returns a connection object that can be used to access other MySQL-related methods. PHP scripts in this book conventionally use $conn to signify connection objects. If the connection attempt fails, connect( ) returns an error object. To determine whether the returned object represents an error, use the PEAR::isError( ) method. Note that the assignment of the connect( ) result uses the =& operator and not the = operator. =& assigns a reference to the return value, whereas = creates a copy of the value. In this context, = would create another object that is not needed. (PHP scripts in this book generally use =& for assigning the result of connection attempts, but see Section 20.3 for one instance that uses = to make sure that the assigned connection object persists longer than the function call in which it occurs.) The last part of the DSN shown in the preceding example is the database name. To connect without selecting a default database, just omit it from the end of the DSN: $dsn = "mysqli://cbuser:cbpass@localhost"; $conn =& DB::connect ($dsn); To try the connect.php script, copy it to your web server's document tree and request it using 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 connect.php Connected Disconnected If you need background on running PHP programs, see Appendix B. As an alternative to specifying the DSN in string format, you can provide the connection parameters using an array: $dsn = array ( "phptype" => "mysqli", "username" => "cbuser", "password" => "cbpass", "hostspec" => "localhost", "database" => "cookbook" ); $conn =& DB::connect ($dsn); if (PEAR::isError ($conn)) print ("Cannot connect to server\n"); To connect without selecting a default database using an array-format DSN, omit the database member from the array. Additional connection parameters. To use a specific Unix domain socket file or TCP/IP port number, modify the parameters used at connect time. The following two examples use an array-format DSN to do this. For localhost connections, you can specify a pathname for the Unix domain socket file by including a socket member in the DSN array: $dsn = array ( "phptype" => "mysqli", "username" => "cbuser", "password" => "cbpass", "hostspec" => "localhost", "socket" => "/var/tmp/mysql.sock", "database" => "cookbook" ); $conn =& DB::connect ($dsn); if (PEAR::isError ($conn)) print ("Cannot connect to server\n"); For non-localhost (TCP/IP) connections, you can specify the port number by including a port member in the DSN array: $dsn = array ( "phptype" => "mysqli", "username" => "cbuser", "password" => "cbpass", "hostspec" => "mysql.example.com", "port" => 3307, "database" => "cookbook" ); $conn =& DB::connect ($dsn); if (PEAR::isError ($conn)) print ("Cannot connect to server\n"); You can use the PHP initialization file (typically named php.ini) to specify a default hostname, username, password, socket path, or port number. For the mysql extension, set the values of the mysql.default_host, mysql.default_user, mysql.default_password, mysql.default_socket, or mysql.default_port configuration variables. For mysqli, the corresponding variable names begin with mysqli (and the password variable is mysql_default_pw). These variables affect PHP scripts globally: for scripts that do not specify those parameters, the defaults from php.ini are used. PythonTo write MySQL programs in Python, you need the MySQLdb module that provides MySQL connectivity for Python's DB-API interface. Appendix A, contains information on getting MySQLdb if it's not already installed. To use the DB-API interface, import the database driver module that 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" To try the script, create a file named connect.py that contains the preceding code. (Under Unix, you may need to change the path on the first line of the script if your Python program is located somewhere other than /usr/bin/python.) You should see the program print two lines of output indicating that it connected and disconnected successfully: % connect.py Connected Disconnected If you need background on running Python programs, see Appendix B. The import lines give the script access to the sys module (needed for the sys.exit( ) method) 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 connect( ) method fails, there is no special return value to check for. Python programs raise exceptions when problems occur. To handle errors, put the statements that might fail inside a TRy statement and use an except clause that contains the error-handling code. Exceptions that occur at the top level of a script (that is, outside of any try statement) are caught by the default exception handler, which prints a stack trace and exits. Because the connect( ) call uses named arguments, their order does not matter. If you omit the host argument from the connect( ) call, its default value is localhost. If you omit the db argument or pass a db value of "" (the empty string), the connect( ) operation selects no default database. If you pass a value of None, however, the call will fail. Additional connection parameters. For localhost connections, you can provide a unix_socket parameter to specify the path to the Unix domain socket file: conn = MySQLdb.connect (db = "cookbook", host = "localhost", unix_socket = "/var/tmp/mysql.sock", user = "cbuser", passwd = "cbpass") For non-localhost (TCP/IP) connections, you can provide a port parameter to specify the port number: conn = MySQLdb.connect (db = "cookbook", host = "mysql.example.com", port = 3307, user = "cbuser", passwd = "cbpass") JavaDatabase programs in Java are written using the JDBC interface, together with a driver for the particular database engine you want to access. That is, the JDBC architecture provides a generic interface used in conjunction with a database-specific driver. Java is similar to Ruby and Python in that you don't test specific method calls for return values that indicate an error. Instead, you provide handlers to be called when exceptions are thrown. Java programming requires a software development kit (SDK), and you will need to set your JAVA_HOME environment variable to the location where your SDK is installed. To write MySQL-based Java programs, you'll also need a MySQL-specific JDBC driver. Programs in this book use MySQL Connector/J, the driver provided by MySQL AB. Appendix A, has information on getting MySQL Connector/J if it's not already installed. Appendix B, has information about obtaining an SDK and setting JAVA_HOME. The following Java program, Connect.java, illustrates how to connect to and disconnect from the MySQL server, and select cookbook as the default database: // 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"); System.exit (1); } 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( ). The Class.forName( ) method requires a driver name; for MySQL Connector/J, use com.mysql.jdbc.Driver. 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. DriverManager.getConnection( ) takes three arguments: a URL that describes where to connect and the database to use, the MySQL username, and the password. The URL string has this format: jdbc:driver://host_name/db_name This format follows the 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, the connect operation selects no default database. However, you should not omit any of the slashes in any case. For example, to connect to the local host without selecting a default database, the URL is: jdbc:mysql:/// To try the program, 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 program is Connect.java.[*] Compile the program using javac:
% javac Connect.java If you prefer a different Java compiler, just substitute its name for javac. The Java compiler generates compiled byte code to produce a class file named Connect.class. Use the java program to run the class file (specified without the .class extension): % java Connect Connected Disconnected You might 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. If you need background on running Java programs or setting CLASSPATH, see Appendix B.
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 can be 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 ;. Additional connection parameters. MySQL Connector/J does not support Unix domain socket file connections, so even connections for which the hostname is localhost are made via TCP/IP. You can specify an explicit port number by adding : port_num to the hostname in the connection URL: String url = "jdbc:mysql://mysql.example.com:3307/cookbook"; |
