Thanks for the tip, pepe... I was optimistic and implemented this in my
class:
def creation_datetime
if (self[:creation_datetime].nil? || self[:creation_datetime].year == 0)
'01-01-1999'.to_date
else
self[:creation_datetime]
end
end
and it would work perfectly, but it does not help for the problem I
encountered, as the values are retrieved from the database *before* this
overridden method is called. I still need my fix in the Oracle Enhanced
adapter, I'm afraid :|
I am now looking into the issue with the developer of the adapter, via the
Oracle Enhanced mailing list, as it is a very, very strange error... When I
have a final conclusion as to how to prevent this error or propertly fix it
in code, I will get back to this list.
Meanwhile, here is what I posted on the Oracle Enhanced mailing list about
the 'guilty' date value in my Oracle database that triggers the error:
When a date field is NULL in Oracle, it *is* retrieved as 'nil' by
ruby-oci8. So that is working perfectly, also in the Oracle Enhanced code.
The problem occurs, when the date field value is not 'nil' nor filled with a
valid date value: it is empty. An 'empty' date is returned as a datetime
with only '0' values by OCI8. And this is where the problem occured.
I created the following SQL to get a better look at the data of a record
that does work, and a record that causes the error:
select fllid, creation_datetime , nvl(creation_datetime, Sysdate),
length(creation_datetime), to_ char(creation_datetime) from filelist where
fllid=959 or fllid=198
It outputs:
FLLID creation_datetime nvl() length() to_char()
----- ----------------- ------------------ -------- ---------
198 24-9-2009 13:49:38
959 9 00-000-00
The date with fllid '198' works perfectly and is treated as a 'nil' value by
OCI and hence by the Oracle Adapter.
The date with fllid '959' is the one that will cause problems: As you can
see, it IS 9 characters long (???) but it is not 'null' (indicated by the
empty nvl(..) column) and it is displayed as an 'empty' date just like the
date above! Only when converting it to 'char', the '0' values are shown...
I tried to create an insert statement that can reproduce a date like this,
but have not succeeded so far. I already tried insert statements like this:
insert into datatest (id) values (1) -- just inserts as 'NULL'
insert into datatest (id, dt) values (2, '') -- just inserts as 'NULL'
insert into datatest (id, dt) values (5, to_date('00/00/0000','MM/DD/YYYY'))
-- is not valid
insert into datatest (id, dt) values (5, to_date('01/01/4712 bc','DD/MM/YYYY
bc')) -- inserts as normal date
insert into datatest (id, dt) VALUES (6, to_date( '00/00/0000 00:00:00 00',
'MM/DD/YYYY HH:MI:SS AM')) -- is not valid
So I'm really a bit at a loss here, how this data was inserted at all into
the database, as you can imagine. I do know this Oracle 10 database was
created by importing (with Oracle's imp.exe) an export (using Oracle's
exp.exe) from a Oracle 8i database...
On Wed, Sep 23, 2009 at 5:10 PM, pepe <[email protected]> wrote:
>
> I don't know if this could help but I had kind of a similar problem at
> one point. After much research I ended up coming across a solution by
> which you can modify in your model the value returned from the DB. You
> have to override the reader method (code below). This worked for me
> only when reading values from the table as I had no problem inserting
> values in it and never inserted a null or empty value but I guess the
> same could be done with the writer method (dob= in this case):
>
> class MyOracleTable < ActiveRecord::Base
> def dob
> self[:dob] or '0001-01-01'.to_date
> end
> end
>
> Every time that a row is retrieved from the table and the value of DOB
> is used the model will return the DOB if there is one or an
> initialized date value. I chose to implement '0001-01-01' but it could
> be anything you want (i.e.: Date.new).
>
> I hope this helps.
>
> On Sep 14, 11:03 am, rheenen <[email protected]> wrote:
> > Hello all. I am a novice Ruby on Rails programmer, starting my first
> > project using a legacy Oracle 10 database. Using 'reverse_scaffold' I
> > have created the models/controllers/views for my existing Oracle
> > tables.
> >
> > All seems to work well, using /model/index, /model/show for most of my
> > tables, *except* when one of the tables contains a Oracle 'date'
> > column, for example when going to ~/filelists/show/959:
> >
> > ActiveRecord::StatementInvalid in FilelistsController#show
> > ArgumentError: invalid date: SELECT * FROM filelist WHERE
> > (filelist.fllid = 959)
> >
> > I already tried updating the record with NULL values for the date
> > columns, or with actual valid date values, but ActiveRecord *or*
> > OracleEnhancedAdapter seems to refuse to load my date values.
> >
> > Is this a known issue or is there something else I should do in my
> > model file to make date support work?
> >
> > Thanks!
> >
>
--~--~---------~--~----~------------~-------~--~----~
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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
