To the CCA User Community
From the Editor We are so pleased that again an article written by Tym Stegner was developed using ideas and shared code from Ardie Schneider of Southwest Texas State University. This seems like the best type of article to present in CCAPRINT - an article that addresses the on-the-job needs of our user community. Just dash off a quick e-mail message telling us what you would like to share or what you would like to see discussed in print. Send your comments and ideas.
From the Editor
We are so pleased that again an article written by Tym Stegner was developed using ideas and shared code from Ardie Schneider of Southwest Texas State University. This seems like the best type of article to present in CCAPRINT - an article that addresses the on-the-job needs of our user community.
Just dash off a quick e-mail message telling us what you would like to share or what you would like to see discussed in print. Send your comments and ideas.
Model 204 Extending the Power: Highlighting JDBC, Part 2
By Richard Voss
In Extending the Power: Highlighting JDBC, Part 1, Mark LaRocca described how you can use the Connect* JDBC Driver for Model 204, which is part of the Connect* suite of products, in a stand-alone Java™ program. In Part 2, I discuss how you can use the JDBC Driver for Model 204 and Java Server Pages™ (JSP™) in a Web server application. Many of the pages you see when visiting Web sites are static -- the content was determined when the code for the page was written. Using JSP, the server can build a page dynamically: it writes the code for the page when it receives a request from a client's browser. For the application I developed, I used JSP in conjunction with the JDBC driver to build pages from the contents of Model 204 files.
Designing the Insight Orders Application
At the CCA Insight 204 symposium, we needed a simple Web application that allowed attendees to order additional software and documentation. We also wanted to provide a mechanism via which the attendees could express comments.
Creating the Tables
The Registration application described in Part 1 had already built the REGISTER table, which contains all conference attendees, so attendee names and addresses were available. I added three additional tables for the Insight Orders application:
Figure 1 lists the columns in each table.
Figure 1. The three additional tables in the Insight Orders application
REGI
PRODID
QTY
DESCRIP
The REGID columns shown in Figure 1 refer to the REGID column in the REGISTER table, which is not shown. The TS column in the COMMENTS table is a timestamp that keeps the comments (REMarks) in sequence.
Working with Java Server Pages
When I started this application, I did not know which products CCA would make available or which attendees had registered for the conference. So, the server has to determine what the PRODUCTS and REGISTER tables contain at runtime and build the HTML code dynamically. As with any Web application, the Insight Orders application could have been developed using any of several technologies. For simplicity, I used JSP.
I chose not to implement servlets, because JSP provides several implicit objects that make coding easier, and using JSP I could mix Java code and normal HTML. The Web application server translates JSP files into servlets, then compiles the servlets. Whenever you change a JSP file during development, the server detects this and translates and compiles the page the next time a browser requests it.
The Role of JSP, Java, and JavaScript
Whenever an attendee enters an order quantity, the Insight Orders application has to verify that a valid numeric string was entered. Verification could be done in the server code; this requires sending the numeric string to the server, validating it, then sending the page back to the attendee if there are errors. It is more efficient to validate entirely in the browser and reduce network time.
I handled the numeric validation with JavaScript™. JavaScript is not Java. And, unlike Java Server Pages, JavaScript executes on the client using services provided by the browser. As you might expect, each browser implements JavaScript a bit differently. This can be a real nuisance (and a good argument for writing as much of your application in Java as possible). I used only a small amount of JavaScript and kept it very simple.
Walking Through the Insight Orders Application
The Insight Orders application is comprised of four Web pages:
1. Capturing the Attendee's Symposium ID
The attendee opens the Insight Orders Main Page page by entering a URL in the browser's location bar in this format: hostname:port/orders
If you have installed the Jakarta-Tomcat servlet container on your PC, you would enter:
localhost:8080/orders
The default Web page is index.html or index.jsp, so I named the Insight Orders Main Page page index.jsp.
Figure 2. index.jsp displaying Insight Orders Main Page page
The most interesting lines of index.jsp code for the Insight Orders Main Page page are shown in Figure 3. The <% %> tags delimit JSP code; here it loads the JDBC Driver for Model 204.
Figure 3. Loading the JDBC Driver for Model 204
Class.forName("com.cca.j204.J204Driver");
%> <!doctype html public "-//W3C//DTD HTML 4.0//EN"> <html> . . . </html>
2. Capturing the Quantities and Comments of an Order
Insight 204 attendees were provided with badges printed with their registration ID in bar code format. The attendee used a bar code reader to swipe their badge to initiate their exchange with the order system. When the attendee clicks the Submit button, the index.jsp code sends the registration ID that has been read, to the order.jsp code. The order.jsp code scans a list of available products, looks for any previous orders or comments from this ID, and displays the current data on the Order Items page shown in Figure 4. For first time use, zeros are displayed in the quantities text boxes and the Comment box is blank. Figure 4 shows data entered or retrieved from the ORDERS table, awaiting attendee acceptance.
Figure 4. order.jsp displaying the Order Items page
Technical Highlights of order.jsp Code
The order.jsp code is going to pass the items and their most recent quantities to confirm.jsp, which in turn builds the Orders Confirmation page.
JDBC calls to the Model 204 server are encapsulated in several functions that use the standard java.sql classes to retrieve information from the application tables. As shown in Figure 5, for example, the displayItems() method creates two Statement objects: one gets the description of each product; the other determines how many of each product the attendee already ordered. For each product, it generates the HTML to display a product line on the page.
Figure 5. order.jsp code
void displayItems(String id) { Statement stmt1 = null, stmt2 = null; ResultSet rs1 = null, rs2 = null; boolean prevOrder = false; try { stmt1 = con.createStatement(); stmt2 = con.createStatement(); // Build a line for each product rs1 = stmt1.executeQuery( "SELECT PRODID, DESCRIP FROM REG.PRODUCTS"); while (rs1.next()) { String prodid = rs1.getString(1); String desc = rs1.getString(2); String qty = "0"; rs2 = stmt2.executeQuery("SELECT QTY FROM REG.ORDERS " +"WHERE REGID = '" +id +"'" +"AND PRODID = '" +prodid +"'"); if (rs2.next()) { qty = rs2.getString(1); prevOrder = true; } buf.append("<input type=text name=" +prodid +" value=" +qty +" maxlength=2 size=2 onFocus='statInt()' " +"onBlur='clearStat()' " +"onkeyup=\"javascript:if(!checkVal(this.value)) {" +"this.value='0'; } \" " +"> \n" +"<label for=" +prodid +">" +desc +"</label><br>\n"); } } catch (SQLException e) { disconnect(); System.out.println("Error executing a Statement: " +e.getMessage()); buf.append("<br> Product list is not available<br>"); return; }
// Tell the confirmation page whether this customer had a previous // order on file. buf.append("<input type=hidden name=prevOrder value=" +prevOrder +">"); }
In the order.jsp code, the checkVal() JavaScript function returns false if the string contains anything other than a valid integer.
The prevOrder variable indicates whether a previous order did exist in the ORDERS table. It is included in the form as a hidden field so it can be passed to the next page without being displayed on the current page.
The displayItems() method builds the HTML code in a StringBuffer. At the end of the program, the buffer is written to the output stream using the out object, which is one of the implicit objects JSP automatically provides.
Figure 6. The out object in action
out.println(buf.toString());
3. Building the SQL statements for the Order
The Order Confirmation page has two tasks:
Figure 7. confirm.jsp displaying the Order Confirmation page
Technical Highlights of the confirm.jsp Code
The Order Confirmation page gets the attendee's input from the Order Items page as a set of request parameters in the form product_id=quantity, and it uses the values to construct SQL update statements.
The Order Confirmation page does not update the database. Instead it passes the SQL statements to the Done page. The Done page then executes the SQL statements if the attendee confirms the order by clicking the OK button.
Figure 8 is a segment of code that handles the product order.
Figure 8. confirm.jsp code
String selProdStmt = "SELECT PRODID, DESCRIP FROM REG.PRODUCTS"; String selRegStmt = "SELECT TITLE, FNAME, LNAME, ADDR1, ADDR2, ADDR3, " +"CITY, STATE, ZIP FROM REG.REGISTER " +"WHERE REGID = '%'"; String inStr = "INSERT INTO REG.ORDERS(QTY, REGID, PRODID) " +"VALUES(%,'%','%')"; String upStr = "UPDATE REG.ORDERS SET QTY=% " +"WHERE REGID='%' AND PRODID='%'"; String delStr = "DELETE FROM REG.ORDERS " +"WHERE REGID='%'"; String comStr = "INSERT INTO REG.COMMENTS(REGID,TS, REM) " +"VALUES('%','%','%')"; StringTokenizer stmtST; String stmtStr, tempStr; StringBuffer itemBuf; Vector stmtList = new Vector(20); ResultSet rs, rsProd; String prodid, desc; String regid = request.getParameter("regid"); String name = request.getParameter("name"); int qty; boolean prevOrder;
// Connect to Model 204 Connection con = connect(); rsProd = executeSelect(selProdStmt); itemBuf = new StringBuffer(); // product item list prevOrder = Boolean.valueOf( (String)request.getParameter("prevOrder")).booleanValue(); while (rsProd.next()) { // for each product prodid = rsProd.getString(1); desc = rsProd.getString(2); tempStr = (String)request.getParameter(prodid); if (tempStr == null || tempStr.length() == 0) { qty = 0; } else { qty = Integer.parseInt(tempStr); }
// Construct the ORDERS update statement: INSERT if a record // exists,otherwise UPDATE. if (prevOrder) { // row already exists in ORDERS for this customer stmtST = new StringTokenizer(upStr, "%"); } else { // row doesn't exist stmtST = new StringTokenizer(inStr, "%"); } stmtStr = stmtST.nextToken() +qty +stmtST.nextToken() +regid +stmtST.nextToken() +prodid +stmtST.nextToken(); stmtList.add(stmtStr); // add this update statement to the list // Create the html to display the number of items ordered if (qty > 0) { itemBuf.append("<tr><td>" +desc +"</td>\n<td align=right>" +qty +"</td></tr>\n"); } } // while
The Vector class is used to maintain a list of the database update requests as they are created.
Also, note in the SQL request strings the use of ' which is an HTML entity for a single quotation character. Had I used an actual quotation mark ('), the string would be truncated at the first quotation mark when it was sent as a parameter to the Order Confirmation page.
I used request, another implicit object that JSP provides, to get the parameters passed from the order.jsp code. It allows me to easily obtain whatever information I need from the HTTP request.
The executeSelect() method called in the code in Figure 8 executes an SQL statement. It returns a ResultSet object that obtains the table column data:
Figure 9. Table column data results
ResultSet executeSelect(String stmtStr) { Statement stmt; try { stmt = con.createStatement(); return stmt.executeQuery(stmtStr); } catch (SQLException e) { disconnect(); System.out.println("Unable to execute a statement: " +e.getMessage()); return null; } }
After building the HTML that displays each product and the quantity ordered, it is written to the output stream in a table. The code sends the table update statements to the Done page, again using hidden form input fields. And as before, after all HTML is built in a buffer, it is written to the output stream.
4. Updating the Tables and Confirming the Request
The Done page executes the update requests received from the Order Confirmation page and lets the attendee know whether the updates were successful.
Figure 10. orderHandler.jsp displays the Done page
If the attendee does not click the Return button within 10 seconds, the Insight Orders Main Page page is automatically displayed. Figure 11 shows how the HTML in orderHandler.jsp recycles the Insight Orders application.
Figure 11. Back to the beginning of the Insight Orders application
<!doctype html public "-//W3C//DTD HTML 4.0//EN"> <html> <head> . . . <meta http-equiv="refresh" content="10; url=index.jsp"> </head> <body> . . . </body> </html>
Writing Your Own Application
This was my first application using HTML, JSP, and JavaScript. After this brief discussion of a simple JDBC application, I hope you might want to write your own application. Check out the following resources for additional information:
Get Your Copy of JDBC Driver for Model 204
The JDBC Driver for Model 204 is supplied with and certified for Model 204 Version 5.1. It is on the distribution CD for the Connect* suite of products.
Free Source Code
All the code for the Insight Orders application is on the Demonstration Source Code CD CCA made available at Insight 204 this past September. If you would like a copy, CCA Customer Support is happy to send you one. Send an e-mail request.
Read a Good Book
If you are not familiar with Java and HTTP, one of the many good books on writing applications can teach you how.
Pick a Web Application Server
You need a Web server that includes an application server. I used the Apache server and Tomcat servlet container, which I downloaded along with a complete Java Web Services Developer Pack from:
http://java.sun.com/webservices/webservicespack.html
If you are new to developing Web applications, you may also want to download the excellent tutorial available at that site.
System 1032 Have You Looked at Your Error Log Lately?
By Tym Stegner
Revisiting S1032_ERR
The System 1032 error log is a sequential file located by the S1032_ERR logical name, containing error and abort events signaled by System 1032. All fatal errors and significant E-level errors, as well as user aborts, are recorded in this file by the System 1032 condition and exit handlers. Process, versions, and node specific information is included to identify the conditions of the error events.
For a full description of the contents of the System 1032 error log, see the article "Using the Error Log", CCAPRINT, October 1998.
This file is intended to assist System 1032 Customer Support in identifying and evaluating fatal System 1032 errors. However, you can also use it to track and identify application and user problems at your site.
Correcting Code Inspired by Error Log Review
Southwest Texas State University developed a scanner for reviewing their error log. They use this scanner to notify the people, who are responsible for managing System 1032 applications, about potential problems relating to their applications. The scanner runs in a nightly batch job, and automatically sends its results via an e-mail distribution list to those whose applications triggered results the previous day.
Figure A details the scanner code, ESCAN.COM.
Figure A. Southwest Texas State University Error Log Scanner COM file
$ ESCAN.COM - generic version of SWT University's Error Log Scanner $ set noon $ escan_verify=f$verify(0) $! $ define/nolog escan_exec dua0:[exec] $ define/nolog escan_output dua0:[output] $! $ submit/after="tomorrow:+00:01"- /queue=sys$batch - /print=sys$print - /keep- /log=escan_output escan_exec:escan.aut $! $ if "''p1'" .eqs. "SUBMIT" then $ exit $! $ search/window=(1,7) S1032_ERR "Report of","Error"- /exact- /match=and- /output=sys$scratch:escan.tmp $! $ escan_yesterday = f$cvtime("''f$time()'-1-00","ABSOLUTE") $ escan_yesterday = f$extract(0,11,escan_yesterday) $! $ search/window=(2,6) sys$scratch:escan.tmp "''escan_yesterday'"- /output=sys$scratch:escan.lis $! $ delete/noconfirm/log sys$scratch:escan.tmp;* $! $ if f$file_attributes("sys$scratch:escan.lis","eof") .gt. 0 $ then $ mail/noedit/nopersonal_name/noself- /subject="System 1032 Errors From ''escan_yesterday'" -
sys$scratch:escan.lis "@escan_exec:escan.dis" $ else $ mail/noedit/nopersonal_name/noself- /subject="System 1032 Errors From ''escan_yesterday'" - sys$scratch:escan.lis "@escan_exec:escanmnt.dis" $ endif $! $ delete/noconfirm/log sys$scratch:escan.lis;* $ exit
How ESCAN.COM Works
The Southwest Texas State University scanner begins by resubmitting a wrapper file, whose sole purpose is to execute this COM file. This action enables the staff to make changes to the COM file without affecting the batch job.
Logical names are defined, and an initial SEARCH command pass is made to extract the entry headers from the error log file. Yesterday's date is calculated and subsequently used to select event headers from only the previous day.
When results are found, they are sent by e-mail message to an audience responsible for managing System 1032 applications, as well as the scanner's Maintainer. If no results are found, only the scanner's Maintainer is notified.
Recently a recipient on this notification list decided to get his application off the daily notification by changing a termination condition in his application, which resulted in the elimination of the error report. Here, we have an example of code improvement undertaken by a desire to escape peer review.
Note: The Southwest Texas State University ESCAN.COM by design excludes reports of Control/Y aborts. For a program that includes them, see the discussion of SCANLOG.COM that follows in this article.
Discovering Hidden Errors
At another site, an ODBC-enabled Web page often reported a 'disconnected' error, and prompted the user to re-register. Examination and analysis of the Web server and ODBC error files did not disclose any error that explained the problem. Then the System 1032 error log was reviewed where it was found that about 500 entries per day of an apparent activation error contributed to the 'disconnected' error.
This error showed up in only the System 1032 error log. Subsequent investigation of the problem lead to the application of an OpenVMS patch (V721_LIBRTL-0400), which eliminated the 500-plus per-day error log entries.
Reviewing S1032_ERR for a Living
System 1032 Customer Support has long used a tool, SCANLOG.COM, for reviewing error logs. (As you might expect, over the years we have examined quite a few error logs.) In Figure B, the qualifiers variable is going to carry a SEARCH command qualifier.
Figure B. System 1032 Customer Support scanner
$! SCANLOG - brief report of System 1032 error log file $! $ if P1 .eqs. "" then P1 = "S1032_ERR" $! $ qualifiers = "" $ if P2 .eqs. "" $ then $ vmsv = f$getsyi("version") !get OpenVMS version $ vmsv = f$extract(1,1,vmsv) !extract base version number $ vmsv = f$int(vmsv) $ if vmsv .ge. 6 then qualifiers = "/page=scroll" $ else $ qualifiers = P2 $ endif $! $ Search'qualifiers'/nohighlight 'P1' - "S1032 Version",- "User:",- "Proc name:",- "Node Name:",- "-F-","-E-","-W-",- "* * INTERN",- "PC ",- "Back End Execution Routine",- "DM_Begin",- "E N D " $! $ exit
How SCANLOG.COM Works
The purpose of SCANLOG.COM is to summarize the entire contents of a System 1032 error log file. A non-default error log file can be specified in P1. If no parameters are provided, the default S1032_ERR file is examined. If the version of OpenVMS is greater than V6, paging on the output is enabled. (To suppress pagination, specify "/NOPAGE" as P2.)
As with ESCAN.COM, the core of the scanner is a SEARCH command. This procedure is looking at support-specific details of the error log entry, including the entry end marker. Should we desire to scan for other significant data, it is easy to add different search terms to the command.
Keeping Maintenance Current
Back in the days when more of System 1032 usage was via interactive users, either the direct command line interface or forms-based applications, the System 1032 Maintainer at a site rarely had to worry about not finding out about significant errors. "Hello, Help Desk? My application just..."
However, these days more System 1032 processing is behind-the-scenes, either via encapsulated applications or ODBC. You as the Maintainer may not have the immediate notification you previously enjoyed.
CCA recommends that to keep your System 1032 applications running well, as well as keeping your datasets from damage, you need to keep yourself informed of what, if any, fatal errors are happening with applications at your site. As always, fatal errors should be reported to CCA System1032 Customer Support by:
Note: A side benefit of reviewing the System 1032 error log is that you can see directly who is in the habit of using Control/Y to get out of those applications.
CCA staff would like to thank Ardie Schneider at Southwest Texas State University for sharing his ESCAN.COM code and related story with us, which inspired this article. -- The Editor of CCAPRINT.
Copyright © 2008 Computer Corporation of America. All right reserved. Published in the United States of America.
Contact CCA Webmaster Copyright 2008
