[Dbix-class] Sql functions in bind parameters question

[Krzysztof Krzyzaniak]

No problemo, traffic is not high and better have all answers here :-)

Nathan Lewis wrote:
> Thank you very much!
> 
> That was what I was looking for but missed in the documentation.
> 
> Cheers,
> 
> Nathan
> 
> And my apologies to the rest of the list, I don't see how to reply only
> to the sender.
> 
> -----Original Message-----
> From: dbix-class-bounces at lists.rawmode.org
> [mailto:dbix-class-bounces at lists.rawmode.org] On Behalf Of Zbigniew
> Lukasiak
> Sent: 03 January 2006 14:13
> To: dbix-class at lists.rawmode.org
> Subject: Re: [Dbix-class] Sql functions in bind parameters question
> 
> 
> Hi,
> 
> Have a look at search_literal.  It allows to pass a literal WHERE clause
> together with bind values.
> 
> -- Zbyszek
> 
> On 1/3/06, Nathan Lewis <Nathan.Lewis at bbc.co.uk> 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.
>>
>>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.
>>_______________________________________________
>>List:
>>http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
>>Wiki: http://dbix-class.shadowcatsystems.co.uk/
>>IRC: irc.perl.org#dbix-class
>>SVN:
>>http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
>>
>>
> 
> 
> 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.
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/


-- 
-------e-l-o-y---------------------------e-l-o-y- at -k-o-f-e-i-n-a-.-n-e-t------

       jak to dobrze, że są oceany - bez nich byłoby jeszcze smutniej