Re^2: SQL LIKE and Binding Variables

Ok, thanks.

If I combine the suggestions from Jenda and erix it seems to work pretty well.

my @vals =("artist1", "artist2");
my $sth = $db->prepare(q{SELECT * FROM Songs WHERE Songs.Artist LIKE '
+%'||?||'%' OR Songs.Artist LIKE '%'||?||'%'});# suggestion from Jenda
my $results = $sth->execute( @vals ) or die("Failed to Execute SQL");
[download]

I'm now struggling to work out the syntax to combine the placeholder '%'||?\\'?' with a join and map statement that I can pass a an array of values.

Does anyone know the trick to get this syntax right?

If it matters - I am running ActivePerl on Vista X64 and using SQLite for my DB back end.

Comment on Re^2: SQL LIKE and Binding Variables Select or Download Code

Replies are listed 'Best First'.
Re^3: SQL LIKE and Binding Variables by Jenda (Abbot) on Nov 06, 2009 at 17:42 UTC
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.	[reply]
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]