Tuesday, September 22, 2009

DBIx::Class and SQL::Abstract

So, I've been working on this database driven website for a while and I'm rather surprised it took me so long to trip over this problem. How do you write an Oracle date range query with DBIx:Class? (The answer is not, as some might have it "stop using oracle".)

The sql is simple enough, but it has the inherent challenge for an ORM of needing to pass functions into the where clause.

select * from mytable where mydate between to_date('20090922 00:00:00', 'YYYYMMYY HH24:MI:SS') and to_date('20090922 23:59:59', 'YYYYMMYY HH24:MI:SS')

The DBIx:Class Cookbook talks a bit about calling functions from a select clause, but doesn't specifically address the where clause as different from the select clause. And between is sort of a funny case anyhow. A look at SQL::Abstract on which DBIx::Class's where clauses are based yielded examples which *help*. You're supposed to be able to use a reference to an array ref to do something like this:

fieldname =>{$operator, \[$function_snippet => @values_to_plug_in]}

But my code attempting multiple variations on this with the between keyword was not working.
So I sent email to the DBIx::Class folks, and sure enough. Looks like there was a bug in SQL::Abstract. Which got swatted almost instantly. Go Peter Rabbitson.

The following code is a working example of a date range query where start and end are dates passed in.

my $rs = $schema->resultset('User_Read_Log')->search(
{
datetime => {
"between",
[
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$start 00:00:00" ],
\[ "to_date(?, 'YYYYMMDD HH24:MI:SS')", "$end 00:00:00" ]
]
}

This is current and working as of SQL::Abstract to 1.60.

40 comments:

  1. Hi, I have a couple jobs that I would like to advertise on your site or via an email list to inform your readers about Perl programming jobs. Please get back to me as soon as you get a chance.

    Look forward to hearing from you.

    Chris

    Chris Rose | Affiliate Management | w 801.692.4059 | crose@enticelabs.com

    ReplyDelete
  2. 你怎麼能經過一片海,而忘記它的藍?.........................

    ReplyDelete
  3. Drive carefully. It is not only cars that can be recalled by their Maker.............................................

    ReplyDelete
  4. If you can not be kind, at least have the decency to be vague.............................................

    ReplyDelete
  5. 偉大的致富萬能之鑰,正是幫你充分掌握自己心志所必須的自律自制..................................................

    ReplyDelete
  6. 一個人的價值,應該看他貢獻了什麼,而不是他取得了什麼.................................................................                           

    ReplyDelete
  7. 當一個人內心能容納兩樣相互衝突的東西,這個人便開始變得有價值了。............................................................

    ReplyDelete
  8. 人生中最重要的是要自尊、自愛、自立、自強、自信。..................................................

    ReplyDelete
  9. 字是活的,人和環境的觀察是活的,思想是活的。不管怎樣,就是要有一兩樣是活的。否則都是平庸。............................................................

    ReplyDelete
  10. 人應該做自己認為對的事,而不是一味跟著群眾的建議走。..................................................

    ReplyDelete
  11. 唯有用熱情、用智慧去觀察事物,這事物才會把他的秘密,洩漏給我們......................................................................

    ReplyDelete
  12. 一個人的際遇在第一次總是最深刻的,有時候甚至會讓人的心變成永遠的絕緣。......................................................................

    ReplyDelete
  13. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...

    Android App Development Company
    iOS App Development Company

    ReplyDelete
  14. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...
    Fitness SMS
    Fitness Text
    Salon SMS
    Salon Text
    Investor Relation SMS
    Investor Relation Text

    ReplyDelete