2009/9/29 Jeremy Evans <[email protected]>:
>
> On Sep 29, 5:31 am, André <[email protected]> wrote:
>> I'm trying to insert a date object in an Oracle database, and I'm
>> getting the following error. What am I doing wrong, (I'm really trying
>> to insert date of the format: 23-sep-2009 10:42:36)
>>
>> Ruby 1.8.7, Sequel 1.3.4, oci8 1.0.6 (reproduced with 2.0.2) Oracle XE
>> 10.2.0
>> Thanks.
>>
>> Error:
>> stmt.c:539:in oci8lib.so: ORA-01874: time zone hour must be between
>> -12 and 14 (OCIError)
>> from /var/lib/gems/1.8/gems/ruby-oci8-1.0.6/lib/oci8.rb:759:in
>> `exec'
>> from /var/lib/gems/1.8/gems/ruby-oci8-1.0.6/lib/oci8.rb:142:in
>> `do_ocicall'
>> from /var/lib/gems/1.8/gems/ruby-oci8-1.0.6/lib/oci8.rb:759:in
>> `exec'
>> from /var/lib/gems/1.8/gems/ruby-oci8-1.0.6/lib/oci8.rb:255:in
>> `exec'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/adapters/
>> oracle.rb:66:in `execute'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/
>> connection_pool.rb:112:in `hold'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/
>> database.rb:481:in `synchronize'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/adapters/
>> oracle.rb:64:in `execute'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/
>> database.rb:312:in `execute_dui'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/
>> database.rb:319:in `execute_insert'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/dataset.rb:
>> 349:in `execute_insert'
>> from /var/lib/gems/1.8/gems/sequel-3.4.0/lib/sequel/dataset.rb:
>> 204:in `insert'
>> from db_test_new.rb:14:in `test_insert'
>> from db_test_new.rb:22
>> [1] 4815 exit 1 ruby db_test_new.rb
>>
>> I've replicated the same with OCI8 version 2.02.
>>
>> Code sample:
>> require 'rubygems'
>> require 'sequel'
>>
>> $DB = Sequel.connect(:adapter => 'oracle', :database => 'XE', :user =>
>> 'myuser', :password=> 'mypassword')
>>
>> def create_t_test
>> $DB << 'create table t_test (
>> F_ID number,
>> F_DATE date
>> )'
>> end
>>
>> def test_insert
>> $DB[:t_test].insert(
>> :F_ID => 1,
>> :F_DATE => Time.now
>> )
>> end
>>
>> # $DB << 'drop table t_test'
>> create_t_test
>> test_insert
>> puts $DB['select * from t_test'].first
>> $DB << 'drop table t_test'
>>
>> exit
>
> The error indicates that the timezone offset hour is less than -12 or
> greater than 14. That seems quite odd, but using a database logger
> would help. Add the following and post the output:
>
> require 'logger'
> $DB.loggers << Logger.new($stdout)
>
> Also, if the field is really a date field and not a timestamp/datetime
> field, you should probably be using Date.today instead of Time.now.
> Try changing that as well.
Well, Oracle DB has a native type of data which really is a datetime.
I'm trying to insert a date time.
With the logger,
inserting Time.now fails, it's inserting a timestamp.
I, [2009-09-29T16:42:39.395419 #12644] INFO -- : INSERT INTO "T_TEST"
("F_ID", "F_DATE") VALUES (1, TIMESTAMP '2009-09-29
16:42:39.395149+0100')
stmt.c:539:in oci8lib.so: ORA-01874: time zone hour must be between
-12 and 14 (OCIError)
Inserting Date.Today inserts a date.
I, [2009-09-29T16:43:12.615765 #12670] INFO -- : INSERT INTO "T_TEST"
("F_ID", "F_DATE") VALUES (1, DATE '2009-09-29')
So, the incorrect SQL is being issued. The following works:
INSERT INTO T_TEST ("F_ID", "F_DATE") VALUES (123,TO_DATE('1998/05/31
12:34:56', 'YYYY/MM/DD HH24:MI:SS'))
Thanks
André
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-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/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
