perl DBI execute doesn't recognize '?'

Question

I have this code:

if($update[$entity_id])
{
    my $sql = "UPDATE cache SET date = '?', value = '?' WHERE item_id = ? AND level = ? AND type = ?;";
}
else
{
    my $sql = "INSERT INTO cache (date, value, item_id, level, type) VALUES ('?','?',?,?,?);";
}
my $db = $self->{dbh}->prepare(q{$sql}) or die ("unable to prepare");
$db->execute(time2str("%Y-%m-%d %X", time), $stored, $entity_id, 'entity', 'variance');

But when it want to run the update I get this error:

DBD::Pg::st execute failed : called with 5 bind variables when 0 are needed.

Why?

in the $stored variable is text, which can contain space, in the date there is also one so it's needed. — Levente Kálmán, Oct 18 '13 at 17:38
No, either you did not remove them, or you are not getting the same error. Show code. — runrig, Oct 18 '13 at 22:11
Remove the q{} from `->execute(q{$sql})` also. What do you think `q{...}` does? — runrig, Oct 18 '13 at 22:12
Also, see DBI docs, and use RaiseError. You then do not need to do 'or die ...' on DBI methods. — runrig, Oct 18 '13 at 22:14

mpapec · Answer 1 · 2013-10-18T18:01:07.767

You're preparing literal '$sql', but that is not your only problem, lexical $sql variables go out of scope outside {}.

Try,

use strict;
use warnings;
#...

my $sql;
if($update[$entity_id])
{
    $sql = "UPDATE cache SET date = ?, value = ? WHERE item_id = ? AND level = ? AND type = ?";
}
else
{
    $sql = "INSERT INTO cache (date, value, item_id, level, type) VALUES (?,?,?,?,?)";
}
my $st = $self->{dbh}->prepare($sql) or die ("unable to prepare");
$st->execute(time2str("%Y-%m-%d %X", time), $stored, $entity_id, 'entity', 'variance');

score 6 · Accepted Answer · answered Oct 18 '13 at 17:56

6

If you had turned on strict and/or warnings, you would see what your problem is.

You're writing

if (...) {
    my $sql = ...;
} else {
    my $sql = ...;
}
execute($sql);

Which means that the $sql variables that you declare in the if branches aren't in scope and you're trying to execute completely empty SQL.

answered Oct 18 '13 at 17:56

hobbs

223,387
19
210
288

1

Of course, that was the biggest problem. But all answer was helpfull, thank you for all – Levente Kálmán Oct 18 '13 at 18:03

perl DBI execute doesn't recognize '?'

2 Answers2