Tutorial -- HowTo Setup A MySQL Database

Tutorials
navigation
HowTo Setup A MySQL Database
BASIC OVERVIEW




Start by skimming the mysql site at MySQL.com.







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 indigostar.com







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







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 http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd" 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 http://theoryx5.uwinnipeg.ca/ppms/DBD-mysql.ppd".







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

(

	TestCaseID   INT AUTO_INCREMENT PRIMARY KEY,

	Description  TEXT,

        RunInterval  INT,

	QueueWebUrl  TEXT,

	AppName      TEXT,

	CommandLine  TEXT,

        TestType     TEXT,

        ScriptOutput TEXT,

        LogType      TEXT,

        CaseStatus   TEXT

);



/*

 ....

 */

create table TestExecution

(

	TestExecutionID   INT AUTO_INCREMENT PRIMARY KEY,

	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,

CaseStatus)

VALUES ('Verify...', '3600', 'http://...',

'Search O&O', '\\\\idp\\tests\\000\\tc0001.pl',

'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\.



#!perl



# 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;





#FOR HTTP IO

use CGI;



#autoflush

$| = 1;



#HTML FORMAT OUTPUT VARIABLE

my $co = new CGI;



#START HTML PAGE

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";

}



exit;