accessing as400 databases with Ruby, Java, and the RubyJavaBridge
iSeries systems and Ruby are separate universes right now; I know of no native Ruby way to connect to an AS400 system. However, with the help of Java we can bridge the gap.
Probably the simplest example is connecting to an iSeries system using JDBC to select from some tables, and with the right libraries it is straightforward. It’s also fun to be doing some nifty quick Ruby but with some preexisting and proven Java libraries.
Also, if you use Java and Ruby, definitely put arton’s RubyJavaBridge in your pocket; you’ll end up using it more than once.
What you’ll need:
- Ruby (well yea)
- Java (ok)
- access to an AS400 system (that’s the whole point here…)
For testing, I’ve been using a free account available from Holger Scherer Software und Beratung. - jt400.jar jdbc drivers from JTOpen, a great open-source Java library for AS400 access.
- RubyJavaBridge is the keystone for this.
Installing the software is the hardest part of this exercise, but it’s more tedium than anything. Once you’ve got it all downloaded and running the fun can begin.
require 'rjb'
Rjb::load('jtopen_5_0/lib/jt400.jar',['-Djdbc.drivers=com.ibm.as400.access.AS400JDBCDriver'])
DriverManager = Rjb::import('java.sql.DriverManager')
begin
connection = DriverManager.getConnection('jdbc:as400://as400.holgerscherer.de','YOUR_USERNAME','YOUR_PASSWORD')
statement = connection.prepareStatement("SELECT count(*) FROM SYSIBM.TABLES")
result_set = statement.executeQuery
while(result_set.next())
puts result_set.getObject(1).toString
end
ensure
result_set.close if defined?(result_set) && !statement.nil?
statement.close if defined?(statement) && !statement.nil?
connection.close if defined?(connection) && !connection.nil?
end
$ ruby as400_rjb.rb
19138
Very readable, I think. Java and Ruby actually play well together given half the chance and arton’s fantastic bridge.
Compare this with the pure Java implementation.
import java.sql.*;
public class As400Jdbc
{
public static void main(String args[])
{
Connection connection = null;
try
{
connection = DriverManager.getConnection("jdbc:as400://as400.holgerscherer.de","USER","PASS");
PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM SYSIBM.TABLES");
ResultSet resultSet = statement.executeQuery();
while(resultSet.next())
{
System.out.println(resultSet.getObject(1));
}
}
catch(SQLException e)
{
throw new RuntimeException(e);
}
finally
{
try{ if(connection!=null) connection.close(); }
catch(SQLException e)
{
throw new RuntimeException(e);
}
}
}
}
$ javac As400Jdbc.java
$ java -cp jtopen_5_0/lib/jt400.jar:. -Djdbc.drivers=com.ibm.as400.access.AS400JDBCDriver As400Jdbc
19138
The Ruby code and the Java code are nearly one-for-one so far, and that’s fine. Still, let’s see if we can’t pull the Java code more into Ruby’s world. The begin...end stuff gets tiresome; it would be nice if these Java classes could handle the resource allocation and disposal drudgery for us, using blocks.
require 'rjb'
Rjb::load('/home/darren/dload/jtopen_5_0/lib/jt400.jar',['-Djdbc.drivers=com.ibm.as400.access.AS400JDBCDriver'])
DriverManager = Rjb::import('java.sql.DriverManager')
class <<DriverManager
def with_connection(*args)
begin
connection = DriverManager.getConnection(*args)
yield connection
ensure
connection.close
end
end
end
DriverManager.with_connection('jdbc:as400://as400.holgerscherer.de','USER_NAME','USER_PASS') do |conn|
statement = conn.prepareStatement("SELECT count(*) FROM SYSIBM.TABLES")
result_set = statement.executeQuery()
result_set.next()
puts result_set.getObject(1).toString
end
I know I’m a geek because stuff like this makes me giggle. So many worlds are colliding here–Ruby, Java, AS400, DB2, the client system (Linux in this case)–and it all comes together so nicely.
I’m not saying I’d build an app out of this, but it will more than suffice for ad-hoc queries and quick scripts.
May 11th, 2006 at 8:54 am
darrend,
I share your entusiasm for this bridge. Following your example I have been able to make use of Java classes that I would not want to rewrite in Ruby if I don’t have to, and it worked beautifully. I am having some trouble, though, accessing our AS/400’s in the same way you do in your examples. It appears I am able to make the JDBC connection (I can see it in the QZDASOINIT job log) but the program just hangs at that point and I have to cancel it. A “Host server error” is recorded in the job log: “Error code 3426 was received while processing the recv() - length function for the host server communications.” My logging suggests that the conn.prepareStatement never happens. I’m suspicious that it might be a CCSID thing, but nothing I have seen lets me work around that. My client is XP, sp2. Did you run into anything like this in your travels? Thanks.
May 11th, 2006 at 11:34 am
I’m not sure, as I haven’t seen that situation before. But I haven’t run the bridge under windows yet either.
Out of curiosity, did you try the pure java code I posted and did it cause the same problem? I’m assuming it would.
May 11th, 2006 at 4:23 pm
I hadn’t thought of that. But I just tried and it worked fine. Although I did need to insert :
“DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());” because it threw the exception “no suitable driver”. Then it executed successfully and returned the expected result. However, I am trying to execute from the RDT perspective in Eclipse. Maybe that’s my problem. I’ll try from outside the IDE and see what happens. Thanks for your response.
Kendall
May 12th, 2006 at 11:15 am
Curiouser and curiouser. Let me know if it runs outside of RDT.
How’d you build your RJB for Windows, btw? I haven’t tried yet. I have googled instructions for crosscompilation of Ruby extensions using MinGW and have an old Visual C++ install I could use, but haven’t gotten off my duff to actually do it yet.
FWIW, you could probably get the pure java implementaiton going in Eclipse if you changed your Run command to include “-Djdbc.drivers=com.ibm.as400.access.AS400JDBCDriver” as a VM parameter. Same diff as registerDrive tho.
May 12th, 2006 at 12:25 pm
Curiouser, indeed! Still hangs when run from the command line… I did apply for an account on the machine in Germany. Maybe if I connect to it…
The WinXP setup of rjb was problematic, especially for a ruby-newbie like myself. The “ruby setup.rb config” failed while trying to locate the jni.h file, which I know is on my machine right where it should be. Another helpful soul had posted similar problems. I contacted him and he let me download the rjb.so file for ruby 1.8.4 from his site. I was then able to run the “ruby setup.rb install” which (I think) put everything in the right place. As I say, rjb works with everything I’ve tried so far, except talking to the 400. Darn.
June 9th, 2006 at 10:11 am
Well, I finally got to try this on the machine in Germany and the results were the same. Except, of course, the error message was in German. ;) So, I guess what we’re left with is an XP or Windows issue. Quite odd.
January 25th, 2007 at 5:00 pm
Hello,
I just found this thread, and even with the latest versions of RJB and Ruby the prepareStatement still hangs.
Does anyone found a solution for this?
Thank you
March 30th, 2007 at 9:14 am
The IBM_DB Rails Plugin and Ruby gem provide Rails/Ruby interfaces for IBM Data Servers. The Ruby adapter and driver are developed and supported by IBM. The adapter and driver currently supports DB2 on Linux/Unix/Windows, DB2 for i5/OS and DB2 for zOS.
Check it out…..
http://rubyforge.org/frs/?group_id=2361
April 11th, 2008 at 10:50 am
I’ve been using rails with iseries at my facility for about a year now without java. For me, the key was setting up unixodbc, rails-odbc, and the IBM iSeriesAccess drivers.
It works great.
My only issue has been with BLOB columns. I’ve not been able to insert file data into them with Rails though I’ve never had this issue with foxpro or .net. Does anyone know the proper format for the binary string for the Iseries? I’ve tried “blob(’value’)”,”blob(x’value’)”,”blob(value)”,’value’,value, {’value’}, {value}
value of course being the binary string. I hope you guys may have solved this issue already.