in reply to DBI Long File names?

I'm no SQL expert but I think you need to change:
$sth = $dbobject->prepare("SELECT * FROM $log");
to something like:
$sth = $dbobject->prepare("SELECT * FROM '$log'");
I think the SQL parser thinks you are trying to subtract 20030721 from "Controller"

UPDATE placeholders would be a better idea.

--

flounder

Replies are listed 'Best First'.
Re: Re: DBI Long File names?
by talwyn (Monk) on Sep 29, 2003 at 21:44 UTC
    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.
    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; }
      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"
      SQL Server uses square brackets:
      SELECT foo FROM [$bar]

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

      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.

        Thanks for the information. Unfortunately none of the methods of quoting worked for me. So ... I suppose this means that DBD::XBASE may be broken. I am going to go dive into that now and see if it has some weird quote mechanism or not.
        Neither quoting mechanism nor the quote_identifier method worked. I also attempted low-level access directly through ::XBase which resulted in an 'invalid argument' for long file names. Any one know of an alternative to DBD::XBASE or ::XBase to read/write .dbf files? Specifically Foxpro files?