Kenneth Ölwing <kenn...@olwing.se> writes:
> 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...?
You need to use an arrayrefref to combine literal SQL and bind
parameters:
$rs->search({ data => { like => \[q{? ESCAPE '\'}, '%\_%'] } });
The SQL::Abstract documentation has more information:
https://metacpan.org/pod/SQL::Abstract#Literal-SQL-with-placeholders-and-bind-values-subqueries
And DBIx::Class::ResultSet documents DBIC-specific bind parameter
details:
https://metacpan.org/pod/DBIx::Class::ResultSet#DBIC-BIND-VALUES
>
> 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 <kenn...@olwing.se
>> <mailto:kenn...@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
--
"I use RMS as a guide in the same way that a boat captain would use
a lighthouse. It's good to know where it is, but you generally
don't want to find yourself in the same spot." - Tollef Fog Heen
_______________________________________________
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
