I wrote a quick file dumper which seems to work on windows for short file
names. However I have some files with special characters such as '-' in the
file name. DBI/DBD::XBASE does not seem to like this one bit. Any help or
suggestions would be appreciated. Attached is the code and the error:
ERROR:
C:\Perl\Perlscripts>dumplog.pl "Controller - 20030721"
DBD::XBase::db prepare failed: Extra characters in SQL command near `-
20030721'
at C:\Perl\Perlscripts\dumplog.pl line 44.
Can't call method "execute" on an undefined value at
C:\Perl\Perlscripts\dumplog
.pl line 45.
#Libraries
# DBI is the Data base Interface it uses a module DBD::XBase
# to access Foxpro database files.
#
#
use DBI;
my $basedir = "G:/";
my $logdir = "LOGS";
read_log ( shift );
sub read_log (@){
my $log = join '',@_;
my $href = get_columns ($log);
my $logref = get_log_dump($log);
print " Confirm ". @$logref. " records \n";
print "Dumping Log ".$basedir.$logdir."/$log\n";
foreach $field ( @$href ) {
print "$field\t";
}
print "\n###################################################\n";
foreach my $log_row ( @$logref){
foreach my $log_entry (@$log_row) {
print "$log_entry\t";
}
print "\n";
}
}
sub get_columns ($) {
my $log = shift;
my @Cols = ();
my $dbobject = DBI->connect("DBI:XBase:".$basedir.$logdir)
or die $DBI::errstr;
# sth is used for the variable it means statement handle
my $sql = "SELECT * FROM $log";
$sth = $dbobject->prepare($sql);
$sth->execute();
for ( $i = 1; $i <= $sth->{NUM_OF_FIELDS}; $i++){
push @Cols, $sth->{NAME}->[$i-1];
}
$dbobject->disconnect();
return [EMAIL PROTECTED]
}
sub get_log_dump ($) {
my $log = shift;
my $dbobject = DBI->connect("DBI:XBase:".$basedir.$logdir)
or die $DBI::errstr;
$sth = $dbobject->prepare("SELECT * FROM $log");
$sth->execute();
my $resultref = $sth->fetchall_arrayref();
print "Retrieved ". @$resultref ." records\n";
return $resultref;
}
Place Holders don't appear to be the solution.
Page 221 of Programming the Perl DBI:
"SELECT name,age FROM ?" # wrong will probably fail.
With most drivers, placeholders can't be used for any element of a statement
that would prevent the database server from validating the statement and
creating a query execution plan for it.
This seems to imply the problem may lie in the driver? (I'm guessing) Am I
off base with this? I also added the following snippet to try to capture the
table name from the database and then open the table. Again everything works
when 8.3 names are used. I get the extra character in SQL statement error
for the weird names.
Comment out or replace read_log(shift) with get_table_list(shift) in the
first listing and add the following subroutine. It is supposed to print the
table name then dump the table for every table in the database.
sub get_table_list ($) {
my $log = shift;
my $dbobject = DBI->connect("DBI:XBase:".$basedir.$logdir)
or die $DBI::errstr;
my @tables = $dbobject->tables;
print "Database contains the following tables:\n________________________\n";
foreach my $table (@tables) {
print "$table\n";
read_log($table);
}
print"\n\n";
$dbobject->disconnect;
}
