in reply to dbi:SQLite multiple query

Such a variable parameter is, in SQL-context, normally called a 'placeholder', and denoted by '?'.

DBD::SQLite manual on Placeholders

UPDATE: After more careful reading I realize you are looking for the SQL IN construct: select  .... where value1 in ('apple', 'pie') In this case I don't think you can use a placeholder to represent the possibly multiple values inside the IN(), i.e., you need separate sql statements for separate numbers of ? as IN() parameters.

As DBI docs say: "placeholders can only represent single scalar values".

(In PostgreSQL this problem can be avoided by (standard SQL): WHERE value1 = ANY ( ? ) or (postgres-specific SQL): WHERE array[value1] <@ ( ? ) which you can then execute, passing in an arrayref for the (single!) placeholder. Both forms are indexable and fast (in a quick 1GB test table with a rowcount of 10M (retrieving 20 rows) takes less than 1 ms). SQLite does not know how to do this (I actually tried but alas ...)) so you are reduced to constructing SQL-statements dependent on the size of the search-set (see other replies). )

Replies are listed 'Best First'.
Re^2: dbi:SQLite multiple query
by Anonymous Monk on Dec 30, 2015 at 12:44 UTC

    This is my problem, the number of variables is not fixed so that I don't know how to use thw SQL In construct (as far as I know it requiers a fixed number of values, which is not may case)

[reply]

      The best approach is then to create the SQL dynamically but to still use placeholders:

      my %query_columns = (
    value1 => 'foo',
    value2 => 'bar',
);

my @search_values;
my @expressions;

for (sort keys %query_columns) {
    push @search_values, $query_columns{ $_ };
    push @expressions, "$_ = ?";
};

my $sql_expression = join " and ", @expressions;

my $sql_find_rows = join "\n",
     "select value1, value2, ... from mytable",
     "where ($sql_expression)";

my $fetch = $dbh->selectall_arrayref($sql_find_rows, @search_values);

      [download]

      The module SQL::Abstract does this, or something very similar. Before using SQL::Abstract, you should think long and hard about whether your SQL will always stay simple like this or whether you will later on need complex join conditions or renaming columns etc. - I prefer to keep most of the SQL as strings, as all abstractions that allow some language syntax to be translated to SQL fail on not so weird corner cases.

[reply]
[d/l]

        Thank you. I'go into it!

[reply]

In Section Seekers of Perl Wisdom