in reply to DBI qw(:sql_types) with placeholders versus SQLite3

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.

Replies are listed 'Best First'.
Re^2: DBI qw(:sql_types) with placeholders versus SQLite3
by mldvx4 (Hermit) on Mar 31, 2024 at 04:39 UTC

    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]

        I would have offloaded the work to the database but the integers in question are from an already complex query involving a three-way join, one of which is a self-join. I'm just barely familiar enough with SQL that I can write the query and do have to think a bit each time I re-examine it. However, if those results can be cached in a temporary table while still preserving the results of that original query, then I'll explore that option just in case. For right now, I like the readability of the Perl-side processing.

[reply]

In Section Seekers of Perl Wisdom