Problem solve Get help with specific problems with your technologies, process and projects.

Web application searches: Powerful SQL tips

Paul Holm shares samples of what you can do with the WOW utility and answers a few frequently asked Web development questions.

Paul Holm

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.

More Information

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:

This application shows iSeries data with the following business functions:

  1. List of Open Orders
  2. List of Closed Orders
  3. Employee Phone List
  4. Customer List
  5. 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:


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:

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:


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:

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:,289483,sid3_gci965490,00.html

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

Dig Deeper on Web Development

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.