ActiveRecord::StatementInvalid (invalid date) with Oracle

Rails
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!

4

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

5

My horrible but working solution:

class UploadController < ApplicationController
require ‘dbi’
…
def method
db=DBI.connect(‘dbi:ODBC:giomai_db_dsn’, “sa”, “.43tco376”)

  begin

…
db.execute(“INSERT INTO valutazionescorta values (’#{data}’,
‘#{code}’, #{scorta})”) if prodotto and scorta and scorta > 0
rescue DBI::DatabaseError => e
puts “Errore: #{e.err}”
puts “#{e.errstr}”
ensure
# disconnect from server
db.disconnect if db
end
end
end

6

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)

7

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:

require ‘rubygems’
gem ‘ruby-oci8’
require ‘oci8’

gem ‘activerecord’
gem ‘activerecord-oracle_enhanced-adapter’
require ‘activerecord’

puts(“Testing Oracle OCI gem by selecting record:”)
OCI8.new(‘ccems’, ‘ccems’, ‘XE’).exec(‘SELECT * FROM filelist WHERE
(filelist.fllid = 959)’) do
|r| puts r.join("\n")
end

puts(“Testing oracle_enhanced installation:”)
ActiveRecord::Base.establish_connection(
:adapter => “oracle_enhanced”, :database => “XE”, :username =>
“ccems”,
:password => “ccems”)

puts(“oracle_enhanced connected succesfully”)

puts(“Testing selection of one record”)
class Filelist < ActiveRecord::Base
set_table_name ‘filelist’
set_primary_key ‘fllid’
#set_date_columns ‘creation_datetime’, ‘modification_datetime’,
‘firstrecord_datetime’
#ignore_table_columns ‘creation_datetime’, ‘modification_datetime’,
‘firstrecord_datetime’
end

filelist = Filelist.find(959);

and, as expected, the very last line returns this error:

D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:219:in
log': ArgumentError: invalid date: SELECT * FROM filelist WHERE (filelist.fllid = 959) (ActiveRecord::StatementInvalid) from D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.1/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:958:inselect’
from
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in
select_all_without_query_cache' from D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:62:inselect_all’
from
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:661:in
find_by_sql' from D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1548:infind_every’
from
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1583:in
find_one' from D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1569:infind_from_ids’
from
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:616:in
`find’
from test.rb:27

8

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

9

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.

10

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…