<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi,<br>
<br>
Thanks for your response. No joy though :-(<br>
<br>
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:<br>
<br>
I have a very simple db:<br>
###<br>
C:\tmp\dbixtest>sqlite3 -cmd .dump test.db<br>
PRAGMA foreign_keys=OFF;<br>
BEGIN TRANSACTION;<br>
CREATE TABLE [test] ([data] VARCHAR);<br>
INSERT INTO "test" VALUES('AAAA');<br>
INSERT INTO "test" VALUES('BB_BB');<br>
INSERT INTO "test" VALUES('CCCC');<br>
INSERT INTO "test" VALUES('DD_DD');<br>
INSERT INTO "test" VALUES('EEEE');<br>
COMMIT;<br>
SQLite version 3.8.5 2014-06-04 14:06:34<br>
Enter ".help" for usage hints.<br>
sqlite><br>
###<br>
<br>
I used the schema loader (dbicdump) to generate basic classes for
it.<br>
<br>
Sample code to use it:<br>
###<br>
use strict;<br>
use warnings;<br>
<br>
use lib qw(lib);<br>
<br>
use My::Schema;<br>
<br>
my $schema = My::Schema->connect("dbi:SQLite:test.db");<br>
my $rs = $schema->resultset('Test');<br>
<br>
my @allRows = $rs->all();<br>
print "All rows (" . @allRows . " rows found):\n";<br>
print($_->data(), "\n") foreach (@allRows);<br>
<br>
print "===\n";<br>
<br>
my @someRows = $rs->search( { data => { like => '%\_%' }
});<br>
print "Rows with embedded underscores (" . @someRows . " rows
found):\n";<br>
print($_->data(), "\n") foreach (@someRows);<br>
###<br>
<br>
The resulting printout is: (with DBIC_TRACE=1):<br>
<br>
###<br>
C:\tmp\dbixtest>perl x.pl<br>
SELECT me.data FROM test me:<br>
All rows (5 rows found):<br>
AAAA<br>
BB_BB<br>
CCCC<br>
DD_DD<br>
EEEE<br>
===<br>
SELECT me.data FROM test me WHERE ( data LIKE ? ): '%\_%'<br>
Rows with embedded underscores (0 rows found):<br>
###<br>
<br>
To be clear, the expectation would be to see 'BB_BB' and 'DD_DD' at
the end.<br>
<br>
So...clearly it doesn't work for me :-/. Maybe I'm doing something
really silly, but I don't think so...:-)?<br>
<br>
After some looksee I figured out that I can do a naughty literal
thing:<br>
<br>
###<br>
...<br>
my @someRows = $rs->search( { data => { like => \q('%\_%'
ESCAPE '\') } });<br>
...<br>
###<br>
<br>
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:<br>
<br>
SELECT * FROM test WHERE (data LIKE ? ESCAPE '\'); # and bind
the data, works in sqlitestudio<br>
<br>
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...?<br>
<br>
ken1<br>
<br>
<div class="moz-cite-prefix">On 2014-07-21 13:22, Bob MacCallum<br>
wrote:<br>
</div>
<blockquote
cite="mid:CAFLiiKdb8DDfvirbmwEC5Vv0sEgHNMq-J=MNMm1mdpNVpEzuog@mail.gmail.com"
type="cite">
<div dir="ltr">Hi Ken,<br>
<br>
This works for me - did you try the obvious?<br>
<div><br>
$resultset->search({ name => { 'like' => '%\_%' } } )<br>
<br>
cheers,<br>
Bob<br>
<br>
<br>
</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Fri, Jul 18, 2014 at 7:24 PM,
Kenneth Ölwing <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:kenneth@olwing.se" target="_blank">kenneth@olwing.se</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi,<br>
<br>
I happen to have text data in my table that has the '_'
(underscore) character, and I wanted to do a like search.<br>
<br>
So to find all rows with '_' I did:<br>
<br>
my @rows = $rs->search( { data => { like =>
'%_%' } } );<br>
<br>
and running with DBIC_TRACE=1, I see:<br>
<br>
SELECT me.data FROM test me WHERE ( data LIKE ? ): '%_%'<br>
<br>
I didn't get the result I was expecting. And duh, it dawned
on me that '_' is itself a wildcard char...<br>
<br>
After realising that, I researched and found out that I
could write something like this (a direct query):<br>
<br>
SELECT * FROM test WHERE (data LIKE '%\_%' ESCAPE '\');<br>
<br>
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.<br>
<br>
TIA, any help appreciated.<br>
<br>
ken1<br>
<br>
<br>
-----<br>
No virus found in this message.<br>
Checked by AVG - <a moz-do-not-send="true"
href="http://www.avg.com" target="_blank">www.avg.com</a><br>
Version: 2014.0.4716 / Virus Database: 3986/7876 - Release
Date: 07/18/14<br>
<br>
<br>
_______________________________________________<br>
List: <a moz-do-not-send="true"
href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class"
target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a moz-do-not-send="true"
href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a moz-do-not-send="true"
href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/"
target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a moz-do-not-send="true"
href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk"
target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
List: <a class="moz-txt-link-freetext" href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a>
IRC: irc.perl.org#dbix-class
SVN: <a class="moz-txt-link-freetext" href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a>
Searchable Archive: <a class="moz-txt-link-freetext" href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a></pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<p class="" avgcert""="" color="#000000" align="left">No virus
found in this message.<br>
Checked by AVG - <a moz-do-not-send="true"
href="http://www.avg.com">www.avg.com</a><br>
Version: 2014.0.4716 / Virus Database: 3986/7891 - Release Date:
07/21/14</p>
</blockquote>
<br>
</body>
<a></a><p class=""avgcert"" align="left" color="#000000">No virus found in this message.<br>
Checked by AVG - <a href='http://www.avg.com'>www.avg.com</a><br>
Version: 2014.0.4716 / Virus Database: 3986/7894 - Release Date: 07/21/14</p></html>