Easily execute an SQL statement from a command line or from within a CL program.
One of the weak links in IBM's
SQL support is the lack of an ad hoc "RUNSQL" type of command that lets us
easily execute an SQL statement from a command line or from within a CL program.
However, programmers who are so inclined can fill in this gap by writing just a
little code of their own.
Let's call our new command "RUNSQL." This
simple version will not support SELECT statements, as that is a very complex
task and beyond the scope of this article. It will let us run SQL statements
like this:
RUNSQL CMD('DELETE FROM PODETAIL WHERE PONBR = 12345')
If you already have a command by this name on your system, simply provide
a different name for this tool.
The purpose of this example is to
highlight the techniques and the technology used, not the tool itself.
Creating the RUNSQL Function
The centerpiece to the RUNSQL command is an RPGLE
service program. The code for the service program, also named RUNSQL, is
illustrated in Figure 1.
Figure 1: This is the complete code for the RUNSQL service program.
(Click images to enlarge.)
The /COPY statement is bringing in the
prototype, shown in Figure 2, from the QCPYSRC source file. The function accepts
an SQL command and returns the value of SQLCOD as a return code.
Figure 2: Here's the prototype copybook for the RUNSQL service
program.
Creating the service program is a two-step operation. First,
create the module. Then, create the service program from that module.
CRTSQLRPGI OBJ(RUNSQL) COMMIT(*NONE) OBJTYPE(*MODULE) DBGVIEW(*SOURCE)
CRTSRVPGM SRVPGM(RUNSQL) EXPORT(*ALL)
Testing It
Now that you've created the service program, test it.
Write a simple CL program, like the one shown in Figure 3, that uses this new
tool to create an SQL schema named TESTSCHEMA.
Figure 3: Use this CLLE code to test the RUNSQL
function.
Compiling the CLLE program requires two steps. First,
create the module and then create the program, using the commands shown below.
Be sure to reference the RUNSQL service program created earlier.
CRTCLMOD MODULE(TESTPGM) DBGVIEW(*SOURCE)
CRTPGM PGM(TESTPGM) BNDSRVPGM(RUNSQL)
After creating the program, you can test it by simply calling it with
this command:
CALL TESTPGM
If you like, add additional code after the CALLPRC to test &SQLCODE
and issue an error message if &SQLCODE is not zero. You could just as easily
call this procedure from an RPGLE program.
Executing from the Command Line
To call the RUNSQL function from a command line, you
need to wrap a System i command around it. This requires a command source file
with the specifications for the command, as shown in Figure 4.
Figure 4: Use this command source for the RUNSQL command.
We
also need a command processing program (CPP) to wrap around our function, as
shown in figure 5.
Figure 5: Here's the source for the RUNSQL CPP.
The SqlCmd variable in the *ENTRY parameter list is
loaded from the command prompt, and this program passes that command to the
RunSql function. If the return code is not zero, an error occurred, and the
program displays an error message using the MSGCL program shown in Figure 6.
Figure 6: The CL message-handling program alerts you to
errors.
That's the complete code for the RUNSQL command. Not bad for about 35 lines
of code, huh? To compile the CPP, use the following statements:
CRTCLMOD MODULE(RUNSQLRPG) DBGVIEW(*SOURCE)
CRTPGM PGM(RUNSQLRPG) BNDSRVPGM(RUNSQL) BNDDIR(KPFLIB/I5TOOLS)
CRTCMD CMD(RUNSQL) PGM(RUNSQLRPG)
CRTCLPGM PGM(MSGCL)
Now, you can run commands from the menu like this:
RUNSQL CMD('DELETE FROM PODETAIL WHERE PONBR = 12345')
When you type RUNSQL, pressing F4 displays the command prompt shown in
Figure 7.
Figure 7: Pressing F4 displays the RUNSQL command
prompt.
DB2 Query Mgr and SQL DevKit
Compiling this tool requires that you have the SQL
Development Kit installed on your System i. If you don't have access to a system
with the development kit, email me at
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
,
and I'll provide you with a compiled copy of this RUNSQL command.
Summary
This is just one simple example of the kinds of
enhancements you can make to your development environment by combining the best
of both SQL and RPGLE. If you don't already have the skills and experience to
develop your own tools like this, DMC
Consulting provides the training you need. Check out DMC's class schedule. Find
out more about this Premier IBM Business Partner at the company's Web site. And check out DMC's
offerings in the MC
Showcase Buyers Guide.
Kevin Forsythe has
worked on IBM midrange systems for more than 20 years. With many years of
experience in programming, analysis, consulting, and instruction, he is uniquely
skilled at making difficult material more easily understood. As the lead
instructor for DMC Consulting’s AS/Credentials training classes, Kevin is
responsible for developing up-to-date courseware as well as providing
instruction on a wide range of topics. This comprehensive background includes
practical application, education, and research and provides a perspective that
allows him to help others steer their way through the technical maze that
surrounds them.
Kevin speaks regularly at
conferences and user group meetings, volunteers as a Subject Matter Expert for
COMMON, has written numerous articles on a variety of iSeries topics, and
authored the bestselling book SQL for eServer i5 and
iSeries.
|
