Can we specify Dynamic SQL statements through CL program

25 pts.
Tags:
CLPROGRAM
Hi Friends, I am using I Series V5R4 and I have question about Dynamic SQL running in CL program. Can we run dynamic SQL in CL program instead of RUNSQLSTM command?

Thanks in advance......

Regards, Raghuveer

Answer Wiki

Thanks. We'll let you know when a new response is added.

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:
<pre>
PGM PARM(&STRING)

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)

RTVJOBA USER(&USER)

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’ )
STRQSH CMD(&CMD)

CHGVAR VAR(&STMF) +
VALUE(&dir *tcat ‘/zz’ *tcat &user *tcat ‘_’ *tcat &string +
*tcat ‘.txt’)
DSPF STMF(&STMF)
RMVLNK OBJLNK(&STMF)
</pre>

Call this pgm with a parameter (data (UPPERCASE) to search for in DBXTXT field), fx
<pre>
CALL pgmname MASTER
</pre>
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.

Goo luck

DanF

==============================================================

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.

Tom

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CharlieBrowne
    Here is a link to another freeware option http://www.freerpgtools.com/downloads/sql.html
    41,370 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following