[Dbix-class] Sql functions in bind parameters question

[Nathan Lewis]

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.