Anbody doing SQL in a CL on the Iseres?
thanks in advance,
Don
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Here it a link to a utility to allow you to run SQL statements from within a CL program.
http://www.as400pro.com/se
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.
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
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
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
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
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
Business Accounts
Answer for Membership
by: tliottaPosted on 2005-03-17 at 18:32:14ID: 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