talwyn has asked for the wisdom of the Perl Monks concerning the following question:

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 \@Cols
}

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;
}

[download]

Replies are listed 'Best First'.
Re: DBI Long File names?
by waswas-fng (Curate) on Sep 29, 2003 at 19:46 UTC
[reply]
[d/l]
      The quote method did not solve the problem. Placeholders do not appear to be the problem.... see My node Note the update section.

      As much as I want to blame my code it is suspiciously starting to look like a problem with DBI or the underlying DBD::XBASE or XBASE modules.

[reply]
      Good catch - exactly right. Use placeholders - they'll increase performance (not necessarily in this particular situation) and fix all quoting issues in one-fell-swoop. No reason not to use placeholders. Read the perldocs on placeholders by doing 
         perldoc DBI

      [download]
      and search (using the forward slash '/') for 'Placeholders'.

      HTH.
[reply]
[d/l]
      Hey, waswas... great link. It does not, however, solve the problem. Please see My Node and the update section there. Placeholders don't appear to be the solution and neither the quote method nor single-quoting the tablename works. Any further comments you might have would be most appreciated.
[reply]
Re: DBI Long File names?
by hmerrill (Friar) on Sep 29, 2003 at 19:45 UTC
    I don't see any debugging prints in there - those can really help you see what's going on. In each subroutine, just after you 'shift' the parameter(s) off of @_, print each one out just to make sure the subroutine is receiving what you think it's receiving, like: 
        sub get_columns ($) {
       my $log = shift;
       ### Add this ###
       print "get_columns sub TOP: \$log = $log\n";
       my @Cols = ();

    [download]
    And, you should also print the sql out before each 'prepare', like: 
        my $sql = "SELECT * FROM $log";
    ### Add this print ###
    print "get_columns sub just before prepare: \$sql = $sql\n";    
    $sth = $dbobject->prepare($sql);

    [download]
    You get the idea - just make sure you're getting everything you think you are. Post back your results.
[reply]
[d/l]
[select]
      The data retrieval works fine, so long as I am accessing a 8.3 filename. It is the file access that is a problem. The minus sign seems to freak it out... haven't tried just spaces yet but I need to be able to open tables with those long/weird filenames since the application I am trying to use this as a diag tool autogenerates the tables that way. I want a tool faster than loading up foxpro to look at a log.
[reply]
Re: DBI Long File names?
by flounder99 (Friar) on Sep 29, 2003 at 19:48 UTC
    I'm no SQL expert but I think you need to change: 
     $sth = $dbobject->prepare("SELECT * FROM $log");

    [download]
    to something like: 
     $sth = $dbobject->prepare("SELECT * FROM '$log'");

    [download]
    I think the SQL parser thinks you are trying to subtract 20030721 from "Controller"

    UPDATE placeholders would be a better idea.

    --

    flounder

[reply]
[d/l]
[select]
      I thought so too. However, neither the use of single quotes nor the $dbobject->quote method works. I read some about placeholders but I don't see an example where they are used for a tablename. Also the Win32 perldoc is drainbamaged and doesn't support unix style searches. If you have a copy of 'Programming the Perl DBI' page number quotes would be helpful. I read a bit on pg 194... but the explanation seems a bit sparse.

      UPDATE 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 sta
+tement that would prevent the database server from validating the sta
+tement and creating a query execution plan for it.

      [download]
      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;
}

      [download]
[reply]
[d/l]
[select]
        Placeholders and $dbi->quote don't work with table and field names. They only work for data elements.

        For identifiers, each database has its own quoting mechanism. The ANSI standard is double quotes: 

        SELECT foo FROM "$bar"

        [download]
        SQL Server uses square brackets: 
        SELECT foo FROM [$bar]

        [download]

        New versions of DBI have the $dbi->quote_identifier method. It can be used the same as $dbi->quote 

        my $quoted_log = $dbi->quote_identifier($log)
my $sql = "SELECT foo FROM $quoted_log";

        [download]

        This may be new enough that DBD::XBase does not support. Then you will have to find out what quoting mechanism it uses and use that.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom