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.

Gaga over Google Maps

I gotta admit it, I'm hooked on Google Maps, specifically its API. I've seen the maps of Hongkong's MTR system and with it you can practically plan your trips down to the nearest entry and exit points.


Cebu of the Philippines is likewise a destination for many. Although several travel guides featuring Cebu abound on the web, maps are seldom employed to aid those needing directions. With the availability of rooftop-level satellite imagery offered by Google Maps, I decided to overlay Cebu's streets and places of interest with markers. To spice up the pages, I interspersed local info with impressive photos at Panoramio and Flickr courtesy of camera enthusiasts.

Before I forget, let me introduce you to Cebumapia - a Google Map mashup of Cebu's places of interest! And shall I add, always a Work In Progress!


I've started with markers, or should I say.. I planned to use only markers. But as complexity started to evaporate, I found myself twiddling with infowindows and polylines to spruce things up. After the 30th or so marker in place, I realized I barely scratched the surface... so many markers, so little time! I needed an easier way to add and modify map objects without further mangling the html and javascript code of my Blogger template.

Google Docs to the rescue! By migrating the coordinates, waypoints and even descriptions into Google spreadsheets, I can now change map info even without having to log into Blogger. Hmmm... I'm now thinking of letting visitors add their own sites so I don't have to do anything at all!

I've learned a lot of new things already while building this site. Enjoy it as much as I did... or still do.

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

Sending mail using Gmail SMTP server with Java

This is a companion project of my previous experiment with S/Mime encryption. This script is also based on Beanshell and it's purpose is basically to connect to Gmail SMTP servers and deliver the encrypted payload to the message recipients.

There are several examples off the Internet but, somehow, I had to combine the techniques before I finally pulled this off. I've enabled debug, otherwise this post would not have any image at all!

Here's the debug output..


Here's the script..

addClassPath( "./mail.jar" );
addClassPath( "./activation.jar" );

import java.security.Security;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.Authenticator;
import javax.mail.internet.MimeMessage;

private class SMTPAuthenticator extends javax.mail.Authenticator {
 public PasswordAuthentication getPasswordAuthentication()
 {
 return new PasswordAuthentication(smtpUsername, smtpPassword);
 }
}

public static final String smtpHost = "smtp.gmail.com";
public static final String smtpUsername = "rexjun@gmail.com";
public static final String smtpPassword = "-change-me-";
public static final String smtpPort = "465";

Security.addProvider(new com.sun.net.ssl.internal.ssl.Provider());
Properties props = new Properties();
props.put("mail.smtp.user", smtpUsername);
props.put("mail.smtp.host", smtpHost);
props.put("mail.smtp.port", smtpPort);
props.put("mail.smtp.starttls.enable","true");
props.put("mail.smtps.auth", "true");
props.put("mail.smtp.debug", "true");
props.put("mail.smtp.socketFactory.port", smtpPort);
props.put("mail.smtp.socketFactory.class", "javax.net.ssl.SSLSocketFactory");
props.put("mail.smtp.socketFactory.fallback", "false");
props.put("mail.smtp.ssl","true");

SecurityManager security = System.getSecurityManager();
Authenticator auth = new SMTPAuthenticator();
Session smtpSession = Session.getInstance(props, auth);
smtpSession.setDebug(true);

MimeMessage smtpMessage = new MimeMessage(smtpSession,
 new FileInputStream("Encrypted.eml"));
smtpMessage.saveChanges();
smtpMessage.setSentDate(new Date());

Transport tr = smtpSession.getTransport("smtp");   
tr.connect(smtpHost, smtpUsername, smtpPassword);
tr.sendMessage(smtpMessage, smtpMessage.getAllRecipients());
tr.close();

S/MIME Encryption using Bouncy Castle Crypto API for Java

At this time and age, it is somewhat irresponsible for banks to accept uploads of plain-text files to their online banking services. Secure HHTP (HTTPS) alone does not address total security as somebody in one's organization has the ability to tamper the files prior to upload. And end-to-end encryption is not a one-sided affair --- both the bank and the customer application has to support it before it can be realized.

Of the four banks I've been involved with, two international banks support both plain-text and encrypted file uploads. One local bank accepts plain-text only; the other uses proprietary encryption BUT gives the encryption algo to your programmers!


At this time and age, there should be no excuse not to adopt industrial-strength cryptography. I managed to create a BeanShell script to encrypt files using the Bouncy Castle cryptography API for Java. The script produces an encrypted file named Encrypted.eml which can be opened and viewed by Mozilla Thunderbird. The encrypted payload can be delivered using this companion code.


