Whether your applications are completely RPG/SQL-based or include processes written in languages such as Java or ASP.NET, dynamic searches are likely a key component.

Are you using SQL within your applications? Whether you're developing applications in RPG IV, Java, .NET, or other languages, performing a variety of searches is often necessary. SQL is commonly used to perform such searches. It provides great flexibility and allows decisions about sorting and selecting to be determined at run time.

In this article, we'll focus on using SQL embedded within RPG IV to perform the search. We'll start with the syntax for handling the basic request, then move on to some of the more advanced considerations, and wrap up with instructions on how to convert that RPGIV program code into a stored procedure callable from SQL.

Basic Syntax

When you're coding SQL within RPG IV programs, you have the option of creating either static or dynamic statements. Static, or "hard-coded," statements may perform slightly faster than dynamic statements, but the added flexibility offered by dynamic statements more than offsets the difference in performance.

When you're creating dynamic SQL statements in RPG IV, the basic process is to assemble an entire SQL statement inside a variable and then execute the statement coded in that variable. This can be through an EXECUTE command for statements that don't return results or by processing a CURSOR associated with the statement.

For example, to create a simple program that performs a search, we could write a program like this:

d   stmt          s            500a   varying

d   name          s             35a

 /free

   Stmt = 'SELECT CNAME FROM CUSTMAST WHERE CMZIP = ''12345''';

   EXEC SQL prepare s1 from :stmt;

   EXEC SQL declare c1 Cursor for s1;

   EXEC SQL open c1;

   EXEC SQL fetch c1 into :name;

   DOW sqlstate = '00000';

       DSPLY name;

       EXEC SQL fetch c1 into :name;

   ENDDO;

   *inlr = *on;

 /end-free

In the previous example, we loaded the statement into a character string and then prepared an SQL statement from that. The PREPARE statement essentially compiles the SQL code in the given host variable into an executable SQL statement. From that point on in the program, the statement named "s1" identifies the executable SQL code.

Declaring the cursor analyzes the SELECT statement, looks at the values returned, and prepares buffers and controls for managing the result of the SELECT statement. We open the cursor to execute the statement.

The Fetch command reads the next record in the cursor (result set) and loads the given host variables with the columns selected. In this example, we selected only one column, so only one host variable is needed. If we select more columns, the Fetch command must change. The following example shows the changes needed to fetch three columns.

d   stmt          s            500a   varying

d   name          s             35a

d   addr1         s             35a

d   city          s             35a

...

   Stmt = 'SELECT CNAME,CADDR1,CCITY FROM CUSTMAST

           WHERE CMZIP = ''12345''';

...

   EXEC SQL fetch c1 into :name,:addr1,:city;

We must define more host variables, modify the SELECT statement to include three columns, and change the Fetch statements to load three host variables. If we wanted to select all the columns in the table, we could use an externally described data structure and SELECT * to handle that, as shown in the next example.

d   stmt          s            500a   varying

d   data        E ds                  extname(CUSTMAST)

...

   Stmt = 'SELECT * FROM CUSTMAST WHERE CMZIP = ''12345''';

...

   EXEC SQL fetch c1 into :data;

When we use this technique, the externally described data structure (data) automatically contains subfields that correspond to the columns in the table it's modeled on. So data has the subfields cname, caddr1, ccity, and cmzip without needing to declare them.

For performance reasons, using SELECT * is discouraged, but for small tables, the extra convenience may outweigh the minute performance difference.

Incorporating Parameters

One of the key ingredients in dynamic SQL is incorporating user input into the structure of the SQL statement. For example, we might modify this example program to receive five parameters: customer number, address, city, state, and ZIP code. Any combination of them--or none of them--can be populated with search criteria. The following example shows how to accomplish that:

d   stmt          s            500a   varying

d   where         s            500a   varying

d   first         s               n   inz(*ON)

d   name          s             35a

C      *Entry      Plist
C                  Parm                CMNbr      5 0
C                  Parm                Caddr1    35

C                  Parm                Ccity     35

C                  Parm                Cstate     2

