DBI SQLite statement problem with %

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

Dear Monks

What is wrong in the following?

my $value1="query";
my $value2="%";
my $selection = $dbh->selectall_arrayref("SELECT Tag1, Tag2, Term1, Te
+rm2 FROM table WHERE Tag1 LIKE '$value' AND Tag2 LIKE '$value2' ");
[download]

What I am trying to do: My query has sometimes a $value2, sometimes $value2 just doesn't need to be used. I want to avoid to have 2 different contructions of $selection, the first with one codition and the second with 2 conditions ... to keep my code shorter and cleaner.

Comment on DBI SQLite statement problem with % Download Code

Replies are listed 'Best First'.
Re: DBI SQLite statement problem with % by runrig (Abbot) on Mar 22, 2016 at 17:18 UTC
I want to avoid to have 2 different contructions of $selection... Don't avoid it, don't worry about it. Just do it, e.g.: `my @filter; push @filter, "tag1 like '$value1'" if $value1; push @filter, "tag2 like '$value2'" if $value2; $sql .= "WHERE " . join(" AND ", @filter) if @filter;` [download]	[reply] [d/l]
Re^2: DBI SQLite statement problem with % by Your Mother (Archbishop) on Mar 22, 2016 at 19:05 UTC
Agree++. Part of why I like postfix conditionals so well. For the OP, if you're heading down this road, SQL::Abstract is worth looking at. It leads on to things like DBIx::Class. Not everyone agrees ORMs are good for the kids but it's helped me out tremendously during the last decade. :P	[reply]
Re: DBI SQLite statement problem with % by Corion (Patriarch) on Mar 22, 2016 at 15:40 UTC
How does the code you showed fail for you? Have you printed the generated SQL? Does the generated SQL work when run in the SQLite command line client? Please see DBI especially about placeholders and bind values. Have you tried searching for "`Scarlett O'Hara`" ? Maybe DBIx::PreQL is interesting for you.	[reply] [d/l]
Re: DBI SQLite statement problem with % by Pope-O-Matik (Pilgrim) on Mar 23, 2016 at 13:21 UTC
What's wrong? :) First of all, it is using dynamic sql. Use a placeholder instead: Second, passing an optional clause like this can actually slow things down. You should instead have two queries, to have execution shorter and cleaner. Regardless, assuming CASE is supported, and no values have underscore or percent signs in them, you can change it to: `Tag1 LIKE CASE ? WHEN NULL THEN Tag1 ELSE ? END AND Tag2 LIKE CASE ? WHEN NULL THEN Tag2 ELSE ? END` If using =, it would be better, but the point is, checking equality for a column against itself is better then LIKE '%'. Another option, assuming any character can be guaranteed to not be used, for example '!': `? IN (Tag1, '!') AND ? IN(Tag2, '!');` Of course, an ugly where construct should also work: `(? IS NULL OR Tag1 LIKE ?) AND (? IS NULL OR Tag2 LIKE ?);`	[reply] [d/l] [select]
Re: DBI SQLite statement problem with % by soonix (Chancellor) on Mar 23, 2016 at 09:15 UTC
Did you cut and paste, or did you retype it for the monastery? You have `my $value1` but in the query you refer to `$value` (no `1`)	[reply] [d/l] [select]