Slide 6.d: Using Perl to access a MySQL database

Using Perl to Access a MySQL database

The following example selects customers from the table Customers using the SQL commands:

   CREATE TABLE  Customers (
     ID   int  AUTO_INCREMENT PRIMARY KEY,
     name varchar( 16 ) NOT NULL );

   SELECT * FROM  Customers  WHERE  name  LIKE  '%$name%';

Comments about the script include:

The statement handle method bind_columns( ) binds Perl variables to output columns of a SELECT statement.
The statement handle method fetch( ) fetches rows from a table.

 #!/usr/bin/perl -w
 use CGI;
 $query = new CGI;
 use DBI;

 $db       = "webdb";
 $host     = "mysql.aero.und.edu";
 $userid   = "wenchen";
 $passwd   = "password";
 $connInfo = "dbi:mysql:$db;$host";

 if ( !( $dbh = DBI->connect( $connInfo, $userid, $passwd ) ) ) {
   print( "Connect failed: $DBI::errstr" );
   exit( );
 }

 else {
   $table = $query->param( 'table' );
   if ( $table eq "Drop" ) {
     $sth = $dbh->prepare( "DROP TABLE Customers" ) || die $dbh->errstr;
     $sth->execute( ) || die "DROP TABLE failed: $DBI::errstr";
     print( "Table Customers dropped" );
     $sth->finish( );
   }

   elsif ( $table eq "Create" ) {
     $stmt = "CREATE TABLE  Customers (
                ID   int  AUTO_INCREMENT PRIMARY KEY,
                name varchar( 16 ) NOT NULL )";
     $sth = $dbh->prepare( $stmt ) || die $dbh->errstr;
     $sth->execute( ) || die "CREATE TABLE failed: $DBI::errstr";
     print( "Table Customers created" );
     $sth->finish( );
   }

   elsif ( $table eq "Insert" ) {
     $name1 = $query->param( 'name1' );
     $stmt  = "INSERT INTO Customers ( name ) VALUES ( '$name1' )";
     $sth   = $dbh->prepare( $stmt ) || die $dbh->errstr;
     $sth->execute( ) || die "INSERT INTO failed: $DBI::errstr";
     print( "Customer $name1 entered" );
     $sth->finish( );
   }

   elsif ( $table eq "Select" ) {
     $name2 = $query->param( 'name2' );
     $stmt  = "SELECT * FROM  Customers  WHERE  name LIKE '%$name2%'";
     $sth   = $dbh->prepare( $stmt ) || die $dbh->errstr;
     $sth->execute( ) || die "SELECT FROM failed: $DBI::errstr";
     # Assign fields to variables.
     $sth->bind_columns( \$ID, \$name );
     # Output customer list to the browser.
     while( $sth->fetch( ) ) {
       print( "$ID, $name<br />" );
     }
     $sth->finish( );
   }
   $dbh->disconnect( );
 }