Wednesday, December 10, 2008

Benchmarking Oracle 10g XE

Free to develop, deploy, and distribute...

Yes, that's the tagline for Oracle Database 10g Express Edition (XE), an entry-level database based on the 10g R2 code base. Certain handicaps are imposed: only one database can be installed per machine, user data is limited to 4GB, only one CPU shall be used, and only up to 1GB of memory.

Although there are several database benchmarking tools available, I've decided to try Benchmark Factory. The licensed version allows you to simulate 100 users whereas the freeware version is limited to only 20 virtual users.

My target database is an Oracle XE installed on a 32-bit Windows XP Pro system. I ran Benchmark Factory on another unit connected via a 100Mbps Ethernet LAN. Here are the workload benchmark results for 5, 10, 15, and 20 virtual users.

I encountered ORA-12519 errors (TNS:no appropriate service handler found) before the number of virtual clients reached 20. This was resolved by increasing the number of processes specified in the server parameter file (spfile). This is done via the following SQL commands:

SQL> alter system set processes=300 scope=spfile;
SQL> alter system set sessions=300 scope=spfile;

Restart the database and verify that the new parameter values have taken effect. Execute the following SQL command to display the current values:

SQL> select name, value from v$parameter where name in ('processes','sessions');

XE's response times did not falter even up to 100 users. Here is the transaction-time vs user-load comparison graph for 20 to 100 virtual users.

The 1GB memory restriction may have been breached at around 350 virtual users when I encountered ORA-12518 errors (TNS:listener could not hand off client connection). At 325 virtual users, the maximum qualified throughput (MQTH) is computed at 469.221 tpmC. The highest published TPC-C result can be found at

Monday, November 17, 2008

ERP on a Flash.. in a Flash

My sister is wishing for a software to help her manage the raw materials inventory of her new bakery franchise. I think I'm going to end up as her Santa this year...

Lots of ERP heavyweights abound the opensource community.. but I want something lighter. Something that can fit in a USB flash drive and delivered in a pouch -- an ERP on a Flash!

And here's my magic combo: TinyERP 4.2.2 + Ubuntu Intrepid Ibex + 2GB Imation NanoDrive.

TinyERP is included in the Ubuntu repositories -- a promise of painless installation via a series of apt-gets. has lots of guides on how to install Linux on a USB flash drive. I favored the Ubuntu 8.10 install using the built-in USB installer. This method provides a persistence feature allowing you to save the changes on subsequent boots.

Once Ubuntu is installed and booted up, we need to issue a sudo apt-get update command in order to synchronize the package index files from their sources. But before doing that, let's edit the list of apt sources via the command sudo vi /etc/apt/sources.list. Uncomment the deb entry for the intrepid universe repository --- the TinyERP packages are contained here. Save the changes and issue the following commands to download the desired packages:

sudo apt-get update
sudo apt-get install tinyerp-server
sudo apt-get install tinyerp-client

At this point, the Postgres database server and client should be installed and two new accounts (postgres and terp) created. We still need to create the terp database and user...

sudo /etc/init.d/postgresql* restart
sudo su - postgres -c "createdb -q --encoding=UNICODE terp"
sudo su - postgres -c "createuser -q --createdb --adduser terp"

..and restart the TinyERP server.

sudo /etc/init.d/tinyerp-server restart

Press Alt-F2 and select Tiny ERP Client from the list of known applications. You can view the User Manual here .

TinyERP is re-branding itself and is now known as OpenERP.

Monday, October 20, 2008

KnowledgeTree integration using Apache Camel

What the hump is Apache Camel?

Camel is a Spring-based Integration Framework that implements routing and mediation rules using a Java-based Domain Specific Language (DSL or Fluent API), via a Spring-based XML configuration files or via Scala DSL.

The upcoming version 1.5 release includes a tutorial using Axis 1.4 with Camel. I used this example as a springboard to craft a document publisher to KnowledgeTree document management system via it's Axis-based web services.

The basic document publisher picks up an XML file, uploads it to KnowledgeTree using Apache HttpClient, and invokes KnowledgeTree's add_document webservice to include the file into the document repository. Here is the associated routing rule:


