Rails - Sep 2009 - ActiveRecord::StatementInvalid (invalid date) with Oracle

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!

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?

On Mon, Sep 14, 2009 at 5:03 PM, rheenen
<rheenen-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 :D ) 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

-- 
Posted via http://www.ruby-forum.com/.

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


-- 
Posted via http://www.ruby-forum.com/.

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
-- 
Posted via http://www.ruby-forum.com/.

Wow, that *is* an ugly solution, with all respects.... ;)

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)

--~--~---------~--~----~------------~-------~--~----~
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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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:in
`select''
        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:in
`select_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:in
`find_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:in
`find_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

--~--~---------~--~----~------------~-------~--~----~
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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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 ;) Cheers



On Wed, Sep 23, 2009 at 12:45 PM, Martijn van Rheenen
<rheenen-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote:
> 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:in
> `select''
>         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:in
> `select_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:in
> `find_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:in
> `find_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
>
>
--~--~---------~--~----~------------~-------~--~----~
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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
<rhee...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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!

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
<Pepe-gUAqH5+0sKL6V6G2DxALlg@public.gmane.org> 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
<rhee...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---