Accessing Database Information Using ODBC

Accessing Database Information Using Perl

So, you've got MySQL on your machine. Now what can you do with it? Obviously, you can use one of the previously mentioned utilities to view and manipulate data, but that isn't nearly as useful as being able to apply programmatic logic to the data. Not surprisingly, Perl can be upgraded using DBI (Database Independent Interface) and DBD (Database Driver) modules to access dozens of database systems, including MySQL.

Perl and MySQL can be combined to create database applications that can be used for anything from storing your personal movie collection to enterprisewide solutions. This portion of the chapter introduces you to the MySQL and Perl connection.

Installing DBI::DBD

As you learned in Chapter 18, "Developing Applications and Widgets Using Scripting Languages," installing a Perl module using CPAN requires nothing more than typing install <module name>. To start the interactive module installation shell, type sudo perl -MCPAN -e shell at a command line.

NOTE

Perl is likely to complain about the version of the CPAN modules you have installed. Although it isn't necessary to upgrade, it won't hurt, and future module installations will be smoother. To upgrade to the latest CPAN modules, type install Bundle::CPAN at the cpan> prompt.

 cpan shell -- CPAN exploration and modules installation (v1.7601) ReadLine support enabled cpan>'' 

At the cpan> prompt, type install DBI::DBD to begin the installation process:

 cpan> install DBI::DBD Local directory now /Users/jray/.cpan/sources/authors/id/T/TI/TIMB ... DBI-1.37 DBI-1.37/DBI.xs DBI-1.37/t DBI-1.37/t/80proxy.t DBI-1.37/t/30subclass.t DBI-1.37/t/70shell.t DBI-1.37/t/60preparse.t DBI-1.37/t/20meta.t DBI-1.37/t/40profile.t ... Installing /usr/share/man/man3/DBI::Shell.3 Installing /usr/share/man/man3/DBI::W32ODBC.3 Installing /usr/share/man/man3/Win32::DBIODBC.3 Installing /usr/bin/dbiproxy Installing /usr/bin/dbish Writing /Library/Perl/darwin/auto/DBI/.packlist Appending installation info to /System/Library/Perl/darwin/perllocal.pod  /usr/bin/make install -- OK 

Depending on your Perl installation and version, you might notice several additional messages during the installation. Don't concern yourself too much about these messages unless the installation fails. In the event of a failure, be absolutely sure that you have the latest version of the developer tools installed.

TIP

Many Perl modules ask some basic questions during the install process. Even the highly automated CPAN shell installation method pauses to collect information it needs so pay attention to your screen during an install.

That's it. The DBD::DBI module, which provides the basis for database access from within Perl, is now installed.

Installing DBD::mysql

To complete the integration of Perl with MySQL, we need the DBD::mysql module. Again, using the CPAN shell, this requires no more effort on our part than install DBD::mysql.

Again, invoke CPAN with sudo perl -MCPAN -e shell:

 cpan shell -- CPAN exploration and modules installation (v1.7601) ReadLine support enabled '' cpan> 

At the cpan> prompt, type install DBD::mysql to begin the installation process:

 cpan> install DBD::mysql GOT /Users/jray/.cpan/sources/authors/id/J/JW/JWIED/DBD-mysql-2.1027.tar.gz DBD-mysql-2.1027 DBD-mysql-2.1027/t DBD-mysql-2.1027/t/mysql2.t DBD-mysql-2.1027/t/akmisc.t DBD-mysql-2.1027/t/60leaks.t DBD-mysql-2.1027/t/10dsnlist.t DBD-mysql-2.1027/t/ak-dbd.t DBD-mysql-2.1027/t/50chopblanks.t ... Installing /usr/share/man/man3/DBD::mysql::INSTALL.3 Installing /usr/share/man/man3/Mysql.3 Writing /Library/Perl/darwin/auto/DBD/mysql/.packlist Appending installation info to /System/Library/Perl/darwin/perllocal.pod/usr/bin/make  install -- OK 

NOTE

If you've already set a root password for MySQL, the install will fail when it attempts to connect to mysql as a test. The easiest way around this is to simply force the install by using the command force install DBD::mysql within the CPAN shell.

DBD::mysql is installed and ready to use. Remember, to view documentation for any of the installed modules, type perldoc <module name>.

Using Perl with MySQL (DBD::mysql)

