SQL::Statement confusing literals and identifiers

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

I'm getting an error with DBD::CSV, although it's actually thrown by SQL::Statement:

DBD::CSV::st execute failed:
Execution ERROR: No such column 'PRINT."set"'.

 [for Statement "
      SELECT id,
             gid,
             card,
             "set",
             illus,
             num
        FROM Print
   "] at problem.pl line 41.
[download]

SQL::Statement doesn't seem to recognize that "set" means set. Unfortunately, set must be quoted since it's a reserved word and SQL::Statement dies complaining of that fact when set is left unquoted.

Does anyone have any workarounds for this? Has this been fixed in a newer version? Looks like I'll have to first copy the CSV with different field names. Fortunately, the tool on which I am working is only used to import CSV into a "real database".

>perl -le"use SQL::Statement; print SQL::Statement->VERSION"
1.15

>perl -le"use DBD::CSV; print DBD::CSV->VERSION"
0.22
[download]

Code and data to reproduce:

673399.pl:

use strict;
use warnings;

use DBI                   qw( );
use File::Spec::Functions qw( catfile );

{
   my $db     = '.';
   my $dsn    = "dbi:CSV:f_dir=$db";
   my $user   = undef;
   my $passwd = undef;

   my $dbh = DBI->connect(
      $dsn, $user, $passwd, 
      {
         RaiseError       => 1,
         PrintError       => 0,
         PrintWarn        => 1,
         AutoCommit       => 1,          # No writing, so no need for 
+transactions.
         FetchHashKeyName => 'NAME_lc',
      }
   );

   for (qw( Print )) {
      my $path = catfile($db, "$_.csv");
      # Xyz and "Xyz" are erroneously considered different tables.
      $dbh->{csv_tables}{    $_  }{file} = $path;
      $dbh->{csv_tables}{qq{"$_"}}{file} = $path;
   }

   my $sth = $dbh->prepare('
      SELECT id,
             gid,
             card,
             "set",
             illus,
             num
        FROM Print
   ');

   $sth->execute();

   # ...
}
[download]

Print.csv:

id,gid,card,set,illus,num
1,1,1,1,"Foo Bar",1
[download]

Comment on SQL::Statement confusing literals and identifiers Select or Download Code

Replies are listed 'Best First'.
Re: SQL::Statement confusing literals and identifiers by Thilosophy (Curate) on Mar 11, 2008 at 06:32 UTC
Does anyone have any workarounds for this? Does DBD::CSV support `select * from Print`? That might work (if you do not mind getting columns you do not need), although I cannot test it, your sample fails for me with a rather cryptic error: `DBD::CSV::st execute failed: Error while reading file /tmp/c/Print.csv +: Bad file descriptor at /usr/share/perl5/DBD/CSV.pm line 210, <GEN0> + chunk 1.` [download] :-(	[reply] [d/l] [select]
Re^2: SQL::Statement confusing literals and identifiers by ikegami (Patriarch) on Mar 11, 2008 at 07:22 UTC
The error you are getting is due to an unrelated bug I fixed locally. It will probably stop manifesting itself if you installed Text::CSV_XS. Silly me, of course `SELECT ` will work, especially since I use `fetchrow_hashref`. The queries that use `WHERE` clauses are only done on the "real database" which has no problems with the quotes. Update*: s/It will probably manifesting/It will probably stop manifesting/	[reply] [d/l] [select]
Re^3: SQL::Statement confusing literals and identifiers by Thilosophy (Curate) on Mar 11, 2008 at 10:25 UTC
The error you are getting is due to an unrelated bug I fixed locally. It will probably manifesting itself if you installed Text::CSV_XS. Ahh, that would explain it. I installed everything using Ubuntu's apt-get, and yes, that gave me Text::CSV_XS, too.	[reply]
Re^4: SQL::Statement confusing literals and identifiers by ikegami (Patriarch) on Mar 11, 2008 at 16:29 UTC
Re: SQL::Statement confusing literals and identifiers by grinder (Bishop) on Mar 11, 2008 at 08:36 UTC
SQL::Statement doesn't seem to recognize that "set" means set. String constants use single quote (') delimiters. Column labels use double quotes ("), and `set` in your example is a string constant, not a column label. So the (non-) problem is that it doesn't like parsing illegal SQL. Rewrite your statement as the following, and you should be fine. `SELECT id, gid, card, 'set', illus, num FROM Print` [download] • another intruder with the mooring in the heart of the Perl	[reply] [d/l]
Re^2: SQL::Statement confusing literals and identifiers by Thilosophy (Curate) on Mar 11, 2008 at 10:30 UTC
No, I think ikegami wants the (double-quoted) column `set` (that is what the column in his CSV file is called), not a constant string.	[reply] [d/l]
Re^3: SQL::Statement confusing literals and identifiers (now with workaround!) by grinder (Bishop) on Mar 11, 2008 at 12:29 UTC
Ok, to restore my credibility, here's a workaround: It's a bug alright. At some point the code lines up the requested columns, and the columns the table has to offer. It chokes on the fact that the `q{SET} eq q{"SET"}` equality fails to hold. It's because at some point the double quotes need to be stripped away, to recover the bare column name underneath and that is not (no longer?) happening. What is strange is that there appears to be code in there that does just this, but has been commented out. In any event, the following patch seems to do the trick, but one would have to run the full regression test suite to be sure. `--- Statement.pm Tue Mar 11 13:13:43 2008 +++ /usr/local/lib/perl5/site_perl/5.8.8/SQL/Statement.pm Tue Ma +r 11 13:23:05 2008 @@ -1590,6 +1590,7 @@ ); } next unless $col; + $col =~ s/\A"([^"]+)"\z/$1/; ###new if (ref $table eq 'SQL::Statement::Table') { $table = $table->name;` [download] • another intruder with the mooring in the heart of the Perl	[reply] [d/l]
Re^4: SQL::Statement confusing literals and identifiers (now with workaround!) by ikegami (Patriarch) on Mar 11, 2008 at 19:29 UTC
Re^4: SQL::Statement confusing literals and identifiers (now with workaround!) by bcarroll (Pilgrim) on May 28, 2009 at 14:31 UTC
Re^3: SQL::Statement confusing literals and identifiers by grinder (Bishop) on Mar 11, 2008 at 11:59 UTC
Oh duh, silly me, I should have known ikegami would not make a mistake like that. Still, I think SQL::Statement is playing the game correctly. Witness: `use strict; use warnings; use SQL::Statement; my $stmt = SQL::Statement->new(<<END_SQL, SQL::Parser->new); SELECT id, gid, card, "set", illus, num FROM Print END_SQL print join( "\n\t" => $stmt->command, map{ $_->name } $stmt->columns), + "\n"; __PRODUCES__ SELECT ID GID CARD "set" ILLUS NUM` [download] Perhaps `Print."set"` may be advisable. In that case, the output changes slightly: `SELECT ID GID CARD "SET" ILLUS NUM` [download] • another intruder with the mooring in the heart of the Perl	[reply] [d/l] [select]
Re: SQL::Statement confusing literals and identifiers by roboticus (Chancellor) on Mar 11, 2008 at 03:01 UTC
ikegami: Update: Nevermind ... I just tried it and it doesn't like those either. ~~Try using square brackets or single quotes:~~ ~~`SELECT id, gid, [foo bar]`~~ ~~[download]~~ ~~I know some SQL servers don't care for double quotes (e.g., MS SQL server), so perhaps SQL::Statement doesn't like 'em either....~~ ...roboticus	[reply] [d/l]
Re^2: SQL::Statement confusing literals and identifiers by ikegami (Patriarch) on Mar 11, 2008 at 04:39 UTC
The error occurs on the `execute` line after `prepare` successfully parsed the statement.	[reply] [d/l] [select]


laziness, impatience, and hubris
	PerlMonks