dprice7
asked on
sql using CL on the Iseries
Anbody doing SQL in a CL on the Iseres?
thanks in advance,
Don
thanks in advance,
Don
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
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
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
ASKER
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