Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


sql using CL on the Iseries

Posted on 2005-03-17
Medium Priority
Last Modified: 2010-05-18
Anbody doing SQL in a CL on the Iseres?

thanks in advance,


Question by:dprice7
LVL 27

Accepted Solution

tliotta earned 300 total points
ID: 13571340

Yes, yes and no.

That is, CL and SQL don't exactly go together; but there are a couple ways to get it to work.

Easiest is via the RUNSQLSTM command. It will execute an SQL statement or a series of statements delimited by semi-colons (";"). The troubles are that (1) the statement must be in a source file member and (2) only statements that can executed immediately are allowed.

There's a more technical explanation of that second part, but I don't have a decent link for it off the top of my head. One critical result is that there's no good way of using a SELECT statement directly via RUNSQLSTM such as you might in interactive SQL, i.e., the STRSQL command.

After RUNSQLSTM, there is STRQMQRY. Now, this also needs a source member to hold the SQL statement and it further needs to be compiled into a *QMQRY object.

But it has a very useful saving grace -- the source is allowed to have a whole bunch of "substitution variables" that get substituted into the 'statement' at run-time. While that only seems useful in general, it becomes seriously useful when you discover that _any_ part of the statement can be replaced by a substitution value.

Consider first:


That means you can choose both your column list and your table list at run-time. Those values would be passed in as parameters to the STRQMQRY command. Not bad.

But now consider this:


The _entire statement_ is nothing but a series of  substitution variables. That is, you can pass in a series of parameter values that string together to create the actual SQL statement on the fly.

There are a couple odd tricks. First, a substitution _value_ cannot be more than 55 characters in length. Second, the substitution variable names are case-sensitive -- when you pass the variable name into STRQMQRY along with the value, you have to pass the name in the proper case. Finally, if you want an SQL statement that will contain quotes, e.g.:

  SELECT a,b,c FROM mylib/myfile WHERE a='A1B2'

...then you need to be careful in how you construct the substitution values for the STRQMQRY command parameters.

Fortunately, most of this has already been done for you. You can run internet searches for SQL and CL and find examples that you can download. I have an odd example at my web site though it's primarily for demonstration because the processing is REXX.

You can look it over at < http://zap.to/tl400 >. Look in the Files section under REXX RunSQL. It provides a RUNSQL command that essentially lets you pass an SQL statement in as a parameter. It breaks the string into 55 character values and passes them into a STRQMQRY command. It also provides for output types of *OUTFILE and *PRINT as well as to the display.

For many displays, I most often use just:

  RUNQRY  *n  mylib/myfile

It's on all AS/400s. If you try to prompt it and use some of the advanced parameters, you'll need Query/400 installed.

Also note that CL can make use of OPNQRYF along with RCVF to process database records.


Author Comment

ID: 13571482

I actually already have used both of these before.

Was talking more about using straight sql as in functions, procedures etc.. SPL in a CL source member.

I have seen it in high level languages like RPG and Cobol but not in CL.

According to IBM there should be a way.


Assisted Solution

ShadowProgrammer earned 200 total points
ID: 13572720
Here it a link to a utility to allow you to run SQL statements from within a CL program.


The only thing you can't do with this is return selected records to the CL program for individual processing - but I get round this by building/populating a work file and then process each record in the work file.

instead of "select * from dbfile where x=y", you would "insert into workfile select * from dbfile where x=y" then process the records in workfile.

But otherwise you can do want you want as normal SQL.

NB. You can run this from a command line and "select..." will display the records on screen

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 27

Expert Comment

ID: 13577265

Sorry, the question is common at the level I answered.

Technically, I suppose one could consider that calling the Process Extended Dynamic SQL (QSQPRCED) API would be an example of using SQL in CL. I think the only serious issue would be setting up pointers to host variables in the SQLDA or getting into advanced options for package creation when using format SQLP0400.

As with many APIs, the trick is to figure out how to manipulate a pointer data type.

But I suspect you're wanting something more like embedded SQL in RPG or other languages.

Well, since there is no SQL pre-compiler for CL, there are no commands such as CRTSQLCLI nor CRTSQLCL. I'd sure be interested in hearing how IBM ever expects it to be done if it isn't something such as QSQPRCED (or RUNSQLSTM or STRQMQRY).

You don't happen to have a reference where IBM implies CL and SQL?

LVL 27

Expert Comment

ID: 13584094

Another idea just came to mind if you have V5R2. (Maybe 5.1 too; not certain.)


 ==>  QSH CMD('db2 ''select * from mylib.usrp''')

The recent db2 utility in QShell can give some useful effects, especially when combined with other QShell utilities in a sequence of commands.

LVL 27

Expert Comment

ID: 13608052
More on QShell and db2 command...

Instead of the QSH command to invoke the db2 command utility, how about:

 ==>  call QZDFMDB2  ('select UPUPRF from mylib.usrp')

The QShell utilities all have programs behind them. In this case, the db2 utility goes through a symbolic link to program QSYS/QZDFMDB2 once QShell is installed under V5R2. Most of those programs can be called directly by passing separate parms instead of QShell command-line switches.

This has some possibilities. Cool.

I do various things by tracking the symbolic links to their programs and calling them directly. It just came to mind that this one might be useful to others.

While SELECT might not be the best choice for a direct call rather than indirect through QShell due to formatting in a terminal session, other SQL verbs could give excellent results perhaps.

LVL 14

Expert Comment

ID: 13612025
I am just going to add a bit of weight to Tom’s comments.
When using SQL in RPG / Cobol etc.. there is a separate compiler – well pre compiler – that converts the high-level SQL into API calls.

There are also some IMB supplied definitions that are automatically copies as part of the compile process.

Once the original source is converted it is then compiled via the standard language compiler.

LVL 27

Expert Comment

ID: 13614924

Though perhaps off-topic, the relationship between the code that comes out of the RPG SQL pre-compiler and the RPG compiler itself seems worth examining. (Similarly for COBOL SQL or other HLL.) This is most interesting in the case of earlier OPM RPG since the pointer data type is relatively new but RPG SQL has been around for a while.

Someday I'd like to take the intermediate source code from an SQL pre-compile and transfer it to a system that doesn't have the SQL Developer kit installed. If I compile and run, will I get errors either at compile-time or run-time?

It's not necessary to have the DevKit installed in order to run an SQL RPG program, so compile-time seems where trouble would be. But I'm not sure how the RPG compiler itself would know what any problem would be.

Therefore, if OPM RPG can compile without pointers and then run, it _seems_ possible that the various structures and calls to SQL functions could be written in CL.


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this step by step method when setting up QuickBooks Online. They will allow you to explore the various features of the advanced settings available to you.
Don’ts and Dos are two important end products of software testing basics that a tester needs to regard. This article attempts to explain the principles of both.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question