You would need your own digital certificate, a public-key for your recipient and the file that you want encrypted. Get your free digital certificates from Comodo. To run the script, execute the command java -classpath ./bsh-2.0b4.jar bsh.Interpreter SnE.bsh, where SnE.bsh is the script name. Lastly, here's the code..

addClassPath( "./mail.jar" );
addClassPath( "./activation.jar" );
addClassPath( "./bcprov-jdk16-138.jar" );
addClassPath( "./bcmail-jdk16-138.jar" );

import javax.activation.FileDataSource;
import javax.activation.DataHandler;

import java.io.FileInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ByteArrayInputStream;
import java.security.KeyStore;
import java.security.Security;
import java.security.PrivateKey;
import java.security.cert.CertificateFactory;
import java.security.cert.Certificate;
import java.security.cert.X509Certificate;
import java.security.cert.CertStore;
import java.security.cert.CollectionCertStoreParameters;
import java.util.Properties;
import java.util.Enumeration;
import java.util.List;
import java.util.ArrayList;

import javax.mail.Message;
import javax.mail.Session;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.activation.MailcapCommandMap;
import javax.activation.CommandMap;

import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.bouncycastle.mail.smime.SMIMEEnvelopedGenerator;
import org.bouncycastle.mail.smime.SMIMEException;
import org.bouncycastle.mail.smime.SMIMESignedGenerator;
import org.bouncycastle.util.Strings;
import org.bouncycastle.asn1.smime.SMIMECapabilityVector;
import org.bouncycastle.asn1.smime.SMIMECapability;
import org.bouncycastle.asn1.smime.SMIMEEncryptionKeyPreferenceAttribute;
import org.bouncycastle.asn1.smime.SMIMECapabilitiesAttribute;
import org.bouncycastle.asn1.ASN1EncodableVector;
import org.bouncycastle.asn1.x509.X509Name;
import org.bouncycastle.asn1.cms.IssuerAndSerialNumber;
import org.bouncycastle.asn1.cms.AttributeTable;

public static final String pkcs12Keystore = "./certs/myOwn.p12";
public static final String rcptCertfile = "./certs/myBank.cer";
public static final String sendFile = "./messages/hello.txt";
public static final String ksPassword = "pipoltek";
public static final String frAddress = "rexjun@somewhere.com";
public static final String toAddress = "ca@myBank.com";

