Re: Select * into an external DS, and then adding columns

Good point.

Anyone know when this changed? And how to tell? Like "any program
compiled for a target release of x and after mm/dd/yy"? To cover both a
release and a TR date when the fix came out? Or something even better
just in case your vendor lags behind on fixes?

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: darren@xxxxxxxxx
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 02/05/2016 03:55 PM
Subject: Re: Select * into an external DS, and then adding columns
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Keep in mind that this new 'select *' behavior in the pre-compiler may
only
benefit newer compiled programs. Said another way, make sure that the
fetch doesn't crash on older programs supplied by the vendor.





From: rob@xxxxxxxxx
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxx>
Date: 02/05/2016 03:17 PM
Subject: Re: Select * into an external DS, and then adding
columns
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Judging by what I am seeing in STRDBG of the generated program I think
y'all are right. The precompiler does seem to be getting a snapshot of
the columns at the time and not really doing a 'SELECT *'.
I like this!

I tested doing an
ALTER TABLE RCO ADD COLUMN...

On an Infor (BPCS) table (RCO).

Since their 'traditional' or RLA reads always use the logical files and
not the physicals,
and the logicals specify each column they want from the physical,
both the RLA maintenance program,
and the SQL 'select *' report program,
worked fine without any recompiling.

Adding a column to a physical does not alter the record format level id of
the logicals.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Mike Jones <mike.jones.sysdev@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 02/05/2016 02:40 PM
Subject: Re: Select * into an external DS, and then adding columns
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Hi Rob,

I would swear that ~12 years ago I fielded a support issue where SELECT *
was used in RPG, the table was modified, the program was not altered or
recompiled, and it failed in production.

Given that was so long ago, it could just be my memory playing tricks on
me.

I think at some point, the RPG SQL pre-compiler was changed, when SELECT *
is present, to go out and fetch the list of columns at RPG pre-compile
time and hard code the current list of columns in the program. I just
learned of that this week. Nice change imo, but it still is not a good
idea to use SELECT * in programs, except in some rare cases.

Mike

date: Fri, 5 Feb 2016 13:45:51 -0500

from: rob@xxxxxxxxx
subject: Select * into an external DS, and then adding columns

Scenario:

You have something like this:
D RCODS E DS EXTNAME(RCO)
C/EXEC SQL
C+ DECLARE C CURSOR FOR
C+ SELECT * FROM RCO
C+ WHERE CMPNY BETWEEN :
C+ W1LWCO AND :W1UPCO AND CRSTS = 'A'
C+ ORDER BY CMPNY FOR FETCH ONLY
C/END-EXEC
C/EXEC SQL
C+ OPEN C
C/END-EXEC
C/EXEC SQL
C+ FETCH C INTO :RCODS
C/END-EXEC
You add a column to the table, and do NOT recompile this program.
Therefore the external DS doesn't match.
ALTER TABLE RCO
ADD COLUMN ...

I thought (many releases ago) that this used to generate a mild warning.
Then it became a hard halt.
I just tried it with seclvl 0 logging and STRDBG running and no warning
whatsoever. The data came from the cursor and worked just fine.

Rob Berendt

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.