02
Sat, Nov
2 New Articles

Free and Easy with WebSQL

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


Free_and_Easy_with_WebSQL03-00.png 400x195

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.

Free_and_Easy_with_WebSQL03-01.png 400x236

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

WebSQL

WebSQL: Interactive SQL for the Internet




SQL Statement:



import java.io.*; import java.sql.*; import java.beans.*;
import javax.servlet.*; import javax.servlet.http.*;
import com.ibm.as400.util.html.*;
import com.ibm.as400.util.servlet.*;

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

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 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

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: