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

Dear Monks

Before I start creating my own solution(which will surely turn out to be inefficient): which is the standard way to reiterate the query of a sqlite database for multiple values?

my $dbh = DBI->connect( "dbi:SQLite:$our_db" ) || die "Cannot connect:
+ $DBI::errstr";
my $all = $dbh->selectall_arrayref("SELECT ID, value1, value2  FROM ta
+ble WHERE value1 = '$query'");

[download]

$query can be a variable number of values (apple, pie, etc. it is not a fix number) I want to match in my database. The first think I can think of is to use a loop... Thank you for pointing me to the right direction.

Replies are listed 'Best First'.
Re: dbi:SQLite multiple query
by erix (Prior) on Dec 30, 2015 at 11:17 UTC

    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). )

[reply]
[d/l]
[select]

      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]
Re: dbi:SQLite multiple query
by Discipulus (Canon) on Dec 30, 2015 at 11:19 UTC
[reply]
[d/l]
Re: dbi:SQLite multiple query
by Your Mother (Archbishop) on Dec 30, 2015 at 16:49 UTC

    To amplify what Corion said about SQL::Abstract—if you are headed down this road, you might want to jump straight to a DB ORM. DBIx::Class is, in my opinion, the best; it has a steep learning curve so it is an investment in your future and maybe overkill for one or two small projects. You can search for “Perl ORM” here and there to get a lot of information and opinions (including those who say ORMs for DBs are all garbage :P).

[reply]

Back to Seekers of Perl Wisdom