Re: [CDBI] select lower(thing) from...

Oliver Jeeves wrote:

>Howdy folks!
>
>I've not seen anything to suggest this can be done in CDBI, but I
>thought I'd give it a shot anyway.
>
>I'm trying to do a case insensitive search on a column in a database.
>I'm using SQLite, which doesn't appear to use ILIKE, so the recipe from
>the cookbook for case insensitive searches won't work.
>
>I would imagine that the best way to achieve this, it to convert my
>search term to lower case, and SELECT LOWER(column) from the database
>for the comparrison. Is there any way I could do this directly in
>Class::DBI?
>
>I suspect that I'll just be getting the DB handle and constructing my
>own SQL here, but any suggestions are welcome.
>  
>
Use set_sql.  You'll still have to write the SQL yourself, however once 
it's done you can always run

My::Package::Object->search_set_sql_name_here($param1,$param2);

As below:

My:Package->set_sql(
    current_services => qq{
        SELECT
            table1.*,
            table2.field4 as field4
        FROM table1,table2
        WHERE table1.field  = ?
        AND table2.id = table1.table2_id
    }
);


And to invoke:

my $objects = My::Package->search_current_services($params);

This is a slightly more complex exampel as it used temporary field names 
in the query:

My::Package->columns(TEMP => qw/field4/);


Hope that helps
Gareth