Powerful generic utilities and products can be built that allow you to query,
display, update and delete records. These utilities may be similar in function to
QUERY/400 and DFU for the Web. The key to doing this is the usage of JDBC
metadata to discover at runtime what libraries, tables and fields are being
operated on.
To do that, we must be able to feed a JDBC connection a SQL string and
then dynamically list the records returned, along with the library, table and
field names that data came from. For example, select * from invoice should
produce a list of invoices, while select * from orders will display a list of
orders. Prior to V5R2, the Java toolbox for the iSeries did not indicate the
library (schema) where the data field was returned from, which made it
difficult to determine when writing generic update code. In V5R2, the
connection can be configured as to allow extended metadata to be returned.
In the following example, we will create a generic utility illustrated by code
snipits that allows any JDBC/SQL Select statement to be run. The result will be
a list of the records returned with a column header that shows the library,
table and column name for each field returned. For real production usage,
simply format the column headers to your liking.
Step1:
First we must create a connection that specifies "extended metadata" =
true property of the connection. Your code would look something like this:
Connection con =
DriverManager.getConnection("jdbc:as400://server_ip;extended
metadata=true;trace=false;" , username, password);
The server_ip is the address of your iSeries, and the username and password
are used for authentication to the server. To give you a better idea about what
the extended metadata property does, below is IBM's description and warnings.
NOTE: This example uses IBM's ToolBox for Java (JT400.JAR).
Information from manual
"Extended metadata" -- Specifies whether the driver requests extended
metadata from the server. Setting this property to true increases the accuracy
of the information returned from the following ResultSetMetaData methods:
getColumnlabel(int), isReadOnly(int), isSearchable(int)
Other information:
Additionally, setting this property to true enables support for the
ResultSetMetaData.getSchemaName(int) method. Setting this property to true
may degrade performance because it requires retrieving more information from
the server. Leave the property as the default (false) unless you need more
specific information from the listed methods. For example, when this property
is off (false), ResultSetMetaData.isSearchable(int) always returns "true"
because the driver does not have enough information from the server to make
a judgment. Turning on this property (true) forces the driver to get the correct
data from the server. You can use extended metadata only when connecting to
a server running OS/400 V5R2 or later.
Step 2:
Using the connection created above, create an SQL statement from a string.
The string may be dynamically composed.
Example: Statement smt = conn.createStatement();
String anySQLSelect = 'select * from qiws.qcustcdt';
ResultSet rs = smt.executeQuery(anySQLSelect);
Step 3:
Loop through the resultSet, getting the column, table and library from the
metadata.
ResultSetMetaData rsmd = null;
while (rs.next == true)
{ //get resultSetMetaData about columns.
rsmd = rs.getMetaData();
//number of columns is set and used to dynamically know how many flds
int numOfColumns = rsmd.getColumnCount();
for(int columnCount = 1; columnCount <= numOfColumns; ++columnCount)
{ //loop thru rsmd to get column, table, and library/schema information
//to output
System.out.println("For Column " + rsmd.getColumnName(columnCount));
System.out.println("the table name is"+rsmd.getTableName(columnCount));
System.out.println("and the Schema or Library name is" +
rsmd.getSchemaName(columnCount));
// now dynamically output the data returned from the rs.
rs.getObject(columnCount)
}
}
Summary
The key additional feature available with "extended metadata" is the ability to
dynamically get the schema name, which in the iSeries means the library
name. This is very powerful and also works for SQL Joins. The only thing you
would need to change is the SQL statement to include a join or multiple joins.
What this all means is that IBM has provided Web developers with a new
capability to develop generic components and utilities without requiring them
to know the library name. Among other things it allows allow for the usage of
library lists, which can be specified on the connection.
---------------------------
About the authors: Paul Holm is a former IBM-Rochester WebSphere,
Java, and DB2 developer/consultant. He specializes in helping iSeries RPG
shops develop Web-based applications. Paul is currently a lead architect for the
"WOW" product, which is Query/400 and DFU for WebSphere (aka WebSphere on steriods).
Paul can be reached at pholm@planetjavainc.com.
Colin Slade is a Web developer at PlanetJ Corp.
