Some time ago I created ruby-plsql gem which provides simple Ruby API for Oracle PL/SQL stored procedures.
Initially this gem supported just MRI with ruby-oci8 library which provides connectivity to Oracle database as this was my main development and production environment for Ruby & Oracle applications. But as JRuby is fast growing alternative Ruby deployment platform and as it can be integrated into Oracle Fusion middleware platform (e.g. Oracle Mix is running on JRuby on Oracle Fusion middleware) then I planned to support JRuby as well.
I started to work on JRuby support during RailsConf JRuby hackfest and initially this did not seem very hard task for me. But as I did not know JDBC very well it took me much more time than initially planned. And unfortunately JDBC is also much less powerful compared to ruby-oci8 library for construction of dynamic PL/SQL calls. In addition I needed to strugle with Ruby and JDBC data type mappings which differs from Ruby and ruby-oci8 data type mappings.
But finally I have completed JRuby support and released ruby-plsql gem version 0.2.0. And good news are that from usage perspective ruby-plsql behaves identically on MRI and JRuby – at least my RSpec tests are telling so.
To install this gem on JRuby execute
sudo jruby -S gem install ruby-plsql
or
sudo jgem install ruby-plsql
depending on how you have installed JRuby on your computer.
Source code of ruby-plsql is located on GitHub where you can find usage examples in RSpec tests.
It seams that you also need to copy ojdbc14.jar to $JRUBY_HOME/lib directory as otherwise jruby cannot load this jar file and ruby-plsql will fail with “No suitable driver” exception.
Comment by Raimonds Simanovskis — June 26, 2008 @ 9:55 pm |
Great job! yep, ojdbc14.jar is actually needed!
Comment by Jesse Hu — June 27, 2008 @ 3:51 am |
Cool stuff, and it’s great to see other gem developers considering JRuby support. Feel free to announce on JRuby user list if you like.
Comment by Charles Oliver Nutter — June 27, 2008 @ 4:30 am |
Ray,
Thanks for the great work! I cannot seem to find the syntax for setting up a connection with jruby. The docs only cover setting up a new OCI8 based connection. If it is documented, would you mind pointing me in the right direction?
Comment by Brett — July 31, 2008 @ 3:27 pm |
You just need to assign JDBC connection object to plsql.connection.
So if you would like to use ruby-plsql without Rails / ActiveRecord then you need to establish connection, for example, in the following way:
import java.sql.Statement
import java.sql.Connection
import java.sql.SQLException
import java.sql.Types
import java.sql.DriverManager
DriverManager.registerDriver Java::oracle.jdbc.driver.OracleDriver.new
jdbc_conn = DriverManager.getConnection(”jdbc:oracle:thin:@server:1521:SID”,”user”,”password”)
plsql.connection = jdbc_conn
if you are using it with Rails / ActiveRecord JDBC adapter then you can just put in environment.rb (or some initialization file):
plsql.connection = ActiveRecord::Base.connection.connection
Comment by Raimonds Simanovskis — August 3, 2008 @ 7:43 pm |
I’m having problems with it.
That is what I did:
- Included ojdbc14.jar in $JRUBY_HOME/lib;
- Added $JRUBY_HOME/lib to PATH windows enviroment variable;
And when I try to execute, it returns the following error:
C:\project\trunk>jruby -S script\console
Loading development environment (Rails 2.1.0)
>> require “ruby_plsql”
To use ruby_plsql you must have Oracle JDBC driver installed.
=> ["RubyPlsql"]
>> plsql.connection = ActiveRecord::Base.connection.raw_connection
ArgumentError: Unknown raw driver
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:17:in `create’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/schema.rb:28:in `connection=’
from (irb):3
If I try this other way
import java.sql.Statement
import java.sql.Connection
import java.sql.SQLException
import java.sql.Types
import java.sql.DriverManager
DriverManager.registerDriver Java::oracle.jdbc.driver.OracleDriver.new
jdbc_conn = DriverManager.getConnection(”jdbc:oracle:thin:@server:port:simu3″,”login”,”pass”)
require “ruby_plsql”
plsql.connection = jdbc_conn
The connection works OK but when I try
plsql.bra_customhr_eval_pk.validate_login_p(:p_username => “MMENEZES”, :p_password => “@senhateste2006″, :p_validate => nil)[:p_validate]
I get the following error
NoMethodError: undefined method `setStringAtName’ for #
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:377:in `set_bind_variable’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:297:in `bind_param’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:157:in `exec’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `each’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `exec’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/package.rb:31:in `method_missing’
from (irb):19
Any help will be appreciate!
Thanks,
Marcelo Murad
Comment by Marcelo Murad — September 17, 2008 @ 8:44 pm |
I found out that the correct way how to assign JDBC connection from Rails JDBC adapter is:
plsql.connection = ActiveRecord::Base.connection.raw_connection.connection
And I also understood why you get this “you must have Oracle JDBC driver installed” error. I use the following code to find ojdbc14.jar in PATH:
ojdbc_jar = “ojdbc14.jar”
ojdbc_jar_path = ENV["PATH"].split(”:”).find{|d| File.exists?(File.join(d,ojdbc_jar))}
but on Windows PATH directories are separated by “;” and not by “:” as in Unix. Sorry that I did not think about Windows collegues :)
But in Rails case probably you can ignore this message as ActiveRecord JDBC adapter will already load all necessary JDBC drivers.
Comment by Raimonds Simanovskis — September 17, 2008 @ 10:22 pm |
Thanks Raimonds,
I did a fast update the file now connection association works, but I still get the setStringAtName error.
>> require “ruby_plsql”
=> ["Statement", "Connection", "RubyPlsql", "Types", "DriverManager", "SQLException"]
>> plsql.connection = ActiveRecord::Base.connection.raw_connection.connection
=> #
>> plsql.bra_customhr_eval_pk.validate_login_p(:p_username => “MMENEZES”, :p_password => “@senhateste2006″, :p_validate => nil)[:p_validate]
NoMethodError: undefined method `setStringAtName’ for #
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:377:in `set
_bind_variable’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/connection.rb:297:in `bin
d_param’
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:157:in `exec
‘
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `each
‘
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/procedure.rb:155:in `exec
‘
from C:/Documents and Settings/murad/Desktop/jruby-1.1.3/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.2/lib/plsql/package.rb:34:in `method_
missing’
from (irb):4
Comment by Marcelo Murad — September 17, 2008 @ 10:47 pm |
It was a older version of ojdbc14.jar. I uypdate it and it works like a charm!
Comment by Marcelo Murad — September 17, 2008 @ 11:27 pm |
Raimonds,
Try as I might, I just cannot get this to work. My current gems are:
actionmailer (2.1.2, 2.0.2)
actionpack (2.1.2, 2.0.2)
activerecord (2.1.2, 2.0.2)
activerecord-jdbc-adapter (0.8.2)
activeresource (2.1.2, 2.0.2)
activesupport (2.1.2, 2.0.2)
composite_primary_keys (1.1.0, 0.8.6)
jruby-openssl (0.3)
rails (2.1.2, 2.0.2)
rake (0.8.3)
rspec (1.1.11)
ruby-plsql (0.2.3)
sources (0.0.1)
And the code I am using is:
require “rubygems”
gem “activerecord”
gem “ruby-plsql”
require ‘active_record’
require ‘ruby_plsql’
require ‘pp’
ActiveRecord::Base.establish_connection(:adapter => “jdbc”,
:driver => “oracle.jdbc.OracleDriver”,
:url => “removed”,
:username => “removed”,
:password => “removed”)
class Foo < ActiveRecord::Base
plsql = ActiveRecord::Base.connection.raw_connection
puts plsql.class.to_s
PLSQL::Procedure.find(plsql,’bal.test_it’)
res = plsql.test_it(’foo’)
pp res
end
I keep getting an error of:
jruby-1.1.5/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/procedure.rb:5:in `find’: undefined method `schema_name’ for # (NoMethodError)
I have been through a lot of the source code, and cannot seem to track down where these extra methods such as ’schema_name’ are getting added to the JdbcConnection class – have you any ideas as to where I am going wrong?
Any help is much appreciated!
Stephen.
Comment by Stephen — November 13, 2008 @ 5:00 pm |
@Stephen
Instead of
plsql = ActiveRecord::Base.connection.raw_connection
you need to write (in JRuby & JDBC case):
plsql.connection = ActiveRecord::Base.connection.raw_connection.connection
And better please put this line outside class definition.
And you should not use PLSQL::Procedure.find :) Just use plsl.package_name.procedure_name
Comment by Raimonds Simanovskis — November 13, 2008 @ 7:36 pm |
Raimonds,
Works perfectly now for my test code at least. Now to port my OCI8 MRI code to ruby-plsql jruby instead …
Thanks!
Comment by Stephen — November 14, 2008 @ 12:27 pm |
There is problem with synonyms. If plsql pacakage is available as synonym only then plsql gem is ignorig this because it selects only objects with type PACKAGE / PROCEDURE / FUNCTION. For schema syonyms it is needed to add objects with type SYNONYM, too. This is needed for class Package and probably Procedure, too.
Comment by Janis — February 11, 2009 @ 2:55 pm |
Hi Raimonds,
ruby-plsql is running fine for most of my work.
today though I encountered following issue.
I have a function in package STAGING
FUNCTION get_created_ddl (object_name in varchar2, schema_name in varchar2 default null)
RETURN t_created_ddl;
where t_created_ddl is a record with 2 date fields.
TYPE t_created_ddl IS RECORD (created_date date, ddl_date date);
in PL/SQL I can execute it in following way:
procedure test_get_created_ddl (object_name in varchar2, schema_name in varchar2 default null) is
result t_created_ddl;
begin
result := STAGING.get_created_ddl(object_name, schema_name);
dbms_output.put_line(object_name || ‘: created: ‘ || result.created_date || ‘ ddl: ‘ || result.ddl_date);
end;
when trying to call the function get_created_ddl() in plsql I get following:
require ‘ruby_plsql’
plsql.connection = OCI8.new(”username”,”pass”,”sid”)
p plsql.staging.get_created_ddl(”lb_individuals_specs_eu_vw”,”lm_emea”)
produces:
d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/oci_connection.rb:1
11:in `ruby_value_to_ora_value’: undefined method `to_datetime’ for “lb_individu
als_specs_eu_vw”:String (NoMethodError)
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:201:in `ruby_value_to_ora_value’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:160:in `exec’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:158:in `each’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/proced
ure.rb:158:in `exec’
from d:/ruby186/lib/ruby/gems/1.8/gems/ruby-plsql-0.2.3/lib/plsql/packag
e.rb:34:in `method_missing’
from pls.rb:7
shell returned 1
Is it a limitation of ruby-plsql that it does not return custom types (like a record in this example)?
many thanks,
chris
Comment by chris — February 17, 2009 @ 2:06 pm |
Yes, ruby-plsql currently does not support custom types as I need to map each Oracle type to Ruby type.
Currently I support just NUMBER, VARCHAR2, DATE, CLOB data types.
Comment by Raimonds Simanovskis — February 17, 2009 @ 4:01 pm |