× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. June 2000

RUNSQL, was 'RE: Change Column Heading for a field'

  • Subject: RUNSQL, was 'RE: Change Column Heading for a field'
  • From: Rob Berendt <rob@xxxxxxxxx>
  • Date: Thu, 1 Jun 2000 8:56:36 -0500
 
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 thread ...
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.