I am often amazed by how easily Internet applications can be developed using Java. Recently, I sat down to write a Web-based interactive SQL utility. What I wanted was a replacement for SQL/400. I often used SQL/400 to create and maintain DB2/400 files, but I was getting tired of starting a 5250 session only to enter one or two simple SQL statements and then log off. What I wanted was a Java servlet that would allow me to enter SQL statements from a Web browser. I had been thinking about developing this servlet for a few months, but, after reviewing the SQL hooks available with IBM’s latest Java Toolbox for the AS/400, I knew I could create my Web-based SQL processor quickly. In fact, it took me less than an hour and only about a hundred lines of Java code. I called the resulting Java-based SQL server WebSQL.

The purpose of this article is twofold: to provide a freeware, Web-based interactive SQL product and to demonstrate how easy it is to develop Web applications with Java. Figure 1 shows the Web page that prompts you for your SQL statement. When you key in a Select statement (be sure to use the dot operator, not the AS/400 slash, for the library qualifier: e.g., DENONCOURT.CUSTMAST), WebSQL responds with a “subfile” (actually, it’s an HTML table). Figure 2 provides an example of such a subfile. Key a Create, Insert, Update, Delete, or Drop command, and the SQL database maintenance statement executes. If anything goes awry, you get an HTML page that tells you what was wrong with your SQL. For example, when I keyed in SELECT * FROM MYLIB.BOGUS, I received the following response on an error page in my Web browser: It would seem that coding WebSQL would be complex, but it was really quite simple. All I did was use a few Java Database Connectivity (JDBC) calls to invoke the SQL statements and delegate the responsibility of formatting query results to a couple of the classes in IBM’s jt400Servlet.jar from Modification 3 of IBM’s AS/400 Toolbox for Java (JT400). Figure 3 shows the HTML code for WebSQL’s prompt. All it contains is a simple HTML form (which is basically like a display file input record format) that is composed of a single field called sql. When the user clicks the Submit button, the value entered in the sql text

java.sql.SQLException: [SQL0204] BOGUS in MYLIB type *FILE not found.

The Code

area input field is sent to the WebSQL servlet to process the SQL request on the AS/400. If the user clicks the Reset button, the input in the SQL text area is simply cleared.

Serving SQL with Java

Figure 4 shows part of the code for my WebSQL Java servlet. (For the complete code go to MC’s Web site at www.midrangecomputing.com/mc). It imports packages of Java classes that perform such things as SQL execution and standard input/output. It also imports the Java extension classes for servlet processing. It then imports two Java packages that are new with JT400 Mod 3: one that encapsulates access to HTML and one that builds Web pages from JDBC result sets. The function called init, which is basically the initialization subroutine of a Java servlet, loads the JDBC driver (with a call to the Class.forName function) and establishes a TCP/IP connection to the AS/400 (with a call to DriverManager.getConnection). Like an RPG initialization subroutine, the init function is only invoked once, so all remote users of WebSQL will use the same SQL connection. The Web server invokes the function called doGet when a user clicks the Submit button of the HTML form shown in Figure 1. WebSQL’s doGet function, after setting the content type and obtaining an output stream for the HTML, retrieves the values of the SQL input parameter SQL. If SQL is a Select statement, doGet invokes the buildTable function; otherwise, doGet invokes the runUpdate function.

The buildTable function uses JDBC’s executeQuery function to retrieve the SQL Select statement’s result set. If JDBC is unable to run the SQL, buildTable calls the itDidntWork function to dump the SQL error into a Web page. But if all goes well, buildTable uses a JT400 Mod 3 class called HTMLTableConverter to construct an HTML table from the SQL result set. However, before HTMLTableConverter can do its stuff, the user must wrapper the SQL result set with SQLResultSetRowData and set the format of an HTML table with the HTMLTable class. Once the user has created the SQLResultSetRowData object and constructed the HTMLTable, he or she invokes the HTMLTableConverter’s convertToTables function to build a table such as the one shown in Figure 2. Finally, the user dumps the table to the HTML output with a call to out.println(htmlTable[0]).