C                  Parm                CMZip     10

 /free

   If CMNbr > 0;

      If first;

        where = 'Where ';

          first = *off;

      Else;

        where = where + ' AND ';

      Endif;

      where = where + 'CMNbr = ' + %EDITC(CMNbr:'Z');

   Endif

   If Caddr1 > ' ';

      If first;

        where = 'Where ';

          first = *off;

      Else;

        where = where + ' AND ';

      Endif;

      where = where + 'Caddr1 = ''' + caddr1 + '''';

   Endif

   If Ccity > ' ';

      If first;

        where = 'Where ';

          first = *off;

      Else;

        where = where + ' AND ';

      Endif;

      where = where + 'Ccity = ''' + ccity + '''';

   Endif

   If Cstate > ' ';

      If first;

        where = 'Where ';

          first = *off;

      Else;

        where = where + ' AND ';

      Endif;

      where = where + 'Cstate = ''' + Cstate + '''';

   Endif

   If CMZip > ' ';

      If first;

        where = 'Where ';

          first = *off;

      Else;

        where = where + ' AND ';

      Endif;

      where = where + 'CMZip = ''' + CMZip + '''';

   Endif

   Stmt = 'SELECT CNAME FROM CUSTMAST ' + where;

   EXEC SQL prepare s1 from :stmt;

   EXEC SQL declare c1 Cursor for s1;

   EXEC SQL open c1;

   EXEC SQL fetch c1 into :name;

   DOW sqlstate = '00000';

       DSPLY name;

       EXEC SQL fetch c1 into :name;

   ENDDO;

   *inlr = *on;

 /end-free

Notice in the previous example that the variable "where" is built up in a series of statements that potentially create multiple tests, all of which must be true to select a record. Replace the AND with OR to select records that meet any one of the conditions.

Handling Quotes

Notice that when testing character fields such as Caddr1, Ccity, Cstate, and CMZip, the values you're comparing to must be wrapped in quotes. To accomplish this, we use a pair of quotes. Two quotes ('') in the code produces one quote (') in the character string. Some programmers find that managing all the quotes in the character strings is awkward. Many programmers prefer an alternative that uses a constant to hold a quote. The following example shows how to use that alternative.

d   stmt          s            500a   varying

d   where         s            500a   varying

d   first         s               n   inz(*ON)

d   name          s             35a

d   quote         c                   ''''

...

   If Caddr1 > ' ';

      If first;

        where = 'Where ';

          first = *off;

      Else;

        where = where + ' AND ';

      Endif;

      where = where + 'Caddr1 = ' + quote + caddr1 + quote;

   Endif

...

SQL Injection

Depending on where the input fields are coming from, you may need to be concerned about SQL injection. This is a fairly common form of security attack experienced by many Web sites. These attacks consist of unexpected data included in the comparison fields. For example, if the address field being passed in as a parameter contains "abc' or 'a' = 'a", then the code shown above would create a where clause of "Where Caddr1 = 'abc' or 'a' = 'a'".

You can see that every record will satisfy the test, and it's possible--depending on how your statement is constructed--that users could gain access to data that they shouldn't.

To prevent this, you may need to "scrub" the data in the comparison fields. The simplest method for this is to replace quotes in the strings. The following example shows one method for doing that.

        where = where + 'REPLACE(Caddr1,' + quote + quote + quote + 

          ',' + quote + '*' + quote + ') = REPLACE(' + quote + caddr1 +

          quote + ',' + quote + quote + quote + ',' +

          '*' + quote + ')';

This code creates this where clause: "Where REPLACE(Caddr1,''','*') = REPLACE('abc' or 'a' = 'a',''','*')". When the REPLACE functions are resolved, this becomes "Where Caddr1 = 'abc* or *a* = *a'". Because the OR is enclosed within the quotes, it is not a new condition but part of the text. So no record is likely to be returned.

Error Handling

In the previous examples, we tested the value of SQLSTATE (aka SQLSTT) to determine whether the previous SQL statement encountered an error. If SQLSTATE is loaded with zeros, then the statement executed successfully; otherwise, it will be loaded with a standard SQL error code.

It is important to verify that your SQL statements execute correctly. Unlike native I/O commands, which trigger escape messages when encountering serious errors, SQL statements will not typically trigger escape messages. So an RPG IV program with embedded SQL statements may encounter serious errors and still end normally.

Adopted Authority

When you compile RPG IV programs with embedded SQL, be aware that adopted authority is handled slightly differently than in RPG IV programs. The User Profile option is split into two separate keywords. The USRPRF keyword handles adopted authority for static (hard-coded) statements while DYNUSRPRF handles adopted authority for dynamic statements. This allows you to define one option for the hard-coded statements that you have complete control over and another for the statements that users might have some influence on.

Set these keywords to *USRPRF if you want the SQL statements to be processed under the authority of the user executing the program. Use *OWNER if you want the SQL statements to be processed under the authority of the user who owns the program. Initially, this is the programmer who compiles the program, though it can be changed with the CHGOBJOWN CL command.  

Stored Procedure

If we want to use a program like this to return data to other programs, we might want to use it as a stored procedure. That allows us to call it through SQL from programs written in other languages, such as Java or ASP.NET. The example below shows how to return the cursor as a result set to calling programs:   

...

   Stmt = 'SELECT CNAME FROM CUSTMAST ' + where;

   EXEC SQL prepare s1 from :stmt;

   EXEC SQL declare c1 Cursor for s1;

   EXEC SQL open c1;

   EXEC SQL SET RESULTS CURSOR c1;

   *inlr = *on;

 /end-free

                                                                                                                               &