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



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.

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.