iSeries Wireless Access with JDBC

by Marc Logemann

[The code for this article is available for download.]

This article will explain how to enhance the iSeriesMonitorME application (see "iSeriesMonitorME: A Real-World Wireless iSeries Application"), which is capable of calling a system program on an iSeries, to show you a way to access an iSeries database from a wireless device with IBM's JTOpen JDBC package, called JdbcMe.

JDBC and JdbcMe: The Java Way to Access Databases

JDBC is the pendant to ODBC from the Microsoft world. It provides a uniform way to access different databases. This means--at least in theory--that you don't have to reprogram your application when you change your database, because the way to access ResultSets or MetaData is the same for each and every database. However, in the real world, we all know that there are some extensions to SQL that some vendors support but others do not. Some vendors don't support the complete SQL set at all, and access to stored procedures is often completely different. But the good thing is, if you really want to be cross-database compliant, you can achieve it through the standardization of SQL and JDBC, with some extra effort, of course.

Once again IBM faced the problem that the complete JDBC package, known as java.sql, was far too big to distribute to a wireless client. The JDBC implementation for desktop or server applications shipped with JTOpen is about 1.5 MB. So IBM created a minimal subset of the interfaces and called them JdbcMe classes.

These classes provide the following functionality, known from regular java.sql classes:

• The ability to insert or update data.
• Transaction control and the ability to modify transaction isolation levels.
• Result sets that are both scrollable and updatable.
• SQL support for calls to stored procedures and drive triggers.

Additionally, JdbcMe supports unique features, like the following:

• A universal driver that enables the majority of the configuration details to be consolidated at a single point on the server side.
• A standard mechanism for persisting data to offline storage.

JdbcMe includes the following classes:

• JdbcMeConnection
• JdbcMeDriver
• JdbcMeException
• JdbcMeLiveResultSet
• JdbcMeOfflineData
• JdbcMeOfflineResultSet
• JdbcMeResultSetMetaData
• JdbcMeStatement

As you can see in the list of classes shipped with JdbcMe, there is also a feature for data synchronisation between the wireless client and the server, allowing the client to do various tasks offline and to synchronize with the server database afterward. In this article we will focus on the live parts--meaning accessing and modifying the database directly.

Setting Up the Database

For our little demo application, we will only need one table (or physical file) on our iSeries. To keep it simple, just go to your SQL console with STRSQL and run the following SQL against the database:

CREATE TABLE USERLIB/ARTPF
(
  JUSANR NUMERIC(6, 0) NOT NULL, 
  JUSART CHAR(30) NOT NULL, 
  JUSADE CHAR(100) NOT NULL, 
  JUSAGR CHAR(30) NOT NULL, 
  JUSMEN NUMERIC(5, 0) NOT NULL
)

Remember that qualifying the library USERLIB can be done with a period (.) or a slash (/). If you have not changed anything on the iSeries yet, use the slash. If you encounter problems, give the period a try.

Here is a short description of the fields:

• JUSANR: Articlenumber
• JUSART: Articlename
• JUSADE: Articledescription
• JUSAGR: Articlegroup
• JUSMEN: Stock Quantity

Where userlib is your personal library on the iSeries.

Next we will populate the table with some sample data. To do this, run the following SQL:

