Oracle Date type errors rails on edit


#1

I have a table with “expires_on” a DATE field.
“Show” and “Destroy” work, but “Edit” errors:

/usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_view/helpers/date_helper.rb:150:in
select_hour' /usr/local/lib/ruby/gems/1.8/gems/actionpack-1.11.2/lib/action_view/helpers/date_helper.rb:149:inselect_hour’

It only happens when hour:minutes:seconds are all zeros. The edit
interface allows all zeros when creating or updating. It seems that this
duck is not one that is recognized.

If hour:minutes:seconds are all zeros rails displays the DATE as a DATE.
If I add a nonzero time then rail displays DATETIME.

Is there a way around this? I just want a DATE not a DATETIME. That is,
I want a DATE type not a DATE with someone. I have a someone. Anyway,
help me out here. I thought that the duck typing worked like “if time is
zero then assume DATE” other wise assume DATETIME.

actionmailer (1.1.5)
actionpack (1.11.2)
actionwebservice (1.0.0)
activerecord (1.13.2)
activesupport (1.2.5)
rails (1.0.0)
rake (0.6.2)

Blake


#2

We experienced a simular problem on a recent project. We had
Spring/Hibernate app plugged into Oracle, SQL Server, & MySQL. Dates
worked great on SQL Server & MySQL, but Oracle had errors processing
dates from the models.

It turned out Oracle had a strange date format. There were postings on
the Internet saying to change the configured date format on the Oracle
server, but doing that changed the format for everyone using the server.

We changed to a different date component and the problem was solved.


#3

Cody,

It turned out Oracle had a strange date format. There were postings on
the Internet saying to change the configured date format on the Oracle
server, but doing that changed the format for everyone using the server.

Do you have a link to these postings?

We changed to a different date component and the problem was solved.

What exactly did you change?

Blake


#4

No I don’t have a link, but if you can wait until this evening, I could
dig up something to refer you.

We tried processing dates as the native Java date formats and also
comparing them as strings. When we went to edit a date, Oracle would
through an Invalid date format exception.

We switched to a completely different date component that handled dates
differently, but I don’t remember the exact details since it has been a
while.

Again, I’ll look into it when I get home this evening and give you more
information. I might have the source form the project at home in an
archive. It won’t directly help solve the Rails date problem, but it
has to be related the same date format problem on Oracle.

I remember when looking at the date returned by Oracle, it would come
back as something like, “10 March, 2006”. SQL Server & MySQL processed
dates as, “03/10/06” if I recall correctly.

When I get home tonight, I’ll do my best to get you information about
this.


#5

Nick S. wrote:

On 3/10/06, Blake removed_email_address@domain.invalid wrote:

It only happens when hour:minutes:seconds are all zeros. The edit
interface allows all zeros when creating or updating. It seems that this
duck is not one that is recognized.

If hour:minutes:seconds are all zeros rails displays the DATE as a DATE.
If I add a nonzero time then rail displays DATETIME.

I’m not sure what behavior you’re looking for but does the following
patch
help? This basically forces AR to always give you back a DateTime
regardless of the hour:minute:seconds.

require ‘active_record/connection_adapters/oci_adapter’
module ActiveRecord
module ConnectionAdapters
class OCIColumn
def simplified_type(field_type)
case field_type
when /char/i : :string
when /num|float|double|dec|real|int/i : @scale == 0 ? :integer :
:float
when /date|time/i : :datetime
when /lob/i : :binary
end
end
end
end
end

Cheers,
/Nick

Blake,

You’ll have to take this code for a spin, but it looks like the same
type of solution we had to switch to.


#6

require ‘active_record/connection_adapters/oci_adapter’
module ActiveRecord
module ConnectionAdapters
class OCIColumn
def simplified_type(field_type)
case field_type
when /char/i : :string
when /num|float|double|dec|real|int/i : @scale == 0 ? :integer :
:float
when /date|time/i : :datetime
when /lob/i : :binary
end
end
end
end
end

Um… I am a newbie to ruby and rails… What do I do with this?

Blake


#7

On 3/10/06, Blake removed_email_address@domain.invalid wrote:

It only happens when hour:minutes:seconds are all zeros. The edit
interface allows all zeros when creating or updating. It seems that this
duck is not one that is recognized.

If hour:minutes:seconds are all zeros rails displays the DATE as a DATE.
If I add a nonzero time then rail displays DATETIME.

