CL: Path name contains embedded nulls (CPD018A)

Question

I am trying to write a simple CL program to take a file name parameter and run the SQL in the file with COMMIT(*CHG) and DBGVIEW(*SOURCE). I am getting CPD018A: Path name contains embedded nulls:

Cause . . . . . : Path name /SQL/TRIGGERS/PCUSTOMERS_INSERT
???????????????????????????????????????????????????????????????????????.SQL
specified for SRCSTMF contains one or more embedded nulls (X'00'). Nulls are not allowed in a path name.

Here is my program:

PGM        PARM(&FILE)                                  
DCL        VAR(&FILE) TYPE(*CHAR) LEN(100)              
RUNSQLSTM  SRCSTMF('/SQL/' || %TRIM(&FILE) || '.SQL') + 
         DBGVIEW(*SOURCE)                           
ENDPGM                                                  

I am calling the program like: CALL CCSQL PARM('TRIGGERS/PCUSTOMERS_INSERT').

This may just be a terrible workaround the real problem: I want to add SET OPTION statements in my trigger:

CREATE OR REPLACE TRIGGER QS36F.PCUSTOMERS_INSERT
INSTEAD OF INSERT ON QS36F.PCUSTOMERS
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
SET OPTION DBGVIEW =*SOURCE -- this causes a failure when run through JDBC
BEGIN
...

I haven't been able to figure out how to get the SET OPTION statements to go through JDBC, so I have to copy the statements into iSeries Navigator everytime. I thought I could improve this workflow with an external procedure calling this CL program to handle the precompile options before calling the SQL in the stream file, but I can't get the CL to run with the parameter I send to the procedure (or when I call it from the command line) CALL MYLIB.CCSQL('TRIGGERS/PCUSTOMER_INSERT');

I'm so far from my original goal at this point... any suggestions are very welcome. If it helps to know, I'm a PHP/SQL kind of person trying to make it on a iSeries 7.2 system.

Thank you for reading.

Answer 1

The call doesn't work because of a idiosyncrasy of the command line interpreter and literal values.

• parameters are always passed by reference, meaning a memory address is placed on the stack.
• memory is allocated for a string literal as if it is a char(32); and padded with blanks. Unless the literal is longer, in which case memory is allocated for the length of the string.
• memory is allocated for a numeric parameter as DEC(15,5).

So you see, 32 bytes are being allocated, but your program is looking at 100 bytes. Thus the infamous "why does my CL program parameter have garbage in it"

Three solutions

• Build a command (*CMD object) front end, this will allow the command line interpreter to know exactly how long your parms are.
• change the parm to be char(32)
• pass 101 bytes, with the 101st being non-blank

(like so)

CALL CCSQL PARM('TRIGGERS/PCUSTOMERS_INSERT                                                                          x')

Answer 2

Here is a sample procedure created for debugging.

CREATE PROCEDURE myschema.ship_it(IN ordnum INTEGER, IN ordtype CHAR(1),
 IN ordweight dec(3,2))
LANGUAGE SQL
SET OPTION DBGVIEW =*SOURCE
sp: BEGIN
DECLARE ratecalc DECIMAL(5,2);

/* Check for international order */
IF ordtype='I' THEN
 SET ratecalc = ordweight * 5.50;
 INSERT INTO wwshipments VALUES(ordnum,ordweight,ratecalc);
ELSE
 SET ratecalc = ordweight * 1.75;
 INSERT INTO shipments values(ordnum,ordweight,ratecalc);
END IF;
END