Rails - Aug 2010 - Rails and Oracle - can select date but cannot save

I''m seeing a behavior in Rails that I find very strange. Having worked
quite a bit with Oracle with other languages I would always dutifully do
ALTER SESSION SET NLS_DATE_FORMAT = <my_date_format> and ensure I use
that date format in my application.
With Rails I still have not figured out how to execute an ALTER SESSION
statement after connecting to the database (I''m using the the jdbc
adapter). And sure enough I get a "date format not recognized" error
when trying to save a value to a DATE column. But surprisingly when I
select pre-inserted dates correctly.
The default Rails date format in my platform is YYYY-MM-DD. The default
date format of my Oracle database is DD-MMM-YY. Ultimately what I''m
after is getting my dates to work both on select and save but I would
welcome an explanation as to why Rails can select correctly with the
application and the database date format being out of sync.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Found how to set the date format for DB conversion on the Rails side as 
opposed to Oracle. It''s good but it''s not enough. I put the
following in
overrides.rb:

module ActiveSupport #:nodoc:
  module CoreExtensions #:nodoc:
    module Date #:nodoc:
      # Converting dates to formatted strings, times, and datetimes.
      module Conversions
        DATE_FORMATS[:db] = "%d-%b-%y"
      end
    end
  end
end


This gives me , for Aug 13th 2010, 13-Aug-10. However, Oracle expects 
12-AUG-10 (i.e. month all caps) and continues to reject the date on 
save. Needless to say select continues to work mysteriously.
Need to figure out how to set NLS_DATE_FORMAT when the connection is 
established.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Fri, Aug 13, 2010 at 12:05 PM, Tomasz Romanowski
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Found how to set the date format for DB conversion on the Rails side as
> opposed to Oracle. It''s good but it''s not enough. I put
the following in
> overrides.rb:
>        DATE_FORMATS[:db] = "%d-%b-%y"
> This gives me , for Aug 13th 2010, 13-Aug-10. However, Oracle expects
> 12-AUG-10 (i.e. month all caps) and continues to reject the date on
> save.
  DATE_FORMATS[:db] = ''%d-%b-%y''.upcase

Regardless, you might want to try the oracle_enhanced adapter --
<http://github.com/rsim/oracle-enhanced>

FWIW,
-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
twitter: @hassan

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

What''s the benefit of using Oracle enhanced over 
activerecord-jdbc-adapter? Is is any faster/more reliable?

I think I figured out the problem which imho is a bug in Oracle''s 
activerecord-jdbc-adapter. Through debugging I noticed that Rails 
converts the date using the Oracle''s TIMESTAMP function for example:

INSERT INTO <table_name> VALUES (TIMESTAMP''2010-03-29'').

However Oracle expects TIMESTAMP to be ALWAYS invoked with the format 
''YYYY-MM-DD H24:MI:SS''. That spells always, regardless of the 
NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT settings. If you the time 
portion is not specified, the statement errors out with a "ORA-01861: 
literal does not match format string" message.
Using the jdbc adapter from activerecord-jdbc-adapter 0.9.7 I found the 
problem to be with the JdbcSpec::Oracle::quoted_date method:

module ::JdbcSpec

  module Oracle

    def quoted_date(value)
        %Q{TIMESTAMP''#{super}''}
      end
    end
  end
end

The method first converts the value to the db format as specified by:

ActiveSupport::CoreExtensions::Date::Conversions::DATE_FORMATS[:db] 
which is pre-set to Y-%m-%d".


which gets the date portion right will NEVER work with TIMESTAMP since 
the time portion is missing. For zero times the DATE method should be 
used instead. With the following fix everything works like a charm:

module ::JdbcSpec

  module Oracle

    def quoted_date(value)
      if value.acts_like?(:time)
        %Q{TIMESTAMP''#{super}''}
      else
        %Q{DATE''#{super}''}
      end
    end
  end
end
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sat, Aug 14, 2010 at 8:18 AM, Tomasz Romanowski
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> What''s the benefit of using Oracle enhanced over
> activerecord-jdbc-adapter? Is is any faster/more reliable?
Less buggy -- I had problems with the activerecord-jdbc-adapter that
were fixed by switching to oracle_enhanced. This was a while ago,
and I don''t remember the details off the top of my head.

-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
twitter: @hassan

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.