Using QSHELL in your CL-pgms adds a lot of nice featurs and options to your CL-pgms.
One of these is the ability to execute SQL-statements.
From my ‘playground’ I can supply you with this fully functional sample:
DCL VAR(&STRING) TYPE(*CHAR) LEN(20)
DCL VAR(&CMD) TYPE(*CHAR) LEN(256)
DCL VAR(&DIR) TYPE(*CHAR) LEN(128) VALUE(‘/tmp’)
DCL VAR(&STMF) TYPE(*CHAR) LEN(128)
DCL VAR(&LNK ) TYPE(*CHAR) LEN(128)
DCL VAR(&USER) TYPE(*CHAR) LEN(10)
CHGVAR VAR(&CMD) VALUE(‘db2 “select’ +
*bcat ‘DBXFIL AS Filename,’ +
*bcat ‘DBXLIB AS Library,’ +
*bcat ‘DBXOWN AS Owner,’ +
*bcat ‘DBXTXT AS Filetext,’ +
*bcat ‘DBXATR AS Attribute’ +
*bcat ‘from qsys.qadbxref’ +
*bcat ‘where upper(DBXTXT) like’ +
*bcat ”’%’ +
*tcat &string +
*tcat ‘%””‘ +
*bcat ‘>’ +
*bcat &dir +
*tcat ‘/zz’ +
*tcat &user +
*tcat ‘_’ +
*tcat &string +
*tcat ‘.txt’ )
CHGVAR VAR(&STMF) +
VALUE(&dir *tcat ‘/zz’ *tcat &user *tcat ‘_’ *tcat &string +
Call this pgm with a parameter (data (UPPERCASE) to search for in DBXTXT field), fx
CALL pgmname MASTER
What this sample pgm does:
1. creates an SQL-statement based on your input-parameter, which is the string that we want to search for in th field DBXTXT.
2. executes the generated SQL-statement and creates the resultset
3. resultset (all selected records) is then piped to a file on the IFS (‘/tmp/zz’ + username + search-string)
4. displays the IFS-file with the DSPF command
5. deletes the temporary IFS-file.
I hope this helps, and opens your eyes to the world of options with QSHELL.
The direct answer to the question is “No, CL cannot do SQL at all. CL is not a database language; it’s a Control Language.”
But the indirect answer allows CL to call out to other languages (or environments) such as the Qshell environment as noted by DanTheDane above. CharlieBrowne points out a RPG possibility. But there are alternatives that do not require loading Qshell on your system nor needing any other compilers.
First, REXX is already on every AS/400 that you’ll ever find and REXX easily handles dynamic SQL. You can create a “DynamicSQL” command that accepts your SQL statement as a parameter and has a REXX procedure as the command-processing program. As with almost all dynamic SQL solutions, there are elements that can need special attention — e.g., INSERTs are easy but SELECTs are almost meaningless without a way to process the result set.
Also, QM queries provide a very useful framework for dynamic SQL. You can create a QM query that consists of nothing more than a series of replacement variables. A dynamic SQL statement can then be parsed into a set of substrings to fit in the 55-character limit for each replacement variable and passed into a STRQMQRY command. This has the advantage of handling SELECTs in a very flexible way.
And finally, as of V5R4, ILE CL can even call all of the SQL CLI APIs directly. If you’re really serious, you can create your own SQL processor in pure CL to handle any part of it any way you wish.