6 Replies Latest reply: Jul 26, 2009 9:24 PM by 713988 RSS

    Activerecord - issue with DATE and TIMESTAMP fields

    696433
      Hi,

      I am new to active record so apologies for the newbie question, but I am trying to set it up to access an Oracle database. Please note, this not a rails app and never will be. I want to use JRuby to write dashboard scripts for monitoring an number of back-end ETL data interfaces for which I am primarily responsible.

      My config is as follows:

      Client PC running Windows
      JRuby 1.2.0
      Java 1.6.0_07
      Oracle driver - ojdbc14.jar
      activerecord-2.3.2
      activerecord-jdbc-adapter-0.9.1

      Remote database hosted on Solaris
      Oracle 10.2.03

      My problem is that active record is failing to typecast date and time fields at all.

      For example, the following cutdown example attempts to access 'RUN_DT' which is defined in the database as a DATE (I am seeing the same issue on TIMESTAMP fields too)...


      require 'java'
      require 'ojdbc14.jar'

      puts "Starting active record"
      require 'rubygems'
      gem 'activerecord-jdbc-adapter'
      require 'jdbc_adapter'
      require 'active_record'

      puts "Connecting to MXGN"
      ActiveRecord::Base.establish_connection(
      :adapter => 'jdbc',
      :driver => 'oracle.jdbc.OracleDriver',
      :url => 'jdbc:oracle:thin:@MYHOST:1550:THEDB',
      :username => 'FOO',
      :password => 'bar'
      )
      puts "Running a query"

      class TblMigrationWork < ActiveRecord::Base
      set_table_name "tblmigrationwork"
      end

      result = TblMigrationWork.find(:first, :conditions => 'run_id = 1')
      puts result.run_dt


      Produces....


      Starting active record
      Connecting to MXGN
      Running a query
      C:/gems/gems/activerecord-2.3.2/lib/active_record/base.rb:1964:in `method_missing': undefined method `string_to_time' for #<Class:0x755866> (NoMethodError)
      from C:/gems/gems/activerecord-jdbc-adapter-0.9.1/lib/jdbc_adapter/jdbc_oracle.rb:58:in `string_to_time'
      from C:/gems/gems/activerecord-2.3.2/lib/active_record/attribute_methods.rb:211:in `run_dt'
      from C:/gems/gems/activerecord-2.3.2/lib/active_record/attribute_methods.rb:244:in `method_missing'
      from H:\sandbox\DBPlay\lib\main.rb:25


      I can query VARCHAR and NUMERIC fields with no problems.

      I have had a good look round the forums but I cannot find any reference to this kind of problem. Can you please suggest a solution or where the problem may be ocurring?

      Many Thanks

      Adrian
        • 1. Re: Activerecord - issue with DATE and TIMESTAMP fields
          586655
          Hi Adrian,

          For the JRuby/activerecord-jdbc-adapter/Oracle problem, you can send mail to JRuby user mailing list ([user@jruby.codehaus.org]) . That should help.
          • 2. Re: Activerecord - issue with DATE and TIMESTAMP fields
            288739
            Try to use Oracle enhanced adapter (http://github.com/rsim/oracle-enhanced) instead of JDBC adapter.
            Oracle enhanced adapter now support JRuby as well and I do not have any issues using it with DATE and TIMESTAMP fields.

            Install the adapter:
            sudo jruby -S gem install activerecord-oracle_enhanced-adapter

            and connect to database using:
            ActiveRecord::Base.establish_connection(
            :adapter => 'oracle_enhanced',
            :host => 'MYHOST',
            :port => 1550,
            :database => 'THEDB',
            :username => 'FOO',
            :password => 'bar'
            )
            • 3. Re: Activerecord - issue with DATE and TIMESTAMP fields
              696433
              Hi,

              Thanks for your reply. This certainly seems promising. However, I am getting a connection error now.

              The following...

              jar_loaded = require 'ojdbc14.jar'
              puts "Oracle jar loaded? #{jar_loaded}"

              puts "Starting active record"
              require 'rubygems'
              gem 'activerecord'
              gem 'activerecord-oracle_enhanced-adapter'
              require 'activerecord'

              puts "Connecting to MXGN"
              ActiveRecord::Base.establish_connection(
              :adapter => 'oracle_enhanced',
              :host => 'THEHOST',
              :port => '1550',
              :database => 'THEDB',
              :username => 'THEUSER',
              :password => 'THEPASSWORD'
              )

              ... produces

              Oracle jar loaded? true
              Starting active record
              Connecting to MXGN
              ERROR: ActiveRecord oracle_enhanced adapter could not load Oracle JDBC driver. Please install ojdbc14.jar library.
              ERROR: ActiveRecord oracle_enhanced adapter could not load Oracle JDBC driver. Please install ojdbc14.jar library.
              C:/jruby/jruby-1.2.0/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:76:in `establish_connection': Please install the oracle_enhanced adapter: `gem install activerecord-oracle_enhanced-adapter` (LoadError) (RuntimeError)
              from H:\sandbox\DBPlay\lib\main_enhanced.rb:12


              I'm confused. Am I missing the driver, or have I failed to setup the enhanced Oracle adapter?

              I have tried moving the jar to $JRUBY_HOME/lib too, but the result was the same.

              All help would be greatly appreciated.

              Many Thanks

              Adrian
              • 4. Re: Activerecord - issue with DATE and TIMESTAMP fields
                288739
                The following code tries to load ojdbc14.jar file, try to investigate why it fails:


                require "java"
                require "jruby"
                # Adds JRuby classloader to current thread classloader - as a result ojdbc14.jar should not be in $JRUBY_HOME/lib
                java.lang.Thread.currentThread.setContextClassLoader(JRuby.runtime.jruby_class_loader)

                ojdbc_jar = "ojdbc14.jar"
                if ojdbc_jar_path = ENV["PATH"].split(/[:;]/).find{|d| File.exists?(File.join(d,ojdbc_jar))}
                require File.join(ojdbc_jar_path,ojdbc_jar)
                end
                java.sql.DriverManager.registerDriver Java::oracle.jdbc.driver.OracleDriver.new


                On some Windows machines ENV["PATH"] is not returning anything as environment variables are case sensitive and Windows path is stored in Path - maybe that could be the reason.

                Raimonds
                • 5. Re: Activerecord - issue with DATE and TIMESTAMP fields
                  696433
                  Hi,

                  Sorry for the delay in responding - I have been out of the office for a while.

                  I'm having problems running your example because ENV.split accesses a private method - ENV is a Hash of course.

                  Is there a particular element of the ENV hash I should be using? I tried Path and CLASSPATH, but neither worked.

                  Many Thanks

                  Adrian
                  • 6. Re: Activerecord - issue with DATE and TIMESTAMP fields
                    713988
                    I had a similar issue when i tried my war file on WINDOWS
                    The problem seems that ENV["PATH"] is nil for windows and the value is found in ENV["Path"]. So at the top of environment.rb I added …
                    ENV["PATH"] ||= ENV["Path"]

                    Marcus