Once the document-uploader function is implemented, the power and versatility of Camel as a routing and mediation framework comes into play. Camel supports a wide array of components allowing for more points of integration. In the above example, the File endpoint can easily be changed to FTP, SMTP, or JMS messaging without affecting the Processor code.

Route interceptors can be used to insert simple tasks like logging or complex ones like payload inspection for conditional application of logic.


A delivery failure is handled automatically by the Dead Letter Channel processor. I wanted failed deliveries to be deposited to another folder and this rule was implemented by a single line of code:


Aside from the Dead Letter Channel processor, Camel supports most of the Enterprise Integration Patterns and lends itself as a component of the Apache ServiceMix ESB project.

Tuesday, August 5, 2008

ZK / Tomcat Hosting at EATJ

Last night, I attempted to upload my ZK app at GoDaddys's shared hosting servers. I've planned to do this for a long time already but I just kept on postponing a possibly dreadful experience. I don't like the idea of waiting 24 hours between restarts of its Tomcat service.

So, how long is 24 hours? It's long enough to compel me to look for another webhosting service. In an hour or so, I was already up and running at EATJ web hosting solutions.

EATJ offers a free account for first-time users. For free accounts, the Tomcat JVM is shut down 4 times daily in order to save on server resources. Your Tomcat server, however, can be restarted easily within 30 secs via your control panel. The paid plans enjoy 24x7 monitoring for continuous server uptime.

I registered for a free account and got my control panel in just a couple of minutes. This guide will help you get started and ready to deploy your own app. I created my own zk307.war file containing the ZK 3.0.7 jars as well as my ZK app and dependencies by executing the command jar cvf ..\zk307.war * in the zk307 directory.

The war file is rather chunky at 21mb (the free account allows for 50mb) and uploading it seemed to be the most challenging chore of all. I had to retry once for the two uploads I did. Here's the listing of the webapps directory contents in the remote server.

Restart the Tomcat server and taadaaaa... my ZK app remotely-hosted at!

If you happen to catch my Tomcat service down, don't wait for me to restart it.. instead, create your own in under an hour!

Sunday, August 3, 2008

ZK Version 3.0.7 released

The ZK front-end app to KnowledgeTree was developed using version 3.0.6. The newer version 3.0.7 was released on August 1 and I had the chance to install ZK from scratch and run the ZK front-end app successfully.

Here are the steps:

  • download from
  • copy the MyApp directory (from the demo package) under the webapps directory of Apache Tomcat. Rename it to accordingly (I used zk307 in this example).
  • Create the WEB-INF/lib sub-directory. Place here all the jars found under the dist/lib directory of the zk-bin-3.0.7 package. Copy the jars under the ext and zkforge directories also but omit the directories - place all jars under WEB-INF/lib
  • Add the ktws.jar and the jars required by Apache Axis (see previous blog entry)

Fire up Tomcat and point your browser to http://localhost:8080/zk307/ktlogin.zul

There are over 9 new features and 22 bug fixes under this new release. Read more about the new ZK release here.

Saturday, August 2, 2008

ZK + SOAP + KnowledgeTree

Here's a working ZK app that performs a SOAP login to KnowledgeTree's web services.

I've uploaded the source code to a KnowledgeTree repository and can be downloaded using the KT-Gadget.

You need to download the following files:
  • ZK front-end app (ktlogin.zul)
  • ZK include script (ktlogin.zk)
You can download the Ajax spinner here or create your own.

My testbed:
  • Apache Tomcat Version 5.5.26
  • ZK 3.0.6
  • Windows XP Prof Service Pack 2
  • Mozilla Firefox 3.0

Sunday, July 20, 2008

Using KnowledgeTree SOAP Services from Java apps

I'm making a front-end to KnowledgeTree using the ZK Ajax framework so I eventually dipped my fingers into KT's web service integration from a Java app. My previous SOAP projects involved a Tcl client and a Google gadget using the javascript/php combo.

KnowledgeTree's wiki article on Web Service integration using Java has been most helpful. Since I'm doing my development work on Windows, I've adapted the procedures slightly. And since ZK uses the Beanshell interpreter to execute the Java code, I also modified the tester client application accordingly.

