#! /usr/local/bin/perl

# $Revision: 1.2 $

use CGI qw (:html2);            # The CGI extension
use CGI::Carp qw (carpout);     # The extra carping routine
use DBI;
use DBD::mysql;

$| = 1;
carpout(\*STDOUT);

my( $dbh, $rv, $sth );
my( $tried ) = 0;

print("Content-Type: text/plain\n\n");

CONNECT:
eval {
	$dbh = DBI->connect("DBI:mysql:test_nile", "memyself", "");
};

if ($@) {
	die "Could not install driver ($DBI::err): $DBI::errstr\n";
}

if (!defined($dbh)) {
	print "Could not connect to db ($DBI::err): $DBI::errstr\n";
	if ($DBI::errstr =~ /Unknown database/) {
		die "Tried once already to create DB\n" if $tried;
		print "Trying to create DB\n";
		&create_db();
		$tried++;
		goto CONNECT;
	} else {
		die "Could not connect to db ($DBI::err): $DBI::errstr\n";
	}
}

print "Dropping tables\n";

$rv = $dbh->do(q{
		DROP TABLE title
	}) or warn "Drop title table: ", $dbh->errstr, "\n";

$rv = $dbh->do(q{
		DROP TABLE transact
	}) or warn "Drop transact table: ", $dbh->errstr, "\n";

print "Creating tables\n";

$rv = $dbh->do(q{
		CREATE TABLE title
		(title_id int not null auto_increment primary key,
		name char(80) not null,
		price float not null,
		edition char(10) not null)
	}) or warn "Create title table: ", $dbh->errstr, "\n";

$rv = $dbh->do(q{
		CREATE TABLE transact
		(transact_id int not null auto_increment primary key,
		title_id int not null,
		number_transacted int not null,
		cust_name char(40) not null,
		cust_address char(80) not null,
		card_type char(10) not null,
		card_number char(20) not null)
	}) or warn "Create transact table: ", $dbh->errstr, "\n";

print "Loading title table\n";

while(<DATA>) {

	chomp;

	($name, $price, $edition) = split(/;;/, $_);

	$rv = $dbh->do(qq{
			INSERT INTO title (title_id, name, price, edition)
			VALUES(0, '$name', $price, '$edition')
		}) or warn "Insert into title table: ", $dbh->errstr, "\n";
}

$sth = $dbh->prepare(q{SELECT * FROM title});
$sth->execute || die "Select *: ", $dbh->errstr, "\n";

DBI::dump_results($sth, 100);

$sth->finish;
$dbh->disconnect;

exit 0;

sub create_db {
	
	my ($drh) = DBI->install_driver('mysql');
	my ($rc);
	my ($database) = "test_nile";

	$rc = $drh->func( "localhost", $database, '_CreateDB' );
	print "Return code is $rc\n";
}
__END__
Programming Perl;;24.99;;4th
Learning Perl;;19.99;;7th
Diving for Perl;;99.99;;9th