Re: easier SQL statements in CL

I too got tired of trying to read the confusing *CATs, *TCATs, ||, and
+s need to include a SQL statement in CL. So, I wrote a little program
to imitate the substitution function that the RPG cursors do (we are
still on V5R4).



The process is setup so I can write a SQL command in a source file
member and add variables to it using the :name convention. You would
not necessarily need to use :name but it has worked well for me so far.



The first procedure takes a SQL source file member and copies it to a
temp name in a QTEMP source file. The second procedure takes the field
and its replacement value and changes any occurrences in the temp source
file member. The second procedure would be repeated as many times as
needed. After all of the substitutions have been completed, a third
procedure runs the SQL source file member using the RUNSQLSTM command
and then deletes the temp member.



The way the procedures work, the source file member can have multiple
SQL statements with each statement have a ";" at the end. The same
variable can be repeated in the source member if needed.



For example, below is one of my SQL source file members:



Source file: LEXCHGS/TXTSRC Member: LPD480P3E



*************** Beginning of data *************************************

/**-----------------------------------------------------------------**/



DELETE FROM QTEMP/DLTFDOC;



INSERT INTO QTEMP/DLTFDOC

SELECT DIDOC# FROM NWSPSFLS/PSDOCI

WHERE (DIFILE = 'PLCASE'

OR DIFILE = 'PLOFNS'

OR DIFILE = 'PLPROP'

OR DIFILE = 'PLSPVM'

OR DIFILE = 'PLSVWM')

AND SUBSTR(DIFKEY,1,12) = ':PCASE#' <== will be
replaced

AND SUBSTR(DIFKEY,32,9) = 'KY0340200'

AND SUBSTR(DIDOC#,1,1) = 'C'

AND DIDCU = 'LPD480R'

FETCH FIRST 1 ROW ONLY;



/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

INSERT INTO LEXCHGS/PSFDOCD1

SELECT * FROM NWSPSFLS/PSFDOC

WHERE FDDOC# =

(SELECT * FROM QTEMP/DLTFDOC);

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/



DELETE FROM NWSPSFLS/PSFDOC

WHERE FDDOC# =

(SELECT * FROM QTEMP/DLTFDOC);



DELETE FROM NWSPSFLS/PSDOCI

WHERE SUBSTR(DIFKEY,1,12) = ':PCASE#' <== will also be
replaced

AND SUBSTR(DIFKEY,32,9) = 'KY0340200'

AND (DIFILE = 'PLCASE'

OR DIFILE = 'PLOFNS'

OR DIFILE = 'PLPROP'

OR DIFILE = 'PLSPVM'

OR DIFILE = 'PLSVWM')

AND DIDOC# =

(SELECT * FROM QTEMP/DLTFDOC);



/**-----------------------------------------------------------------**/



The snippet out of the CL would be:



/**~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~**/

DCL VAR(&PCASE#) TYPE(*CHAR) LEN(12)

DCL VAR(&MBRNME) TYPE(*CHAR) LEN(50)

DCL VAR(&SQLFLD) TYPE(*CHAR) LEN(50)

DCL VAR(&SQLDTA) TYPE(*CHAR) LEN(50)

DCL VAR(&RC) TYPE(*CHAR) LEN(1)



CHGVAR VAR(&PCASE#) VALUE('201200002117') <==
replacement data



/** CREATE TEMP WORK FILE IN QTEMP **/

CRTDUPOBJ OBJ(DLTFDOC) FROMLIB(*LIBL) OBJTYPE(*FILE) +

TOLIB(QTEMP)

MONMSG MSGID(CPF2130)

/** CREATE TEMP WORK SQL SOURCE FILE ONCE **/

CRTSRCPF FILE(QTEMP/TXTSRC) RCDLEN(92) +

TEXT('temp lib source file to update SQL variables')

MONMSG MSGID(CPF7302) /* ignore file exists error */




CHGVAR VAR(&MBRNME) VALUE('LPD480T3E')



/** CREATE WORKING COPY OF SQL MEMBER **/

CPYSRCF FROMFILE(LEXCHGS/TXTSRC) +

TOFILE(QTEMP/TXTSRC) FROMMBR(&MBRNME)



/** OVER RIDE TEXT FILE TO WORKING FILE NAME **/

OVRDBF FILE(TXTSRC) TOFILE(QTEMP/TXTSRC) MBR(&MBRNME)




/** SET SQL VARIABLE NAME & VALUE; CALL PGM TO UPDT SQL WORKG MBR **/

/** SQL VARIABLE NAME & VALUE REMAIN THE SAME FOR ENTIRE JOB **/

CHGVAR VAR(&SQLFLD) VALUE(':PCASE#') <== matching
replacement name

CHGVAR VAR(&SQLDTA) VALUE(&PCASE#) <==
replacement data field

CALL PGM(UTLSQL2R) PARM(&SQLFLD &SQLDTA &RC)

DLTOVR FILE(*ALL)



RUNSQLSTM SRCFILE(QTEMP/TXTSRC) SRCMBR(&MBRNME) +

COMMIT(*NONE)

/**~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~**/



The RPGLE source for procedure UTLSQL2R is as follows:



*************** Beginning of data *************************************

F*****************************************************************

F* *

F* SQL: Change a Variable in a SQL Member b4 running *

F* *

F*****************************************************************

ftxtsrc up e disk

F RENAME(txtsrc:txtsrcr)

*

C eval rc = ' '

* scan and replace sql variable

C exsr scanReplc

C*

C*******************************************************

C* scan replace amper(&) codes & CRLFs *

C*******************************************************

CSR ScanReplc BEGSR

C*

C z-add 0 tPos 5 0

C z-add 0 tLen 5 0

C*

C z-add 1 tstlop 5 0

C tstlop doueq 0

C EVAL tLen = %len(%trimr(sqlF))

C EVAL tPos = %scan(%trimr(sqlF): srcdta)

C tPos ifgt 0

C Eval SRCdta =

C %replace (%trimr(sqlD): SRCdta:

C tPos: tlen)

C update txtsrcr 50

C Else

C z-add 0 tstlop 5 0

C ENDIF

C ENDdo

C*

CSR ENDSR

* in parameters

C *entry plist

C parm sqlF 50

C parm sqlD 50

C parm rc 1

****************** End of data ****************************************



I am sure I adapted (stole) a lot of the RPGLE coding from someone
else's previous example.



This process might not be the most elegant in the world but it helps me
wrap my mind around the SQL statement with out a lot of extra work.



It has sure saved me from a lot of errors and a lot of time in working
out the concatenation issue on my SQL. This is fairly simple and does
not have a lot of parts that might break.



Let me know if you would need further information.



Chuck Sallee

Lexington Police

Lexington, Kentucky