One of the best parts of this book is its introduction to using Common Gateway Interface (CGI) and Perl to power a Web site with a MySQL/mSQL database. This section offers complete information on using mSQL Perl (and the emerging Database Independent [DBI] standard) for developing CGI database scripts in Perl, and it includes clear examples (including a student database). The book then moves from Perl on to other programming languages--Python and Java. Reference material to all the relevant APIs is featured for each language.
Whatever programming API you choose, MySQL and mSQL are ready to meet the needs of the small to moderate-size Web site. This book delivers essential information on these packages and will help both Web masters and programmers get the most out of these powerful freeware database tools. --Richard Dragan
From Library Journal
Copyright 1999 Reed Business Information, Inc.
From the Publisher
About the Author
Tim King has been working with computers since the early 1980s, when he programmed games on his Commodore 64 computer and founded a computer club in his high school. He earned a bachelor's degree in computer science from the University of Minnesota Institute of Technology in 1991. While there, he taught Unix and vi classes and was the leader of a rag-tag group of vi devotees called the "VI Zombies." Presently, Tim is a software consultant in San Francisco, CA, specializing in database and web technologies. His favorite activity is snowboarding, but he also enjoys photography and reading. You can reach him at email@example.com
George Reese has taken an unusual path into business software development. After earning a B.A. in philosophy from Bates College in Lewiston, Maine, George went off to Hollywood where he worked on television shows such as "The People's Court" and ESPN's "Up Close". The L.A. riots convinced him to return to Maine where he finally became involved with software development and the Internet. George has since specialized in the development of Internet-oriented Java enterprise systems and the strategic role of technology in business processes. He is the author of Database Programming with JDBC and Java, 2nd Edition and the world's first JDBC driver, the mSQL-JDBC driver for mSQL. He currently lives in Minneapolis, Minnesota with his wife Monique and three cats, Misty, Gypsy, and Tia. He makes a living as the National Practice Director of Technology Strategy for digital@jwt in Minneapolis.
Excerpt. © Reprinted by permission. All rights reserved.
The Perl programming language has gone from a tool primarily used by Unix systems administrators to the most widely used development platform for the World Wide Web. Perl was not designed for the web, but its ease of use and powerful text handling abilities have made it a natural for CGI programming. Similarly, when mSQL first entered the scene, its extremely small footprint and execution time were very attractive to web developers who needed to serve thousands of transactions a day. MySQL, with its enhanced speed and capabilities provided an even greater incentive for web developers. Therefore, it was only natural that a Perl interface to both MySQL and mSQL was developed that allowed the best of both worlds.
TIP: At the time of this writing there are two interfaces between MySQL and mSQL and Perl. The original consists of Mysql.pm and Msql.pm, custom interfaces that work only with MySQL and mSQL, respectively. The other, newer, interface is a plug-in for the Database Independent (DBI) set of modules. DBI is an attempt to provide a common Perl API for all database accesses and enable greater behind-the-scenes portability. The DBI interface has become the most robust and standard, and the makers of MySQL recommend that all work be done using DBI as development of the Mysql.pm and Msql.pm modules has ceased. However, many legacy systems still use these modules, so both will be covered here.
The recommended method for accessing MySQL and mSQL databases from Perl is the DBD/DBI interface. DBD/DBI stands for DataBase Dependent/DataBase Independent. The name arises from the two-layer implementation of the interface. At the bottom is the database dependent layer. Here, modules exist for each type of database accessible from Perl. On top of these database dependent modules lies a database independent layer. This is the interface that you use to access the database. The advantage of this scheme is that the programmer only has to learn one API, the database independent layer. Every time a new database comes along, someone needs only to write a DBD module for it and it will be accessible to all DBD/DBI programmers.
As with all Perl modules, you must use DBI to get access:
use CGI qw(:standard);
TIP: When running any MySQL/mSQL Perl programs, you should always include the -w command line argument. With this present, DBI will redirect all MySQL and mSQL specific error messages to STDERR so that you can see any database errors without checking for them explicitly in your program.
All interactions between Perl and MySQL and mSQL are conducted through what is known as a database handle. The database handle is an object--represented as a scalar reference in Perl--that implements all of the methods used to communicate with the database. You may have as many database handles open at once as you wish. You are limited only by your system resources. The connect() method uses a connection format of DBI:servertype:database:hostname:port (hostname and port and optional), with additional arguments of username and password to create a handle:
my $dbh = DBI->connect('DBI:mysql:mydata', undef, undef);
my $dbh = DBI->connect('DBI:mSQL:mydata:myserver', undef, undef);
my $dbh = DBI->connect('DBI:mysql:mydata','me','mypass');
The servertype attribute is the name of the DBD database-specific module, which in our case will be either "mysql" or "mSQL" (note capitalization). The first version creates a connection to the MySQL server on the local machine via a Unix-style socket. This is the most efficient way to communicate with the database and should be used if you are connecting to a local server. If the hostname is supplied it will connect to the server on that host using the standard port unless the port is supplied as well. If you do not provide a username and password when connecting to a MySQL server, the user executing the program must have sufficient privileges within the MySQL database. The username and password should always be left undefined for mSQL databases.
TIP: Perl 5 has two different calling conventions for modules. With the object-oriented syntax, the arrow symbol "->" is used to reference a method in a particular class (as in DBI->connect). Another method is the indirect syntax, in which the method name is followed by the class name, then the arguments. The last connect method above would be written as connect
DBI 'DBI:mysql:mydata', 'me', 'mypass'. Early versions of the Msql.pm used the indirect syntax exclusively and also enforced a specific method of capitalization inspired by the mSQL C API. Therefore, a lot of older MsqlPerl code will have lines in it like SelectDB $dbh 'test' where a simple $dbh->selectdb('test') would do. If you haven't guessed, we are partial to the object-oriented syntax, if only because the arrow makes the relationship between class and method clear.
Once you have connected to the MySQL or mSQL server, the database handle--$dbh in all of the examples in this section--is the gateway to the database server. For instance, to prepare an SQL query:
WARNING: When using mSQL you may select only one database at a time for a particular database handle. The mSQL server imposes this limit. However, you may change the current database at any time by calling connect again. With MySQL, you may include other databases in your query by explicitly naming them. In addition, with both MySQL and mSQL, if you need to access more than one database concurrently, you can create multiple database handles and use them side by side.
Chapter 21, Perl Reference, describes the full range of methods and variables supplied by DBI as well as Mysql.pm and Msql.pm.