What's needed:

The proxy classes were generated using the WSDL2Java utility provided by the Apache Axis. I'm providing the packaged ktws.jar available for download here. To generate the proxy classes yourself, you would need the Java Compiler and not just the JRE. Here's the batch file to generate the jar package.

set CP=axis-1_4/lib/axis.jar;axis-1_4/lib/commons-discovery-0.2.jar
set CP=%CP%;axis-1_4/lib/commons-logging-1.0.4.jar;axis-1_4/lib/jaxrpc.jar
set CP=%CP%;axis-1_4/lib/log4j-1.2.8.jar;axis-1_4/lib/saaj.jar
set CP=%CP%;axis-1_4/lib/wsdl4j-1.5.1.jar
java -cp %CP% org.apache.axis.wsdl.WSDL2Java -c T1 -p com.pipoltek.ktws
"%JAVA_HOME%\bin\javac" -d . -classpath %CP% com/pipoltek/ktws/*.java
"%JAVA_HOME%\bin\jar" cvf ktws.jar .\com\pipoltek\ktws\*.class

Here's the Beanshell script to execute a login via SOAP.

filename: ktsoaptest.bsh
to run, type from the command-line:
java -cp bsh-2.0b4.jar bsh.Interpreter ktsoaptest.bsh

// add axis classes
addClassPath ("./axis-1_4/lib/axis.jar");
addClassPath ("./axis-1_4/lib/axis-ant.jar");
addClassPath ("./axis-1_4/lib/jaxrpc.jar");
addClassPath ("./axis-1_4/lib/commons-logging-1.0.4.jar");
addClassPath ("./axis-1_4/lib/commons-discovery-0.2.jar");
addClassPath ("./axis-1_4/lib/log4j-1.2.8.jar");
addClassPath ("./axis-1_4/lib/wsdl4j-1.5.1.jar");
addClassPath ("./axis-1_4/lib/saaj.jar");

// knowledgetree proxy classes
addClassPath ("./ktws.jar");

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

import com.pipoltek.ktws.KnowledgeTreeBindingStub;
import com.pipoltek.ktws.Kt_response;
import com.pipoltek.ktws.Kt_folder_contents;
import com.pipoltek.ktws.Kt_workflow_transitions_response;
import org.apache.axis.client.Service;
import com.pipoltek.ktws.Kt_folder_item;

URL url = new URL("");

KnowledgeTreeBindingStub stub = new KnowledgeTreeBindingStub(url, new Service());

System.out.println("Logging into KnowledgeTree");
Kt_response response = stub.login("rexjun","cabanilla","");

int status_code=response.getStatus_code();
String session;
if (status_code == 0) {
session = response.getMessage();
System.out.println("Session: " + session);
} else {
System.out.println("Status Code: " + status_code);
System.out.println("Session: " + response.getMessage());

I'll be using this piece of code to invoke SOAP services from my ZK app. More on this later..

Friday, June 20, 2008

KnowledgeTree document download via Google Gadget

This is an installment feature for the KT-Gadget, a widget for KnowledgeTree, an open-source document management system.

As mentioned in the previous post, I employed an application proxy to act as a bridge between a Google Gadget and KnowledgeTree's SOAP services. Originally, the application proxy responses were formatted in XML. Moving forward, I decided to adopt the JSON format instead of XML owing to simpler methods in handling JSON feeds from both PHP and (of course) Javascript worlds.

The application proxy's SOAP client is based on PHP and converting an associative array of response items to JSON format is implemented simply by using PHP's json_encode function. At the gadget side, the requests for remote data are invoked using Google's _IG_FetchContent function. Using Javascript's eval() function, the returned JSON text response is converted to an object whose members can be easily accessed using dot or subscript operators.

Here are the details.. To download a document, KT-Gadget sends a request to the helper application by invoking the url:

The JSON response by the application proxy is of the form:

The relevant block of KT-Gadget code is reproduced hereunder:

var url = "" + sessionID__MODULE_ID__ + "&fid=6";
_IG_FetchContent(url, function (jsonFeed) {
var jsonData = eval('(' + jsonFeed + ')');
if (jsonData.response.status_code != 0) {
alert ('Error in retrieving document!');
} else{
dd_link = jsonData.response.message;
_gel("ktg-lnk").innerHTML = '<a href="' + dd_link + '">@';

Once the application proxy has responded, the JSON text response (jsonFeed) is converted to an object (jsonData) using the eval() function. The response contains the download URL the value of which can be referenced as jsonData.response.message.

Where did the Session-ID (sid) and Document-ID (did) used as URL parameters come from? The Session-ID is assigned during login and the Document-ID, together with the rest of the document details, is returned by a call to get_folder_contents.

Here's the login invocation:

The invocation to get_folder_contents:

If you get a "Session is invalid" error, it's because the link is using an ID of an expired session. Change the URL parameter (sid) to a fresh Session-ID returned by the login call to get better results.

You may try out KT-Gadget by adding it to your iGoogle page or view the gadget source code here.

Thursday, June 12, 2008

Accessing Soap Services from Google Gadgets

The earlier gadget projects I developed have utilized third-party web services from a variety of sources:

  • Google Maps for geographic data
  • Google Docs spreadsheet as XML datasource
  • IP-Geolocation (IP address->Country) service from HostIP.Info
  • User profile and gaming statistics as XML feeds from Xfire.
Given the constraints of a space-restricted, remotely-hosted, javascript-only application, gadgets primarily rely on web services to provide richer data content without the attendant complexity. However, javascript is beset by cross-domain restrictions that even impact xmlHttpRequest, the very underpinnings of Ajax. Since Google gadgets are javascript apps, the same-origin security policy prevents it from making requests outside the domain it is running on.

Although Google addressed this problem by providing the _IG_FetchXMLContent function, it lacks support for https, SOAP and POST. It looks like the newer function is intended to address these deficiencies. However, the availability of the* functions in Google's production servers is yet forthcoming.

I decided not to hold my breath..

KT-Gadget is a widget front-end to KnowledgeTree - an open-source document management system. To access KnowledgeTree's SOAP services, I had to deploy another application to receive the requests from Google's _IG_FetchXMLContent calls, make the corresponding SOAP requests, and generate the appropriate XML output. Two basic functions are implemented: login and get_folder_contents. The functions are based on PHP, the same language that was used to develop KnowlegeTree.

After pushing the login button, the gadget will indicate the progress of the subsequent operations:
  • Logging in
  • Fetching Documents
  • Sorting Documents
In its current state, the gadget is useful as a watcher of some sort of inbox for workflow-enabled documents like incoming faxes needing disposition or requisitions requiring approval. Sit around and watch your work pile up...

I plan to implement two more functions: download_document and perform_document_workflow_transition. We don't want work to pile up, do we? Other nice-to-have features: watch all accessible folders and sub-folders; show number of documents per folder; folder and document colors based on certain thresholds like document count, age, metadata contents (priority, etc); automatic refresh.

But don't hold your breath...

Thursday, May 22, 2008

Xfire Gadget

Have XML feed, will gadgetize..

Xfire provides xml feeds on gamer profiles, gameplays, friends, screenshots -- a whole lot of goodies. An Xfire gadget would be a good addition to one's blog, website or iGoogle.

One of the goals was to determine how close the gadget can resemble the real thing so it was initially developed using the default Xfire skin. Making a cross-browser scrollable table was not a trivial task and once the problem was solved, I started to hate the over-sized scroll bars. More coding lies ahead..

Employing the Ajax paradigm of single-page experience, remote services, rich visual controls and other design patterns, any web app can now come real close to the regular desktop UI.

A live gadget using a COD4 skin is displayed in another blog.

Tuesday, April 22, 2008

OpenID login for Knowledgetree

I have replaced (not integrated) KnowledgeTree authentication with OpenID. Supporting both default and OpenID authentication requires more work and that can come later.

This is by no means final since only limited testing has been performed so far. I've decided to post this in its raw form so I can solicit comments and suggestions from the community. And besides, my blog has not been updated for awhile now..

I'll detail the procedures here. I took notes but I may have missed something. Diligence is not one of my virtues..

  • Copy login.php to login-orig.php (save original, let's modify login.php)
  • Change template ktcore/login -> ktcore/openid
  • Copy ./templates/ktcore/login.smarty to openid.smarty
    - change invocation of stylesheet kt-login.css -> kt-openid.css
    - change the Username prompt label to OpenID
    - add class="openid" to input tag for username
    - change name="username" to name="openid_url"
    - remove the password input field

  • Download the OpenID logo
  • Upload logo as openid-bg.png to ./resources/graphics
  • Copy ./resources/css/kt-login.css to kt-openid.css
  • Add input.openid to kt-openid.css, as follows:
    input.openid {
    border: 1px solid #666;
    width: 232px;
    background: url(../graphics/openid-bg.png) no-repeat;
    padding-left: 18px;
Note the OpenID logo in the input field. This is implemented by the input.openid section of the stylesheet. I also changed the input field name to openid_url in conformance to OpenID specifications. I'm using Verisign PIP as OpenID Identity Provider and the Seatbelt browser plug-in kicks in only when openid_url is used as identifier.

I installed version 2.x.x of the PHP OpenID Library, as follows:
  • Create the directory ./thirdparty/OpenID
  • Copy the file common.php found in the examples directory. Also copy the Auth directory from the library.
  • Add the following functions to common.php
    function fixslashes($s) {
    return get_magic_quotes_gpc() ? stripslashes($s) : $s;

    function normOpenIDUrl($oid_url) {
    $claimed_id = strtolower (fixslashes($oid_url));
    $has_scheme = preg_match ('#^https\://#', $claimed_id) === 1;
    $has_scheme = $has_scheme || preg_match ('#^http\://#', $claimed_id) === 1;
    $has_tslash = preg_match ('#/$#', $claimed_id) === 1;
    return (($has_scheme?'':'http://') . $claimed_id . ($has_tslash?'':'/'));
  • Overwrite the original functions in common.php with these
    function getReturnTo() {
    return sprintf("%s://%s:%s/login.php",
    getScheme(), $_SERVER['SERVER_NAME'],

    function getTrustRoot() {
    return sprintf("%s://%s:%s/",
    getScheme(), $_SERVER['SERVER_NAME'],
  • Change config/dmsDefaults.php to include the library in the path..
    $KTInit->prependPath(KT_DIR . '/thirdparty/OpenID');
The rest of the changes are in login.php, the source of which can be found here.

I've disabled automatic sign-up so make sure to create an OpenID account with Administrator privileges before you apply the changes. To prevent duplicate accounts, the usernames are OpenID URLs in normalized form (with leading http:// and trailing /). Thus, the username applies to any of the following acceptable OpenID identity URLs:
I've successfully authenticated using my OpenID accounts from Blogspot, Technorati and Verisign PIP. However, I had login failures using my Yahoo OpenID and a delegate identity URL.

My testbed:
  • VMWare Server version
  • KnowledgeTree OSS 3.4.2 VM Appliance from rPath
  • Microsoft Windows XP Home Service Pack 2
  • Mozilla Firefox

Sunday, April 6, 2008

Verisign Labs PIP, my favorite OpenID Provider

As a long-time user of Yahoo, Blogger, and Technorati, I inherited the following OpenIDs:

In due time, I may have another one courtesy of Flickr. I enrolled at and that added to my arsenal of OpenIDs.

My last OpenID provider might as well be the PIP, or Personal Identity Provider, from Verisign Labs. And this is primarily due to their Seatbelt Firefox plugin which adds ease and security when using your OpenID.

SeatBelt detects that you have clicked on an OpenID sign in field while not signed into your PIP account and prompts you to sign in. Once you have signed in, SeatBelt automatically returns you to the OpenID sign in page with your PIP URL filled in. The sign in session continues as normal.

Since redirection to the login page of your OpenID provider is done by the Seatbelt browser plugin, you can be assured that you are sending your credentials to Verisign and not a probable fake one generated by a phishing site.

One other neat feature is support for OpenID delegation allowing me to use as my OpenID URI in place of the lengthy This is made possible by adding two link tags inside the head section of my home page.

Wait.. there's more! You can protect your Verisign PIP account with two-factor authentication by the use of a security card, token, or a Sandisk U3 flash drive. Verisign PIP even supports the US$5 Paypal security key aside from its own.

As a lead player in identity protection services and a recent member of the OpenID Foundation, one can expect Verisign to continue providing innovative solutions supportive of the OpenID framework. So far, I'm not disappointed.

Saturday, April 5, 2008

Getting and Using your OpenID

I stumbled upon OpenID last year when I was foraging for single sign-on resources in the Internet. It is interesting to note that OpenID has grown by leaps and bounds in just over a couple of years since its inception in 2005. At the start of the year, OpenID's popularity ballooned when Yahoo! launched the public beta of its own OpenID identity service. The following month, the OpenID Foundation announced that heavyweights Google, IBM, Microsoft, VeriSign, and Yahoo! have joined its board. This recent development gives promise of significant things yet to come.

OpenID aims to simplify one's online experience by providing an account that can be used across multiple websites. Blogger already supports OpenID commenting and a Wordpress plug-in is also available to achieve the same purpose. An OpenID is a passport to hundreds of sites listed here.

Some people strongly feel that using OpenID is akin to putting all eggs in one basket since a hacked account becomes a master-key to several sites all at once. The specter of phishing super-accounts is disturbing, and real. Privacy concerns are also associated with identity providers amassing too much personal data. One practical answer would be to have several OpenID accounts (there goes OpenID's mission) --- having five OpenIDs instead of juggling fifty accounts is still nirvana to me... Ok, make that thirty - I can't recall the twenty already! And anti-phishing counter-measures need to be present, either built-in or as a supplement, OpenID or not. Identity-theft is not limited to phishing --- a significant chunk of the world's netizens can easily lose their online identity due to weak passwords, key-loggers or plain social engineering tactics.
Ready to take on OpenID and its challenges? To enable a Yahoo! account for OpenID, start here. Most may not know it but AOL, Technorati, and Livejournal users already have an OpenID associated with their accounts. A blog owner at Blogger or Wordpress can use their blog url (e.g. as OpenID account. If you're not one of the millions of Yahoo (248M) or AOL (63M) users, or just want to experiment with OpenID, a host of public OpenID identity providers can be availed of here.

Give it a spin by posting a hello on this blog using your OpenID account.

Friday, March 28, 2008

Circumventing IP Geolocation

Although IP geolocation can be fairly accurate, it can also be easily thwarted with the use of global gateways or proxy servers that are geographically distant from the actual location of the user.

Using's IP geolocation service with an online PocketPC via Smart GPRS puts my location at Makati, Philippines. The country is correct but the city is a good 570 kilometers from mine, or an hour's ride by plane.

On the other hand, using Anchorfree's Hotspot Shield wifi gateway plants me on the other side of the globe at Washington DC. Well, at least it's the same planet..

Sunday, March 23, 2008

IP Geolocation Gadget

Geolocation by IP address is the method of determining the geographic location of an Internet-connected device by examining data related to the IP address it is using. Google is effectively using geolocation to deliver targeted content by redirecting Google visitors to pages translated into their local language. On the other hand, geolocation can also be used to filter content. Just recently, selected YouTube videos have been discovered to bear special tags indicating that it is inappropriate for viewing in some countries .

There are several IP geolocation services in the Internet so I gave some of them a spin. Here are the results according to increasing accuracy in determining my location.

- IP2location got my country right but was not able to provide my city.
- IPligence got my city wrong.. or maybe it did not know my city and gave the national capital instead as default.
- Geobytes and Maxmind got my city right.
- not only got my city right but also has an API to go with it. And did I mention that the service is free?

Point your browser to and you get an XML feed containing not only your City and Country but also your geographic coordinates as well. Sweet!

I can't resist the temptation to use this geolocation service and mix it up with Google Maps API into a Google gadget.. so I came up with another widget called WTHAMI?. Here it is in action..

If it doesn't get your location right, don't sue me. You can update your location at

Saturday, March 22, 2008

Cebumapia, the Google Gadget

I've always wondered if I could package Cebumapia as a Google Gadget.. After taking a peek at the other Gadget samples, I was determined to build it. The wondering stage is over now..

The gadget uses the Google Maps API to render the map, overlay markers and trigger events when the mouse hovers on markers and when these are clicked. Markers are stored in xml files and fetched using the gadget-specific _IG_FetchXmlContent function.

The Cebumapia gadget code is miniscule - only 200 lines. But then, functionality is trimmed down to the very basic and UI is just as minimalistic. Take out the module header, css and html portions, and you are left with only 100 lines of javascript code.

If you've been wanting to create your own killer gadget and thinking you don't have the time, start now! My advise.. Start with small reliable code and incrementally build on it. Me? I'm planning to add a local news feed.. spruce up the UI for an iPhone-ish look.. or maybe not.

Add the Cebumapia gadget to your iGoogle page..

See the gadget embedded in Blogger template at Cebumapia.

Thursday, March 13, 2008

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 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;
// String url = "jdbc:odbc:Fred";
String mySchema = "REXJUN1";
String myAS400 = "";
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
//close the jdbc connection

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.

Tuesday, March 4, 2008

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.

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;

private Connection connection = null;
private Statement s = null;
private String mySchema = "REXJUN1";
private String myAS400 = "";
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.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.out.println("Dumping table..");
try {
ResultSet rs = s.executeQuery("select * from basicjdbc");
int i = 0;
while ( {
System.out.println("| " + rs.getInt(1) + " | " + rs.getString(2) + "|");
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());
try {
if (connection != null)
} catch (Exception e) {
System.out.println("Caught exception: ");

Sunday, February 3, 2008

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 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 = "";
public static final String smtpUsername = "";
public static final String smtpPassword = "-change-me-";
public static final String smtpPort = "465";

Properties props = new Properties();
props.put("mail.smtp.user", smtpUsername);
props.put("", smtpHost);
props.put("mail.smtp.port", smtpPort);
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", "");
props.put("mail.smtp.socketFactory.fallback", "false");

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

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

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

Saturday, February 2, 2008

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.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 = "";
public static final String toAddress = "";

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");

/* 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!");
Certificate[] chain = keystore.getCertificateChain(keyAlias);

/* Get the private key to sign the message with */
PrivateKey privateKey = (PrivateKey)keystore.getKey(keyAlias,
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));
MimeMultipart bodyMulti = new MimeMultipart();

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

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