try
{
 MailcapCommandMap mailcap = (MailcapCommandMap)CommandMap .getDefaultCommandMap();
 mailcap .addMailcap("application/pkcs7-signature;; x-java-content-handler=org.bouncycastle.mail.smime.handlers.pkcs7_signature");
 mailcap .addMailcap("application/pkcs7-mime;; x-java-content-handler=org.bouncycastle.mail.smime.handlers.pkcs7_mime");
 mailcap .addMailcap("application/x-pkcs7-signature;; x-java-content-handler=org.bouncycastle.mail.smime.handlers.x_pkcs7_signature");
 mailcap .addMailcap("application/x-pkcs7-mime;; x-java-content-handler=org.bouncycastle.mail.smime.handlers.x_pkcs7_mime");
 mailcap .addMailcap("multipart/signed;; x-java-content-handler=org.bouncycastle.mail.smime.handlers.multipart_signed");
 CommandMap.setDefaultCommandMap(mailcap);
 
 /* Add BC */
 Security.addProvider(new BouncyCastleProvider());

 /* Open the keystore */
 KeyStore keystore = KeyStore.getInstance("PKCS12", "BC");
 keystore.load (new FileInputStream(pkcs12Keystore), ksPassword.toCharArray());
 Enumeration e = keystore.aliases();
 String keyAlias = null;
 while (e.hasMoreElements() && (keyAlias == null)) {
  String  alias = (String)e.nextElement();
  keyAlias = keystore.isKeyEntry(alias) ? alias : null;
 }
 if (keyAlias == null) {
  System.err.println("Can't find a private key!");
  System.exit(0);
 }
 Certificate[] chain = keystore.getCertificateChain(keyAlias);

 /* Get the private key to sign the message with */
 PrivateKey privateKey = (PrivateKey)keystore.getKey(keyAlias,
   ksPassword.toCharArray());
 if (privateKey == null) {
  throw new Exception("No private key for alias: " + keyAlias);
 }

 /* Get public key of recipient for encryption */
 FileInputStream fis = new FileInputStream(rcptCertfile);
 BufferedInputStream bis = new BufferedInputStream(fis); 
 CertificateFactory cf = CertificateFactory.getInstance("X.509"); 
 Certificate rcptCert = cf.generateCertificate(bis);

 /* Attach the file to encrypt */
 MimeBodyPart bodyPart = new MimeBodyPart();
 FileDataSource fds = new FileDataSource(sendFile);
 bodyPart.setDataHandler(new DataHandler(fds));
 bodyPart.setFileName(fds.getName());
 MimeMultipart bodyMulti = new MimeMultipart();
 bodyMulti.addBodyPart(bodyPart); 

 Session session = Session.getDefaultInstance(System.getProperties());
 MimeMessage body = new MimeMessage(session);
 body.setFrom(new InternetAddress(frAddress));
 body.setRecipient(Message.RecipientType.TO, new InternetAddress(
   toAddress));
 body.setSentDate(new Date());
 body.setSubject("Encrypted Mail");
 body.setContent(bodyMulti,bodyMulti.getContentType());
 body.saveChanges();

 /* Create the SMIMESignedGenerator */
 SMIMECapabilityVector capabilities = new SMIMECapabilityVector();
 capabilities.addCapability(SMIMECapability.dES_EDE3_CBC);
 capabilities.addCapability(SMIMECapability.rC2_CBC, 128);
 capabilities.addCapability(SMIMECapability.dES_CBC);

 ASN1EncodableVector attributes = new ASN1EncodableVector();
 attributes.add(new SMIMEEncryptionKeyPreferenceAttribute(
  new IssuerAndSerialNumber(
   new X509Name(((X509Certificate)chain[0])
    .getIssuerDN().getName()),
    ((X509Certificate)chain[0]).getSerialNumber())));
 attributes.add(new SMIMECapabilitiesAttribute(capabilities));

 SMIMESignedGenerator signer = new SMIMESignedGenerator();
 signer .addSigner(privateKey, (X509Certificate)chain[0],
  "DSA".equals(privateKey.getAlgorithm()) ? 
  SMIMESignedGenerator.DIGEST_SHA1 : SMIMESignedGenerator.DIGEST_MD5,
  new AttributeTable(attributes), null);

 /* Add the list of certs to the generator */
 List certList = new ArrayList();
 certList.add(chain[0]);
 CertStore certs = CertStore.getInstance("Collection",
   new CollectionCertStoreParameters(certList), "BC");
 signer.addCertificatesAndCRLs(certs);

 /* Sign the message and copy all headers from original message */
 MimeMultipart multiPart = signer.generate(body, "BC");
 MimeMessage signedMessage = new MimeMessage(session);
 Enumeration headers = body.getAllHeaderLines();
 while (headers.hasMoreElements()) {
  signedMessage.addHeaderLine((String)headers.nextElement());
 }
 signedMessage.setContent(multiPart);
 signedMessage.saveChanges();

 /* Create the encrypter and encrypt the message */
 SMIMEEnvelopedGenerator encrypter = new SMIMEEnvelopedGenerator();
 encrypter.addKeyTransRecipient((X509Certificate)chain[0]);
 encrypter.addKeyTransRecipient(rcptCert);
 MimeBodyPart encryptedPart = encrypter.generate(signedMessage,
   SMIMEEnvelopedGenerator.RC2_CBC, 128, "BC");
 ByteArrayOutputStream out = new ByteArrayOutputStream();
 encryptedPart.writeTo(out);
   
 /* Create a new MimeMessage for the encrypted and signed content */
  
 Properties props = new Properties();
 Session smtpSession = Session.getInstance(props, null);
 MimeMessage smtpMessage = new MimeMessage(smtpSession,
  new ByteArrayInputStream(out.toByteArray()));
 smtpMessage.saveChanges();
 
 /* Set all original MIME headers in the encrypted message */
 headers = body.getAllHeaderLines();
 while (headers.hasMoreElements()) {
  String headerLine = (String)headers.nextElement();
  /* Do not override content-* headers from the original message */
  if (!Strings.toLowerCase(headerLine).startsWith("content-")) {
   smtpMessage.addHeaderLine(headerLine);
  }
 }
 
 smtpMessage.writeTo(new FileOutputStream("Encrypted.eml")); 
  
}
catch (SMIMEException ex)
{
 ex.getUnderlyingException().printStackTrace(System.err);
 ex.printStackTrace(System.err);
}
catch (Exception ex)
{
 ex.printStackTrace(System.err);
}