|
Call iSeries Programs Using SQL
by Kevin Vandever
[The code for this article is available for download.]
Though RPG applications once reigned as the center of our development efforts, they have since been
relegated to the role of legacy apps, as our attention has turned toward other platforms and other
technologies. The fact remains, however, that our most vital business rules still exist inside those RPG
apps. So how do you get to those apps from the outside world? Well, one way is to call them using SQL.
This article will explain how to do just that.
Why SQL?
So what's so special about SQL? SQL is the universal language used to access data from most, if not all,
relational databases in existence today. This includes Oracle, Access, SQL Server, and DB2, the native
database for the iSeries. High-level languages such as Java, Visual Basic, and C++ are able to access
relational databases by loading a database driver (JDBC or ODBC, depending on the language) and
embedding SQL statements to work with that driver.
One of the features of SQL is the stored procedure. A stored procedure is nothing more than a program that
is created and stored as part of the database and accessed via SQL. This program can be created using SQL
statements, but it doesn't have to be. And that's where RPG comes in. DB2 on the iSeries allows stored
procedures to be created in any language that runs on the iSeries. So you can create a stored procedure from
an ILE RPG program and call that program using the SQL call statement from other platforms. Let's talk a
little more about creating a stored procedure from an RPG program.
Stored Procedures
It is not my intent in this article to fully explain stored procedures. I will do that in a future article. What I
want to do here is show you how easy it is to turn an RPG program into a stored procedure. The first thing
you need to do is to start an interactive SQL session on your iSeries. You can do this typing
STRSQL on the command line. (You can also enter SQL statements interactively using Operations
Navigator, but I will use STRSQL for the purposes of this article.) Now that you have an interactive SQL
session, you are now ready to create a stored procedure. Type the following command and press F4 to
prompt:
CREATE PROCEDURE
A screen that looks much like the one in the following figure should appear:
Type a name for your stored procedure and a library to place it in. The next parameter.RESULT SETS,
allows you to state the maximum number of result sets that will be returned from this procedure. For my
purpose in this exercise, I leave this field blank; however, depending on how you are calling this stored
procedure and what you are trying to accomplish, you may want to set a maximum number. The next
parameter, Language, is the language in which this stored procedure was written. Notice all the wonderful
options available to you. You could choose any of them, but for this example I want to use RPGLE. The
last option on this first screen is the parameter style. I stated that I wanted a general parameter style option.
As you can see, depending on what you are passing and the language you are passing to, you may want to
enter a different option here.I will explain more about parameter styles, as well as all the available options,
in the next issue when I cover stored procedures in more detail. Press Enter to get to the second screen,
shown here:
The first thing you do on this screen is enter the name of the external program you are going to call. In my
example, it is an RPG program (as stated on the previous screen) called CUSTCHECK, and it resides
in a library called NEXSOURCE. The next parameter, IS DETERMINISTIC, is pretty cool. It specifies
whether or not the procedure will always return the same result from repeated calls containing the same
input values. If the answer is yes, DB2 will cache the results and the next time a call is made using the
same input parameters, the result will be returned to the caller without the external program ever being
called. Since my external program checks the validity of a customer ID passed to it, I want it always
checked and therefore answer N to this parameter.
Next I specify whether or not I want the external program called when my input parameters are null. I leave
the last parameter, SPECIFIC, blank. You use this parameter if you want a specific name for your
procedure. The interesting part is that this name has to be the same as the procedure name, so I have never
really figured out why to use this parameter. Anyway, press Enter one more time to get a screen like the
one shown here:
This is where you enter your input and output parameters. My RPG program contains two parameters in the
*ENTRY PLIST. The first parameter will contain the customer ID passed to the program. The second
parameter will not contain anything coming into the program, but it will be set to 1 or 0, depending on
whether or not the customer is valid. So when setting my parameters in the stored procedure, I not only
match the data type of the parms in my RPG, I want to match usage as well. This is important even though
RPG doesn't care whether you use parameters as input, output, or both. Press Enter and--presto!--you have
created a stored procedure from an RPG program. The cool thing is that you can now call this program
using an SQL call from another language on a different platform, and you haven't stopped its ability to call
it as you always have inside your iSeries application.
Let's take a look at how to call the stored procedure you just created from another language.
A Java Example
In this example, I am going to use JDBC as my database driver and Java as my language, but the concept
works the same for the ODBC driver and languages such as Visual Basic and C++; you basically register
the database driver, connect to a database, prepare your SQL statement, and then execute that statement.
Then, depending on whether you are expecting a return value, you get that value and do what you will with
it.
Take a look at my Java source code (staticSqlCall).
In this program, I pass in a customer ID from the command line
and that customer ID is used as the input parameter to my stored procedure. After I call the stored
procedure, I get the output parameter and, depending on the value, display a message to the screen stating
whether or not the customer ID passed was valid. Let's take a look at the SQL-related lines in the Java app.
Skip down to the block of code contained within the border of asterisks. The first thing I do is create a
Connection object called con. Next I create a String called url and set it to jdbc:as400://, followed by the
machine's IP address. Next I register the driver using the registerDriver method in the DriverManager class.
Now I'm ready to instantiate my connection object with a connection to my iSeries database. I do this by
using the getConnection method in the DriverManager class, passing my url string along with a valid user
ID and password. For the purpose of illustration, I have hard coded the user ID and password, but in a
production environment you would want to retrieve that information from some secure storage area such as
a database file or LDAP directory. Now that I have connected to the database, I am ready to build my SQL
statement.
In my example, I am going to use the CallableStatement class. The first thing I do is create a new
CallableStatement object. I called it sqlCall. Now, using the prepareCall method from my connection
object, I instantiate the sqlCall with the actual CALL statement that I will use. The question marks (?)
represent the parameters that the stored procedure contains. The sqlCall object is now ready to go. We just
have to fill in the question marks. Remember we had one input parameter and one output parameter? It is
important to remember this here because it will determine how you deal with each parameter. For the input
parameter, I use the setString method from the sqlCall object to set the first parameter to the string (args[0])
that I entered on the command line. The setString method accepts the parameter number, in this case 1, and
the String that will be passed to the stored procedure. The setString method is used because the parameter is
of CHAR data type. If it were another data type, a different SET method would be used. Now that the input
parameter is set, it is time to register the output parameter.
I do this using the registerOutParameter method in my sqlCall object. This method accepts the parameter
number and the fully qualified expected data type. The input parameter has been set and the output
parameter registered so that the sqlCall object now knows what the question marks really represent. The
next thing to do is call the stored procedure, and I do this using the execute method contained in my sqlCall
object. This method accepts no parameters; rather, it executes this sqlCall object, which we just
spent the last few statements defining. The next thing I do is retrieve my output parameter. This is done
using the getBoolean method in my sqlCall object. The getBoolean method accepts the parameter number
as input and its return value is a Boolean true or false. The getBoolean method is used because that is the
method used to retrieve SMALLINT data types. If the output parameter were CHAR, the getString method
would have been used instead. Now that I have the value contained in my output parameter, I can perform
an IF test and tell the user whether or not the customer ID keyed on the command line was valid. The last
two method calls are used to close the sqlCall object and disconnect the database connection, respectively.
To run the application, type the following from your DOS command line, Qshell command line on the
iSeries, the CODE/400 command shell or any other environment you might use:
java staticSqlCall 00000001
The 00000001 represents the customer ID that you want to validate. Don’t worry, if you forget to type the
parameter, the java application will ask you to try again. Also, I’ve included a sample RPG program with
the downloadable code for this article that you can use with the java application to test out this technique.
iSeries Applications Extended
This article was not meant to discuss the specifics of Java (for that, check out my article
"Java Concepts for iSeries Programmers"),
nor was it meant to be a tutorial on JDBC or the
IBM Toolbox for Java, or to touch on the inner workings
of stored procedures (tune in later for that one). It was, however, meant to show you how to make your
RPG and other iSeries programs accessible from just about anywhere within your IT infrastructure, using a
standard database driver and SQL. So design away and do so happily, knowing that your iSeries programs
are just a call away.
|
