ActiveRecord::StatementInvalid (invalid date) with Oracle
47 views
Skip to first unread message
rheenen
Sep 14, 2009, 12:00:48 PM9/14/09
to Oracle enhanced adapter for ActiveRecord
Somehow, I can not get any Oracle DATE column working... As soon as I
try, for example:
myactions.first
or just follow the 'show' route using
http://localhost:3000/models/myactions/show/1
I immediately get an error back 'invalid date'. I have tried
everything, tried any combination of this in the model.rb file:
set_date_columns 'creation_datetime', 'modification_datetime',
'firstrecord_datetime'
ignore_table_columns 'creation_datetime', 'modification_datetime',
'firstrecord_datetime'
and also tried any combination in my config\initializers
\oracle_advanced.rb file:
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
self.emulate_integers_by_column_name = true
self.emulate_dates_by_column_name = false
self.emulate_booleans_from_strings = false
self.string_to_date_format = "%d.%m.%Y"
self.string_to_time_format = "%d.%m.%Y %H:%M:%S"
end
specifying dates_by_column as false or true, enabling set_date_columns
or enabling ignore_table_columns options, no matter what, I always get
this specific error back...
Is there some configuration option I am missing, I really would like
what the problem is, since I spent more than 4 hours on this today
already... :(
Thanks for any help!
try, for example:
myactions.first
or just follow the 'show' route using
http://localhost:3000/models/myactions/show/1
I immediately get an error back 'invalid date'. I have tried
everything, tried any combination of this in the model.rb file:
set_date_columns 'creation_datetime', 'modification_datetime',
'firstrecord_datetime'
ignore_table_columns 'creation_datetime', 'modification_datetime',
'firstrecord_datetime'
and also tried any combination in my config\initializers
\oracle_advanced.rb file:
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.class_eval do
self.emulate_integers_by_column_name = true
self.emulate_dates_by_column_name = false
self.emulate_booleans_from_strings = false
self.string_to_date_format = "%d.%m.%Y"
self.string_to_time_format = "%d.%m.%Y %H:%M:%S"
end
specifying dates_by_column as false or true, enabling set_date_columns
or enabling ignore_table_columns options, no matter what, I always get
this specific error back...
Is there some configuration option I am missing, I really would like
what the problem is, since I spent more than 4 hours on this today
already... :(
Thanks for any help!
Raimonds Simanovskis
Sep 15, 2009, 4:00:14 PM9/15/09
to Oracle enhanced adapter for ActiveRecord
Can you show error stack trace so that I could understand where you
get this error?
Can you show your table definition and the record on which you get
error?
And can you show the table record values on which you get this error?
Raimonds
get this error?
Can you show your table definition and the record on which you get
error?
And can you show the table record values on which you get this error?
Raimonds
Martijn van Rheenen
Sep 23, 2009, 6:31:13 AM9/23/09
to oracle-...@googlegroups.com
Sorry for the late response, had to finish a demo for work (which I wanted to do in Ruby, but had to revery to Symfony because of this :( )....
I'm still experiencing this problem, and as requested by Raimonds, here is some more information:
I go to this url:
http://localhost:3000/filelists/show/198
I still get the error (call stack included below) no matter what record I select.
This is my filelist.rb model file:
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
No matter whether I comment out the last two lines (set_date_columns or ignore_table_columns) or one of them, the error still occurs.
This is the database table in Oracle 10g I'm querying:
CREATE TABLE FILELIST
(
FLLID NUMBER(10) NOT NULL,
PROJECT NUMBER(10) NOT NULL,
AUTHOR CHAR(64 BYTE) NOT NULL,
DESCRIPTION CHAR(255 BYTE) NOT NULL,
COMMENTS CHAR(255 BYTE),
COLOR NUMBER(10),
CREATION_DATETIME DATE,
FIRSTRECORD_DATETIME DATE NOT NULL,
NUMBEROFRECORDS NUMBER(10),
TIMEINTERVAL NUMBER(10) NOT NULL,
OVERWRITE NUMBER(3),
MODIFICATION_DATETIME DATE,
QUANTITY NUMBER(10) DEFAULT 1 NOT NULL
)
and this is the data of the specific record I'm querying:
Insert into FILELIST
(FLLID, PROJECT, AUTHOR, DESCRIPTION, COMMENTS, COLOR, FIRSTRECORD_DATETIME, NUMBEROFRECORDS, TIMEINTERVAL, OVERWRITE, MODIFICATION_DATETIME, QUANTITY)
Values
(198, 81, 'test', 'test', 'test', 16711680, TO_DATE('01/01/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 300, 0, TO_DATE('09/02/2004 11:04:29', 'MM/DD/YYYY HH24:MI:SS'), 1);
I'm still experiencing this problem, and as requested by Raimonds, here is some more information:
I go to this url:
http://localhost:3000/filelists/show/198
I still get the error (call stack included below) no matter what record I select.
This is my filelist.rb model file:
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'
No matter whether I comment out the last two lines (set_date_columns or ignore_table_columns) or one of them, the error still occurs.
This is the database table in Oracle 10g I'm querying:
CREATE TABLE FILELIST
(
FLLID NUMBER(10) NOT NULL,
PROJECT NUMBER(10) NOT NULL,
AUTHOR CHAR(64 BYTE) NOT NULL,
DESCRIPTION CHAR(255 BYTE) NOT NULL,
COMMENTS CHAR(255 BYTE),
COLOR NUMBER(10),
CREATION_DATETIME DATE,
FIRSTRECORD_DATETIME DATE NOT NULL,
NUMBEROFRECORDS NUMBER(10),
TIMEINTERVAL NUMBER(10) NOT NULL,
OVERWRITE NUMBER(3),
MODIFICATION_DATETIME DATE,
QUANTITY NUMBER(10) DEFAULT 1 NOT NULL
)
and this is the data of the specific record I'm querying:
Insert into FILELIST
(FLLID, PROJECT, AUTHOR, DESCRIPTION, COMMENTS, COLOR, FIRSTRECORD_DATETIME, NUMBEROFRECORDS, TIMEINTERVAL, OVERWRITE, MODIFICATION_DATETIME, QUANTITY)
Values
(198, 81, 'test', 'test', 'test', 16711680, TO_DATE('01/01/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 0, 300, 0, TO_DATE('09/02/2004 11:04:29', 'MM/DD/YYYY HH24:MI:SS'), 1);
ActiveRecord::StatementInvalid in FilelistsController#show
ArgumentError: invalid date: SELECT * FROM filelist WHERE (filelist.fllid = 198)
RAILS_ROOT: D:/Projects/CCEMSView
Application Trace |
Framework Trace |
Full Trace
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:219:in `log'
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'
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'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:81:in `cache_sql'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:661:in `find_by_sql'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1548:in `find_every'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1583:in `find_one'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:1569:in `find_from_ids'
D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb:616:in `find'
D:/Projects/CCEMSView/app/controllers/filelists_controller.rb:21:in `show'Request
Parameters:
{"id"=>"198"}
---
Response
Headers:
{"Content-Type"=>"",
"Cache-Control"=>"no-cache"}Martijn van Rheenen
Sep 23, 2009, 6:47:06 AM9/23/09
to oracle-...@googlegroups.com
I think I narrowed the error down to this simple Ruby script:
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 (ActiveRecord) 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
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 (ActiveRecord) 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'
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
Raimonds Simanovskis
Sep 23, 2009, 10:03:56 AM9/23/09
to Oracle enhanced adapter for ActiveRecord
It seems that some Time method is failing because of your local time
format. Currently I do not see exactly in which place it is as
ActiveRecord log method is rescuing this exception.
Please instead of
filelist = Filelist.find(959)
execute
Filelist.connection.instance_variable_get("@connection").select
exception.
Raimonds
> D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract_adapter.rb:219:in
> `select'
> from
> D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_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/connect ion_adapters/abstract/query_cache.rb:62:in
> > Application Trace <http://localhost:3000/filelists/show/198#> | Framework
> > Trace <http://localhost:3000/filelists/show/198#> | Full Trace<http://localhost:3000/filelists/show/198#>
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract_adapter.rb:219:in `log'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.1/l ib/active_record/connection_adapters/oracle_enhanced_adapter.rb:958:in `select'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:60:in `select_all'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:81:in `cache_sql'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:60:in `select_all'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:1331:in `perform_action_without_filters'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/filte rs.rb:617:in `call_filters'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/filte rs.rb:610:in `perform_action_without_benchmark'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/bench marking.rb:68:in `perform_action_without_rescue'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.4/lib/active_support/core_ ext/benchmark.rb:17:in `ms'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.4/lib/active_support/core_ ext/benchmark.rb:10:in `realtime'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.4/lib/active_support/core_ ext/benchmark.rb:17:in `ms'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/bench marking.rb:68:in `perform_action_without_rescue'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/rescu e.rb:160:in `perform_action_without_flash'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/flash .rb:146:in `perform_action'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:532:in `send'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:532:in `process_without_filters'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/filte rs.rb:606:in `process'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:391:in `process'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:386:in `call'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/routi ng/route_set.rb:437:in `call'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/dispa tcher.rb:87:in `dispatch'
>
> ...
>
> read more »
format. Currently I do not see exactly in which place it is as
ActiveRecord log method is rescuing this exception.
Please instead of
filelist = Filelist.find(959)
execute
Filelist.connection.instance_variable_get("@connection").select
('SELECT * FROM filelist WHERE (filelist.fllid = 959)')
and then post where in oracle_enhanced_oci_connection.rb you get the
exception.
Raimonds
> `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/l ib/active_record/connection_adapters/oracle_enhanced_adapter.rb:958:in
> (filelist.fllid = 959) (ActiveRecord::StatementInvalid)
> from
> `select'
> from
> D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_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/connect ion_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
>
> 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
>
> > Trace <http://localhost:3000/filelists/show/198#> | Full Trace<http://localhost:3000/filelists/show/198#>
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract_adapter.rb:219:in `log'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.1/l ib/active_record/connection_adapters/oracle_enhanced_adapter.rb:958:in `select'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:60:in `select_all'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:81:in `cache_sql'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:60:in `select_all'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :661:in `find_by_sql'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1548:in `find_every'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1583:in `find_one'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1569:in `find_from_ids'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :616:in `find'
> > D:/Projects/CCEMSView/app/controllers/filelists_controller.rb:21:in `show'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract_adapter.rb:219:in `log'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.1/l ib/active_record/connection_adapters/oracle_enhanced_adapter.rb:958:in `select'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:60:in `select_all'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:81:in `cache_sql'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:60:in `select_all'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :661:in `find_by_sql'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1548:in `find_every'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1583:in `find_one'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1569:in `find_from_ids'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :616:in `find'
> > D:/Projects/CCEMSView/app/controllers/filelists_controller.rb:21:in `show'
>
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract_adapter.rb:219:in `log'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-oracle_enhanced-adapter-1.2.1/l ib/active_record/connection_adapters/oracle_enhanced_adapter.rb:958:in `select'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:60:in `select_all'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/connect ion_adapters/abstract/query_cache.rb:81:in `cache_sql'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :661:in `find_by_sql'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1548:in `find_every'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1583:in `find_one'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1569:in `find_from_ids'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :616:in `find'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:1331:in `send'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1548:in `find_every'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1583:in `find_one'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :1569:in `find_from_ids'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.4/lib/active_record/base.rb :616:in `find'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:1331:in `perform_action_without_filters'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/filte rs.rb:617:in `call_filters'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/filte rs.rb:610:in `perform_action_without_benchmark'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/bench marking.rb:68:in `perform_action_without_rescue'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.4/lib/active_support/core_ ext/benchmark.rb:17:in `ms'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.4/lib/active_support/core_ ext/benchmark.rb:10:in `realtime'
> > D:/Ruby/lib/ruby/gems/1.8/gems/activesupport-2.3.4/lib/active_support/core_ ext/benchmark.rb:17:in `ms'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/bench marking.rb:68:in `perform_action_without_rescue'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/rescu e.rb:160:in `perform_action_without_flash'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/flash .rb:146:in `perform_action'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:532:in `send'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:532:in `process_without_filters'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/filte rs.rb:606:in `process'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:391:in `process'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/base. rb:386:in `call'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/routi ng/route_set.rb:437:in `call'
> > D:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.4/lib/action_controller/dispa tcher.rb:87:in `dispatch'
>
> ...
>
> read more »
Martijn van Rheenen
Sep 23, 2009, 10:59:37 AM9/23/09
to oracle-...@googlegroups.com
Well, I found the problem this way, thanks for that! And thanks for your patience and quick replies ofcourse.
Seems that oracle not only allows 'null' dates, but also 'empty' dates. On 'empty' dates, the error would come up! I totally misjudged the results of the error then, it seems...
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. All I did is make 'empty' dates detectable and skip converting them to a date too, effectively making them 'null' values too ;) Starts around line 128:
Seems that oracle not only allows 'null' dates, but also 'empty' dates. On 'empty' dates, the error would come up! I totally misjudged the results of the error then, it seems...
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. All I did is make 'empty' dates detectable and skip converting them to a date too, effectively making them 'null' values too ;) 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
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
Raimonds Simanovskis
Sep 23, 2009, 11:53:33 AM9/23/09
to Oracle enhanced adapter for ActiveRecord
Is it so that NULL value in CREATION_DATETIME is retrieved by ruby-
oci8 as OraDate value with all year, month, day, hour, minute, second
values as 0?
In my case it is retrieved as nil.
Which ruby-oci8 version are you using (check OCI8::VERSION)? And which
version of Oracle client are you using? And as I understand you are
doing this on Windows?
Wondering which component is causing this strange effect :)
Raimonds
On Sep 23, 5:59 pm, Martijn van Rheenen <rhee...@gmail.com> wrote:
> Well, I found the problem this way, thanks for that! And thanks for your
> patience and quick replies ofcourse.
>
> Seems that oracle not only allows 'null' dates, but also 'empty' dates. On
> 'empty' dates, the error would come up! I totally misjudged the results of
> the error then, it seems...
>
> Below is what I changed in oracle_enhanced_oci_connection.rb in
> lib\ruby\gems\activerecord-oracle_enhanced-adapter-1.2.1\lib\connection_ada pters\.
oci8 as OraDate value with all year, month, day, hour, minute, second
values as 0?
In my case it is retrieved as nil.
Which ruby-oci8 version are you using (check OCI8::VERSION)? And which
version of Oracle client are you using? And as I understand you are
doing this on Windows?
Wondering which component is causing this strange effect :)
Raimonds
On Sep 23, 5:59 pm, Martijn van Rheenen <rhee...@gmail.com> wrote:
> Well, I found the problem this way, thanks for that! And thanks for your
> patience and quick replies ofcourse.
>
> Seems that oracle not only allows 'null' dates, but also 'empty' dates. On
> 'empty' dates, the error would come up! I totally misjudged the results of
> the error then, it seems...
>
> Below is what I changed in oracle_enhanced_oci_connection.rb in
Martijn van Rheenen
Sep 24, 2009, 8:12:59 AM9/24/09
to oracle-...@googlegroups.com
OCI8::VERSION gives '1.0.6'.
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...
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...
Reply all
Reply to author
Forward
0 new messages