Creating an Searchable field in RPGIV?

you can always load the file into db2 and then just search the field using the like predicat
but i don't know what are your performance requirements so this solution is maybe simple, but not that good in terms of performance

Momi:
How would you suggest I load the subfile for display purposes then?  Would you read every record and compare to see if the search term was in the name field?  I start to get fuzzy when I think about it that way - currently the file is simply sequenced alphabetically (the logical file) and the subfile displays until EOF.  
Can you elaborate on your suggestion?
thanks!

wendelina,

Momi's option is an SQL one, will not work in RPG.

How many records do we talk about?
100, 1000, 10.000, 100.000, 1.000.000 ?

An option is to make the file user open and do a OPNQRYF before open the file.
the OPNQRYF will filter all records that are within the filter criteria.

The function is an equivalent of:   Select * from MyFile where Name Like '%smith%'
but OPNQRYF will not use SQL.

Build the  OPNQRYF in a CL-program and call this with the selection
the CL looks like this  see snippet:

(This works fine with a couple of thousend records)

Good Luck
Murph

PGM  PARM(&name)
 
DCL &NAME *CHAR 35
 
OPNQRYF    FILE(MyFIle) QRYSLT('Name *CT "'|< &NAME |< '"') +
    OPTION(*INP) KEYFLD((Name *ASCEND))
 
 
ENDPGM

Select allOpen in new window

Hi there,
Well maybe SQL isn't such a bad option after all.  Sorry I am making decisions based on fear of the unknown - I have the ability to open STRSQL and create recordsets that way, so, how much more difficult would it be to load a subfile with the results of a SQL statement than by positioning a logical file?  Sorry to "waffle" its just that we don't have any SQL programs on our 400 and I am rather hesitant at pioneering anything new, BUT, if it really makes sense...why not?  
Is there like a SQL400 for Dummies?  I mean, if I want to take my display file and RPG program and hack them up to use SQL instead, are we talking much difficulty?  
Thanks for the opinions and wisdom...

Murphy - I didn't answer your question.  There are about 180K records, with a few hundred being added every day.  
-Wendy

Nope no difficulties,

In RPG you need to create your statement and open the file,
then instead of your read you do a fetch
and when you are ready with reading, you close it.
That's all

I can post an example tomorrow.

Regards,
Murph

Hi wendelina,

Attached you find all components to create your SQLRPGLE program,
First change your program type from RPGLE to SQLRPGLE.

some smal explenation:
  DMYREC_DS       E DS                  EXTNAME(MYFILE)
This line will put all fields of file MYFILE in the DS, so that the SQL Fetch will act the same as a read.

  C     SQLCOD        DoWNe     100
This is the same as the EOF indicator or the %found function.

   C+ FETCH C1 INTO :MYREC_DS
This will fill the MYREC_DS with the next record.

Just use the PF file in your select, SQL will find the optimal index of logical

I'm sure someone will react to tell that testing SQLCOD <> 100 is totally wrong...
maybe he/she is right, but I use it that way in a lot of programs and it works fine.

good luck and have fun:)
Murphey
sqlrpgle.txt

oops

Sorry  I typed this in a text editor so no syntax checking

In the attached file,
     C                                 #MySelect + '%'''
     C                               ' ORDER BY MyName'

