[Dbix-class] Sql functions in bind parameters question

[Zbigniew Lukasiak]

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/
>
>