Guru: Dealing With RPG Errors and Embedded SQL
April 8, 2019 Ted Holt
Hey, Ted! I’m having trouble using some of the new techniques I learned at the RPG and DB2 Summit. Below is a screen shot of a program I am writing. I cannot figure out why the compiler doesn’t like it. Can you see anything that would be causing the declarations to fail?
I glanced over Mike’s code and noticed that he used a correlation name in the SELECT and WHERE clauses, but did not define that correlation name for any of the tables, like this:
SELECT x.onefield, x.twofield, x.redfield, x.bluefield FROM MYTABLE WHERE x.onefield = :TestValue;
He added the correlation name to FROM, but that did not completely solve the problem. Let me show you some more code and you’ll see what I should have seen.
dcl-s TestValue dec(3);
You are correct. The data type should be packed, not dec.
The reason I didn’t see it is that dec makes sense to me. It is easy for someone who works in several programming languages to read source code without seeing such syntax errors, and I use *DEC in CL programs all the time. Fortunately, one of Mike’s coworkers saw the mistake and the story had a happy ending. The program compiled and ran correctly.
The question I asked myself is why the system did not do a better job of pointing out such a simple error. If Mike had been using SEU to edit the source code, I could understand. SEU doesn’t understand DCL-S — never has, never will. But Mike was using RDi.
The answer comes to this: it would be unrealistic for the SQL precompiler, and by extension the RDi syntax checker, to fully include RPG syntax checking in an RPG program with embedded SQL. They can only do so much.
So how do you determine the cause of those less-than-ideal SQL precompiler messages, especially SQL0312 (Variable &1 not defined or not usable) and SQL5011 (Host structure array &1 not defined or not usable) when the error comes from a violation of RPG syntax?
One easy way is to comment out the SQL statements and try to compile the program with a command that invokes the RPG compiler, such as Create Bound RPG Program (CRTBNDRPG). Rather than comment out each statement, I like to use an undefined compiler directive.
dcl-s RecCount dec(3); *inlr = *on; /if defined(xyz) exec sql select count(*) into :RecCount from qiws.qcustcdt; /endif return;
The RPG compiler listing shows me the problem.
1 dcl-s RecCount dec(3); ======> bbbbbbbb aaa *RNF3308 20 a 000100 Keyword name is not valid; the keyword is ignored.
While I’m on the subject, let me share some good news. IBM is improving the quality of the messages coming from the SQL precompiler. Enhancements in versions 7.2 and 7.3 cause the precompiler to append a reason description to messages SQL0312 and SLQ5011. To enable these enhancements, install a PTF.
In the next release, the reason will be incorporated into the message text with a reason code.
See the link below for more information about these enhancements.
Thanks to Scott Forstie and Sue Romano of IBM for this wonderful news!