The DBD::mysql module uses an object-oriented model to carry out database translations. Because object-oriented programming is a bit beyond the scope of this book, we'll take a look at two examples: adding information to a database and displaying information contained in a table. You should be able to modify these examples for your own applications, or, if you need more functionality, I recommend adding a Perl book to your library.

Displaying the Results of a Query

The easiest way to retrieve information from a MySQL database is to compose a query and retrieve the results, one record at a time. To be able to do this, you must connect to the database, issue the query, determine the number of results, and loop through a display of each one. Listing 19.1 shows the surprisingly short code necessary to do just that.

Listing 19.1. Display the Result of a MySQL Query

 1: #!/usr/bin/perl 2: 3: use DBI; 4: 5: $user=""; 6: $pass=""; 7: $database="employee"; 8: $dsn="DBI:mysql:database=$database;host=localhost"; 9: $sql="select firstname,lastname,title from          tblemployee,tbljobclassification where tblemployee.titleID=          tbljobclassification.titleID"; 10: 11: $dbh=DBI->connect($dsn,$user,$pass); 12: $sth=$dbh->prepare($sql); 13: $sth->execute; 14: 15: $numrows=$sth->rows; 16: $numfields=$sth->{'NUM_OF_FIELDS'}; 17: $nameref=$sth->{'NAME'}; 18: 19: for ($x=0;$x<$numrows;$x++) { 20:   $valueref = $sth->fetchrow_arrayref; 21:   print "----------------------------\n"; 22:   for ($i=0;$i<$numfields;$i++) { 23:     print "$$nameref[$i] = $$valueref[$i]\n"; 24:   } 25: } 

The following list describes how the Perl code interacts with the MySQL database through the DBI module:

• Line 3 Use the DBI module. This must be included in any Perl application that accesses MySQL.

• Lines 5 9 Set up the username, password, database name, and SQL that will be used to access the database. The $dsn variable contains a string that will be used to set up the connection to MySQL. The format of this string cannot change, although the database and hostname can.

• Line 11 Connect to the database using the previously defined connection string and username and password. The variable $dbh is a handle that references the database connection.

• Line 12 Prepare the SQL for execution.

• Line 13 Execute the SQL statement and return a reference to the results in the variable $sth.

• Line 15 Store the number of returned rows in the $numrows.

• Line 16 Store the number of fields (columns) in the result within $numfields.

• Line 17 Store a reference to an array containing the field names in the variable $nameref.

• Lines 19 25 Loop through each of the rows in the result.

• Line 20 Fetch a row of the result and return the field values in an array referenced by $valueref.

• Line 21 Print a divider between each output record.

• Lines 22 24 Loop based on the number of fields in the result. Display each field name followed by the value stored in that field.

Executing the code (assuming that the employee database from earlier in the chapter is in place) produces output like this:

 % ./display.pl ---------------------------- firstname = Maddy lastname = Green title = Programmer/Analyst ---------------------------- firstname = Will lastname = Ray title = Programmer/Analyst ---------------------------- firstname = Joan lastname = Ray title = Programmer/Analyst ---------------------------- firstname = Jack lastname = Derifaj title = Programmer/Analyst ---------------------------- ...and so on... 

Obviously, the syntax of this code is a bit different from the Perl that you've seen so far, but it should be easy enough to understand that you can modify the code to fit your application.

Storing Data

You probably noticed that the code for displaying the results of a query was very modular. In fact, you can use the same code to insert a record into the database. Listing 19.2 demonstrates the code needed to store data in the tblemployee table.

Listing 19.2. Display the Result of a MySQL Query

 1: #!/usr/bin/perl 2: 3: use DBI; 4: 5: $user=""; 6: $pass=""; 7: $database="employee"; 8: $; $firstname="Troy"; $lastname="Burkholder"; 9: $title; $salary="45000"; 10: $dsn="DBI:mysql:database=$database;host=localhost"; 11: $sql="insert into tblemployee values ('$id','$firstname','$lastname',                                           '$titleID','$salary')"; 12: 13: $dbh=DBI->connect($dsn,$user,$pass); 14: $sth=$dbh->prepare($sql); 15: $sth->execute; 

The only difference between this code and the previous script is the definition of the values for an insert (lines 8 and 9) and the definition of the insert statement itself (line 11). The SQL statement can be whatever arbitrary SQL code you want. If the statement returns results, they can be read and displayed with the techniques in the previous code.