Home | | Internet & World Wide Web HOW TO PROGRAM | | Internet Programming | | Web Programming | Using DBI to Connect to a Database - Perl

Chapter: Internet & World Wide Web HOW TO PROGRAM - Perl and CGI (Common Gateway Interface)

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail

Using DBI to Connect to a Database - Perl

Database connectivity allows system administrators to maintain information on user ac-counts, passwords, credit-card information, mailing lists, product inventory, etc. Databases allow companies to enter the world of electronic commerce and maintain crucial data.

Using DBI to Connect to a Database


Database connectivity allows system administrators to maintain information on user ac-counts, passwords, credit-card information, mailing lists, product inventory, etc. Databases allow companies to enter the world of electronic commerce and maintain crucial data.


In order to access various relational databases in Perl, we need an interface (in the form of software) that allows us to connect to and execute SQL statements (queries). The Perl DBI (Database Interface) allows us to do this. This interface was created to access different types of databases uniformly. In this section, we access and manipulate a MySQL database. The examples in this section require that MySQL (www.mysql.org) be installed. The Perl DBI module and the MySQL driver, DBD::mysql (specified on lines 6–7 of Fig. 27.19), are also required.


If you are using ActiveState Perl, you can download these files using the Perl Package Manager (PPM), which is part of ActiveState Perl. Using PPM, you can download and install Perl modules and packages (provided that you are connected to the Internet at the time you are running the program). To use PPM, type ppm at the command prompt. This command starts the package manager in interactive mode, providing you with the ppm> prompt. Type install DBI and press Return to install DBI. To install the MySQL driver, type install DBD::mysql and press Return.



If you do not have the Perl Package Manager, you can search for the module or package on CPAN, the Comprehensive Perl Archive Network (www.cpan.org). Finally, you will need to use the database books. This database is located in the Chapter 27 exam-ples directory on the CD-ROM that accompanies this book. The examples directory con-tains a subfolder named books, which contains all the database files. In your mysql directory, there is a data directory that contains MySQL databases. Each folder is a data-base and contains all the files that comprise that database. Copy the books folder into this data directory.


In Fig. 27.19, the client selects an author from a drop-down list (the authors are num-bered according to their ID value). When Get Info is clicked, the chosen author and the author’s ID are posted to the Perl script in Fig. 27.20 that queries the database for all books published by that author. The results are displayed in an XHTML table. To create and exe-cute SQL queries, we create DBI objects known as handlesDatabase handles create and manipulate a connection to a database, while statement handles create and manipulate SQL statements (queries) to a database.



    # Fig. 27.19: fig27_19.pl

    # CGI program that generates a list of authors.


    use CGI qw( :standard );

    use DBI;

    use DBD::mysql;

    $dtd =

      "-//W3C//DTD XHTML 1.0 Transitional//EN\"


print( header() );         


print( start_html(         { dtd => $dtd,

            title => "Authors" } ) );

      # connect to "books" database, no password needed

