[Dbix-class] Sql functions in bind parameters question

[Matt S Trout]

On Tue, Jan 03, 2006 at 01:55:28PM -0000, Nathan Lewis wrote:
> 
> Hi Everyone, I just joined your list.
> 
> I seem to be hitting my head against a problem in DBI that I can't seem
> to avoid in DBIx::Class. When passing bind parameters, it appears that
> sql functions cannot be used. I run into this problem when constructing
> a search in DBIx::Class
> 
> The database is sqlite (3)
> 
> Before calling the search I set the conditions as follows
> 
>           $conditions->{'abs(strftime("%s",me.published_time))'} =
>                     { '<=' => qq(abs(strftime("%s",'$now'))) };
>           $conditions->{qq(abs(strftime("%s",'$now')))} =
>             { '<=' => q(abs(strftime("%s",me.published_time)) +
> abs(strftime("%s", '1970-01-01T' || me.published_duration))) };
> 
> And I add
> 
> $attribs{logic} = 'and';
> 
> Then run
> 
> @results = $class->search( $conditions, \%attribs ); 
> 
> This generates a query that looks like
> 
> SELECT me.published_time, me.published_duration, <and lots of other
> fields>
> FROM <tablename> me  JOIN <to other tables named in the prefetch> 
> WHERE ( abs(strftime("%s",'2001-10-11T10:11:00')) <= ? AND
> abs(strftime("%s",me.published_time)) <= ? ) ORDER BY published_time
> 
> And the bind parameters are
> 
> abs(strftime("%s",me.published_time)) + abs(strftime("%s", '1970-01-01T'
> || me.published_duration))
> And
> abs(strftime("%s",'2001-10-11T10:11:00'))
> 
> I tried running this query in plain DBI and found that
> If I inserted those parameters directly into the query in the prepare
> statement it all works fine but as bind parameters they do not. I am
> guessing that the problem is that I can't use functions as bind
> parameters, I can only use fields and values as bind parameters.

I do wonder whether that's a bug in the DBD - maybe try and bring it down
to a test case you can submit to the DBD::SQLite RT queue.

You should be able to just load the CDBICompat::ImaDBI segment and not the
rest (or at least no more than ColumnGroups). If this is not the case, a
patch against branches/DBIx-Class-resultset that adds a failing test for this
would be much appreciated.

Oh and please do reply to list saying "yes, this works for me" when something
does, for the benefit of people browsing/googling the archives.

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +