In a recent tip, I showed how to use a free WebSphere/Web utility called WOW to create Web applications using iSeries data. Hundreds downloaded the utility, and I was quickly overrun with questions such as, "Can I access my JDE files?" "Would it be feasible to build an order-monitoring application?" Can I do this and can I do that?
It was great to get those questions because it shows the iSeries development environment is starting to get more into the Web development world.
To help answer those questions, I'll share my samples of what you can do with the WOW utility and also answer a few additional frequently asked Web development questions.
Tip A: What can I do with WOW entry?
As previously stated, WOW entry is a free Web development Rapid Application Development (RAD) tool that allows you to develop iSeries Web applications without having to write Java, HTML, Servlets and JSPs. The utility can be used to create any type of data centric Web application. Here are some real-world examples: http://www.planetjavainc.com/wow63/runApp?id=350&_pj_lib=WOWSAMP60
This application shows iSeries data with the following business functions:
- List of Open Orders
- List of Closed Orders
- Employee Phone List
- Customer List
- Current Products
You can do anything as long as your data is in a relational format. WOW does all the hard work for you. This application was created in 15 minutes.
Tip B: Ports
Security is at the top of everyone's mind. When accessing the iSeries with WebSphere and Java, the server must have certain ports open to allow JDBC/SQL and other forms of access. This table shows the most commonly used ports:
PC Function Server Name Port Non-SSL Port SSL
Server Mapper as-svrmap 449 449
Database Access as-database 8471 9471
Data Queues as-dtaq 8472 9472
Remote Command as-rmtcmd 8475 9475
Signon Verification as-signon 8476
9476
I use a freeware utility called Scan Port, which can scan a URL and check what ports are open.
To use it, download the utility and enter your system or Web site address into the System field, then enter a range of ports to scan. The Open radio button will show the ports open at that address specified. It works pretty slick.
Tip C: SQL searches that aren't case-sensitive
SQL/JDBC are the dominant ways to access your database files. Often users want to search for text without considering upper or lower case characters. The following SQL is an example of a Web search that is not case-sensitive:
SELECT * FROM QIWS.QCUSTCDT WHERE UPPER(city) = UPPER(CAST ( ? AS CHAR(6)))
The key items here are the use of the "UPPER" SQL clause, which forces both the field's value and the user's entered value to uppercase. The "CAST" clause is required by SQL to indicate the value entered will be a character field. The "?" is for the prepared statement and will be set after the user enters a search value for city.
You can try it out here: http://www.planetjavainc.com/wow63/runApp?lvid=1462&_pj_lib=wowsamp60
From the example, enter 'dallas' for the city, then click search. Next enter 'DALLAS' and not the behavior.
Tip D: Optional SQL searches
Building on the previous example, let's say you want show all customers if nothing is entered for the city. The following SQL is an example of a Web search that is optional:
SELECT cusnum, lstnam, city FROM QIWS.QCUSTCDT WHERE UPPER(city) = VALUE (UPPER(TRIM(CAST ( ? AS CHAR(6)))), UPPER(CITY))
The key items here are the use of the "VALUE" SQL clause, which takes the first non-null value and uses it in the selection criteria.
You can try it out here: http://www.planetjavainc.com/wow63/runApp?lvid=1463&_pj_lib=wowsamp60
From the example, enter nothing for the city, then click search. All records will be returned. Next enter 'DALLAS', and only customers in Dallas will be returned. For further details on using SQL Value, check out this previous tip: http://search400.techtarget.com/tip/1,289483,sid3_gci965490,00.html
Summary
There are tons of issues we face daily as Web developers. As a Web consultant for iSeries shops, I frequently get asked these. I hope this helps out at least a few of you. Carry on! :^)
---------------------------
About the author: Paul Holm is a former IBM-Rochester WebSphere, Java, and DB2 developer/consultant. He specializes in helping iSeries RPG shops develop Web-based applications. Paul can be reached at pholm@planetjavainc.com.
