Tomasz Romanowski
2010-Aug-13 01:11 UTC
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 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 groups.google.com/group/rubyonrails-talk?hl=en.
Tomasz Romanowski
2010-Aug-13 19:05 UTC
Re: Rails and Oracle - can select date but cannot save
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 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 groups.google.com/group/rubyonrails-talk?hl=en.
Hassan Schroeder
2010-Aug-13 19:18 UTC
Re: Re: Rails and Oracle - can select date but cannot save
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 -- <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 groups.google.com/group/rubyonrails-talk?hl=en.
Tomasz Romanowski
2010-Aug-14 15:18 UTC
Re: Re: Rails and Oracle - can select date but cannot save
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 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 groups.google.com/group/rubyonrails-talk?hl=en.
Hassan Schroeder
2010-Aug-14 15:24 UTC
Re: Re: Re: Rails and Oracle - can select date but cannot save
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 groups.google.com/group/rubyonrails-talk?hl=en.