PERL - DBI Module(s)

PERL is capable of running SQL and MySQL queries including: inserts, selects, updates, deletes, etc through a module termed DBI. Often your web host will already have this module as well as DBD::mysql already installed. DBI stands for database interface. Any functions associated with DBI should work with all the available SQL platform including: SQL Server, Oracle, DB2, and MySQL.

Before continuing, be sure the following modules are installed:

DBI
DBD::mysql

Once they are installed, we can build the introduction to our script by telling PERL to use these modules as follows:

dbimodules.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING

use DBI;

use DBD::mysql;

Again, these modules allow for us to call upon functions specific to working with a any database platform including MySQL. These modules must be in "use" to ensure proper functionality of our scripts.

PERL - DBI Config

We will be calling on our database, table, and host machine from time to time. We recommend setting up a some variables for your database and table name, so that you can call upon them as you wish throughout this brief tutorial. You may also set up some variables for your user name and password as we will also be needing to connect to your MySQL web host.

dbiconfig.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING

use DBI;

use DBD::mysql;

# DBI CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

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

The variable $DBI::errstr contains the error information.

PERL - DBI Query

Queries must be prepared and then executed. Two lines of code are required for this, first the prepare() function and then the execute() function.

PERL - DBI Prepare()

Inside the prepare() function lies the actual SQL query. Essentially the prepare function acts precisely like the console of an SQL platform. If you've been following along, all we need to do is define a variable with a(n) SQL statement. Then create a query handle and run our $connect statement along with the prepare function as outlined below.
The only main difference is that we have to use PERL's escaping characters and we probably have to use them more often.

dbipreparequery.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

$connect = DBI->connect($dsn, $user, $pw);

# PREPARE THE QUERY

$query = "INSERT INTO inventory (id, product, quantity) VALUES (DEFAULT, tomatoes, 4)";

$query_handle = $connect->prepare($query);

PERL - DBI Execute

Once the query has been prepared, we must execute the command with the execute function. This is accomplished in one final line appended to the code above.

dbiexecutequery.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:$platform:$database:$host:$port";

# PERL DBI CONNECT

$connect = DBI->connect($dsn, $user, $pw);

# PREPARE THE QUERY

$query = "INSERT INTO inventory (id, product, quantity) VALUES (DEFAULT, 'tomatoes', '4')";

$query_handle = $connect->prepare($query);

# EXECUTE THE QUERY

$query_handle->execute();

PERL - DBI Select Queries

Select queries fetch results and then return those results in the form of an array. Accessing the results of the array requires first that we bind the columns to variable names. Then we just need to set up a loop to loop through each row and print back the results to our browser.

dbiselectquery.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

$connect = DBI->connect($dsn, $user, $pw);

# PREPARE THE QUERY

$query = "SELECT * FROM inventory ORDER BY id";

$query_handle = $connect->prepare($query);

# EXECUTE THE QUERY

$query_handle->execute();

# BIND TABLE COLUMNS TO VARIABLES

$query_handle->bind_columns(undef, \$id, \$product, \$quantity);

# LOOP THROUGH RESULTS

while($query_handle->fetch()) {

   print "$id, $product, $quantity <br />";

Two new functions were introduced in that last example, the bind_columns and the fetch() functions. Both are fairly self explanatory. Variable names are assigned to our column values via the bind_column function and the fetch() function goes out and fetches the rows matching the query

PERL - MySQL Module

MySQL queries and the like can be executed with PERL via the MySQL Module. This module should already be installed with your web server if not contact your web host.

As a quick overview, this module installs the necessary functions required to execute MySQL queries using a PERL script. Please take note that this module only works with the MySQL platform. Other SQL platforms will require the use of the DBI module discussed in our PERL DBI Module lesson.

PERL - MySQL Config

Before we dive head first into the functions, we may want to set up some config variables that we will be calling upon in each script to first connect to our database. Have the following information easily accessible.

Our Web Host's data source name (DSN)
User Name for the MySQL Database
Password for the MySQL Database
Name of Database
Name of Table(s)

perlmysqlconfig.pl:

#!/usr/bin/perl

# PERL MODULE WE WILL BE USING

use Mysql;

# MySQL CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

A config set-up like this simplifies our connection script and the queries that will be executed later.

PERL - MySQL Connect

The MySQL module works only with the MySQL platform. We can maintain the same variables from the previous example to connect to MySQL.

perlmysqlconnect.pl:

#!/usr/bin/perl

# PERL MODULE

use Mysql;

# HTTP HEADER

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

# CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

# PERL MYSQL CONNECT

$connect = Mysql->connect($host, $database, $user, $pw);

If this script was run on your web server through a web browser, you should be starring at a blank white screen and all is well.

PERL - MySQL listdbs()

Once PERL has established a connection we can execute any of the built in module functions. A great introductory function is the listdbs function. This function reads from the MySQL platform and places the name of each database into an array.

listdbs.pl:

@databases = $connect->listdbs;

We can then loop through this array and print out our results to the browser.

listdbs2.pl:

#!/usr/bin/perl

# PERL MODULES

use Mysql;

# MYSQL CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

# PERL CONNECT()

$connect = Mysql->connect($host, $database, $user, $pw);

# LISTDBS()

@databases = $connect->listdbs;

foreach $database (@databases) {

        print "$database<br />";

PERL - Select Database

In order to perform even the simplest of queries we must first select a database to be working with. Since we have our database name already listed with our config variables, things will be quite simple.

perlmysqlselectdb.pl:

#!/usr/bin/perl

# PERL MODULE

use Mysql;

# MYSQL CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

# PERL CONNECT()

$connect = Mysql->connect($host, $database, $user, $pw);

# SELECT DB

$connect->selectdb($database);

Notice how the syntax requires that we connect to our host each time we perform a function. You will see this with nearly every script we execute. Once we are connected, the sky is the limit as to what queries we can execute.

PERL - List Tables Function

A function exists to list the tables in a database just like the listdbs() function. Use the listtables() function to list each table in a database.

listtables.pl:

#!/usr/bin/perl

use Mysql;

# HTTP HEADER

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

# MYSQL CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

# PERL MYSQL CONNECT()

$connect = Mysql->connect($host, $database, $user, $pw);

# SELECT DB

$connect->selectdb($database);

# LISTTABLES()

@tables = $connect->listtables;

# PRINT EACH TABLE NAME

@tables = $connect->listtables;

foreach $table (@tables) {

        print "$table<br />";

The database is defined when we run the $connect variable. To change the script to a different database simply run a new selectdb() function or change the $database variable.

PERL - MySQL Query

Executing a query using the MySQL module is a two step process - very straight forward. We define a query in the form of a scalar variable then call upon that variable using our connection script and the query function.

perlmysqlquery.pl:

# DEFINE A MySQL QUERY

$myquery = "INSERT INTO $tablename

(id, product, quantity)

VALUES (DEFAULT,'pineapples','15')";

# EXECUTE THE QUERY FUNCTION

$execute = $connect->query($myquery);

PERL - MySQL Insert Query

Here we introduce the affectedrow() function along with the insertid() function. You can probably guess what the affected rows function does but insertid is unique. Inserid() returns the 'id' of the last inserted row, that is it will return an id if you have an id field set up to auto-increment in your MySQL table.

perlinsertquery.pl:

#!/usr/bin/perl

use Mysql;

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

# MYSQL CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

# PERL MYSQL CONNECT()

$connect = Mysql->connect($host, $database, $user, $pw);

# SELECT DB

$connect->selectdb($database);

# DEFINE A MySQL QUERY

$myquery = "INSERT INTO

$tablename (id, product, quantity)

VALUES (DEFAULT,'pineapples','15')";

# EXECUTE THE QUERY FUNCTION

$execute = $connect->query($myquery);

# AFFECTED ROWS

$affectedrows = $execute->affectedrows($myquery);

# ID OF LAST INSERT

$lastid = $execute->insertid($myquery);

print $affectedrows."<br />";

print $lastid."<br />";

These functions could be run without defining them as scalar variables as well.

PERL - MySQL SELECT Query

Queries that use the SELECT clause are a little more exciting. Here we introduce two new functions, the numrows() function and the numbfields() function. Both of these do exactly as they say, one fetches the number of rows returned with as the query executes while the other fetches the number of fields returned.

easyselectfunctions.pl:

#!/usr/bin/perl

use Mysql;

# HTTP HEADER

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

# MYSQL CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

# PERL MYSQL CONNECT()

$connect = Mysql->connect($host, $database, $user, $pw);

# SELECT DB

$connect->selectdb($database);

# DEFINE A MySQL QUERY

$myquery = "SELECT * FROM $tablename";

# EXECUTE THE QUERY

$execute = $connect->query($myquery);

$rownumber = $execute->numrows();

$fieldnumber = $execute->numfields();

# PRINT THE RESULTS

print $rownumber."<br />";

print $fieldnumber."<br />";

Two numbers should be printed to your web browser.

PERL - MySQL fetchrow()

The fetchrow() function does exactly as it says it does, it goes out and fetches a row that matches your MySQL Query. An array is returned and each element represents a column value for the fetched row. If the query is intended to return multiple rows, fetchrow() must be called again and again. This is easily accomplished with a while loop.

fetchrow.pl:

#!/usr/bin/perl

use Mysql;

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

# MYSQL CONFIG VARIABLES

$host = "localhost";

$database = "store";

$tablename = "inventory";

$user = "username";

$pw = "password";

# PERL MYSQL CONNECT()

$connect = Mysql->connect($host, $database, $user, $pw);

# SELECT DB

$connect->selectdb($database);

# DEFINE A MySQL QUERY

$myquery = "SELECT * FROM $tablename";

# EXECUTE THE QUERY FUNCTION

$execute = $connect->query($myquery);

# HTML TABLE

print "<table border='1'><tr>

<th>id</th>

<th>product</th>

<th>quantity</th></tr>";

# FETCHROW ARRAY

while (@results = $execute->fetchrow()) {

        print "<tr><td>"

        .$results[0]."</td><td>"

        .$results[1]."</td><td>"

        .$results[2]."</td></tr>";

print "</table>";

Pages

Tuesday, 25 March 2014

PERL DATABASE

dbimodules.pl:

PERL - DBI Config

dbiconfig.pl:

PERL - Data Source Name (DSN)

datasourcename.pl:

localhost.pl:

PERL - DBI Connect

DBIconnect.pl:

PERL - Database Handle

databasehandle.pl:

PERL - Connection Error(s)

databasehandle.pl:

PERL - DBI Query

PERL - DBI Prepare()

dbipreparequery.pl:

PERL - DBI Execute

dbiexecutequery.pl:

PERL - DBI Select Queries

dbiselectquery.pl:

PERL - MySQL Module

PERL - MySQL Config

perlmysqlconfig.pl:

PERL - MySQL Connect

perlmysqlconnect.pl:

PERL - MySQL listdbs()

listdbs.pl:

listdbs2.pl:

PERL - Select Database

perlmysqlselectdb.pl:

PERL - List Tables Function

listtables.pl:

PERL - MySQL Query

perlmysqlquery.pl:

PERL - MySQL Insert Query

perlinsertquery.pl:

PERL - MySQL SELECT Query

easyselectfunctions.pl:

PERL - MySQL fetchrow()

fetchrow.pl:

No comments:

Post a Comment