Mac OSX 10.3 - DBI:CSV - automatically updating the database handle's tables hash after opening a file

[Thuan-Jin Kee]

Hi everyone,

I've got a problem updating the database handle's tables hash when going
to read an already existing table

I'm pretty new to dbi, and I'm working through the examples in the
perldocs for CSV.pm and the exercises at
http://www.perl.com/pub/a/1999/10/DBI.html

and
#http://ironbark.bendigo.latrobe.edu.au/subjects/int32we/

I've got this fragment of runnable code that works ok to create a file
called "customer"

#!/usr/bin/perl -Wall
#script for creating a table called customer as a csv file
use DBI;

my($dbh, $sth);
my($key);

$dbh = DBI->connect("DBI:CSV:f_dir=./")
            or die "Cannot connect: " . $DBI::errstr;

$sth = $dbh->prepare("CREATE TABLE customer
                         (custID   INTEGER NOT NULL,
                          family   CHAR(30) NOT NULL,
                          given    CHAR(30))")
             or die "Cannot prepare: " . $dbh->errstr();

$sth->execute() or die "Cannot execute: " . $sth->errstr();

foreach $key (sort keys %{$dbh->{csv_tables}})
{
        print $key. "=> ". $dbh->{csv_tables}->{$key};
}
$dbh->disconnect() or die "Can't disconnect ". $dbh->errstr();

#end bit that works


and the file "customer" appears with its heading row and everything.

In the next script I try to write to it with an INSERT statement.

Unfortunately even after manually adding the customer table file name to
the $dbh->{csv_tables} hash I still get "bad table name" errors.


#!/usr/bin/perl -Wall
use DBI;

my($dbh, $sth);
my($key, $code);

$dbh = DBI->connect("DBI:CSV:f_dir=./")
            or die "Cannot connect: " . $DBI::errstr;

foreach $key (sort keys %{$dbh->{csv_tables}})
{
        print $key. "=> ". $dbh->{csv_tables}->{$key};
}

$dbh->{csv_tables}->{"customer"} = {file =>"./customer"};

$code = <<MARKER;
INSERT INTO  customer
(custID, family, given)
VALUES
(1, " . $dbh->quote("adams") . ", " . $dbh->quote("mortica") . ");
MARKER

$dbh->do($code)
        or die "Cannot prepare: " . $dbh->errstr();

$dbh->disconnect() or die "Can't disconnect ". $dbh->errstr();

#end of script that doesn't work.

Please help! Without being able to reconnect to the csv file for a simple
insert, I'm at a loss as to how to connect to do SELECTs.

I can't figure out how to discover the tables, especially if there is more
than one table of formerly excel data to read in...

I'm sure there must be a well known and easy data conversion method I'm
missing, because ripping data out of excel generated csv files must be
being done all over the world almost every day, right?

Yours
Jin