?
Solved

sql using CL on the Iseries

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

thanks in advance,

Don

0
Comment
Question by:dprice7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 27

Accepted Solution

by:
tliotta earned 300 total points
ID: 13571340
Don:

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:

  SELECT &a FROM &b

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:

  &a&b&c&d&e&f

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.

Tom
0
 

Author Comment

by:dprice7
ID: 13571482
Tom,

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.

Don
0
 
LVL 9

Assisted Solution

by:ShadowProgrammer
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.

http://www.as400pro.com/servlet/sql.tipView?key=159&category=SQL

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

Tony.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 27

Expert Comment

by:tliotta
ID: 13577265
Don:

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?

Tom
0
 
LVL 27

Expert Comment

by:tliotta
ID: 13584094
Don:

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

 ==>  DSPUSRPRF USRPRF(*ALL) OUTPUT(*OUTFILE)
            OUTFILE(mylib/USRP)

 ==>  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.

Tom
0
 
LVL 27

Expert Comment

by:tliotta
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.

Tom
0
 
LVL 14

Expert Comment

by:daveslater
ID: 13612025
Hi
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.

Dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 13614924
Dave:

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.

Tom
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
What's worse than having your data encrypted by ransomware? Getting attacked by a so-called "wiper," which simply destroys the data and offers you no hope of ever seeing it again.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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