PERL - Data Source Name (DSN)

In order to connect to our database platform we first need to know our web server's data source name. This information should be readily accessible in your server's documentation. There are four pieces that actively make up a DSN.

• Name of SQL Platform (SQL Server, Oracle, DB2, MySQL, etc).
• Database Name
• Host Name (www.myhost.com)
• Port Number

This information is available from your web host provider and can be defined in PERL as follows:

datasourcename.pl:

$dsn = "dbi:SQL Platform:database_name:host_name:port";

Since we plan on executing our scripts from our web server through our browser, we can alternatively substitute our host's name with the term localhost.

localhost.pl:

$dsn = "dbi:SQL_Platform:database_name:localhost:port";

PERL - DBI Connect

Previously, we had set up a config script with some information about our web host and SQL platform including a user name and password. We can now plug all those variables into the connection string and connect to our database.
We can establish a connection with a script like the following.

DBIconnect.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# CONFIG VARIABLES
$platform = "mysql";
$database = "store";
$host = "localhost";
$port = "3306";
$tablename = "inventory";
$user = "username";
$pw = "password";

#DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";


# PERL DBI CONNECT
$DBIconnect = DBI->connect($dsn, $user, $pw);

PERL - Database Handle

On a side note, we have also created what is known as a database handle. Our variable, $DBIconnect, is now the handle which we will have to use each time we wish to execute a query. We should probably go ahead and shorten up that handle since we will be using it in every query script.

databasehandle.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# CONFIG VARIABLES
$platform = "mysql";
$database = "store";
$host = "localhost";
$port = "3306";
$tablename = "inventory";
$user = "username";
$pw = "password";

#DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";

# PERL DBI CONNECT (RENAMED HANDLE)
$dbstore = DBI->connect($dsn, $user, $pw);

The handle has been changed from $DBIconnect, to a more descriptive name.

PERL - Connection Error(s)

An error string variable exists for this module. We can further modify our script with the die() function to terminate the script upon connection failure. The error message is usually printed in your web server's error log(s).

databasehandle.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# CONFIG VARIABLES
$platform = "mysql";
$database = "store";
$host = "localhost";
$port = "3306";
$tablename = "inventory";
$user = "username";
$pw = "password";

#DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";

# PERL DBI CONNECT (RENAMED HANDLE)
$dbstore = DBI->connect($dsn, $user, $pw) or die "Unable to connect: $DBI::errstr\n";

Posted in: PERL