$databaseHandle = DBI->connect( "DBI:mysql:books"

          "root", "", { RaiseError => 1 } );


          # retrieve the names and IDs of all authors

          $query = "SELECT FirstName, LastName, AuthorID

          FROM Authors ORDER BY LastName";


          # prepare the query for execution, then execute it

          # a prepared query can be executed multiple times

          $statementHandle = $databaseHandle->prepare( $query );



          print( h2( "Choose an author:" ) );


          print( start_form( { action => 'fig27_20.pl' } ) );

      print( "<select name = \"author\">\n" );


      # drop-down list contains the author and ID number

      # method fetchrow_array returns a single row from the result

      while ( @row = $statementHandle->fetchrow_array() ) {

      print( "<option>" );

      print( "$row[ 2 ]. $row[ 1 ], $row[ 0 ]" );

      print( "</option>" );


      print( "</select>\n" );


      print( submit( { value => 'Get Info' } ) );

      print( end_form(), end_html() );

      # clean up -- close the statement and database handles



Fig. 27.19  Perl script that queries a MySQL database for authors


On lines 19–20, we connect to the database by calling DBI method connect. The first argument specifies the data source (i.e., the database). Notice that we first specify the interface name (DBI), followed by a colon (:), then the database driver (mysql), followed by another colon and the name of the data source (books). The second argument specifies the user, and the third argument specifies the password for the database. This database does not require a username or password, so we simply use the empty string (""). The fourth argument ({ RaiseError => 1 }) is used for error checking. If an error occurs when trying to connect to the database, function die is called and passed an error message. Set-ting this hash reference to 1 is like setting a variable to true—this value “turns on” the error checking, saving the programmer from writing extra code to handle this problem or from having the program crash unexpectedly. If the connection succeeds, function connect returns a database handle that is assigned to $databaseHandle.


In this example, we query the database for the names and IDs of the authors. We create this query on lines 23–24. On line 28, we use our database handle to prepare the query (using the method prepare). This method prepares the database driver for a statement, which can be executed multiple times. The statement handle returned is assigned to $statementHandle. We execute the query by calling method execute on line 29.


Once the query has been executed, we can access the results by calling method fetchrow_array (line 39). Each call to this function returns the next set of data in the resulting table until there are no data sets left. A data set, or row in the resulting table, con-tains one of the elements that satisfied the query. For example, in the first program, a query was executed that returned the ID and name of each author. This query created a table that contained two columns, one for the author’s ID and one for the author’s name. A row con-tained the ID and name of a specific author. Each row was returned as an array and assigned to @row. We print these values as list options on lines 40–42. The option chosen is sent as the parameter "author" (line 35) to the Perl script in Fig. 27.20. On lines 51–52,we close the database connection (using method disconnect), and we specify that we are finished with this query by calling method finish. This function closes the statement handle and frees memory, especially if the resulting table was large.

Figure 27.20 presents the script fig27_20.pl, which takes the specified author and queries the database for information about that author.


This program creates an XHTML page that displays the title of each book written by the current author, along with the ISBN number and book publisher. In order to obtain this information, we need the author’s ID number, because the AuthorISBN table contains a field for the author’s ID, not the author’s name. Recall that the author’s ID was posted to this script by fig27_19.pl. The ID is the numerical value that precedes the author’s name in the author parameter. To retrieve the ID and author name, we call method substr on lines 16–17. This statement returns the first character in the string (an offset of zero indicates the beginning of the string), which contains the ID value. On line 17, we specify an offset of three, because the author’s name begins after the third character. Notice that in this call we do not specify a length, because we want all characters from the offset to the end of the string, inclusive.



    # Fig. 27.20: fig27_20.pl

    # CGI program to query a MySQL database.


    use CGI qw( :standard );

    use DBI;

    use DBD::mysql;

    $dtd =

      "-//W3C//DTD XHTML 1.0 Transitional//EN\"


        print( header() );

      # retrieve author's ID and name from the posted form

      $authorID = substr( param( "author" ), 0, 1 );

$authorName = substr( param( "author" ), 3 );

print( start_html( { dtd => $dtd,       

title => "Books by $authorName" } ) );


$databaseHandle = DBI->connect( "DBI:mysql:books",     

"root", "", { RaiseError => 1   } );

          # use AuthorID to find all the ISBNs related to this author

          $query1 = "SELECT ISBN FROM AuthorISBN

          WHERE AuthorID = $authorID";

      $statementHandle1 = $databaseHandle->prepare( $query1 );


      print( h2( "$authorName" ) );


      print( "<table border = 1>" );

      print( th( "Title" ), th( "ISBN" ), th( "Publisher" ) );


      while ( @isbn = $statementHandle1->fetchrow_array() ) {

      print( "<tr>\n" );

              # use ISBN to find the corresponding title

      $query2 = "SELECT Title, PublisherID FROM titles

                          WHERE ISBN = \'$isbn[ 0 ]\'";

      $statementHandle2 = $databaseHandle->prepare( $query2 );


      @title_publisherID = $statementHandle2->fetchrow_array();


      # use PublisherID to find the corresponding PublisherName

$query3 = "SELECT PublisherName FROM Publishers

WHERE PublisherID = \'$title_publisherID[ 1 ]\'";

      $statementHandle3 = $databaseHandle->prepare( $query3 );


      @publisher = $statementHandle3->fetchrow_array();

      # print resulting values

      print( td( $title_publisherID[ 0 ] ), "\n" );

      print( td( $isbn[ 0 ] ), "\n" );

      print( td( $publisher[ 0 ] ), "\n" );

          print( "</tr>" );






      print( "</table>" );



      print( end_html() );







Fig. 27.20     Perl script that queries a MySQL database for author information



After connecting to the database, we specify and execute our first query, on lines 26– 30. This query returns all the ISBN numbers for the specified author. We place these values in a table. On line 37, we begin a while loop that iterates through each row matched by the query. The rows are retrieved by calling fetchrow_array, which returns the current data set as an array. When there are no more data sets to return, the condition evaluates to false. Within the loop, we use the ISBNs to obtain the title and publisher values for the cur-rent table row. The query on lines 41–45 uses the ISBN value to determine the book’s title and the publisher’s ID number. The next query (lines 48–53) uses the publisher’s ID to determine the name of the publisher. These values are printed on lines 57–59.

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail

Copyright © 2018-2020 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.