ActiveRecord::StatementInvalid (invalid date) with Oracle

rubyonrails-talk
1

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!

2

As an extra hint: Can this be caused by a wrongly configured NLS_LANG setting on my Windows client, causing ActiveRecord to get an ‘unexpected’ date format back?

3

Martijn van Rheenen wrote:

As an extra hint: Can this be caused by a wrongly configured NLS_LANG setting on my Windows client, causing ActiveRecord to get an 'unexpected' date format back?

Hi Martijn, I've the same problem storing in the Date column.

I'm using this code( with a mix of italian and english words :smiley: ) in the controller

        writing = ValutazioneScorta.create(:Codice => code, :Scorta => scorta,:Data => data ) if code != "" and data

so it's really embarrassing to read:

DBI::DatabaseError: 23000 (515) [Microsoft][ODBC SQL Server Driver][SQL Server]Impossibile inserire il valore Null nella colonna 'Data, tabella 'giomai_db.dbo.ValutazioneScorta'. La colonna non supporta valori Null. INSERT avrà esito negativo.: INSERT INTO [ValutazioneScorta] ([Scorta], [Data], [CodiceMaietta]) VALUES (5, NULL, 'CNC-1320B008AA')

showing "Null for Data( date ) columns" ( "Null nella colonna 'Data, ..." )

I'm sure that is a framework problem but I can't say where the value is lost. Error Stack is in attachment!

Attachments: http://www.ruby-forum.com/attachment/4042/Full_Trace.txt

4

Of course, overriding the create method( how explained here http://railsruby.blogspot.com/2006/01/activerecordstatementinvalid-in.html ) is not a solution.

5

Wow, that is an ugly solution, with all respects… :wink:

I still have not found a solution for the original issue with Oracle, somehow retrieval of ‘date’ / ‘datetime’ values is not working still. I have already tried to set the NLG_LANG environment variable to American, but this did not solve the problem. I will get back to you all with a full stack trace of the error.

(meanwhile I reverted back to Symfony / PHP / Doctrine to at least be able to finish my prototype application, but would rather have seen RoR for this project ofcourse)

6

As far as I can see now, the problem is in the oracle_enhanced adapter or in ActiveRecord. I created this small Ruby script to test each component:

7

Finally, I found the error with ‘invalid date’ on Oracle 8. Seems that oracle not only allows ‘null’ dates, but also ‘empty’ dates. On ‘empty’ dates, the error would come up!

Below is what I changed in oracle_enhanced_oci_connection.rb in lib\ruby\gems\activerecord-oracle_enhanced-adapter-1.2.1\lib\connection_adapters. The changed lines are marked with #MVR as comment above it. Starts around line 128:

          # ruby-oci8 1.0 returns OraDate
          when OraDate
            # MVR: treat 'empty' date/time strings as null too!
            if !(v.hour == 0 && v.minute == 0 && v.second == 0 && v.year == 0 && v.month == 0 && v.day == 0)

              # RSI: added emulate_dates_by_column_name functionality
              if OracleEnhancedAdapter.emulate_dates && (v.hour == 0 && v.minute == 0 && v.second == 0)
                v.to_date

              else
                # code from Time.time_with_datetime_fallback
                begin
                  Time.send(Base.default_timezone, v.year, v.month, v.day, v.hour, v.minute, v.second)

                rescue
                  offset = Base.default_timezone.to_sym == :local ? ::DateTime.local_offset : 0
                  ::DateTime.civil(v.year, v.month, v.day, v.hour, v.minute, v.second, offset)

                end
              end
            end
          # ruby-oci8 2.0 returns Time or DateTime
          when Time, DateTime
            # MVR: treat 'empty' date/time strings as null too!

            if !(v.hour == 0 && v.minute == 0 && v.second == 0 && v.year == 0 && v.month == 0 && v.day == 0)
              if OracleEnhancedAdapter.emulate_dates && (v.hour == 0 && v.min == 0 && v.sec == 0)

                v.to_date
              else
                # recreate Time or DateTime using Base.default_timezone
                begin
                  Time.send(Base.default_timezone, v.year, v.month, v.day, v.hour, v.min, v.sec)

                rescue
                  offset = Base.default_timezone.to_sym == :local ? ::DateTime.local_offset : 0
                  ::DateTime.civil(v.year, v.month, v.day, v.hour, v.min, v.sec, offset)

                end
              end
            end
          else v
          end

Hope this is of any help to those fighting with Oracle :wink: Cheers

8

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.

9

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 :expressionless:

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…