[Dbix-class] Sql functions in bind parameters question

[Nathan Lewis]

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.

Does DBIx::Class have an equivalent method to Class::DBI's set_sql that
I could use to write the query directly?  Note: I would use CDBICompat
but that breaks other bits of the code. 

Or is there another way of doing this I am missing?


Thanks very much in advance,

Nathan




http://www.bbc.co.uk/

This e-mail (and any attachments) is confidential and may contain
personal views which are not the views of the BBC unless specifically
stated.
If you have received it in error, please delete it from your system. 
Do not use, copy or disclose the information in any way nor act in
reliance on it and notify the sender immediately. Please note that the
BBC monitors e-mails sent or received. 
Further communication will signify your consent to this.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060103/603136f5/attachment.htm