Programming for SQL: Perl CGI

Perl CGI with PostgreSQL connection is easly to do. First of all you need to know how to program in Perl. I will not be talking about that here but if you visit www.Perl.org you can find a lot of information.

Things you need to do before using these examples.

Install Apache httpd web server.
Install Perl.
Install mod_perl the Perl module that Appache will use.
Install PostgreSQL.
Install Pg.pm, the Perl modual for PostgreSQL. Linux distro's keep moving it from rpm to rpm so you may have to hunt for it.
Configure your Apache to let you run cgi-bin scripts.
Configure your PostgreSQL server to let you connect to it.

Many of the examples below are snipits from the example web site you can download here. The Bold path and file name above the code shows you the file in the example site you can find the code.
Create a basic index.html and to make sure that your cgi-bin in Apache is configured correctly add an include virtual to do a simple text file print to the socket. Socket: The socket is the connection that the client browser has with your page.

www/html/index.html

The next thing to do is make a Perl CGI script and include the PostgreSQL Perl module.
www/cgi-bin/Library.cgi

use Pg;

Make a connection to the PostgreSQL database server and test for an error like so.
www/cgi-bin/Library.cgi

$DBConnection = Pg::connectdb("user=librarian dbname=library");
if($DBConnection->status eq PGRES_CONNECTION_OK)
{
print "We are connected!";
} else
{
print "Error connecting to database!";
}

Construct your SQL command and execute it.
www/cgi-bin/Library.cgi

$SQL_Command = "SELECT * FROM $TableName";
$Result = $DBConnection->exec($SQL_Command);

Find the number of records and fields in the result.
www/cgi-bin/Library.cgi

$NumberOfRecords = $Result->ntuples();
$NumberOfFields = $Result->nfields();

Get the data out of the result.
www/cgi-bin/Library.cgi

$SomeText = $Result->getvalue($ALoopCounter,$BLoopCounter);

Post some data to the database and check for an error.

$SQL_Command = "INSERT INTO mytable VALUES ('PostgreSQL', 'is', 'easy')";
$Result = $DBConnection->exec($SQL_Command);
if($Result->resultStatus ne PGRES_COMMAND_OK)
{
print "Error connecting to database!";
} else
{
print "We are connected!";
}