in reply to Mysql Select Query by Perl

endymion:

In SQL, you need to quote strings, otherwise they'll be interpreted as column names and such. That's what the warning is trying to tell you. I see you're trying to properly quote the table name and column, but you're failing to quote the value. Try the quote() method.

Frequently, you'll find that using placeholders will give you more robust code, so you may want to learn to use them. It's certainly no harder than building the SQL strings yourself, and less likely to fail. The only problem in this case is that you can't pass in an identifier (at least for the databases I typically use).

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Replies are listed 'Best First'.
Re^2: Mysql Select Query by Perl
by endymion (Acolyte) on Jun 22, 2012 at 12:11 UTC
    Hello roboticus, I can't find the site at the moment, but I had read that the the columns always need to be quoted to identify them als columns from mysql, and the values not ? Couldn't it be that my primary key has a problem ? Because all numbers without letters are taken.
[reply]

      No, that's backwards. In SQL (at least in MySQL), you need to quote string values, not column names. A statement like this:

      SELECT * FROM mytable WHERE customer = Smith;

      tries to find rows where the value of the customer field equals the value of the Smith field. So it gives you an error like the one you got, complaining that there isn't a column named Smith. If you're looking for a customer whose name is Smith, you do:

      SELECT * FROM mytable WHERE customer = 'Smith';

      In Perl/DBI, you can do that a couple different ways. The second one below, using placeholders, is much safer, especially when searching on user-provided data (which is the case most of the time). It's also easier, because you let DBI do the quoting for you.

      my $name = 'Smith';
my $st = $db->prepare( qq| SELECT * FROM mytable WHERE customer = '$na
+me'; | );
$st->execute or die $DBI::errstr;

# or with placeholders
my $name = 'Smith';
my $st = $db->prepare( q| SELECT * FROM mytable WHERE customer = ?; | 
+);
$st->execute($name) or die $DBI::errstr;

      [download]

      Aaron B.
      Available for small or large Perl jobs; see my home node.

[reply]
[d/l]
[select]
        Thanks so much Aaron ! With your descrition I get the information in my head :-) My mistake was not to quote '%s', I directly quoted '$sel'. *lol*
[reply]

In Section Seekers of Perl Wisdom