Return to the SQL Tips
Execute SQL statements using QMQRY. Works on systems without the SQL product.
Execute SQL statements using QMQRY. Works on systems without the SQL product.
(I believe this came from Midrange Computing)
QMQRY Source - GENQRY
*************** Beginning of data *************************************
0001.00 --====================================================================
0002.00 -- To compile:
0003.00 --
0004.00 -- CRTQMQRY QMQRY(XXX/GENQRY) SRCFILE(XXX/QQMQRYSRC)
0005.00 --
0006.00 --====================================================================
0007.00 &S01&S02&S03&S04&S05&S06&S07&S08&S09&S10
0008.00 &S11&S12&S13&S14&S15&S16&S17&S18&S19&S20
0009.00 &S21&S22&S23&S24&S25&S26&S27&S28&S29&S30
0010.00 &S31&S32&S33&S34&S35&S36&S37&S38&S39&S40
0011.00 &S41&S42&S43&S44&S45&S46&S47&S48&S49&S50
****************** End of data ****************************************
CL Source - SQL003CL
*************** Beginning of data ************************************
0001.00 /*==================================================================*/
0002.00 /* To compile: */
0003.00 /* */
0004.00 /* 1. Insure that a QQMQRYSRC source file exists in your library */
0005.00 /* list. */
0006.00 /* */
0007.00 /* 2. CRTCLPGM PGM(XXX/SQL003CL) SRCFILE(XXX/QCLSRC) */
0008.00 /* */
0009.00 /*==================================================================*/
0010.00 SQL003CL: +
0011.00 PGM PARM(&STM &OUTPUT &SRCF &SRCMBR &EDIT &OUTFILE &OUTMBR)
0012.00
0013.00 DCLF FILE(QQMQRYSRC)
0014.00
0015.00 /* Parameter variables */
0016.00 DCL VAR(&EDIT) TYPE(*CHAR) LEN(4)
0017.00 DCL VAR(&MBROPT) TYPE(*CHAR) LEN(8)
0018.00 DCL VAR(&OUTFILE) TYPE(*CHAR) LEN(20)
0019.00 DCL VAR(&OUTMBR) TYPE(*CHAR) LEN(20)
0020.00 DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(8)
0021.00 DCL VAR(&SRCF) TYPE(*CHAR) LEN(20)
0022.00 DCL VAR(&SRCMBR) TYPE(*CHAR) LEN(10)
0023.00 DCL VAR(&STM) TYPE(*CHAR) LEN(2750)
0024.00 /* Variables used for contrived values */
0025.00 DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1)
0026.00 DCL VAR(&LASTCHAR) TYPE(*CHAR) LEN(10)
0027.00 DCL VAR(&OFFSET) TYPE(*DEC) LEN(5 0) VALUE(0)
0028.00 DCL VAR(&MBRNAM) TYPE(*CHAR) LEN(10)
0029.00 DCL VAR(&MBROPT) TYPE(*CHAR) LEN(8)
0030.00 DCL VAR(&OUTFLIB) TYPE(*CHAR) LEN(10)
0031.00 DCL VAR(&OUTFNAM) TYPE(*CHAR) LEN(10)
0032.00 DCL VAR(&REMAINS) TYPE(*DEC) LEN(5 0) VALUE(0)
0033.00 DCL VAR(&SRCFLIB) TYPE(*CHAR) LEN(10)
0034.00 DCL VAR(&SRCFNAM) TYPE(*CHAR) LEN(10)
0035.00 /* Error processing variables */
0036.00 DCL VAR(&ERRBYTES) TYPE(*CHAR) LEN(4) VALUE(X'00000000')
0037.00 DCL VAR(&ERROR) TYPE(*LGL) VALUE('0')
0038.00 DCL VAR(&MSGKEY) TYPE(*CHAR) LEN(4)
0039.00 DCL VAR(&MSGTYP) TYPE(*CHAR) LEN(10) VALUE('*DIAG')
0040.00 DCL VAR(&MSGTYPCTR) TYPE(*CHAR) LEN(4) VALUE(X'00000001')
0041.00 DCL VAR(&PGMMSGQ) TYPE(*CHAR) LEN(10) VALUE('*')
0042.00 DCL VAR(&STKCTR) TYPE(*CHAR) LEN(4) VALUE(X'00000001')
0043.00
0044.00 MONMSG MSGID(CPF0000 QWM0000) EXEC(GOTO CMDLBL(ERRPROC))
0045.00
0046.00 /* Validate input */
0047.00 IF COND(&OUTPUT *EQ '*OUTFILE') THEN(DO)
0048.00 CHGVAR VAR(&OUTFNAM) VALUE(%SST(&OUTFILE 1 10))
0049.00 CHGVAR VAR(&OUTFLIB) VALUE(%SST(&OUTFILE 11 10))
0050.00 CHGVAR VAR(&MBRNAM) VALUE(%SST(&OUTMBR 3 12))
0051.00 CHGVAR VAR(&MBROPT) VALUE(%SST(&OUTMBR 13 8))
0052.00 IF COND(&OUTFLIB *NE '*CURLIB' *AND &OUTFLIB *NE '*LIBL') +
0053.00 THEN(DO)
0054.00 CHKOBJ OBJ(&OUTFLIB) OBJTYPE(*LIB)
0055.00 ENDDO
0056.00 ENDDO
0057.00
0058.00 RTVJOBA TYPE(&JOBTYPE)
0059.00
0060.00 IF (&STM = '*SOURCE') THEN(DO)
0061.00 CHGVAR VAR(&SRCFNAM) VALUE(%SST(&SRCF 1 10))
0062.00 CHGVAR VAR(&SRCFLIB) VALUE(%SST(&SRCF 11 10))
0063.00 IF COND(&EDIT = '*YES' *AND &JOBTYPE *NE '0') THEN(STRSEU +
0064.00 SRCFILE(&SRCFLIB/&SRCFNAM) SRCMBR(&SRCMBR) OPTION(2))
0065.00 OVRDBF FILE(QQMQRYSRC) TOFILE(&SRCFLIB/&SRCFNAM) MBR(&SRCMBR)
0066.00 CHGVAR VAR(&STM) VALUE(' ')
0067.00 READLOOP: +
0068.00 RCVF
0069.00 MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(FORMAT))
0070.00 IF COND((&LASTCHAR > ' ') *AND (%SST(&SRCDTA 1 1) > ' ')) +
0071.00 THEN(CHGVAR VAR(&STM) VALUE(&STM *TCAT &SRCDTA))
0072.00 ELSE CMD(CHGVAR VAR(&STM) VALUE(&STM *BCAT &SRCDTA))
0073.00 CHGVAR VAR(&LASTCHAR) VALUE(%SST(&SRCDTA 79 1))
0074.00 GOTO CMDLBL(READLOOP)
0075.00 ENDDO
0076.00
0077.00 FORMAT: +
0078.00 CHGVAR VAR(&OFFSET) VALUE(&OFFSET + 55)
0079.00 CHGVAR VAR(&REMAINS) VALUE(2751 - &OFFSET)
0080.00 IF COND(%SST(&STM &OFFSET 1) *EQ ' ' *AND %SST(&STM &OFFSET 2) +
0081.00 *NE ' ') THEN(CHGVAR VAR(&STM) VALUE(%SST(&STM 1 &OFFSET) +
0082.00 *CAT %SST(&STM &OFFSET &REMAINS)))
0083.00 IF COND(&OFFSET *LT 2695) THEN(GOTO CMDLBL(FORMAT))
0084.00
0085.00 IF COND(&OUTPUT = '*OUTFILE') THEN(STRQMQRY QMQRY(GENQRY) +
0086.00 OUTPUT(&OUTPUT) OUTFILE(&OUTFLIB/&OUTFNAM) OUTMBR(&MBRNAM +
0087.00 &MBROPT) SETVAR((S01 %SST(&STM 1 55)) (S02 %SST(&STM 56 55)) +
0088.00 (S03 %SST(&STM 111 55)) (S04 %SST(&STM 166 55)) (S05 +
0089.00 %SST(&STM 221 55)) (S06 %SST(&STM 276 55)) (S07 %SST(&STM 331 +
0090.00 55)) (S08 %SST(&STM 386 55)) (S09 %SST(&STM 441 55)) (S10 +
0091.00 %SST(&STM 496 55)) (S11 %SST(&STM 551 55)) (S12 %SST(&STM 606 +
0092.00 55)) (S13 %SST(&STM 661 55)) (S14 %SST(&STM 716 55)) (S15 +
0093.00 %SST(&STM 771 55)) (S16 %SST(&STM 826 55)) (S17 %SST(&STM 881 +
0094.00 55)) (S18 %SST(&STM 936 55)) (S19 %SST(&STM 991 55)) (S20 +
0095.00 %SST(&STM 1046 55)) (S21 %SST(&STM 1101 55)) (S22 %SST(&STM +
0096.00 1156 55)) (S23 %SST(&STM 1211 55)) (S24 %SST(&STM 1266 55)) +
0097.00 (S25 %SST(&STM 1321 55)) (S26 %SST(&STM 1376 55)) (S27 +
0098.00 %SST(&STM 1431 55)) (S28 %SST(&STM 1486 55)) (S29 %SST(&STM +
0099.00 1541 55)) (S30 %SST(&STM 1596 55)) (S31 %SST(&STM 1651 55)) +
0100.00 (S32 %SST(&STM 1706 55)) (S33 %SST(&STM 1761 55)) (S34 +
0101.00 %SST(&STM 1816 55)) (S35 %SST(&STM 1871 55)) (S36 %SST(&STM +
0102.00 1926 55)) (S37 %SST(&STM 1981 55)) (S38 %SST(&STM 2036 55)) +
0103.00 (S39 %SST(&STM 2091 55)) (S40 %SST(&STM 2146 55)) (S41 +
0104.00 %SST(&STM 2201 55)) (S42 %SST(&STM 2256 55)) (S43 %SST(&STM +
0105.00 2311 55)) (S44 %SST(&STM 2366 55)) (S45 %SST(&STM 2421 55)) +
0106.00 (S46 %SST(&STM 2476 55)) (S47 %SST(&STM 2531 55)) (S48 +
0107.00 %SST(&STM 2586 55)) (S49 %SST(&STM 2641 55)) (S50 %SST(&STM +
0108.00 2696 55))))
0109.00 ELSE STRQMQRY QMQRY(GENQRY) OUTPUT(&OUTPUT) SETVAR((S01 +
0110.00 %SST(&STM 1 55)) (S02 %SST(&STM 56 55)) (S03 %SST(&STM 111 +
0111.00 55)) (S04 %SST(&STM 166 55)) (S05 %SST(&STM 221 55)) (S06 +
0112.00 %SST(&STM 276 55)) (S07 %SST(&STM 331 55)) (S08 %SST(&STM 386 +
0113.00 55)) (S09 %SST(&STM 441 55)) (S10 %SST(&STM 496 55)) (S11 +
0114.00 %SST(&STM 551 55)) (S12 %SST(&STM 606 55)) (S13 %SST(&STM 661 +
0115.00 55)) (S14 %SST(&STM 716 55)) (S15 %SST(&STM 771 55)) (S16 +
0116.00 %SST(&STM 826 55)) (S17 %SST(&STM 881 55)) (S18 %SST(&STM 936 +
0117.00 55)) (S19 %SST(&STM 991 55)) (S20 %SST(&STM 1046 55)) (S21 +
0118.00 %SST(&STM 1101 55)) (S22 %SST(&STM 1156 55)) (S23 %SST(&STM +
0119.00 1211 55)) (S24 %SST(&STM 1266 55)) (S25 %SST(&STM 1321 55)) +
0120.00 (S26 %SST(&STM 1376 55)) (S27 %SST(&STM 1431 55)) (S28 +
0121.00 %SST(&STM 1486 55)) (S29 %SST(&STM 1541 55)) (S30 %SST(&STM +
0122.00 1596 55)) (S31 %SST(&STM 1651 55)) (S32 %SST(&STM 1706 55)) +
0123.00 (S33 %SST(&STM 1761 55)) (S34 %SST(&STM 1816 55)) (S35 +
0124.00 %SST(&STM 1871 55)) (S36 %SST(&STM 1926 55)) (S37 %SST(&STM +
0125.00 1981 55)) (S38 %SST(&STM 2036 55)) (S39 %SST(&STM 2091 55)) +
0126.00 (S40 %SST(&STM 2146 55)) (S41 %SST(&STM 2201 55)) (S42 +
0127.00 %SST(&STM 2256 55)) (S43 %SST(&STM 2311 55)) (S44 %SST(&STM +
0128.00 2366 55)) (S45 %SST(&STM 2421 55)) (S46 %SST(&STM 2476 55)) +
0129.00 (S47 %SST(&STM 2531 55)) (S48 %SST(&STM 2586 55)) (S49 +
0130.00 %SST(&STM 2641 55)) (S50 %SST(&STM 2696 55)))
0131.00
0132.00 GOTO CMDLBL(ENDPGM)
0133.00
0134.00 ERRPROC: +
0135.00 IF COND(&ERROR) THEN(GOTO CMDLBL(ERRDONE))
0136.00 ELSE CMD(CHGVAR VAR(&ERROR) VALUE('1'))
0137.00
0138.00 /* Move all *DIAG messages to previous program queue */
0139.00 CALL PGM(QMHMOVPM) PARM(&MSGKEY &MSGTYP &MSGTYPCTR &PGMMSGQ +
0140.00 &STKCTR &ERRBYTES)
0141.00
0142.00 /* Resend last *ESCAPE message */
0143.00 ERRDONE: +
0144.00 CALL PGM(QMHRSNEM) PARM(&MSGKEY &ERRBYTES)
0145.00 MONMSG MSGID(CPF0000) EXEC(DO)
0146.00 SNDPGMMSG MSGID(CPF3CF2) MSGF(QCPFMSG) MSGDTA('QMHRSNEM') +
0147.00 MSGTYPE(*ESCAPE)
0148.00 MONMSG MSGID(CPF0000)
0149.00 ENDDO
0150.00
0151.00 ENDPGM: +
0152.00 ENDPGM
****************** End of data ****************************************
EXCSQLSTM - CMD Source
*************** Beginning of data *************************************
0001.00 /*==================================================================*/
0002.00 /* To compile: */
0003.00 /* */
0004.00 /* CRTCMD CMD(XXX/EXCSQLSTM) PGM(XXX/SQL003CL) + */
0005.00 /* SRCFILE(XXX/QCMDSRC) */
0006.00 /* */
0007.00 /*==================================================================*/
0008.00 EXCSQLSTM: CMD PROMPT('Execute SQL Statement')
0009.00
0010.00 PARM KWD(SQLSTM) TYPE(*CHAR) LEN(2750) +
0011.00 DFT(*SOURCE) EXPR(*YES) PROMPT('SQL +
0012.00 Statement')
0013.00
0014.00 PARM KWD(OUTPUT) TYPE(*CHAR) LEN(8) RSTD(*YES) +
0015.00 DFT(*) VALUES(* *PRINT *OUTFILE) +
0016.00 PROMPT('Output')
0017.00
0018.00 PARM KWD(SRCF) TYPE(Q1) PMTCTL(PC1) PROMPT('Query +
0019.00 source file')
0020.00 Q1: QUAL TYPE(*NAME) LEN(10) DFT(QQMQRYSRC)
0021.00 QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
0022.00 SPCVAL((*LIBL) (*CURLIB)) PROMPT('Library')
0023.00 PARM KWD(SRCMBR) TYPE(*NAME) LEN(10) PMTCTL(PC1) +
0024.00 PROMPT('Query source member')
0025.00 PARM KWD(EDIT) TYPE(*CHAR) LEN(4) RSTD(*YES) +
0026.00 DFT(*NO) VALUES(*NO *YES) PMTCTL(PC1) +
0027.00 PROMPT('Edit query source member')
0028.00 PC1: PMTCTL CTL(SQLSTM) COND((*EQ *SOURCE))
0029.00
0030.00
0031.00 PARM KWD(OUTFILE) TYPE(Q2) PMTCTL(PC2) +
0032.00 PROMPT('File to receive output')
0033.00 Q2: QUAL TYPE(*NAME) LEN(10) DFT(SQLOUT)
0034.00 QUAL TYPE(*NAME) LEN(10) DFT(QTEMP) +
0035.00 SPCVAL((*LIBL) (*CURLIB)) PROMPT('Library')
0036.00
0037.00 PARM KWD(OUTMBR) TYPE(E2) PMTCTL(PC2) +
0038.00 PROMPT('Output member options')
0039.00 E2: ELEM TYPE(*NAME) LEN(10) DFT(*FIRST) +
0040.00 SPCVAL((*FIRST)) PROMPT('Member')
0041.00 ELEM TYPE(*CHAR) LEN(8) RSTD(*YES) DFT(*REPLACE) +
0042.00 VALUES(*REPLACE *ADD) PROMPT('Replace or +
0043.00 add records')
0044.00 PC2: PMTCTL CTL(OUTPUT) COND((*EQ *OUTFILE))
****************** End of data ****************************************
[report a broken link by clicking here]