CODEWITHSUNDEEP
perlsqliteselectbinddbi
knv17
knv17

Reputation: 101

Perl SQLite DBI SELECT with bind not working

Why does the second SELECT statement in this sample code return an empty result?

use DBI;
use Data::Dumper;

my $dbh = DBI->connect('dbi:SQLite:dbname=test.db', '', '', { AutoCommit =>1, PrintError => 1, RaiseError => 1 }) or die $DBI::errstr;

my $r = $dbh->selectall_arrayref('select 123 where 5 > 2', { Slice => {} }) or die $dbh->errstr;

print Dumper $r;

$r = $dbh->selectall_arrayref('select 123 where 5 > ?', { Slice => {} }, 2) or die $dbh->errstr;

print Dumper $r;

Output

$VAR1 = [
          {
            '123' => 123
          }
        ];
$VAR1 = [];

Upvotes: 1

Views: 260

Answers (2)

harmic
harmic

Reputation: 30577

In the DBD::SQLite documentation it says this:

This is because DBD::SQLite assumes that all the bind values are text (and should be quoted) by default.

One way around it is this:

$r = $dbh->selectall_arrayref('select 123 where 5 > (?+0)', { Slice => {} }, 2)
    or die $dbh->errstr;

Another probably better way is to set the sqlite_see_if_its_a_number database handle attribute before querying, or even when connecting.

$dbh->{sqlite_see_if_its_a_number}=1;

Upvotes: 4

ysth
ysth

Reputation: 98388

Try this instead:

$r = $dbh->selectall_arrayref('select 123 where 5 > 0+?', { Slice => {} }, 2);

Alternatively, do:

my $sth = $dbh->prepare('select 123 where 5 > ?');
$sth->bind_param(1, 2, DBI::SQL_INTEGER);
$sth->execute;
$r = $sth->fetchall_arrayref({});

Upvotes: 3

Related Questions