Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: SQL::Statement confusing literals and identifiers

by grinder (Bishop)
on Mar 11, 2008 at 08:36 UTC ( [id://673430]=note: print w/replies, xml ) Need Help??


in reply to SQL::Statement confusing literals and identifiers

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

• another intruder with the mooring in the heart of the Perl

Replies are listed 'Best First'.
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.

      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;

      • another intruder with the mooring in the heart of the Perl

        That would surely fix the problem in my case, but it's a very incomplete fix. Simply removing the quotes is too minimalistic. That fix would remove some (maybe all) false negatives, but would introduce false positives. Specifically, the following name column pairs become indistinguishable:

        • "foo.bar".baz and foo."bar.baz"
        • "foo.bar" and foo.bar
        • "*" and *

        The simplest solution might be to *add* quotes everywhere internally.

        That said, I'll use the previously suggested SELECT * rather than applying this incomplete patch.

        The problem is that the .csv file referenced has fields with quotes in it. Remove the quotes and the errors should go away. Pay attention to the "chunk" that is referenced in the error, that number should be the line number of the problematic text. I spent a lot of time debugging this same error and once I removed the double-quotes (") from the text in the .csv all errors went away.

      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

      Perhaps Print."set" may be advisable. In that case, the output changes slightly:

      SELECT ID GID CARD "SET" ILLUS NUM

      • another intruder with the mooring in the heart of the Perl

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://673430]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chilling in the Monastery: (2)
As of 2024-03-19 04:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found