Forum: Ruby on Rails Oracle Date type errors rails on edit

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Blake (Guest)
on 2006-03-10 20:47
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:in
`select_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
Cody S. (Guest)
on 2006-03-10 21:20
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.
Blake (Guest)
on 2006-03-10 21:25
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
Nick S. (Guest)
on 2006-03-10 21:28
(Received via mailing list)
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
Cody S. (Guest)
on 2006-03-10 21:32
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.
Cody S. (Guest)
on 2006-03-10 21:42
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.
Blake (Guest)
on 2006-03-10 21:48
>> 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
Blake (Guest)
on 2006-03-10 21:49
Can I change it like this:
<         when /date|time/i                     : :datetime
>         when /date|time/i                     : :date
Blake
Blake (Guest)
on 2006-03-10 21:53
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
Cynthia K. (Guest)
on 2006-03-10 22:07
(Received via mailing list)
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'
Nick S. (Guest)
on 2006-03-10 22:25
(Received via mailing list)
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
Blake (Guest)
on 2006-03-10 23:38
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
Michael Schoen (Guest)
on 2006-03-11 00:42
(Received via mailing list)
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'}
Blake (Guest)
on 2006-03-14 00:39
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
Blake (Guest)
on 2006-03-15 17:50
I seem to have fixed it.

I edited "app/views/machines/_form.rhtml"
and the line that read
<%= datetime_select 'machines', 'warranty_expires_on'  %></p>
I changed to
<%= date_select 'machines', 'warranty_expires_on'  %></p>
everything works now.
That was simple. I think you guys didn't know how much of a nube I am.
Blake
This topic is locked and can not be replied to.