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
When you load the Web interface, the first thing you'll see is something like Figure 1:
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 )
C/End-Exec
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
One last thing to point out is the link to MapQuest. The program code that builds the link in FORM012A looks like this:
link = '<a href="javascript:PopWin('
+ q
+ 'http://www.mapquest.com/maps/'
+ 'map.adp?address=&city='
+ %trim(city)
+ '&state='
+ %trim(state)
+ '&zipcode='
+ '&country='
+ %trim(country)
+ '&cid=lfmaplink'
+ q
+ ',300,300);"'
+ ' '
+ 'title="'
+ 'Latitude:'
+ %trim(PNLAT)
+ '/'
+ 'Longitude:'
+ %trim(PNLONG)
+ '"'
+ '>'
+ 'Map';
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.
To display the map in a pop-up, I use a small JavaScript function to display the map in full screen window:
<script language="JavaScript" data-mce-type="text/javascript">
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.
Try It
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.
Jan Jorgensen is a programmer at Electrolux Laundry Systems Denmark. He works with RPG, HTML, JavaScript, and Perl and is trying hard to learn C#.
You can reach him at
More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.
TRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now! Request Now.
Forms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.
Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:
LATEST COMMENTS
MC Press Online