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: http://www.planetjavainc.com/wow63/runApp?id=350&_pj_lib=WOWSAMP60

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:

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.


This was first published in January 2005

Dig deeper on Web Development

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close