ILIKE not supported in MySQL

[gregapompe]

case insensitive search has to be implemented some other way fcd702967da090ac6c5b7e723d10663622b6b438

[racke]

Why? Please give details!

[gregapompe]

Well DBIx just passes ILIKE to sql statment and is not supported in MySQL, what is there to explain :)

Error looks like this: 05/Mar/2015 09:03:43 [12152] error @0.019083> request to GET /api/Node/list crashed: DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ILIKE '%contact%' )' at line 1 [for Statement "SELECT COUNT( * ) FROM node me WHERE ( me.type ILIKE ? )" with ParamValues: 0='%contact%'] at lib/TableEdit/Routes/API.pm line 482 in /usr/local/share/perl/5.14.2/Dancer/Handler.pm l. 101

[racke]

It is just good practice to supply an error message. Maybe we use ILIKE only if database is postgres?

[gregapompe]

It would be best if DBIx would add this functionality (or we should find it, if already exists). I would try to stay away from database specific stuff in TE.

[racke]

What about describing the problem on the mailing list and asking for advice?

[yure]

well ilike would actually be

SELECT * FROM countries WHERE LOWER( countries.title ) LIKE '%kosovo%'

can this be simulated somehow i guess this is DBIx task, but it will probably slower?

On Thu, Mar 5, 2015 at 9:59 AM, Grega Pompe notifications@github.com wrote:

It would be best if DBIx would add this functionality (or we should find it, if already exists). I would try to stay away from database specific stuff in TE.

— Reply to this email directly or view it on GitHub https://github.com/interchange/TableEditor/issues/64#issuecomment-77328508 .

[gregapompe]

That's good solution. I think it should't be much or any slower.

[racke]

OK let's do that.

[racke]

This causes other problems - see GH #68 and http://www.perlmonks.org/?node_id=906652.

[racke]

This happens when the Schema is using DBIx::Class::Helper::Schema::QuoteNames, in this case Interchange6::Schema.

[racke]

Works with Postgres as well.