Hi, Brian,
(Sending again, to fix poor formatting due to HTML e-mail vs. "plain text" e-mail ...)
Looking at the output of PRTSQLINF, you can see that what is executed at "run-time" is in fact a "SELECT * FROM ..." ... and so, if some columns have changed, either changes to the definitions (data type, length, etc.) or fields were added or removed, the compiled code is expecting certain columns to be populated that may no longer exist.
The whole issue is, what happens in those cases?
It is not that much extra work to just go ahead and list all of the fields you want to include on the select statement, at compile time. This will avoid this issue in most cases. And, when you are going to "remove" a column from a table, you can do a FNDSTRPDM or equivalent to find all the programs that are using that column name, so you know which ones may need to be adjusted.
If you just code "SELECT * FROM ..." I do not think you can rely upon FNDSTRPDM or similar tools in that case.
RFE?
====
Perhaps another way to resolve this would be an RFE to request a new compiler option on the CRTRPGSQLI command, and a new SQL SET ... option, to specify that the SQL pre-processor should transform such an embedded SQL statement, e.g.:
SELECT * FROM QIWS/QCUSTCDT
to its equivalent fully-expanded form:
SELECT CUSNUM, LSTNAM, INIT, STREET, CITY, STATE, ZIPCOD, CDTLMT,
CHGCOD, BALDUE, CDTDUE FROM QIWS/QCUSTCDT
and that way, you at least have some "protection" from the problems of using "SELECT *" in a compiled application program using embedded SQL.
Anyone agree or disagree? Who wants to type up the RFE? :-)
Hope that might help ... just my two cents.
All the best,
Mark S .Waterbury
On Tuesday, May 5, 2020, 12:52:14 PM EDT, Brian Parkins <goodprophet.bp@xxxxxxxxx> wrote:
Unless I am missing something, the PRTSQLINF report appears to provide
little useful information in this context, i.e. which columns the SELECT
* delivers. Apart from a repetition of the compile command, all I get is:
SELECT * INTO : H ,: H ,: H FROM xxxxxx / TABLETEST
SQL4021 Access plan last saved on 20-05-05 at 16:29:17.
SQL4020 Estimated query run time is 0 seconds.
SQL4010 Table scan access for table 1.
Is there more I should be seeing?
Brian.
On 04/05/2020 16:22, Mark Waterbury wrote:
Rather than speculating about what may or may not be happening at "compile-time" versus "run-time", use the PRTSQLINF command ...
The output will show you what the SQL precompiler has stored within the program object -- this will show you what SQL statements will be executed "at run-time."
midrange.com