INSERT INTO USERLIB.ARTPF VALUES(123456,
             'Pilawa AS4821DTBK TFT', 
             'Farbkontrolle horizontale/vertikale
             Zentration horizontal/vertical 
      Size Clock Phase','Monitor',5)
INSERT INTO USERLIB.ARTPF VALUES(123457,
            'Belinea 101536 TFT','max. 
            Resolution: 1024x768 Pixel','Monitor',10)
INSERT INTO USERLIB.ARTPF VALUES(123458,
            'Eizo L565 TFT','Farbcontrol 
   horizontal/vertical Zentrigation 
            Auto Adjust Brightness','Monitor',14)
INSERT INTO USERLIB.ARTPF VALUES(123459,
            'Samsung SyncMaster 191N AP','max. 
            Resolution: 1280x1024 Pixel','Monitor',3)
INSERT INTO USERLIB.ARTPF VALUES(123461,
            'HP DeskJet 3420C','Thermic 
            Bubbleprinter Drop-on-demand','Printer',7)
INSERT INTO USERLIB.ARTPF VALUES(123462,
            'Epson Stylus C42UX','1440x720 dpi 
            MicroPiezo Variable Sized Droplet','Printer',11)
INSERT INTO USERLIB.ARTPF VALUES(123463,
            'Epson EPL-5900L','1200 x1200 dpi 
             Laserprinter','Printer',21)

We just created our little article database and filled it with some sample articles. Later we will access this data from our wireless device. Please remember the library where you created the table; you will need it in the wireless Java code.

The last things to check are the TCP/IP port settings on your iSeries and the MEServer, if it runs on a different server from the database. The following ports must be accessible to let your device query the database:

Since the iSeriesMonitorME application also uses the CommandCall feature, as you have seen in my previous articles, it is also included in the table.

At the end, make sure that the library where you created the table is in your iSeries user library list.

Enhance the Wireless Application

In our application, we already created a menu item, which you will reach after a successful login to the iSeries, called "JDBC call." All we must do in the CommandMenu.java file is to implement the correct action when selecting the menu. This is done by modifying the private Method doJDBC.

   private void doJDBC() {
      JDBCgetData jdbcGet = new JDBCgetData();
      try {
         List list = jdbcGet.runQuery();
         list.addCommand(COMMAND_BACK);
         list.setCommandListener(new CommandListener() {
            public void commandAction(Command command, 
                  Displayable displayable) {
               if (command == COMMAND_BACK) {
                  display.setCurrent(new CommandMenu(m, as400));
               }
            }
         });
         display.setCurrent(list);
      } catch (SQLException e) {
         e.printStackTrace();
      }
   }

The main part of this method is pure MIDP programming. It creates a command to go back from, and display, the next screen. The interesting part for this article is the method call to runQuery of the JDBCgetData class. Here we retrieve a List, which is an MIDP GUI screen for displaying multiple entries, like a combo box. As you might expect, the JDBCgetData class does all the SQL work for you, so let's see what it does exactly.

public class JDBCgetData implements CommandListener{
   private String sql = "select * from userlib.artpf";
   public List runQuery() throws JdbcMeException, SQLException{
      Connection conn =
       JdbcMeDriver.getConnection("jdbc:as400://10.10.10.10;
                  meserver=10.10.10.10;",
                 "user","password");
      Statement stmt = conn.createStatement();
      List l = new List("Data", List.IMPLICIT);
      ResultSet rs = stmt.executeQuery(sql);
      while(rs.next()) {
         String artName = rs.getString(2);
         l.append(artName, null);
      }
      return l;
   }
}

First we define an SQL query that we will run against the database later on. Don't forget to replace userlib with the library name you created before. With this SQL we will receive all records from the table artpf. The method runQuery() will first retrieve a connection to the database with the getConnection() call. Here you will have to replace the IP address with that of your iSeries. Use the same IP address for meserver when it runs on the same iSeries. The other two parameters are for security purposes: Provide a username that has your created library in his library list and provide a valid password.

Then we will create a connection object from the retrieved connection and execute the SQL containing the statement. Before, we created a new list object in order to place the results from the database in it. With List.IMPLICIT, we declare that only one selection at a time is possible with this list. The while loop iterates over the ResultSet we got from the database. In fact, the loop will iterate over all articles in the database, and the second column, which is the articlename, will be added to the list. The last thing we do is to return the List object back to the caller--the doJDBC() method--responsible for making the list active on the screen.

You can expect to see a list on your mobile device containing all article names in the ARTPF table. We have not implemented anything extra in this example, but it's very easy to select an item from the list and perform additional actions, like showing the stock quantity and displaying those results on another screen. It's also no problem to manipulate the data and send an update statement to the database nearly the same way we did the select.

JDBC the Wireless Way

As you can see, doing JDBC from a wireless device is not much different from doing JDBC for a desktop application. Those programmers who know interfaces, like Connection or Statement, shouldn't have too many problems. There are only the minor differences that I mentioned in this article.

If you'd like to try it, you can grab the application from "iSeriesMonitorME: A Real-World Wireless iSeries Application", as well as download the patch. Both ZIP files contain "readme" files, which explain what to do.

Marc Logemann is a senior e-business consultant at Spirit/21 AG, a German consulting company that focuses on iSeries services and development. Marc is involved in Java- and PHP-based open-source projects and likes reading books about new technologies. E-mail: ms@logemann.info

Related Articles and Resources

• iSeriesMonitorME: A Real-World Wireless iSeries Application
• iSeriesMonitorME: Set Up a Wireless Development Environment
• iSeriesMonitorME: An Intro to Wireless Programming with iSeries ToolboxME
• iSeriesMonitorME Resource Web Site

Sponsored By T.L. ASHFORD

BARCODE400 by T.L. Ashford is the easiest and fastest way to create and print Compliance Labels directly from the AS/400 and iSeries. Ashford's comprehensive library of Compliance formats is available to Barcode400 users. AIAG labels for Ford and Motorcraft, GM, and many more are available. BARCODE400 is backed by the best Technical Support Team in the industry. FREE Guide to Bar Code Labeling www.tlashford.com or call 800.541.4893