#!/usr/local/bin/perl
#############################################################################
# Generic SQL access to Database
# output can either be HTML table (or) XML
# Written by: Sam Sultan
#############################################################################
use CGI "param";
use DBI;
$DBdriv = 'mysql';
$DBname = 'demo';
$DBuser = 'demo';
$DBpswd = 'demo';
$DBhost = '';
$DBport = '';
$sql = 'show tables';
$output = 'HTML';
$output = 'HTML' if (! $output); # if not supplied use HTML
$output = uc($output); # convert to upper case
if ($output eq 'HTML') { #if HTML output
print "Content-type: text/html \n\n";
print "
SQL Access - Sam Sultan ©
Database Results
";
}
if ($output eq 'XML' or $output eq 'XSLT') { #if XML or XSLT output
print "Content-type: text/xml \n\n";
print " \n";
if ($output eq 'XSLT') { #if XSLT, add an XSLT stylesheet
print " \n";
}
print " \n";
}
if ($DBdriv eq 'Oracle') {
$ENV{'ORACLE_HOME'} = "/opt/oracle8i/OraHome1";
$ENV{'LD_RUN_PATH'} = "/opt/oracle8i/OraHome1/lib";
}
$dbh = ($DBhost eq '' && $DBport eq '')
? DBI->connect("DBI:$DBdriv:$DBname","$DBuser","$DBpswd")
: DBI->connect("DBI:$DBdriv:$DBname:$DBhost:$DBport","$DBuser","$DBpswd");
if (!defined($dbh)) {
if ($output eq 'HTML') {
print "Cannot open database connection -
$DBI::errstr";
print " |
|---|
";
}
if ($output eq 'XML' or $output eq 'XSLT') {
print "Cannot open database connection - $DBI::errstr \n";
print "";
}
exit;
}
$sql =~ s/(#|--).*(\n|$)//g; #strip off # or -- comments
$sql =~ s/\/\*(.|\n)*?\*\///g; #strip off /*...*/ comments
($sql =~ /(select|desc|show|explain)/i) #Is this a select,...
? &select() #Yes- call select subroutine
: &update(); #No - call update subroutine
$dbh->disconnect();
if ($output eq 'HTML') {
print " \n";
print " \n";
print " \n";
}
if ($output eq 'XML' or $output eq 'XSLT') {
print " \n";
}
exit(0);
##################### SELECT subroutine ####################################
sub select {
$cursor = $dbh->prepare($sql);
if (!defined($cursor)) {
if ($output eq 'HTML') {
print "Cannot prepare statement:
$sql
$DBI::errstr \n";
}
if ($output eq 'XML' or $output eq 'XSLT') {
print "Cannot prepare statement: $sql - $DBI::errstr \n";
}
return;
}
$rc = $cursor->execute();
if (!defined($rc)) {
if ($output eq 'HTML') {
print " |
|---|
Cannot execute statement:
$sql
$DBI::errstr \n";
}
if ($output eq 'XML' or $output eq 'XSLT') {
print "Cannot execute statement: $sql - $DBI::errstr \n";
}
return;
}
$nameref = $cursor->{'NAME'}; # get ref to column headers
@column_names = @$nameref; # load into array
if ($output eq 'HTML') { # print heading
print " |
|---|
";
foreach $column_name (@column_names) { # loop thru column_names
print "| $column_name | "; # print column headings
}
print "
\n";
}
$rows=0;
while( @columns = $cursor->fetchrow_array() ) {
if ($output eq 'HTML') {
print "";
for ($i=0; $i<@columns; $i++) { #loop thru all columns
$column = $columns[$i];
$column =~ s/\n/
/g; #chamge all \n to
print " $column | ";
}
print "
\n";
}
if ($output eq 'XML' or $output eq 'XSLT') {
print " \n";
for ($i=0; $i<@columns; $i++) { #loop thru all columns
$column_name = $column_names[$i];
$column_name =~ s/\W/_/g; #change non-alpha to _
$column = $columns[$i];
print " <$column_name>$column \n";
}
print "
\n";
}
$rows++;
}
if ($output eq 'HTML') {
print "| $rows rows returned |
\n";
}
}
##################### UPDATE subroutine ####################################
sub update {
$rc = $dbh->do($sql);
if (!defined($rc)) {
if ($output eq 'HTML') {
print "Cannot execute statement:
$sql
$DBI::errstr \n";
}
if ($output eq 'XML' or $output eq 'XSLT') {
print "Cannot execute statement: $sql - $DBI::errstr \n";
}
return;
}
if ($output eq 'HTML') {
($rc == 0)
? print " |
|---|
| Update Performed Successfully \n"
: print " |
|---|
| Update is Successful - $rc rows effected\n";
}
if ($output eq 'XML' or $output eq 'XSLT') {
($rc == 0)
? print "Update Performed Successfully \n"
: print "Update is Successful - $rc rows effected \n";
}
}
###########################################################################################
|
|---|