in reply to Re^2: SQL LIKE and Binding Variables
in thread SQL LIKE and Binding Variables

Each placeholder can accept only one value so you have to build your SQL statement with the right number of question marks. Or (if your database supports table valued functions) use something like this to split a single string on the database side. (I doubt things like this are supported by SQLite, but I may be mistaken.)

Jenda
Enoch was right!
Enjoy the last years of Rome.

Replies are listed 'Best First'.
Re^4: SQL LIKE and Binding Variables
by Mad_Mac (Beadle) on Nov 06, 2009 at 22:40 UTC

    Oh, wait ... got it.

    my $sql = q{SELECT * FROM Songs WHERE Songs.Artist LIKE '%'||} .join(q
+{||'%' OR Songs.Artist LIKE '%'||},map {"?"} @vals).q{||'%'};

    [download]

    Thanks to everyone for you patience and suggestions!

[reply]
[d/l]
Re^4: SQL LIKE and Binding Variables
by Mad_Mac (Beadle) on Nov 06, 2009 at 22:23 UTC

    Right. I understand about needing a placeholder for each value. I know how to do it with most other SQL operators, such as IN:

    # could be any number of artists
my @vals =("Artist1", "Artist2")
# And now I build the SQL statement with placeholders based on values
my $sql = 'SELECT * FROM Songs WHERE Songs.Artist IN (' .join (", ", m
+ap {"?"} @vals) .')';

    [download]

    What's got me stumped is the syntax do do something like the .join (", ", map {"?"} @vals) . part of the statement, when using a LIKE operator and wrapping it % wildcards.

    Thanks again for your help.

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom