The Data Source Name (DSN)

A DSN is a string that passes all of the connect information to the database interface. It begins with DBI:. For some reason, this first part is not case-sensitive, but the rest of the DSN is case-sensitive. This is followed by the database driver name and a colon. For a MySQL database, we'll begin with something like this: DBI:mysql:. The database driver name is case-sensitive-so if you get an error but have spelled the driver name correctly, make sure the case is also correct.

Next comes the database name. This is where different databases require different data. For MySQL, which is what the preceding connect statement is connecting to, we simply list the database name, quizzer. With PostGreSQL, which we'll have an example of in a bit, the database name must be preceded with dbname=. For our PostgreSQL example, this becomes dbname=quizzer. With MySQL, you can use dbname= if you want, but it is not required. Other databases, such as Oracle, use different strings here, whereas the CSV driver uses the string f_dir=/path (where path leads to the directory where the data files are stored).

The server address and port number can also go on in the DSN! In our previous example, we list the server name, dbserver.perlguy.net; since we are using the default MySQL port (3306), we can leave it off.

A full DSN can look something like this:

 DBI:mysql:dbname=quizzer:dbserver.somewhere.com:1234 

The bold characters are required, and the italicized are optional.

Wow! That is really a lot of information, but it is definitely important to be able to connect to the database.

The next two parts of the connect string are the username and password. These are not the username and password for a normal user; they are instead the username and password for the database and/or tables being accessed. Databases typically have a permissions system of their own to restrict access to certain data-these username and password values belong to the database permission system.

Line 9 is a continuation of line 8. It tells the system to call the die function and to print an error message if there is an error when connecting to the database. $DBI::errstr is a variable, named $errstr in the DBI module, that gets set whenever an error is encountered. This line causes the error message to print and the program to stop execution at this point.

10: my $dbh_p = DBI->connect("DBI:Pg:dbname=test",         "postgres","")  11:     or die("Cannot connect: $DBI::errstr");

Line 10 is the connect statement for the PostGreSQL database we are also connecting to. The DSN for this database is DBI:Pg:dbname=test. This connect statement is a lot shorter than the previous example because we do not need the servername or port, as this database server is on the same system as this program. The username for this database is postgres; there is no password, so we pass an empty string.

Line 11 is the same as line 9. It prints an error message and stops the program execution if there is a problem connecting to the database.

At this point in the program, we have two different connections to two different databases on two different servers! The database handle to the MySQL database is $dbh_m, and the database handle to the PostGreSQL server is $dbh_p.

12: my $sql = qq(select * from test_config);

Line 12 creates a new scalar variable named $sql and sets it to the string on the right. The qq function is nice because it acts exactly like double quotes; if your string has double quotes, you do not have to place a backslash character in front of them.

my $sql = qq(select * from test WHERE foo = "bar"); 

is easier to read than something like:

my $sql = "select * from test WHERE foo = \"bar\"";

Although the above example is very simple, if you start creating queries with many quoted variables, you will see how much easier and less prone to typing errors using qq() is compared to quotes and backslashes.

This $sql variable stores the SQL statement we are going to run against both databases. The SQL statement is telling the database server to select all data from the test_config table. Both databases have a table named test_config.

13: my $sth_m = $dbh_m->prepare($sql); 14: my $sth_p = $dbh_p->prepare($sql);

Lines 13-14 both declare a new scalar variable that will be used as a handle to the statement we are preparing on the right side. The prepare method takes the SQL statement and stores it in an internal, compiled format that the database can work with.

The $sth_ is similar to our database handles $dbh_. The commonly accepted way to name a statement handle is $sth or $sth_description.

15: $sth_m->execute; 16: $sth_p->execute;

Lines 15-16 also perform the same task. They call the execute method on the statement handle. The execute method takes the prepared SQL statement and runs it on the database engine.

17: while(my $p = $sth_m->fetch){ 18:     print "MySQL: @$p\n"; 19: }

Line 17 begins a while loop. This while loop fetches the data that results from the executed SQL statement on line 15. The fetch method you see inside the while condition grabs one row (a.k.a. record) of returned data and returns a reference (a.k.a. pointer) to an array containing this data. The pointer to this array is stored in the variable $p. (The fetch method is covered in more detail later in this book.)

Line 18 prints the row of data. We don't use special formatting here; we are just dumping the data out.

Line 19 ends the while loop that begins on line 16.

20: print "\n"; 

Line 20 simply prints a blank line. This helps separate the output from the two databases.

21: while(my $p = $sth_p->fetch){ 22:     print "PostGreSQL: @$p\n"; 23: }

Lines 21-23 do the same thing as lines 17-19 but on the PostGreSQL database instead of on the MySQL database.

24: $dbh_m->disconnect; 25: $dbh_p->disconnect;

Lines 24-25 disconnect the program from the databases. Disconnecting ensures that all data is written properly to the tables. Some databases care whether or not you disconnect; others do not. Always use the disconnect method so you don't have to worry about it.