ASN1EncodableVector attributes = new ASN1EncodableVector();
attributes.add(new SMIMEEncryptionKeyPreferenceAttribute(
new IssuerAndSerialNumber(
new X509Name(((X509Certificate)chain[0])
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();
CertStore certs = CertStore.getInstance("Collection",
new CollectionCertStoreParameters(certList), "BC");

/* 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()) {

/* Create the encrypter and encrypt the message */
SMIMEEnvelopedGenerator encrypter = new SMIMEEnvelopedGenerator();
MimeBodyPart encryptedPart = encrypter.generate(signedMessage,
SMIMEEnvelopedGenerator.RC2_CBC, 128, "BC");
ByteArrayOutputStream out = new ByteArrayOutputStream();

/* 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()));

/* 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.writeTo(new FileOutputStream("Encrypted.eml"));

catch (SMIMEException ex)
catch (Exception ex)

Tuesday, January 29, 2008

Getting a Commercial Digital Certificate

I need to acquire a digital certificate to be used for encrypting files prior to uploading it to a bank's online service. Three commercial Certificate Authorities (CAs) were recommended by the bank, namely: Entrust, Thawte and Verisign.

I was given six (6) characteristics of what I should be looking for. These items cover specs like the type of MDA, encryption algo, key size, certificate export formats, etc. Couldn't I just have the SKU number?

An SKU would have been most useful. The CA websites and storefronts were basically stocked with a slew of server SSL products -- my digital certificate is buried in some obscure page somewhere. Before I entirely lost my shopping appetite, Verisign's livechat popped into peripheral view. Good thing! I was about to put this off for another day.

After a couple of minutes, I got my answer. I need a Class 1 Digital ID and the product page is tucked here. Thawte has a livechat button, too -- but after three reps and twice the wait, I was informed politely that Thawte does not sell client certificates for commercial use. Entrust has no livechat and email response is rather sluggish.

You can purchase a Verisign Digital ID online for US$19.95 per year. A 60-day trial is also available for free. International customers, though, are served by the VeriSign Trust Network of International affiliates. That's me! I pray that dealing with their local affiliate is just as breezy.

Monday, January 21, 2008

A Tcl Web Service client for KnowledgeTree Document Management System

Sometime last year, I dabbled with KnowledgeTree's web services and (somehow I've forgotten why) I ended up with a Tcl client to perform a document upload.

Before I lose this chunk of code or forget about it altogether (and it happens a lot), I decided to post it here:

package require SOAP
package require http
package require TclCurl

set prx ""
set url ""
set usr "admin"
set pas "admin"
set ipa "any"

set upf "upload-me.txt"
set typ "multipart/form-data"

proc log_it { chn msg } {
set stm [clock format [clock seconds] -format "%Y/%m/%d %H:%M:%S %a"]
puts $chn "$stm : $msg"

SOAP::create kt_login \
-proxy $prx \
-params { "username" "string" "password" "string" "ip" "string"} \
-name login
SOAP::create kt_add_document \
-proxy $prx \
-params { "session_id" "string" "folder_id" "int" "title" "string" "filename" "string" \
"documentype" "string" "tempfilename" "string" } \
-name add_document
SOAP::create kt_logout \
-proxy $prx \
-params { "session_id" "string"} \
-name logout

set log "debug.log"

if { [catch {open $log a} out] } {
puts stderr "Error: $out"
set out "stderr"

set rsp [kt_login $usr $pas $ipa]
set sta [lindex $rsp 1]
set ses [lindex $rsp 3]
log_it $out "Login: status ($sta); session ($ses)"

set opt "session_id $ses action A"
set crl [curl::init]
set rsp [$crl configure -url $url -bodyvar rsp -post 1 \
-httppost [list name "file1" file $upf contenttype $typ] \
-httppost [list name "session_id" contents $ses] \
-httppost [list name "action" contents A] \
-httppost [list name "output" contents php] \
catch { $crl perform } curlErrorNumber
if { $curlErrorNumber != 0 } {
error [curl::easystrerror $curlErrorNumber]
$crl cleanup

log_it $out "Upload: response ($rsp)"

set ps1 [string first "\"tmp_name\";s:" $rsp 0]
set ps2 [string first ":\"" $rsp [expr $ps1 + 12]]
set ps3 [string first "\";s:5:\"error\"" $rsp 0]
set tmp [string range $rsp [expr $ps2 + 2] [expr $ps3 - 1]]

# Define folder where to upload files
set fld 1;
set ttl "My Document";
set doc "Default"

set rsp [kt_add_document $ses $fld $ttl $upf $doc $tmp]
log_it $out "Add Document: response ($rsp)"

set rsp [kt_logout $ses]
log_it $out "Logout: response ($rsp)"

close $out

The upload facility is implemented by upload.php and returns a response coming out of php's serialize() function. I've mangled upload.php to return a response in xml or json format. The code changes are posted in the Knowledgetree community forum here.

The above Tcl code digests the php's serialize() output.

For xml output, here are the relevant changes:

set crl [curl::init]
set rsp [$crl configure -url $url -bodyvar xml -post 1 \
-httppost [list name "file1" file $upf contenttype $typ] \
-httppost [list name "session_id" contents $ses] \
-httppost [list name "action" contents A] \
-httppost [list name "output" contents xml] \
set top [dom parse $xml]
set sel [$top selectNodes /results/uploads/document/tmp_name/text()]
set tmp [$sel nodeValue]
$top delete

And for json..

set rsp [$crl configure -url $url -bodyvar rsp -post 1 \
-httppost [list name "file1" file $upf contenttype $typ] \
-httppost [list name "session_id" contents $ses] \
-httppost [list name "action" contents A] \
-httppost [list name "output" contents json] \
set ps1 [string first "\"tmp_name\":\"" $rsp 0]
set ps2 [string first "\",\"error\"" $rsp 0]
set tmp [string range $rsp [expr $ps1 + 12] [expr $ps2 - 1]]

Check the contents of debug.log for any errors. A successful operation produces this output: