Installing DBI and Using MySQL with Perl in Linux

You will learn in this tutorial how to install the DBI (the Perl interface to databases) in RedHat Linux 7.0 and above. There is another version for Windows here. You can obtain a more detailed introduction to DBI by reading its FAQ. You can see below the basic DBI model. The scripts are written in Perl using standard Perl variables, commands and syntax. The DBI has methods and handles which are database software independent. You program to access, change or query a database using the standard SQL language, combined with the DBI methods and handles. You install as many DBD::type modules as you need to support the different database software you may have, but you Perl script will be the same.

You will also see in this tutorial the basics of using DBI to perform queries, and display the results. Once you learn the basics you can use Perl to program the other SQL commands we saw briefly in our class on MySQL, and you should have learned in more details in the DB course. Please note that we have on reserve at the Library the book Programming the Perl DBI, the authoritative reference to DBI.



Installation

You will need to download and install three pieces of software: DBI, ShowTable and MySQL DBD module. If you were installing support for another database like Oracle, Informix, etc, you would also have to download their DBD modules.

I recommend that you compile each one of these programs, rather than install from rpm. Unfortunately, no set of rpms I tried worked with different versions of Linux, even from the same distribution, e.g. RedHat 6.2 rpm does not work with 7.0. If you did not compile programs in Linux before, don't get scared. They come with programs which run in your machine (the Makefile script), see what you have in terms of software and hardware and create a set of instructions (an executable script/program) which takes care of the compilation.

  1. first run Makefile -- the script to prepare the compilation instructions.
  2. second run make -- the script which compiles the program (for example complies DBI).
  3. third run make test -- a script to test if the compiled program works (it may ask login information in your database).
  4. finally run make install -- a script which installs the program you compiled in your file system, including documentation (at least man pages).

  1. Install DBI
  2. Download the latest version from CPAN (Comprehensive Perl Archive Network) DBI directory. At the time this writing the latest version was DBI-1.15.tar.gz . You can also read the readme file for it. You will only need to download one version of DBI to use with any database: MySQL, Oracle, Informix, etc.

    Once you download the DBI module file, you will need to extract (it is compressed) and compile it, as follows:

    1. Unpack the archive

      tar xvfz DBI-1.15.tar.gz

    2. Generate the "Makefile":

      cd     DBI-1.15
      perl Makefile.PL

    3. Make the installable files:

      make

    4. Test the new files:

      make test

    5. Install the modules and the DBI program into the configured Perl library and binary directories.

      make install

    In each of these steps you will see a variety of output generated to the shell window. You can ignore all, or almost all of this verbiage. If questions are present answer pressing enter, or selecting the default. Once all the above steps are run the DBI module is installed and you can delete the directory DBI-1.15. The following script let you test if your installation is working fine:
    #!/usr/bin/perl -w
    use strict;
    use DB_File;
    my %database;
    tie %database, 'DB_File', "createdb.dat" or die "Can't initialize database: $!\n";
    untie %database;
    exit;
    
    If all went well a file createdb.dat will be created by the script.


  3. Install Data_ShowTable
  4. Download the latest version from CPAN (Comprehensive Perl Archive Network) ShowTable directory. At the time this writing the latest version was Data-ShowTable-3.3.tar.gz . You can also read the readme file for it. You will only need to download one version of ShowTable to use with any database: MySQL, Oracle, Informix, etc.

    Once you download the ShowTable module file, you will need to extract (it is compressed) and compile it, as follows:

    1. Unpack the archive

      tar xvfz Data-ShowTable-3.3.tar.gz

    2. Generate the "Makefile":

      cd     Data-ShowTable-3.3
      perl Makefile.PL

    3. Make the installable files:

      make

    4. Test the new files:

      make test

    5. Install the modules and "showtable" program into the configured Perl library and binary directories.

      make install

    In each of these steps you will see a variety of output generated to the shell window. You can ignore all, or almost all of this verbiage. If questions are present answer pressing enter, or selecting the default. Once all the above steps are run the ShowTable module is installed and you can delete the directory Data-ShowTable-3.1 -- the source for the installation.


  5. Install the MySQL module
  6. Download the latest version from CPAN (Comprehensive Perl Archive Network) DBD directory. At the time this writing the latest version of the MySQL module was Msql-Mysql-modules-1.2219.tar.gz (please note that it does not have DBD in front of the file name, like Oracle and DB2 have, and are located almost at the end of the list). You can also read the readme file for it. You will need to download one version of the DBD module to use with each database: MySQL, Oracle, Informix, etc. These instructions refer to downloading and installing the DBD module for MySQL, but you can install more than one of the modules to access different types of database software. The same Perl commands wich work for one database type will work for the other types: the code is portable not only across platforms, but also across database software.

    Once you download the MySQL DBD module file, you will need to extract (it is compressed) and compile it, as follows:

    1. Unpack the archive

      tar xvfz Msql-Mysql-modules-1.2219.tar.gz

    2. Generate the "Makefile":

      cd     Msql-Mysql-modules-1.2219
      perl Makefile.PL

    3. Make the installable files:

      make

    4. Do not test the new files: the test is broken, skip this step

    5. Install the modules and MySQL DBI interface into the configured Perl library and binary directories.

      make install

    In each of these steps you will see a variety of output generated to the shell window. You can ignore all, or almost all of this verbiage. If questions are present answer pressing enter, or selecting the default. Once all the above steps are run the DBI module is installed and you can delete the directory Msql-Mysql-modules-1.2219.

    This completes the installation of Perl modules to manipulate MySQL databases. The following script let you test if your installation is working fine with MySQL, assuming you have created a MYTEST database:

    #!/usr/bin/perl  -w
    use DBI;
    #definition of variables
    $db="MYTEST";
    $host="localhost";
    $user="root";
    $password="rootpass";  # the root password
    
    #connect to MySQL database
    my $dbh   = DBI->connect ("DBI:mysql:database=$db:host=$host",
                               $user,
                               $password) 
                               or die "Can't connect to database: $DBI::errstr\n";
    
    #disconnect  from database 
    $dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";
    
    exit;
    
    

Using MySQL with the Perl DBI Interface

The basic documentation for the MySQL module is the reference table and methods for the DBI Interface, including specific commands only available in MySQL.

Lets see some basic concepts and a series of examples to illustrate them.

You are not limited to queries when using DBI. You can create and drop tables, insert, update, etc.


This page is maintained by Al Bento who can be reached at abento@ubmail.ubalt.edu. This page was last updated on October 28, 2002. Although we will attempt to keep this information accurate, we can not guarantee the accuracy of the information provided.