Wednesday, February 6, 2008

Using Beanshell with the iSeries/AS400 JTOpen JDBC driver

What seemed to be simple and straightforward task is not as what I expected it to be. And I'm referring to making JTOpen JDBC work with Beanshell. Isn't it supposed to be as easy as load and connect? I guess not..

The common method of invoking java.sql.DriverManager to load the JTOpen JDBC drivers for IBM iSeries (erstwhile AS400) doesn't seem to work with Beanshell. The subsequent calls to DriverManager.getConnection result to a message "No suitable driver found..".

The closest clue I got without going thru the code is contained in this post. Sad to say it is in Spanish --- but there's always Babelfish to the rescue. Still, the translation is wanting, but good enough to tell me not to use DriverManager.

Here's an alternative code. Most of the statements were lifted from IBM.


addClassPath ("./jt400.jar");

import java.sql.*;
import java.util.Properties;
import com.ibm.as400.access.AS400;
import com.ibm.as400.access.AS400JDBCDriver;

private Connection connection = null;
private Statement s = null;
private String mySchema = "REXJUN1";
private String myAS400 = "m170pub1.rzkh.de";
private String myUserId = "REXJUN";
private String myPasswd = "--change-me-";

System.out.println("Loading JDBC driver..");
try {
AS400JDBCDriver d = new AS400JDBCDriver();
AS400 o = new AS400(myAS400, myUserId, myPasswd);
Properties p = new Properties();
Connection c = d.connect (o, p, mySchema);
s = c.createStatement();
} catch (Exception e) {
System.out.println("Caught exception: " + e.getMessage());
System.exit(0);
}
System.out.println("Setting up connection..");
try {
s.executeUpdate("drop table basicjdbc");
} catch (SQLException e) {
// Do not perform anything if an exception occurred. Assume
// that the problem is that the table that was dropped does not
// exist and that it can be created next.
System.out.println("Table may not have been dropped.");
}
System.out.println("Creating table..");
try {
s.executeUpdate("create table basicjdbc(id int, name char(15))");
s.executeUpdate("insert into basicjdbc values(1, 'Frank Johnson')");
s.executeUpdate("insert into basicjdbc values(2, 'Neil Schwartz')");
s.executeUpdate("insert into basicjdbc values(3, 'Ben Rodman')");
s.executeUpdate("insert into basicjdbc values(4, 'Dan Gloore')");
} catch (SQLException sqle) {
System.out.println("Failure occurred while setting up " +
" for running the test.");
System.out.println("Test will not continue.");
System.exit(0);
}
System.out.println("Dumping table..");
try {
ResultSet rs = s.executeQuery("select * from basicjdbc");
System.out.println("--------------------");
int i = 0;
while (rs.next()) {
System.out.println("| " + rs.getInt(1) + " | " + rs.getString(2) + "|");
i++;
}
System.out.println("--------------------");
System.out.println("There were " + i + " rows returned.");
System.out.println("Output is complete.");
} catch (SQLException e) {
System.out.println("SQLException exception: ");
System.out.println("Message:....." + e.getMessage());
System.out.println("SQLState:...." + e.getSQLState());
System.out.println("Vendor Code:." + e.getErrorCode());
e.printStackTrace();
}
System.out.println("Clean-up..");
try {
if (connection != null)
connection.close();
} catch (Exception e) {
System.out.println("Caught exception: ");
e.printStackTrace();
}

No comments: