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.
The issue arises because if the references exist, then the statements
will be syntax-checked and validity-checked at compile-time. Thus if
the files are not available by the specified name [IMO should also hold
for an overridden name to a non-existent file, but IME that is not
helpful], then the compiler will be unable to do any validity checking.
The effect will be visible in the Print SQL Information (PRTSQLINF) as
msg SQL4013 "Access plan has not been built." or perhaps msg SQL5065
"Access plan not found." for those embedded statements for which the
named file can not be accessed during the pre-compile.
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?
We know that we can code the statement dynamically to get round the
issue, but as a rule we try to avoid dynamic SQL.
But there are two statements, and the ALTER is really quite dynamic
both in nature and effect. And there are better reasons to have an
Access Plan built for an UPDATE than [though AFaIK there is none ever
really created] for an ALTER; ALTER being effective DDL vs DML, despite
the inherent I\O effects for a TABLE with rows. So compiling against
the altered version of the TABLE and coding the ALTER as dynamic
prevents validity checking from being an issue; i.e. in this scenario,
avoiding msg SQL0612 "&1 is a duplicate column name." diagnosed for the
ALTER statement. IMO this is probably the easiest and most sensible
solution, to keep the DML as embedded.
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.
FWiW a pre-compiler could probably be modified to intuit and resolve
the simplest of scenarios without having to override the Severity Level
(GENLVL) to effect passing the pre-compiled source to the RPG, for
example by simply skipping validity checking of any DML statement that
references a table-name from any embedded ALTER, thus decidedly not
creating the access plan. But I would suppose the capability could be
difficult to extend generally without great effort and would be very
little value to the majority of customers; seems something that could be
understood as and resolved by:
Conceptually the scenario is two distinct run-time environments, one
referencing a down-level file [the ALTER statement] and another
referencing the current-level file [the UPDATE statement]. Thus a
typical resolution is to have two compile environments, whereby one
compile is performed against the down-level file [the ALTER statement],
and another compile is performed against the current-level file [the
UPDATE statement]. When the references are qualified, this may be the
more obvious choice than trying to prevent validity checking by ensuring
the objects do not exist.
midrange.com