after %''' a +  is missing :)

Wendy,

I'd use SQL.  It is very handy for this sort of application.

Here's a very basic SQLRPGLE that executes a query and reads the results.  It is pretty easy to do a simple "read" loop SQL:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajp/rzajprpgi.htm

Lots of good info in the IBM Information Center (you didn't specify your version, so here's V5R3) :

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajp/rzajprpgi.htm)

Howard Arner's outstanding book is the thing to read first, though:

http://www.amazon.com/iSeries-AS-400-SQL-Work/dp/1583470247

You can do the same thing in traditional RPG by declaring the file USROPN, and then calling a CL or QCMDEXEC to do an an OPNRQYF, and then opening and closing the file between queries.  Or you can do a front-end search program that does an OPNQRYF and then just calls the display program that shares th open data path and reads the results of the query.

Performance can be an issue, especially if you have a lot of users banging away at it.  I've built similar search functions using an index file that is populated by triggers on the master file.  In high-volume environments, this may be preferable, since "CONTAINS" queries require a table scan (DB has to inspect every record to resolve the query).

Post back if you have any questions.

-Gary Patterson

This static SQL statement performs a "contains" search:
 
SELECT fld1, fld2, fld2
FROM table1
WHERE fld1 LIKE '%Tom%'
 
Matches "Tom Jones", "Jim Tomson", "Ralph O'Tomas".

Select allOpen in new window

Hi wendelina,

normal SQL is just a thing that you have to DO, type on a command line STRSQL and press enter,
On the sql screen you get you type SELECT and press F4.
as long as you dont use the delete or update, you can play around with it.

If you like to praktise at home with all other commands like crate table etc.
I advise you to install MySQL on your pc, you will learn a lot by "playing" with it.
for MYSQL . This is a nice site to begin with
http://php.about.com/od/troubleshooting/u/mysql_basics.htm
Most of the mySQL things are working also on AS/400.

Good luck and have fun,
Murphe

Hello,
Thank you for the great suggestions - I am going to pursue.  Hopefully I will have some feedback/progress/problems today as I start this.
Fortunately, I have a pretty good understanding of SQL - just a mental block when it comes to incorporating it into the RPG program(s) I have.  I always think of SQL as a PC function - so it is more fear/habit than anything else.  If I can get this going, the other 400 programmers can clone it for their applications so it could be a great thing.
Thanks again,
I'll be letting you know how it goes.  Note: I only work Mondays and Tuesdays so please don't think I've abandoned the issue...
Wendy

Gary the IT Pro:
You have me wondering more about your suggestion to
"You can do the same thing in traditional RPG by declaring the file USROPN, and then calling a CL or QCMDEXEC to do an an OPNRQYF, and then opening and closing the file between queries.  "
Does that mean that the main search program would call a CL that would accept as variables to the OPNQRYF statement the search term the user keys in?
thanks
Also a performance issue, potentially?  Is OPNQRYF really just creating a SQL search in the background?
TY.
~W.

Hi wendelina,

That was exact the first comment I did send you:

2 options are possible,
1. two program option
in the first program the search name is entered,
the progam calls a CL with an OPNQRYF command (to select the records)
the next program (subfile program) is started.

2. One program option
the file is defined as USROPN
in the first screen the search name is entered,
a call to a CL with the OPNQRYF (or QCMDEXEC) is done (CL is easier but the result is the same)
after returning from the CL RPG will open the file.

option 2 will require that the file will be closed after reading,
and a CLOF from the overridden file so the CL will look like this

             PGM        PARM(&MYSELECT)                                
                                                                       
             DCL        VAR(&MYSELECT) TYPE(*CHAR) LEN(30)             
             DCL        VAR(&SELECT) TYPE(*CHAR) LEN(50)               
                                                                       
             CHGVAR     VAR(&SELECT) VALUE('*' |< &MYSELECT |< '*')    
                                                                       
             CLOF       OPNID(MYFILE)                                  
             MONMSG     MSGID(CPF0000)                                 
             DLTOVR     FILE(MYFILE)                                   
             MONMSG     MSGID(CPF0000)                                 
                                                                       
             OVRDBF     FILE(MYFILE) SECURE(*YES) SHARE(*YES)          
             OPNQRYF    FILE((EDIOHD)) QRYSLT('charfld = %WLDCRD(''' + 
                          |< &SELECT |< '''')                          
                                                                       
             ENDPGM                                                    

Select allOpen in new window

Hi wendelina,

Forget to tell:  imbedded SQL is a lot faster then OPNQRYF.
as/400 SQL is case sensitive, so Smith can't be find by typing 'smith'
I don't think you can handle this with OPNQRYF

in SQL it will work like this
Try it on your production file and look how fast or slow it is.

SELECT * FROM MyLib/MyFile WHERE upper(Name) like upper('%smith%') order by Name

Just start SQL and try :) Select will NOT harm your tables.

Regards,
Murph

Hi wendelina,

I just run a little test on a file of 190.K records and found with the sample I just sent...
5 record foune out of 196.000 in 4 seconds.. accaptable I think.

Good Luck,
Murph

Hi Murph,
Thank you for the additional information.  Sounds like SQL is the way I am going to want to go for this one.  I am going to print out your example and review it.  I am hoping to get back to this on Monday the 28th.  
Wendy

wendelina:

Be aware of a particular snag with embedded SQL if you are searching a table column that is not variable length. From the V5R3 SQL Reference manual:

In your case, you aren't using a purely static statement -- you're filling in a value that's going to come from user-input. You need to be aware of how to insert that value into your SQL statement. You have a couple general techniques -- static or dynamic statements. The technique that you choose will make a difference in exactly how you handle the user-input value in your program.

Tom

Tom,

I see that the words are english, but I really dont know what you have entered there.
I didn't ever heard of an "parameter marker" in the past 25 it years. :)
Til now %WYSE% allways worked perfectly.

Regards,
Murph

Murph:

There is no error in the way that you do it. However, it does require a new construction and PREPARE of the entire statement whenever the desired search value changes. It's only really necessary to construct the statement once and PREPARE it once.

That is, the SQL statement can be PREPAREd once. When a new search value is entered, it's only necessary to OPEN the cursor again with OPEN cursorname USING :newsearchvalue.

The :newsearchvalue host variable is inserted into the previously PREPAREd statement at the point where the parameter marker ("?") was placed.

In that way the PREPAREd statement can be used over and over again without having the SQL parser and all that goes along with it being invoked.

Which way it's done might depend on how much CPU, etc., you want to use up in interactive queries. If you have sufficient resources, it's perfectly valid to execute the whole cycle every time. A lot of systems won't notice the difference.

On systems that do notice, though, it's well worth keeping parameter markers, etc., in mind.

My apologies. Sometimes I think too much in terms of needing to please every customer at once.

Tom

Hi Wendy,

If you like to know what a parameter marker is (like I do), please read this article :
http://www.dalprogrammers.com/?q=node/22

Regards,
Murph

Murph:

You can possibly guess why I might think of parameter markers immediately. Their use in the link you posted can be extremely valuable as a fundamental security element.

But once they're recognized in that kind of scenario, they start to become more obvious even others. In short, the PREPAREd statement has [ ' AND TVPERD = ?' ] where you have [ ' AND TVPERD = ' + #PER ] in the example that you uploaded. The "?" indicates to the SQL parser that a parameter marker is there.

When the statement is PREPAREd at run-time, the database supplies the definition for TVPERD. It knows if it's CHARACTER or NUMERIC, for example, and its size. As such, you don't even have to supply quote-marks for the string -- all you have to supply is the correct _value_ for the variable. The value is supplied when the cursor is OPENed by USING an appropriate host-variable, which might be defined LIKE(TVPERD).

The PREPARE is done one time and the cursor may be OPENed many times USING different values. This almost makes it like a static statement, with the parsing limited to that one initial PREPARE.

But the difference between variable- and fixed-length is important, as noted in the SQL Reference.

Once it's worked through in an example, it makes more sense. Your example should be almost trivial to change.

Tom

ooooooooooh oke, I know that..... SQL injection....?

>> ..... SQL injection....?

Makes an excellent question as well as an excellent google search [ SQL injection ].

Lots of good stuff on the first page.

Tom

Hello Murph,
Rather than leave this question open forever I am going to give you the points - even though I have not yet implemented your suggestion.  Based on your example, however, I have been able to get a prototype that at least uses the embedded sql to return results.  The rest will just be a matter of trial and error to get the program to accept the parameters from a search screen and load a subfile for display instead of a physical file.  Thank you,
Wendy

Hi Wendy,

Remember, you can allways use STRSQL to test the speed of the selection you  like to use in the program.
That will help you a lot.

Regard and good luck
Theo.

Thank you Theo.  I did not know that, in fact, there is MUCH I do not know about this subject.
Have a great day!
Wendy  

Hi Wendy,

Just an other extra on your search on name:
by replacing
SELECT * FROM MyLib/MyFile WHERE upper(Name) like upper('%smith%') order by Name
by
SELECT * FROM MyLib/MyFile WHERE SOUNDEX(Name) like SOUNDEX('smith') order by Name

The query will also fine Smith when you type smit or smithe :) but not Mr Smith :(

Regards,
Murph