#!perl

# Note that we use "DBI:mysqlPP" instead of "DBI:mysql"


my $title = "MySQL Sample Test Script V1.0";
my ($dbname, $user, $password, $host, $port, $socket) = ("test", "", "", "localhost", "", "");


BEGIN {
    $| = 1;
    open (STDERR, ">&STDOUT");
    #uncomment line below to view compiler errors
    #print "Content-type: text/html\n\n<pre>\n";
}

use strict;
use DBI;

print "Content-type: text/html\n\n";
print "<html><body>\n";
print "$title on ", scalar localtime(), "<br><hr>\n";

my $dbh;
$socket = "/tmp/mysql.sock" if ($host eq "localhost" && $^O ne "Win32");
my $dsn = "DBI:mysqlPP:$dbname:host=$host:port=$port:mysql_socket=$socket";
my $dbh = db_connect($dsn, $user, $password);

my $table = "test_table";
my $sql;
db_dosql($dbh, "DROP TABLE IF EXISTS $table");

db_dosql($dbh, "CREATE TABLE $table (
    subject varchar(80),
    msg varchar(255)
)");

db_addrow("$table", "subject" => "This is subject1", "msg" => "This is message 1");
db_addrow("$table", "subject" => "This is subject2", "msg" => "This is message 2");
db_listrows($table);

$dbh->disconnect;
print "</body></html>\n";
exit(0);


sub db_connect
{
    my ($dsn, $user, $password) = @_;
    #print "Connecting to dsn = $dsn<br>\n";
    my $dbh = DBI->connect($dsn, $user, $password, {PrintError => 0}) ||
                quit("Can't connect to the mysql server.\n");
    return $dbh;
}

sub db_dosql
{
	my ($dbh, $sql) = @_;
    $dbh->do($sql) || quit("db_do error, sql = '$sql'\n");
    return $dbh;
}



sub quit
{
    print @_, "<br>" if @_;
    print  "$DBI::errstr <br>\n";
    exit 1;
}

sub db_addrow
{
    my ($table, %data) = @_;

    my @keys = keys %data;
    my @values = map($dbh->quote($_), values %data);

    my $query = "INSERT INTO $table (" . join(",", @keys) . ")\n";
    $query .= "VALUES (" . join(",", @values) . ")";

    #print "query = \n$query\n";
    $dbh->do($query) || quit();

}


sub db_listrows
{
    my ($table) = @_;
    my $sth = $dbh->prepare ("SELECT * FROM $table");
    $sth->execute() || quit();
    print "Contents of table $table<br>\n";
    my $hashref;
    while ($hashref = $sth->fetchrow_hashref()) {
        my $f;
        foreach $f (keys (%{$hashref}) ) {
            print "$f = " . $hashref->{$f} . "<br>\n";
        }
    }
}

