Testing ZK Ajax Framework with iSeries/AS400 using JDBC

The ZK Ajax Framework standalone zkdemo application has a sample program that uses JDBC to provide database support to an Ajax app. I decided to give it a try using an iSeries datasource. Here's how it was done..

ZK downloads are here.

- Download ZK-quickstart-x.y.z.pdf
- Download and install Tomcat. I used version 5.5 as this was the version referred to in the manual.
- Download zk-demo-x.y.z.zip and extract the package. Copy the zkdemo.war package to the webapps directory of Tomcat (C:\Program Files\Apache Software Foundation\Tomcat 5.5)
- Restart Tomcat and open the url http://localhost:8080/zkdemo/userguide/

Shutdown Tomcat. I copied the ...\webapps\zkdemo directory to ...\webapps\myzk so I can make changes to the sample code without touching the demo application. I then copied ...\webapps\myzk\userguide\dbconnect\jdbc.zul to ...\webapps\myzk\dbconnect\jdbc.zul and modified the submit() function, as follows:

void submit() {
  // Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  // DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());
  Class.forName("com.ibm.as400.access.AS400JDBCDriver");
  // String url = "jdbc:odbc:Fred";
  String mySchema = "REXJUN1";
  String myAS400 = "m170pub1.rzkh.de";
  String myUserId = "REXJUN";
  String myPasswd = "-change-me-";
  String url = "jdbc:as400://" + myAS400 + "/" + mySchema;
  Connection con = DriverManager.getConnection(url,myUserId, myPasswd);
  PreparedStatement stmt = con.prepareStatement("INSERT INTO BASICJDBC values(?,   ?)");
  //insert what end user entered into database table
  stmt.setString(1, id.value);
  stmt.setString(2, name.value);
  //execute the statement
  stmt.executeUpdate();
  //commit
  con.commit();
  //close the jdbc connection
  con.close();
}

Add the jt400.jar from JTOpen to the ...\webapps\myzk\WEB-INF\lib directory. This jar file contains the JDBC driver for iSeries.

Restart Tomcat. Opening the url http://localhost:8080/myzk/dbconnect/jdbc.zul should give you this..


A wrong password does not trigger an error and the application seems to hang -- the host system may have requested a password re-entry but the UI failed to render.

Reviewing the file contents shows the successful addition of new records..


ZK also released ZK Mobile 0.8.7 for the mobile platform on November 07, 2007.

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();
}