Use Dynamic SQL that is based on a Version/Feature flag. Put the Version flag into a table that is read at the beginning of the program.
Something like this:
SQLStatement = 'Select Field1, Field2, Field3';
If Version >= 23.1;
SQLStatement = %Trim(SQLStatement) + ' ,Field4, Field5, Field6';
EndIf;
SQLStatement = %Trim(SQLStatement) + ' Into ?, ?, ?';
If Version >= 23.1;
SQLStatement = %Trim(SQLStatement) + ' ,?, ?, ?';
EndIf;
SQLStatement = %Trim(SQLStatement) + ' From Table';
Chris
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Reeve <rfritchman@xxxxxxxxx>
Date: Monday, June 12, 2023 at 6:24 PM
To: RPG programming on IBM i <RPG400-L@xxxxxxxxxxxxxxxxxx>
Subject: Handling SQLSTT=42703
I have customers running different versions of my transportation management
system application and I want to ignore columns that my newest code may
reference but some customers' databases don't support. My main goal is to
eliminate commenting out chunks of code when sending code updates; another
goal is preventing SQL0206 messages ("Column not found") from showing up in
the job log. Finally, I'd like this solution to be upwardly compatible: if
the table gets updated and missing columns are added, the code should
perform without additional source code changes.
Currently, I'm putting the new columns in a separate SQL SELECT. If I get
SQLSTT='42703' , the column doesn't exist in the table and I skip the
associated business logic (but I still get the SQL0206 in the job log). If
I get a '00000' or '02000' means the column exists and I execute the new
logic. My coding standards don't allow "unmonitored" application
error/exception messages, like SQL0206, in the job log. The code in
question is in a service program so the solution doesn't have to be
duplicated in other source.
Reasonable solutions:
1. Look for the column in the SYSCOLUMNS table. If the row is found,
execute the new logic; if not, skip it. When dealing with one or two new
columns, this is not bad. More new columns add a slight performance
penalty and the need to code more logic. It survives the upgrade scenario.
Rejected solutions
1. Copy/include's are not bad but would require a lot of reengineering
to ensure that the solution for this instance worked across the entire
application.
2. Reading the row into a locally-defined data structure. I can
foresee that this could become a mess.
3. Build a view and JOIN in a blank column to act as a proxy for the
missing column in the base table.
4. SELECT IFNULL(new_column,'A') doesn't work.
5. Add the column to the end of the table and use LVLCHK(*NO). Remember
when the Emperor was using lightning fingers on Luke Skywalker? That's
exactly how I feel when merely bringing this idea up in public. Like Luke,
I mean.
6. Add the column to the table where it belongs and compile a couple of
hundred programs (the entire exercise would take 30 minutes or less). I
have a utility to identify level-check errors but it's a big ask of a
customer (IT department: manager, systems programmer, apps programmer,
operator) running 7x24x365 with an HA site as well. For now, only one
service program is looking at this column so the reasonable solution will
take far less effort than coordinating a database change in a production
system. This is truly the right solution: it precludes building dozens of
extension and add-on files in a typical application; it simplifies file
maintenance programming; there are fewer file open's required; and querying
is simplified. Systems need to go off-line periodically for PTF's and
hardware maintenance so it's good discipline for IT and the business to
plan for periodic brief outages over long weekends. CHGPF makes the
exercise a no-brainer and a good excuse for pizza on the Company's dime.
Just saying.
Tol-ja-so solutions
1. Use SQL and defined columns (i.e., not "SELECT *") in all programs.
Problem solved with an ALTER TABLE.
What I'd like is to use an EXISTS clause or using a VALUES statement. Am I
sniffing at a good solution or is there something else I've missed?
Thanks!
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l<
https://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/rpg400-l<
https://archive.midrange.com/rpg400-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
midrange.com
