|
midrange.com
To get a free sql processor do the following:
Put the following in a source file:
&LINE01&LINE02&LINE03&LINE04&LINE05&LINE06&LINE07&LINE08&LINE09&LINE10
Do a CRTQMQRY against this object.
Create the following CL program:
/********************************************************************/
/* */
/* Program - ZUCXSQL */
/* This is the command processing program for command RUNSQL. */
/* */
/********************************************************************/
/* */
/* Description: Process SQL commands using QM query. */
/* */
/********************************************************************/
SQL: PGM PARM(&LINES &TYPE &OUTFILE &OUTMBR &PRTFILE)
DCL VAR(&TYPE) TYPE(*CHAR) LEN(10)
DCL VAR(&LINES) TYPE(*CHAR) LEN(550)
DCL VAR(&LINE01) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE02) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE03) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE04) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE05) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE06) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE07) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE08) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE09) TYPE(*CHAR) LEN(55)
DCL VAR(&LINE10) TYPE(*CHAR) LEN(55)
DCL VAR(&OUTFILE) TYPE(*CHAR) LEN(20)
DCL VAR(&OFIL) TYPE(*CHAR) LEN(10)
DCL VAR(&OLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&OUTMBR) TYPE(*CHAR) LEN(20)
DCL VAR(&OMBR) TYPE(*CHAR) LEN(10)
DCL VAR(&ORPL) TYPE(*CHAR) LEN(10)
DCL VAR(&PRTFILE) TYPE(*CHAR) LEN(20)
DCL VAR(&PLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&PFIL) TYPE(*CHAR) LEN(10)
/* Standard error handler fields */
DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(100)
DCL VAR(&MSGF) TYPE(*CHAR) LEN(10)
DCL VAR(&MSGFLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&ERRORSW) TYPE(*LGL)
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
CHGVAR VAR(&LINE01) VALUE(%SST(&LINES 1 55))
CHGVAR VAR(&LINE02) VALUE(%SST(&LINES 56 55))
CHGVAR VAR(&LINE03) VALUE(%SST(&LINES 111 55))
CHGVAR VAR(&LINE04) VALUE(%SST(&LINES 166 55))
CHGVAR VAR(&LINE05) VALUE(%SST(&LINES 221 55))
CHGVAR VAR(&LINE06) VALUE(%SST(&LINES 276 55))
CHGVAR VAR(&LINE07) VALUE(%SST(&LINES 331 55))
CHGVAR VAR(&LINE08) VALUE(%SST(&LINES 386 55))
CHGVAR VAR(&LINE09) VALUE(%SST(&LINES 441 55))
CHGVAR VAR(&LINE10) VALUE(%SST(&LINES 496 55))
CHGVAR VAR(&OFIL) VALUE(%SST(&OUTFILE 1 10))
CHGVAR VAR(&OLIB) VALUE(%SST(&OUTFILE 11 10))
CHGVAR VAR(&OMBR) VALUE(%SST(&OUTMBR 1 10))
CHGVAR VAR(&ORPL) VALUE(%SST(&OUTMBR 11 10))
CHGVAR VAR(&PFIL) VALUE(%SST(&PRTFILE 1 10))
CHGVAR VAR(&PLIB) VALUE(%SST(&PRTFILE 11 10))
IF COND(&TYPE = '*OUTFILE') +
THEN(DO)
STRQMQRY QMQRY(ZUQXSQL) OUTPUT(&TYPE) +
OUTFILE(&OLIB/&OFIL) OUTMBR(&OMBR &ORPL) +
ALWQRYDFN(*YES) SETVAR((LINE01 &LINE01) +
(LINE02 &LINE02) (LINE03 &LINE03) (LINE04 +
&LINE04) (LINE05 &LINE05) (LINE06 +
&LINE06) (LINE07 &LINE07) (LINE08 +
&LINE08) (LINE09 &LINE09) (LINE10 &LINE10))
MONMSG MSGID(QWM0000) EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('+
Syntax error in SQL statement. See previous +
messages for details') MSGTYPE(*DIAG)
ENDDO
ENDDO
ELSE (DO)
OVRPRTF FILE(QPQXPRTF) TOFILE(&PLIB/&PFIL)
STRQMQRY QMQRY(ZUQXSQL) OUTPUT(&TYPE) +
ALWQRYDFN(*YES) SETVAR((LINE01 &LINE01) +
(LINE02 &LINE02) (LINE03 &LINE03) (LINE04 +
&LINE04) (LINE05 &LINE05) (LINE06 +
&LINE06) (LINE07 &LINE07) (LINE08 +
&LINE08) (LINE09 &LINE09) (LINE10 &LINE10))
MONMSG MSGID(QWM0000) EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('+
Syntax error in SQL statement. See previous +
messages for details') MSGTYPE(*DIAG)
ENDDO
DLTOVR FILE(QPQXPRTF)
ENDDO
RETURN /* Normal end of program */
ERROR: /* Standard error handler */
IF COND(&ERRORSW) THEN(SNDPGMMSG MSGID(CPF9999) +
MSGF(QCPFMSG) MSGTYPE(*ESCAPE)) /* +
Function check message */
CHGVAR VAR(&ERRORSW) VALUE('1')
ERROR2: RCVMSG MSGTYPE(*DIAG) MSGDTA(&MSGDTA) MSGID(&MSGID) +
MSGF(&MSGF) MSGFLIB(&MSGFLIB)
IF COND(&MSGID = ' ') THEN(GOTO +
CMDLBL(ERROR3))
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) MSGTYPE(*DIAG)
GOTO CMDLBL(ERROR2)
ERROR3: RCVMSG MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) +
MSGF(&MSGF) MSGFLIB(&MSGFLIB)
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) MSGTYPE(*ESCAPE)
ENDPGM
Create the following command:
/********************************************************************/
/* */
/* Command definition for RUNSQL command */
/* Command processing program is ZUCXSQL */
/* */
/********************************************************************/
/* */
/* Description: Process SQL commands using QM query. */
/* */
/********************************************************************/
CMD PROMPT('Process SQL commands')
PARM KWD(REQUEST) TYPE(*CHAR) LEN(550) MIN(1) +
PROMPT('SQL request')
PARM KWD(OUTPUT) TYPE(*CHAR) LEN(10) RSTD(*YES) +
DFT(*) VALUES(* *PRINT *OUTFILE) MIN(0) +
PROMPT('Output')
PARM KWD(OUTFILE) TYPE(T1) MIN(0) PMTCTL(P1) +
PROMPT('File to receive output')
PARM KWD(MEMBER) TYPE(T2) MIN(0) PMTCTL(P1) +
PROMPT('Output member options')
PARM KWD(PRTFILE) TYPE(T3) MIN(0) PMTCTL(P2) +
PROMPT('Printer file to use')
T1: QUAL TYPE(*NAME) LEN(10)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
SPCVAL((*LIBL) (*CURLIB)) PROMPT('Library')
T2: QUAL TYPE(*NAME) LEN(10) DFT(*FIRST) +
SPCVAL((*FIRST))
QUAL TYPE(*NAME) LEN(10) RSTD(*YES) DFT(*REPLACE) +
SPCVAL((*REPLACE) (*ADD)) PROMPT('Replace +
or add records')
T3: QUAL TYPE(*NAME) LEN(10) DFT(QPQXPRTF)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
SPCVAL((*LIBL) (*CURLIB)) PROMPT('Library')
P1: PMTCTL CTL(OUTPUT) COND((*EQ *OUTFILE))
P2: PMTCTL CTL(OUTPUT) COND((*EQ *PRINT))
You are then done.
Sample execution:
runsql ('Select * from library/file')
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