I’m not sure what behavior you’re looking for but does the following
patch
help? This basically forces AR to always give you back a DateTime
regardless of the hour:minute:seconds.

require ‘active_record/connection_adapters/oci_adapter’
module ActiveRecord
module ConnectionAdapters
class OCIColumn
def simplified_type(field_type)
case field_type
when /char/i : :string
when /num|float|double|dec|real|int/i : @scale == 0 ? :integer :
:float
when /date|time/i : :datetime
when /lob/i : :binary
end
end
end
end
end

Cheers,
/Nick


#8

This is how you change the format in sqlplus:

alter session set nls_date_format = ‘yyyy-mm-dd’;

The current default format is ‘dd-mon-yy’. I can understand why it would
choke on that.
Is there a parameter in the oci connection string to set environment up?
Blake


#9

Can I change it like this:
< when /date|time/i : :datetime

    when /date|time/i                     : :date

Blake


#10

On 3/10/06, Blake removed_email_address@domain.invalid wrote:

This is how you change the format in sqlplus:

alter session set nls_date_format = ‘yyyy-mm-dd’;

Is there a parameter in the oci connection string to set environment up?
Blake

Oracle doesn’t distinguish between date and datetime formats. All Oracle
dates have milisecond granularity - but to make things rather confusing,
the
default date format is just the date, with no time component showing. I
don’t know enough about ROR’s date handling to comment on the solution
to
your issue. But for what it’s worth, one can generally set the date
format
via an environment variable with syntax very similar to the stuff above.
If
you wanted a US date with a military time, you would include the
following
in your startup scripts (or in ROR, possibly in one of the
environment.rbfiles?)

export NLS_DATE_FORMAT =‘MM-DD-YYYY HH24:MI:SS’


#11

On 3/10/06, Blake removed_email_address@domain.invalid wrote:

Um… I am a newbie to ruby and rails… What do I do with this?

Sorry for the terse (or lack of) instructions.

You can make a tiny plugin out of this – just put the code in a file
called, say, vendor/plugins/ora_date/init.rb and it should be loaded for
you
on startup. Oh, and yeah I think you should be able to change the
:datetime
to :date, give it a try and let me know if it works for you!

/Nick


#12

Nick S. wrote:

On 3/10/06, Blake removed_email_address@domain.invalid wrote:

Um… I am a newbie to ruby and rails… What do I do with this?

Sorry for the terse (or lack of) instructions.

You can make a tiny plugin out of this – just put the code in a file
called, say, vendor/plugins/ora_date/init.rb and it should be loaded for
you
on startup. Oh, and yeah I think you should be able to change the
:datetime
to :date, give it a try and let me know if it works for you!

/Nick

I tried it both ways, neither ade a differnce.
I’m going to the airport for a flight lesson,
Blake


#13

Blake wrote:

This is how you change the format in sqlplus:

alter session set nls_date_format = ‘yyyy-mm-dd’;

The current default format is ‘dd-mon-yy’. I can understand why it would
choke on that.
Is there a parameter in the oci connection string to set environment up?
Blake

The oracle adapter already does this, ensuring that the app has a sane
(and standard) date format.

conn.exec %q{alter session set nls_date_format = ‘YYYY-MM-DD
HH24:MI:SS’}


#14

I seem to have fixed it.

I edited “app/views/machines/_form.rhtml”
and the line that read
<%= datetime_select ‘machines’, ‘warranty_expires_on’ %>


I changed to
<%= date_select ‘machines’, ‘warranty_expires_on’ %>
everything works now.
That was simple. I think you guys didn’t know how much of a nube I am.
Blake

#15

Michael Schoen wrote:

Blake wrote:

This is how you change the format in sqlplus:

alter session set nls_date_format = ‘yyyy-mm-dd’;

The current default format is ‘dd-mon-yy’. I can understand why it would
choke on that.
Is there a parameter in the oci connection string to set environment up?
Blake

The oracle adapter already does this, ensuring that the app has a sane
(and standard) date format.

conn.exec %q{alter session set nls_date_format = ‘YYYY-MM-DD
HH24:MI:SS’}
This onliner returns the data/time formated as above.
“ruby -r oci8 -e 'OCI8.new(“username”, “password”,
oracle.machine.org”, nil).exec(“select sysdate from dual”) do |r| puts
r.join(”,"); end’"
Blake