Home > AS/400 Tips > WebSphere Strategies for iSeries professionals > Web application searches: Powerful SQL tips

	iSeries 400 Tips:	EMAIL THIS

	TIPS & NEWSLETTERS TOPICS

	WEBSPHERE STRATEGIES FOR ISERIES PROFESSIONALS Web application searches: Powerful SQL tips Paul Holm 01.20.2005 Rating: -4.50- (out of 5) Digg This!     StumbleUpon     Del.icio.us    [TABLE] 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. [TABLE]			[IMAGE]

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:

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:

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Web Development IBM i shop boosts online sales with RPG-based Web platform Migrating from RPG to EGL on IBM i Groovy programming on IBM i Running PHP open source applications: NOBODY needs authority Zend Web software teams up with IBM System i The best technologies and tools for System i programmers in 2009 Seven IBM i project lessons learned in 2008 AS/400 lessons from the past, present, and future: A holiday tale Application modernization strategies for System i RPG application modernization for i5 Web Tools Programming for the Web on the IBM i, what is possible Zend Web software teams up with IBM System i Using geocoding on AS/400 to enhance your Web presence The iSeries Blog has a new home on IT Knowledge Exchange The best technologies and tools for System i programmers in 2009 Search400.com Products of the Year 2008 Application modernization strategies for System i Natively supported Web applications for Power running i System i PHP authoring tool tries to fill void in Web-coding know-how Lazy coder: What does PHP on the i5 mean? iSeries application development tools Migrating from RPG to EGL on IBM i EGL Rich UI on IBM i: Do you Dojo? Running PHP open source applications: NOBODY needs authority Zend Web software teams up with IBM System i The best technologies and tools for System i programmers in 2009 AS/400 lessons from the past, present, and future: A holiday tale Documenting nested program structures on the AS/400 Learn the i: iSeries DevCon coming up in Orlando Application modernization in the i world System i virtualization prevents T-Mobile busy signal iSeries application development tools Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary WebSphere Development Studio Client (WDSC)  (Search400.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

mso-font-pitch:variable; mso-font-signature:50331648 0 0 0 1 0;} @font-face {font-family:"Trebuchet MS"; panose-1:0 2 11 6 3 2 2 2 2 2; mso-font-charset:0; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:50331648 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Trebuchet MS";} pre {margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Courier New"; color:black;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

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

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.

Rate this Tip To rate tips, you must be a member of Search400.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.