Activerecord - issue with DATE and TIMESTAMP fields - Ruby/Ruby On Rails(Archived)

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 

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. 

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'
) 

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 

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 

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 

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

Related

loading java class in oracle

Hi
I have to upload a class named hello1.class in oracle 9i
I have Create procedure and create table privilege
I have granted permission by means of
call dbms_java.grant_permission('CUST_REP', 'SYS:oracle.aurora.security.JServerPermission', 'loadLibraryInClass.*', null);
and also
call dbms_java.grant_permission('CUST_REP', 'SYS:oracle.aurora.security.JServerPermission', 'loadLibraryInClass.hello1.class', null);
But when I try to upload class it says insufficient privileges.I have seen in user_java_policy and it does show record inserted for this
Is there anything I am missing out on?Or what would be the required privileges other than this to upload a java class onto a database? 
user497328,
Please provide more information.
1. Exact database version, 9.??
2. Java version, 1.??
3. Platform (windows, linux, solaris, etc)
4. Command you are using to load the java class
5. Entire error message (and stack trace?) you are getting
You may find the answers in the Oracle documentation, which can be accessed from here:
http://www.oracle.com/technology/documentation/index.html
The java documentation is not the same for every release of Oracle, so I can't tell you the exact place to look without knowing what database version you are using. But I believe you should try the Java Developer's Guide.
Good Luck,
Avi. 
Hi Avi
Ok here answer to all ur queries:
1.9i
2.jdk 1.5.0_06
3.databse on linux and batch file ll reside on Oracle Application server
4.m loading java class from toad->tools->java manager
5.ora-01031:insufficient privileges
the java class will call a batch file on Oracle AS to which parameters will be supplied and excel file will b created using JACOB and file will b saved in the AS 
user497328,
I'm sorry, my question was not clear enough. You already mentioned you were using Oracle 9i. I meant the exact version number, for example 9.2.0.4. The reason I asked is because the 9.2.x versions are quite different to the 9.1.x versions.
In any case, Oracle 9i -- all versions -- only supports JDK 1.3. (For your information.)
Personally, when I load java classes into the database, I put the ".class" files on the same machine as the database server, and use the "loadjava" command from the command line.
And I still suggest you read the documentation. Have you done that?
Good Luck,
Avi.

sqlj runtime error with oracle 10G in Websphere6 or 5.1

