I recently stumbled across something I discovered in the past and almost forgot about. That's how I re-discovered the Soundex algorithm. After reading some trivial examples, I decided to create a truly useful application, and that's how this TechTip came alive.
Before I go into detail about the tip, let me briefly explain what Soundex is. Soundex is a phonetic algorithm that was developed nearly a century ago! The Soundex code consists of one letter followed by three numbers. Each letter in a string of characters has a different "weight," and by using five simple rules, the Soundex algorithm can balance different-sounding words into a Soundex code, which then can be used for searches. Wikipedia explains Soundex brilliantly.
OK, back to the tip. As always, I'll give you a short overview of what you can achieve by reading on:
- An RPG CGI program will let you search a U.S. cities database using the SQL Soundex code (or the SQL Like code).
- Another RPG CGI program will show you all the cities and their ZIP codes found and allow you to click a link to see the city on a map stored at the MapQuest Web site. (I have no financial interest in MapQuest; the only reason I used that Web site was that it offered a free and easy-to-understand interface.)
So let's jump into it.
If you have read some of my previous TechTips, you should already be familiar with the way I write the RPG CGI programs. So, to save space and avoid repeating myself, I will not spend much time on that. If you are a "first-timer," please read some of my other tips or just download the code, fire up the debugger, and see it for yourself.
Let's start with the databases. This tip uses two databases: WWWCITIES (18950 records) and WWWUSZIP (42741 records).
WWWCITIES contains two fields: CITY and SDXCITY, which is also the keyfield.
SDXCITY is the Soundex code based on the content of CITY. I have done it this way to make it easy to see the Soundex code, and I also believe it actually speeds up the SQL SELECT. But I could just as well have used the Soundex scalar function to retrieve the Soundex code on the fly.
To create a Soundex code based on a field, execute the following SQL statement against the database (remember to define a four-byte alfa field to store the Soundex code in).
If you download the database, you'll find that I've already done this, but I thought you might like to know how I created the Soundex code.
The Web interface
Figure 1: This is the main search interface. (Click images to enlarge.)
You can enter something in the City search field and then select how you want to search.
If you enter "Danmark" (which is how we spell it here in DK) and select the LIKE search, nothing will be displayed, but if you select the SOUNDEX search, the WWWCITIES database will find five entries:
Figure 2: A Soundex search on "Danmark" returns these results.
I also show the Soundex code for both the search argument and the entries found.
You will of course remove this in a real working application, but it is very useful here to give you an idea of how the Soundex algorithm works.
When you click on the "Show all" link for Denmark, the following will be shown:
Figure 3: All cities with the name of Denmark are found in WWWCITIES.
You can now click on a map link to see where the city is located. If you click the DENMARK IA link, a new window will pop up and the following will be shown:
Figure 4: You now have a map for Denmark, Iowa.
Notice that I do not pass the ZIP code to the MapQuest interface. The reason is that, if I do, the red star won't be placed correctly for some reason.
What Happens Behind the Scenes?
The interesting part of the RPG CGI program FORM012 happens in the SQL statements.
FORM012 has two subroutines where the SQL SELECT occurs: SubrSoundex and SubrLike.
Figure 5 shows the SQL code in action:
Figure 5: Here's the SQL SELECT using Soundex.
First, I use the Soundex scalar in a SET statement:
C+ Set :SearchStringSoundex = Soundex( :zSearchString )
This will return the four-byte Soundex code in the field SearchStringSoundex.
Then, I execute an SQL SELECT:
Notice the "fetch first 20 rows only." This is the same as the MS Access "Top XX." In other SQL flavors, it's the same as the LIMIT statement.
The result of the SELECT is that a maximum of 20 entries will be displayed in the list.
Every time I get a hit from the SELECT statement, I write it out to the browser, and at the same time, I build a query string link to RPG CGI program FORM012A, which is used to display all the cities and their ZIP codes found in file WWWUSZIP.
The Link to MapQuest
+ ' '
How did I know what the query string to MapQuest should look like? A lot of Web sites offer this kind of service for free, so if you don't like the interface of MapQuest, find your own favourite and look around to see if you can find some sample links you can use. Some sites offer a wide range of services if you want to pay for them, so it's just a matter of finding one you like.
Notice that fullscreen=yes is the keyword that does the whole trick.
Downloading and Installing
- Create a directory in your root dir called /root/mcpressonline by entering this command from a command line: md '/rootdir/mcpressonline/'
- Create a directory called likesoundex inside mcpressonline. Enter the following: md '/rootdir/mcpressonline/likesoundex'
- Download likesoundex.zip, unzip it, and upload everything to '/web/mcpressonline/likesoundex'. It should look like this:
- Compile WWWCITIES and WWWUSZIP or restore savefile wwwsoundex.zip to your i5.
- Save copy book for BUFIO_H to your i5.
- Download and save the source to FORM012 and FORM012A on your i5.
- If you do not already have it, download and compile CGIPARSEZ.
Before compiling, remember to change "your-data-lib" in the RPG sources and SQL SELECT statements to where you placed the data files. Also, change "your-root-dir" in constant IFSpath to your actual root directory.
When you have installed everything and compiled the RPG programs (compile instructions can be found in the header description), you are ready to test the Soundex search.
Load the search by entering http://your-server-name/cgi-bin/form012frame.htm.
I know Soundex is not new on the i5, but the examples I found in the IBM manuals were really poor, so I hope you can use this tip. Try building it into, for example, a customer or product file.