× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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."


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.