| Once you know that you can successfully get a Perl script to work and that the mysql DBI module is present, you can start writing scripts that interact with the database. The first step in doing so is to connect to the server. For this you use the DBI->connect() method. Its first argument is a string of text in the form of DBI:mysql:database:host:port. The host and port are optional. The next two arguments are the username and the password. A full connection attempt might look like this: $dbh = DBI->connect('DBI:mysql: accounting:localhost', 'username', 'password');
The first argument in the preceding line, DBI:mysql:accounting:localhost, referred to as the data source name, is sometimes assigned to a $dsn variable and then incorporated into this function: my $dsn = 'DBI:mysql:accounting:localhost'; $dbh = DBI->connect($dsn, 'username', 'password');
The username, hostname, and password values are those you need to connect to MySQL from any application. These values should be the same as those used to connect through the mysql client. I'll discuss error management later in the chapter, but another, optional, argument should be used in calling the function. By setting the RaiseError flag to true (give it a value of 1), any MySQL errors will be reported: [View full width] $dbh = DBI->connect('DBI:mysql:accounting: localhost, 'username', 'password', {RaiseError  => 1})
The result of the DBI->connect() line is assigned to a $dbh (database handle) variable that the rest of the script will refer to. Whenever you use the DBI->connect() method, you also need to use disconnect() when the script has concluded using the database: $dbh->disconnect();
For the first MySQL-Perl script, I'll use these techniques to simply connect to and disconnect from the database. Once you have this working, you can begin to execute queries. To connect to MySQL: 1. | Create a new text document in your text editor.
| 2. | Start with the shebang line, if necessary (Script 8.2).
#!/usr/bin/perl -w
| 3. | Enforce strict programming and incorporate the DBI module.
use strict; use DBI;
| 4. | Connect to MySQL.
[View full width] my $dbh = DBI->connect('DBI:mysql: accounting:localhost', 'username', 'password', {RaiseError => 1});
For this example, I will be using the accounting database that has been discussed throughout the book. Change the username and password values to whatever has permission to access this database on your server.
Script 8.2. The mysql_connect.pl script merely checks for a successful connection to the MySQL server.| [View full width] 1 #!/usr/bin/perl -w 2 3 # Script 8.2 - mysql_connect.pl 4 # This script attempts to connect 5 # to the database and reports 6 # upon the results. 7 8 # Use what needs to be used. 9 use strict; 10 use DBI; 11 12 # Connect to the database. 13 my $dbh = DBI->connect('DBI:mysql: accounting:localhost', 'username', 'password', {RaiseError => 1}); 14 15 # Report on the success of the connection attempt. 16 if ($dbh) { 17 18 print "Successfully connected to the database! \n"; 19 20 } 21 22 # Disconnect. 23 $dbh->disconnect;
|
| | | 5. | Print a message indicating success.
if ($dbh) { print "Successfully connected to the database! \n"; }
The $dbh variable, which is a reference to the database connection, can be used to test whether or not the connection went through. Because I am using RaiseError, an inability to establish a connection will kill the script, so a "Could not connect to the database!" message (the potential else part of this conditional) is unnecessary.
| 6. | Close the connection.
$dbh->disconnect;
This is an important step because it clears up resources used by both MySQL and Perl.
| 7. | Save the file as mysql_connect.pl, change the permissions (if necessary), and run the script (Figures 8.14 and 8.15).
Figure 8.14. If you have the proper permissions to access the database, you'll see this message; otherwise... 
Figure 8.15. ...you'll see an error message and the script will stop running. 
This scriptassuming you have successfully run the test.pl scriptshould work or not work solely on the basis of the permissions of the username, host name, and password you use. If you have problems connecting to the database, check those parameters first.
|
 Tips
In Perl you cannot make persistent connections to MySQL, unless you use mod_perl. In the data source name (DSN), mysql must be lowercase, and the host can be omitted if it is localhost.
|