We are trying to migrate from oracle 9i to 10G and we are compiled all our sqlj code with codegen = -oracle option.(previously we used to use codegen=-jdbc which for some reason doesnot work).it compiles fine with out any erros.. But when we try to run then get this exception. java.lang.IllegalArgumentException: SQLJ programs translated with the option -codegen=oracle must be run under Oracle JDBC 9.0.0 or higher. If the Oracle JDBC driver is wrapped, then the wrapper must implement all of the oracle.jdbc.OracleXxxx interfaces. Alternatively, you can translate SQLJ programs either with -codegen=iso. java.lang.IllegalArgumentException: SQLJ programs translated with the option -codegen=oracle must be run under Oracle JDBC 9.0.0 or higher. If the Oracle JDBC driver is wrapped, then the wrapper must implement all of the oracle.jdbc.OracleXxxx interfaces. Alternatively, you can translate SQLJ programs either with -codegen=iso. at sqlj.runtime.ref.ConnectionContextImpl.getOracleConnection(ConnectionContextImpl.java:167) at sqlj.runtime.ExecutionContext$OracleContext.prepareOracleStatement(ExecutionContext.java:1397) We are using ojdbc14.jar that came with oracle 10G. Hope any one can give any pointers.
You may try -codegen=iso and see 
tried using -codegen=iso option and it worked. But we dont want to have that extra profile file created with this option. Previously we were using -codegen=jdbc option which used to work for some reason it doesnot work anymore.
really want to know the difference between -codegen=iso and -codegen=oracle options so that we can make our connections work. 
You're not alone in having this issue.. It sounds like we are having the same exact problem with 10g.
I will post any solutions to solving this error when I come across them. 
I'm having same issue - but I'm having it when I try to invoke a method that contains a database Object type from a Tibco Business Works Process called a Java Method.
How exactly do you do the codegen=iso configuration?
I've tried:
1. Project Properties / Compiler SQLJ and then select from Code Generation drop down iso.
2. From Connections / Database / Navigate to package / right-click / Generate Java / Browse to my custom jpub.properties file where I have tried multiple entries such as (by themselves and in some combination.:
- jpub.codegen=jdbc
- jpub.codegen=iso
- jpub.compatible=9i
- jpub.compatible=8i
- compatible=both8i
- jpub.usertypes=oracle
- jpub.compatible=customdatum
- jpub.compatible=sqlj
I've also played with changing from the default some of the Mapping Options in Connections / Database / Navigate to package / right-click / Generate Java / Mapping Options.
This doc here, says that code should change (section 6 JPublisher Input Files - based on configuration of Input files)
Oracle® Database
JPublisher User's Guide
10g Release 2 (10.2)
B14188-01
No matter what I do, the code that gets generate is the same.
Also, the documentation talks about running things from the command line but how exactly do you do that? Do I need to be in specific directory? I tried the c:\ C:\jdev\system and c\jdev\ and c:jdev\bin and always get error message: 'jpub' is not recognized as an internal or external command, operable program or batch file.
I've built a java client in JDeveloper to successfully call the same method and I don't see the error.
Edited by: disaak on Mar 9, 2010 11:51 AM

Problem with cx_Oracle.execute()

Hi, everybody!
I've encountered the following problem while using cx_Oracle extension for my Python programme: method cx_Oracle.execute() hangs with no response or errors in case of sudden loss of net connection. Here is my special simple code for testing this bug:
import cx_Oracle
connstr = raw_input('connstr:') # in a form of 'user/passwd#host'
con = cx_Oracle.connect(connstr)
cursor = con.cursor()
query = raw_input('query:') # Let's print query (for example 'select * from table1'), then switch the internet connection off and push Enter
cursor.execute(query) # here it hangs
print 'Here' # it won't be printed
res = cursor.fetchall()
print res
In presence of the Internet? all's OK.
by the way, MySQLdb - a python extension for MySQL databases rises operational error in such case ( mysqlexceptions.OperationalError: (2006, 'MySQL server has gone away')), which might be easily processed.
If it helps: I've got Linux Fedora 14, Python 2.7, cx_Oracle-5.0.4
Please help if possible.
Thanx in advance!
Yury. 
I'd initially suspect this is a feature of the underlying network layer and not particularly related to cx_Oracle.
You might want to look at the fantastically named ENABLE=BROKEN feature of Oracle Net and/or adjust your
TCP stack keepalive parameters.
See http://download.oracle.com/docs/cd/E11882_01/network.112/e10835/tnsnames.htm#NETRF431

Statically build oci driver

I am trying to build an oci driver. It works fine dynamically, with libclntsh.so.12.1. But I wanted statically, and from my understanding I should generate and use the libclntst12.a.But this library gives me undefined references, like this one:  /usr/lib/gcc/x86_64-linux-gnu/4.6/../../../../lib/libclntst.a(ippdcmerged_pdclzo_split_u8_ippsEncodeLZO_8u.o): In function `u8_ippsEncodeLZO_8u':pdclzo_split_u8_ippsEncodeLZO_8u.i:(.text+0x79): undefined reference to `u8_ownEncodeLZO1X_asm' Is there another library I need to use, or what should I do? Thanks.
I managed to use only static oracle libraries by importing -lclntst -lippsmerged -lippdcmerged, so now I only get these dynamic dependencies:linux-vdso.so.1 =>  (0x00007fff007f0000)libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f47f642e000)libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f47f622a000)librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f47f6021000)libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f47f5e04000)libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f47f5a44000)/lib64/ld-linux-x86-64.so.2 (0x00007f47f6735000) However if I try to compile statically I get this:/usr/lib/gcc/x86_64-linux-gnu/4.6/../../../../lib/libclntst.a(generic12_skgnfs.o): In function `skgnfs_bind':skgnfs.c:(text.unlikely+0xeb0): undefined reference to `bindresvport' Is there a way to solve this problem?
(BTW: I ended up with a $ORACLE_HOME/lib/libclntst12.a of size zero. If I would have tried, I would have got undefined references like 'OCIEnvCreate', so that is not comparable to your problem. See Linux: Preferred method to link / locate libclnt(sh)?) I am interested why someone would want to link statically? I guess you are restricting to a certain client version (which may lead you into some trouble), but you hope to avoid the database client installations on target computers, are you?

getDefaultConnection not doing anything

Hi there.
I have a java class that is loaded into Oracle 8.1.7. This class gets a string as parameter and performs an INSERT and UPDATE (on the record inserted). The connection I use inside the Java class its retreived via the getDefaultConnection, that means, I get the imlpicit and existing connection inside Oracle.
Problem: This class was working fine on a Oracle Server. The DB Support guys changed servers and had a new Oracle, with JServer support also intalled (in SYS), other java classes that dont use this getDefaultConnection work fine. In this new server my Java class seems not to get the Connection object though the getDefaultConnection, it assigns a erference, the refence to Connection is not null, so its pointing somewhere, but now that I run it the class just dont perform the INSER or UPDATE...it doesnt throw any error.
Anyone knows what might be happening?
Regards,
Ernesto 
Hi Ernesto,
This is only a suggestion (which you may have already tried :-)
Have you looked at the Oracle trace files? You can add the following
entry to your "init<sid>.ora" file:
event="1401 trace name processstate level 10"
This will produce lots of trace messages in the trace files
located in the "user_dump_dest" directory (where "user_dump_dest"
is also defined in the "init<sid>.ora" file).
It may help you find out what's going on.
Good Luck,
Avi.

Categories

Resources