HowTo Setup A MySQL Database

This article shows you how to connect your Perl/PHP scripts to a MySQL database.


    Start by skimming the mysql site at

    The download section has medium and max grade versions of the my sql database. Free to download.

    You might even want to get a copy of "MySQL Control Center" which let's you administer that database and users with a handy GUI.

    Under Contributed APIs, there is a link to "DBI" which let's you connect to MySQL with Perl. There are other drivers down there to connect to whatever platform you want.

    Indigoperl is a good installation that comes with Apache and ModPerl. It's available at

    It doesn't hurt to get yourself a copy of activeperl from

    Afer installing activeperl, just bring up a command prompt and type: "ppm install DBI". That will install all the DBI perl related drivers for you. "perldoc DBI" at the command prompt will give you a lot of information about how to use it.

    "ppm install DBD::mysql" is supposed to work, but has a conflict. So you need to use: "ppm install" which installs the mysql wrapper that you need.

    You have to be in ./indigoperl/perl/bin and run the command "ipm install DBI". That will install the drivers for indigoperl/apache.

    Similarly to install the mysql wrapper for indigoperl/apache type: "ipm install".

    Use the admin tool to create user "db_user" with password "password" granting permissions to at least do queries on tables.

    You may need to type mysqld.exe to startup the mysql database. Bring up the client using mysql.exe. Paste the following to create a table to work with.

    create database qafw01;
    use qafw01;
    drop table TestCase;
    drop table TestExecution;
    create table TestCase
    	Description  TEXT,
            RunInterval  INT,
    	QueueWebUrl  TEXT,
    	AppName      TEXT,
    	CommandLine  TEXT,
            TestType     TEXT,
            ScriptOutput TEXT,
            LogType      TEXT,
            CaseStatus   TEXT
    create table TestExecution
    	TestCaseID        INT,
            LastTimeRun       TEXT,
            Result            TEXT,
            ResultDescription TEXT
    -- Setup the following contraints
            alter table TestExecution
            add foreign key(TestCaseID) 
            references TestCase (TestCaseID);
    -- Create some dummy data to query.
    INSERT INTO TestCase
    (Description, RunInterval, QueueWebUrl, AppName,
    CommandLine, TestType, ScriptOutput, LogType,
    VALUES ('Verify...', '3600', 'http://...',
    'Search O&O', '',
    'Perl', 'PIPE', '|', 'A');

    Here's an example of a Perl script that works for me in indigoperl. Just put it in ./indigoperl/apache/cgi-bin/ .

    # In DBD there is "No close statement".
    # Whenever the scalar that holds a database or statement handle
    # loses its value, Msql chooses the appropriate action (frees the
    # result or closes the database connection). So if you want to free
    # the result or close the connection, choose to do one of the following:
    #         undef the handle 
    #         use the handle for another purpose
    #         let the handle run out of scope 
    #         exit the program.
    use strict;
    #To connect to the mysql database
    use Mysql;
    use CGI;
    $| = 1;
    my $co = new CGI;
    print $co->header;
    my $host     = "localhost";
    my $database = "qafw01";
    my $user     = "db_user";
    my $password = "password";
    # Connect to database
    my $dbh = Mysql->connect($host, $database, $user, $password);
    # Do a query
    my $sql_statement = "SELECT * FROM TestCase LIMIT 1;";
    my $sth = $dbh->query($sql_statement);
    # Fetch the result
    my %result = $sth->fetchhash;
    # Print the output
    foreach my $key (keys %result)
       print "$key: $result{$key}" . $co->br . "n";

Leave a Reply