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

Greetings. I have a question about prepare-execute statements when passing a list of integers.

I'd like to figure out how to get the first query below ( SELECT recno, body FROM body WHERE recno IN (?); ) to produce the same three db records that the second query produces. Currently Dumper() shows an empty set for the first query but shows the correct three records for the second query. So the first query produces and empty set. The second query produces three records as expected. The difference seems to be a matter of query syntax. I am stumped as to the correct syntax for the prepare statement. 

#!/usr/bin/perl

use DBI;
use Data::Dumper;

use strict;
use warnings;

my %bodies = ();
my $sth;
my @recnos = (2284, 2285, 2286);

my $dbfile="database-example.sqlite3";
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", undef, undef,
                       { AutoCommit => 0, RaiseError => 1 })
    or die("Could not open database '$dbfile': $!\n");

# this produces an empty set
my $query = 'SELECT recno, body FROM body WHERE recno IN (?);';
$sth = $dbh->prepare($query)
    or die("prepare statement failed: $dbh->errstr()\n");
$sth->execute( join(',', @recnos) )
    or die("execute statement failed: $dbh->errstr()\n");

my $bodies = $sth->fetchall_arrayref;

print "Part A\n",Dumper($bodies);

print "-"x39,"\n";

# this produces the expected three records
my $query = 'SELECT recno, body FROM body WHERE recno IN (2284, 2285, 
+2286);';
$sth = $dbh->prepare($query)
    or die("prepare statement failed: $dbh->errstr()\n");
$sth->execute( )
    or die("execute statement failed: $dbh->errstr()\n");

my $bodies = $sth->fetchall_arrayref;

print "Part B\n",Dumper($bodies);

$sth->finish;
$dbh->disconnect;

exit(0);

[download]

A work-around would be to just create a large query string and not worry about the place holder, but somehow I have it in my mind that place holders are the way to go. Sorry, I can't package this one up as a standalone, ready-to-run script as far as I know.

Replies are listed 'Best First'.
Re: DBI qw(:sql_types) with placeholders versus SQLite3
by 1nickt (Canon) on Mar 30, 2024 at 23:55 UTC

    Hi,

    You can't stuff multiple values for IN into a single placeholder. You need one for each. Try something like this: 

    my $query = sprintf('SELECT recno, body FROM body WHERE recno IN (%s)'
+, join ',', map {$_ = '?'} @recnos);
...
$sth->execute(@recnos);

    [download]
    ... or this ... 
    my $query = sprintf('SELECT recno, body FROM body WHERE recno IN (%s)'
+, join ',', ('?') x @recnos);
...
$sth->execute(@recnos);

    [download]

    Hope this helps!

    Update: added second example


    The way forward always starts with a minimal test.
[reply]
[d/l]
[select]

      Thanks, that solved the problem at hand. So I guess there is no way around creating a large query string on the fly, with or without place holders. I do like the sprintf approach, it is more elegant than the alternate approach which I had in mind.

[reply]
[d/l]
        So I guess there is no way around creating a large query string on the fly, with or without place holders.

        Well, you could simply create a temporary table with your big list of items in it and join against it. Let the DB take the strain - that's what it's there for.

        🦛

[reply]

Back to Seekers of Perl Wisdom