You need to establish a connection to the server to access a database, and to shut down the connection when you’re done.
Each 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.
The programs in this section show how to perform three fundamental operations that are common to the vast majority of MySQL programs:
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 that is running the server, as well as the username and password for the MySQL account to use for accessing the server.
Most MySQL programs select a default database.
Each API provides a way to close an open connection. It’s best to close the connection as soon as you’re done using 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 enables the server to perform an immediate orderly close on its end.
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.
To 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 "; print "Connected "; $dbh->disconnect (); print "Disconnected ";
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:
.
The second colon in the DSN is not optional,
even if you specify no options.options
The three DSN components 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 that specify additional connection options. The order of
any option pairs 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 default database you
want to use.
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 ";
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});
Checking for Errors 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";
To 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:
The first component is always DBI
or dbi
.
The second component tells DBI which database driver to
use. For MySQL, the name is Mysql
.
The third component, if present, is either a database name
and hostname separated by a colon, or a semicolon-separated list
of name
=
value
pairs that specify additional connection options. The order of
any option pairs you provide doesn’t matter. For our purposes
here, the two most relevant options are host
and database
. They specify the hostname of
the server on which MySQL is running and the default database
you want to use. As with Perl DBI, the second colon in the DSN
is not optional, even if you specify no
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, 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"
To 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 Using Apache to Run Web Scripts.
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! "); ?> </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 "); print ("Connected "); $conn->disconnect (); print ("Disconnected "); ?>
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:
include
instructs PHP
to read the named file. require
is like include
except that PHP reads the file
even if the require
occurs
inside a control structure that never executes (such as an
if
block for which the
condition is never true
).
include_once
and
require_once
are
likeinclude
and
require
except that if the
file has already been read, its contents are not processed
again. This is useful for avoiding multiple-declaration problems
that can easily occur in situations where library files include
other library files.
$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 Using MySQL-Based Storage with the PHP Session Manager 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 ");
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 ");
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 ");
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.
To 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")
Database 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.[7] 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";
[7] 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.
18.227.134.133