2

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.

Sarah Kemp
  • 2,670
  • 3
  • 21
  • 29
  • What exactly does "-- this causes a failure when run through JDBC" mean? – Charles Dec 22 '16 at 11:57
  • When I try to run the SQL file through PhpStorm (using a JTOpen connection), it treats it like two statements (as if there were a terminator after the SET OPTION statement). On the first statement I get `[42601][-104] [SQL0104] Token was not valid. Valid tokens: DAY GET SET DROP LOCK PATH WHEN YEAR.` On the second I get: `[42601][-29] [SQL0029] INTO clause missing from embedded statement.` Running the same statement through Navigator it is successful. – Sarah Kemp Dec 22 '16 at 17:27
  • If you want to use a literal parm to CALL a stored proc, the input parm needs to be declared at least as a VARCHAR. The quoted literal will be implemented as a VARCHAR. How was `MYLIB.CCSQL` defined? – user2338816 Dec 24 '16 at 04:50
  • `CREATE PROCEDURE MYLIB.CCSQL ( IN SQL_FILE CHAR(100) ) LANGUAGE CL SPECIFIC MYLIB.CCSQL NOT DETERMINISTIC MODIFIES SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'MYLIB/CCSQL' PARAMETER STYLE GENERAL ; ` – Sarah Kemp Dec 27 '16 at 18:42

2 Answers2

2

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')
Charles
  • 21,637
  • 1
  • 20
  • 44
  • Thank you for the explanation. The idea of this program is to be able to pass different file names (of different lengths), so I don't think I can manage with changing the parm to char(32). I also intend to call it through JDBC, and I don't know if a command front end would work for that? I would also be typing the filename in each time, so it's unlikely I could reliably count out 101 characters each time... There is nothing I can do in the CL program to deal with the "missing" characters? – Sarah Kemp Dec 21 '16 at 22:53
  • You should be able to call the program with an SQL CALL, but you should probably define it as an "external" stored procedure. – Charles Dec 22 '16 at 12:00
  • @SarahKemp A command is the preferred way to pass CL variables as parameters for any type of "command line" CALL because it allows you to define parameters, even run via JDBC. BTW, a CL "CALL" is just as much a "command" as any that you create; it just allows undeclared parameters. So your command would be just as appropriate. – user2338816 Dec 22 '16 at 13:26
  • I did set it up as an "external" stored procedure, that is why I am unsure how a *CMD object front end would fit in - are the two compatible? Or how would I call a *CMD object front end via JDBC? – Sarah Kemp Dec 22 '16 at 17:29
  • If it's defined as an external SP, you don't need a command. A JDBC call is not the same as a CALL from the command line. Post your SP definition and the Java code trying to call it. – Charles Dec 22 '16 at 21:24
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
danny117
  • 5,581
  • 1
  • 26
  • 35