[Dbix-class] Using 'like' query with data containing wildcard characters requiring ESCAPE?

[Kenneth Ölwing]

Hi,

Thanks for your response. No joy though :-(

So yes, I tried the obvious escaping first thing, but I couldn't get it 
working. When you say it works for you I began to wonder if I'd made a 
mistake...but I can't spot one:

I have a very simple db:
###
C:\tmp\dbixtest>sqlite3 -cmd .dump test.db
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE [test] ([data] VARCHAR);
INSERT INTO "test" VALUES('AAAA');
INSERT INTO "test" VALUES('BB_BB');
INSERT INTO "test" VALUES('CCCC');
INSERT INTO "test" VALUES('DD_DD');
INSERT INTO "test" VALUES('EEEE');
COMMIT;
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
sqlite>
###

I used the schema loader (dbicdump) to generate basic classes for it.

Sample code to use it:
###
use strict;
use warnings;

use lib qw(lib);

use My::Schema;

my $schema = My::Schema->connect("dbi:SQLite:test.db");
my $rs = $schema->resultset('Test');

my @allRows = $rs->all();
print "All rows (" . @allRows . " rows found):\n";
print($_->data(), "\n") foreach (@allRows);

print "===\n";

my @someRows = $rs->search( { data => { like => '%\_%' } });
print "Rows with embedded underscores (" . @someRows . " rows found):\n";
print($_->data(), "\n") foreach (@someRows);
###

The resulting printout is: (with DBIC_TRACE=1):

###
C:\tmp\dbixtest>perl x.pl
SELECT me.data FROM test me:
All rows (5 rows found):
AAAA
BB_BB
CCCC
DD_DD
EEEE
===
SELECT me.data FROM test me WHERE ( data LIKE ? ): '%\_%'
Rows with embedded underscores (0 rows found):
###

To be clear, the expectation would be to see 'BB_BB' and 'DD_DD' at the end.

So...clearly it doesn't work for me :-/. Maybe I'm doing something 
really silly, but I don't think so...:-)?

After some looksee I figured out that I can do a naughty literal thing:

###
...
my @someRows = $rs->search( { data => { like => \q('%\_%' ESCAPE '\') } });
...
###

That does print the expected two rows, but it seems a bit messay to have 
to go to literal sql to get it done, especially since this should be 
generic code that gets the actual search value from user input and thus 
should work with bind data which apparently works with ESCAPE clause as 
intended:

     SELECT * FROM test WHERE (data LIKE ? ESCAPE '\'); # and bind the 
data, works in sqlitestudio

So even if a literal piece would be required, I don't at the moment see 
how I can do it so bind is used in the Perl code...?

ken1

On 2014-07-21 13:22, Bob MacCallum
  wrote:
> Hi Ken,
>
> This works for me - did you try the obvious?
>
> $resultset->search({ name => { 'like' => '%\_%' } } )
>
> cheers,
> Bob
>
>
>
>
>
> On Fri, Jul 18, 2014 at 7:24 PM, Kenneth Ölwing <kenneth at olwing.se 
> <mailto:kenneth at olwing.se>> wrote:
>
>     Hi,
>
>     I happen to have text data in my table that has the '_'
>     (underscore) character, and I wanted to do a like search.
>
>     So to find all rows with '_' I did:
>
>         my @rows = $rs->search( { data => { like => '%_%' } } );
>
>     and running with DBIC_TRACE=1, I see:
>
>         SELECT me.data FROM test me WHERE ( data LIKE ? ): '%_%'
>
>     I didn't get the result I was expecting. And duh, it dawned on me
>     that '_' is itself a wildcard char...
>
>     After realising that, I researched and found out that I could
>     write something like this (a direct query):
>
>         SELECT * FROM test WHERE (data LIKE '%\_%' ESCAPE '\');
>
>     Being a beginner with DBIx::Class, I may not have found the right
>     place to look, but so far no luck...can I achieve this effect with
>     the Perl code, and if so how? I'm using SQLite for the moment and
>     Perl 5.16 and latest DBIx::Class.
>
>     TIA, any help appreciated.
>
>     ken1
>
>
>     -----
>     No virus found in this message.
>     Checked by AVG - www.avg.com <http://www.avg.com>
>     Version: 2014.0.4716 / Virus Database: 3986/7876 - Release Date:
>     07/18/14
>
>
>     _______________________________________________
>     List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>     IRC: irc.perl.org#dbix-class <http://irc.perl.org#dbix-class>
>     SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>     Searchable Archive:
>     http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>
>
> No virus found in this message.
> Checked by AVG - www.avg.com <http://www.avg.com>
> Version: 2014.0.4716 / Virus Database: 3986/7891 - Release Date: 07/21/14
>




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4716 / Virus Database: 3986/7894 - Release Date: 07/21/14
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20140721/f8dcdbca/attachment.htm>