JDBC: 设定你的AS/400库列表

(JDBC) is one of the most important features of Java on any platform. You can use JDBC objects to define and run standard SQL statements to select and update AS/400 data files and call any RPG batch program easily. In addition, if you don't know Java but want to create Web applications for the AS/400, the easiest way is to use IBM's WebSphere Studio and visually build JDBC calls to your AS/400 database and RPG programs. So, JDBC is key.

 

Given the AS/400's nonstandard library system, it is important when using a JDBC connection to be able to dynamically set the library or libraries you want to access.

How do I use JDBC?

With JDBC, there is a sequence of statements you run for most scenarios, whether you access data using SQL or stored procedures to call RPG programs. You must do the following:

• Load a JDBC driver
  
• Create a JDBC connection object to the AS/400
  
• Create a statement object and set parameters
  
• Execute the statement object
  
• Receive the results from the execution
  
• Close the statement
  
• Close the connection

The examples below show how to select data from an AS/400 table and list it in a simple report.

Which JDBC driver should I use?

Different drivers work in different application environments. You can use JDBC drivers for any Java application to access AS/400 data and programs.

Sun JdbcOdbc Driver

String jdbcDriverName = "sun.jdbc.odbc.JdbcOdbcDriver";

applications supported
applets (NOT without special security authorizations), servlets (NOT on AS/400), remote AS/400 applications (e.g., a Windows application with ODBC installed and configured)

Every Java includes a Sun driver (JdbcOdbcDriver) used when you have no other choices. You need an ODBC data source defined first (for instance, in Microsoft Windows). Then this driver translates the ODBC to JDBC for your Java application. This driver is primarily used to access local ODBC data sources on a Windows PC.

IBM AS/400 Java JDBC Driver

String jdbcDriverName = "COM.ibm.db2.jdbc.app.DB2Driver";

applications supported
servlets, local AS/400 applications, remote AS/400 applications (via AS/400 DRDA support ONLY)

This driver is the preferred driver where it can be used. Generally, it will provide the best performance in most scenarios. It is included as part of AS/400 Java (program 5769-JV1), which is free from IBM. It is packaged in the classes.zip file.

IBM Java Toolkit for AS/400 JDBC Driver

String jdbcDriverName = "com.ibm.as400.jdbc.access.AS400JDBCDriver";

applications supported
applets, servlets, local AS/400 applications, remote AS/400 applications

This driver is part of IBM's Java Toolkit for AS/400, which is also distributed free (program 5769-JC1). Is is packaged in the jt400.jar.

The Java toolkit driver works great for any remote connection to an AS/400 as well as local. The native driver has some advantages, including performance if you are doing local access to your AS/400 database (e.g., a Java servlet running on the same machine where the data is, an AS/400 Java application, etc.).

What are the options for dynamically setting library lists?

1. Of course, you can always control a library list for any job using the job description. For JDBC, the job description used depends on the JDBC driver selected. Look at QZDASOINIT jobs in QSERVER for the remote Java toolbox driver session. For the native driver, it is running in your Java job on the AS/400 (see jobs in WRKACTJOB and check the job description. It should be the same as the job in which you started your Java job).

2. Use the IBM drivers with SQL naming option = sql and set the library as part of the URL. ( to see an example.)

3. Use the IBM drivers with SQL naming option = system and set the library as part of a libraries property attribute on the JDBC properties object for a connection. ( to see an example.)

4. After opening a connection on the AS/400, you can call a custom CL program via a stored procedure and pass it a library name you want to set with ADDLIBLE or CHGCURLIB. This can be called over and over again, dynamically changing the library list without creating a new connection. This option takes more work, but it has the most flexibility.

Which naming method should I use for a connection?

There are specific 'AS/400' type properties available under the system naming convention for the Toolkit and native JDBC drivers (e.g., using a libraries property for the driver ) vs. using an SQL schema qualifier. Our preference is to use SQL naming, which maximizes the of your JDBC code. (This is assuming you would dynamically configure URLs, etc. so none of the code has to be rewritten).

--------------------------
About the authors: Jim Mason is president of Cape Cod Bay Systems, and he writes, consults, teaches, designs and develops AS/400 Web applications using Java, WebSphere, DB2, Lotus Domino and the WebSphere Development Tools for AS/400. Dave Slater is World Wide Market Manager of AS/400 Application Development at IBM Canada.