When a user keys an SQL statement that is not a Select statement, WebSQL’s doGet function calls runUpdate. The runUpdate function simply invokes JDBC’s executeQuery function, passing to it the SQL statement that the user entered. If the SQL statement doesn’t run properly, the itDidntWork function notifies the Web browser user of the error.

Potential

I now have Web-based interactive SQL, and if you download the code for this article from MC’s Web site (www.midrangecomputing.com/mc), so will you. WebSQL begs for improvements and enhancements. For one thing, you might want to shore up security for WebSQL. You may have noticed that all users of WebSQL, as it is written today, use the same profile. One easy way to add a layer of security is to add a password/profile HTML prompt to enable the AS/400’s object-based security. Another suggested enhancement is to store the last 10 or so SQL statements executed by a user in a cookie. Then, using JavaScript in the WebSQL.html file, you can display the last 10 SQL statements so the user can cut and paste them into the SQL statement’s text area.

The real focus of this article is not Web-based interactive SQL but the potential for Java-based Internet applications. If you understand the architecture for my quickly coded yet powerful WebSQL application, then you will begin consideration for crafting business applications for Internet deployment.

Figure 1: The Submit button of an HTML input form functions like the Enter key of a 5250 screen, and the Reset button works like F5=Refresh.

Figure 2: HTML tables are basically subfiles for the Internet.




public class WebSQL extends HttpServlet {

Connection connection; Statement sql;

public void init(ServletConfig sc)

throws ServletException {

super.init(sc);

try {

Class.forName("com.ibm.as400.access.AS400JDBCDriver");

Figure 3: The DDS for WebSQL’s HTML file contains one “record format” (HTML form) and one input field.

} catch( ClassNotFoundException e) {...}

try {

connection = DriverManager.getConnection (
"jdbc:as400://AS400IPorDomain", "profile", "password"); sql = connection.createStatement();

} catch (SQLException e) {...}

}

public void doGet(HttpServletRequest req,

HttpServletResponse resp)

throws ServletException, IOException {

resp.setContentType("text/html");

PrintWriter out=new PrintWriter(resp.getOutputStream());

String sqlStatement = req.getParameter("sql");

if(sqlStatement.toUpperCase().indexOf("SELECT") != -1){

buildTable(resp, sqlStatement, out);

} else {runUpdate(resp, sqlStatement, out); }

}

private void buildTable(HttpServletResponse resp,

String sqlStatement, PrintWriter out)

throws ServletException, IOException {

ResultSet rs = null;

try { rs = sql.executeQuery(sqlStatement);

} catch (SQLException e) {

itDidntWork(resp, e, out); return;

}

SQLResultSetRowData recs = null;

try {recs = new SQLResultSetRowData(rs);

} catch (RowDataException e) { ...}

HTMLTable table = new HTMLTable();

try { table.setBorderWidth(2);

table.setCellSpacing(1);table.setCellPadding(1);

} catch (PropertyVetoException e) {...}

HTMLTableConverter conv = new HTMLTableConverter();

HTMLTable[] htmlTable = null;

try {

conv.setTable(table);

conv.setUseMetaData(true);

htmlTable = conv.convertToTables(recs);

} catch (Exception e) {...}

out.println("");

out.println("

Select Statement Results

out.println(htmlTable[0]);

out.println("");

out.close();

}

...

Figure 4: The AS/400’s Web server invokes the doGet function of the WebSQL Java servlet when a user clicks the Submit button of the HTML form.

Don Denoncourt is a freelance consultant. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

---

MC Press books written by Don Denoncourt available now on the MC Press Bookstore.

		Java Application Strategies for iSeries and AS/400 Explore the realities of using Java to develop real-world OS/400 applications. List Price $89.00 Now On Sale

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions