Re: SQL Pre-compiler Issue

On 21-Oct-2015 02:03 -0500, Jonathan Mason wrote:

On 20-Oct-2015 10:46 -0500, CRPence wrote:

On 20-Oct-2015 03:18 -0500, Jonathan Mason wrote:

We have a problem with an embedded SQL RPG program that he is
writing. The program uses an ALTER TABLE to add columns to an
existing table and then uses an UPDATE to populate them. The
problem is that the UPDATE statement is kicking out an error as
the pre-compiler doesn't recognise the new columns as belonging
to the table at the time of the update.

Apparently sev(30) msg SQL0206 "Column &1 not in specified tables."
for the embedded UPDATE, because the compile is against the
down-level\pre-ALTERed TABLE.

<<SNIP>>

If he can set the GENLVL to 31 to get past the pre-compiler
error,

Much like when the object references do not exist, the effect is
that no Access Plan will be created for the statement that has
errors [other than TABLE not found; in this case, column(s) not
found].

but then the RPG compiler fails as the generated CLI code is
incorrect.

Not sure what is meant by CLI code? AIUI the SQL pre-compiler for
SQLRPG will still generate RPG code that then gets passed to the
RPG compiler; effectively the code to CALL SQLROUTE is what gets
generated.

What is the failure issued by the RPG compiler? Maybe an example
program [and the pre-requisite\pre-ALTER DDL] could be produced,
that when compiled with the noted compile-command specifications
against that TABLE will reproduce the error being seen?

<<SNIP>>

<<SNIP>>

My question is, should this be something the pre-compiler ought
to be able to deal with or should we be looking at using dynamic
SQL or splitting the program into two?

The pre-compiler should be able to deal with the issue, given the
specification of GENLVL(31); i.e. the pre-compiler should pass the
generated RPG and the RPG compiler AFaIK should be able to create
the program from that source. Thus why I suggested an example might
help elucidate, and asking specifically how the RPG compile fails.
<<SNIP>>

<<SNIP>> when my colleague tried deleting the file first so it
wasn't in the library list the program compiled successfully. The
file only existed in the first place as he had tested each
individual SQL statement interactively before coding in the program.

FWiW: That the GENLVL(31) was not sufficiently effective seems troublesome. Indeed, again, that situation would appear to be "something the pre-compiler ought to be able to deal with". There seems quite possibly to be a defect, at least as described; a defect, that if not there, would have allowed that Severity Level specification to overcome the issue. Effectively, by deleting the file, the problem has been circumvented, but not resolved; i.e. the apparent defect remains to be encountered again in another [or a repeat] incident.

FWiW: In my own attempt to mimic the described scenario on v5r3 [i.e. not an example from which knowingly the problem was exhibited on a new release], and the GENLVL(31) was quite capable of allowing the pre-compiler to complete, allowed the RPG compiler to complete the compile of the generated source, and then the run-time exhibited no problem either. The same effect as was experienced when instead, the problem with the incompatible file was resolved by Delete File (DLTF) [or DROP TABLE]. So again, seems there may be a defect issue, if indeed "the RPG compiler fails" in such a scenario.