For compiling a SQLRPLE program, I've noticed that the precompiler checks the file as it is in the library list.
I don't know if the ALTER TABLE worked or not in your case, you didn't specifically say, meaning did it add the new columns to the *FILE object or not.
But since the pre-compiler does its thing before the program gets created and therefore before the ALTER TABLE can run at all, the pre-compiler cannot see the new columns in the existing file and so balks at compiling.
In my opinion this is a hole that should be fixed by the pre-compiler team if possible. But it is not as simple an issue for them, because theoretically they have no way of knowing how the program logic works, and whether the ALTER TABLE will execute before the new columns are used. If they were to begin going down that "rabbit hole", they would have to pretty much analyze the logic enough to almost re-write the compiler.
WORK-AROUND: Make sure the current file is not in the library list in the job where the compile happens. That's how I've done it with similar misleading error messages where I had added fields to my SQLRPGLE code that creates the table. Of course that also means extra logic for preserving the data.
--Alan Cassidy
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, October 20, 2015 7:47 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Pre-compiler Issue
Altering a table, and then modifying the new columns is probably not something the compiler team thought of. I can see an application for this from software vendors and their version upgrade programs.
I would suggest that if you feel this is a worthwhile feature then I suggest you first open a PMR to see if there is some possible thing you may be doing wrong. Failing that, then I would submit a request for a design change. Having a pmr number will at least allow you to fill in the optional fields on the DCR. IDK if it helps.
PMR site:
http://www.ibm.com/support/servicerequest
Design Change Request site:
https://www-912.ibm.com/r_dir/ReqDesChange.nsf/Request_for_Design_Change?OpenForm
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: Jonathan Mason <Jonathan.Mason@xxxxxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 10/20/2015 04:19 AM
Subject: SQL Pre-compiler Issue
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
All
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.
If he can set the GENLVL to 31 to get past the pre-compiler error, but
then the RPG compiler fails as the generated CLI code is incorrect.
We know that we can code the statement dynamically to get round the
issue, but as a rule we try to avoid dynamic SQL. 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?
Thanks
Jonathan
________________________________
Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts
Festival.
Registered Address: Ageas House, Hampshire Corporate Park, Templars Way,
Eastleigh, Hampshire, SO53 3YA Registered Number: 354568 England
Authorised by the Prudential Regulation Authority and regulated by the
Financial Conduct Authority and the Prudential Regulation Authority
This e-mail together with any attachments are intended for the addressee
only and may be private and confidential. If you are not the intended
recipient, or the person responsible for delivering it to the intended
recipient, you must not open any attachments, or copy, disclose,
distribute, retain or use this e-mail, including any attachments, in any
way whatsoever; please return it to us immediately using the reply
facility on e-mail.
Consider the environment and think before you print this email.
--
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.
--
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.
--------------------------------------------------------------------------------
Confidentiality Notice: This email may contain confidential information or information covered under the Privacy Act, 5 USC 552(a), and/or the Health Insurance Portability and Accountability Act (PL 104-191) and its various implementing regulations and must be protected in accordance with those provisions. It contains information that is legally privileged, confidential or otherwise protected from use or disclosure. This e-mail message, including any attachments, is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Thank you.
--------------------------------------------------------------------------------
midrange.com
