Re^2: SQL LIKE and Binding Variables

Thanks. I thought blackslash was the right way to escape "%" in a SQL statement also, but I couldn't seem to get the syntax right.

I tried your recommendation, but all I managed to get was a different error: "Modification of a read-only value attempted at ..." and it gives me a reference to the line with the my @values = map { s/([_%])/\\$1/g; "%$_%" } bit. Maybe I didn't implement it quite right?

Here's what my test code looks like at the moment:

#!/usr/bin/perl
use warnings;
use DBI;
my $db = DBI->connect("dbi:SQLite:music.sqlite","","");
my @values = map { s/([_%])/\\$1/g; "%$_%" } ( 'pearl', 'temple');
my $like_clause = join( " or ", map { "Songs.Artist LIKE ?" } @values)
+;
my $sth = $db->prepare( "SELECT * FROM Songs where ($like_clause)" );
my $all = $sth->execute( @values ) or die("Failed to Execute SQL");

foreach my $row (@$all) {
my ($id, $Track, $Title, $Artist, $Album, $Year, $Genre, $Path) = @$ro
+w;
print "\nid: $id\nTrack: $Track\nTitle: $Title\nArtist: $Artist\nAlbum
+: $Album\nYear: $Year\nGenre: $Genre\nPath: $Path\n";
[download]

Any other thoughts?

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 Anonymous Monk on Nov 03, 2009 at 23:07 UTC
$ perl -Mdiagnostics -e "s//1/g for 1" Modification of a read-only value attempted at -e line 1 (#1) (F) You tried, directly or indirectly, to change the value of a constant. You didn't, of course, try "2 = 1", because the compile +r catches that. But an easy way to do the same thing is: sub mod { $_[0] = 1 } mod(2); Another way is to assign to a substr() that's off the end of the s +tring. Yet another way is to assign to a foreach loop VAR when VAR is aliased to a constant in the look LIST: $x = 1; foreach my $n ($x, 2) { $n *= 2; # modifies the $x, but fails on attempt to mo +dify the 2 } Uncaught exception from user code: Modification of a read-only value attempted at -e line 1. at -e line 1 [download]	[reply] [d/l]

Replies are listed 'Best First'.

Re^3: SQL LIKE and Binding Variables
by Anonymous Monk on Nov 03, 2009 at 23:07 UTC

$ perl -Mdiagnostics -e "s//1/g for 1"
Modification of a read-only value attempted at -e line 1 (#1)
    (F) You tried, directly or indirectly, to change the value of a
    constant.  You didn't, of course, try "2 = 1", because the compile
+r
    catches that.  But an easy way to do the same thing is:

        sub mod { $_[0] = 1 }
        mod(2);

    Another way is to assign to a substr() that's off the end of the s
+tring.

    Yet another way is to assign to a foreach loop VAR when VAR
    is aliased to a constant in the look LIST:

            $x = 1;
            foreach my $n ($x, 2) {
                $n *= 2; # modifies the $x, but fails on attempt to mo
+dify the 2
            }

Uncaught exception from user code:
        Modification of a read-only value attempted at -e line 1.
 at -e line 1
[download]

[reply]
[d/l]