Thanks Chuck. The SQL0204 error is what I've seen in my testing if the file
does not exist.
Other than invoking a CHKOBJ from the program, I just wasn't sure if there
were a simple way with minimal overhead instead of forcing an error.
If it doesn't exist, the program just exits - no harm/no foul in this
situation. Realistically, the range of retro-active updates should pretty
much guarantee that a non-existence would be a rarity - maybe not worth the
worry - other than trapping the error and allowing a graceful exit.
Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
CRPence
Sent: Monday, November 02, 2015 6:39 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Check If File Exists (via RPG)
On 02-Nov-2015 17:48 -0600, Roger Harman wrote:
I have a situation where I need to update/add/delete records in a
historical snapshot file (or files) if the current live file is
changed (retro-active changes).
The historical files are named by year/month and may or may not exist
for a selected record.
Looking for suggestions on the quickest easiest way to verify a file's
existence. This will be in a trigger program.
Normally the DML [UPDATE, DELETE, or INSERT] statement would just fail
with the -204 [SQL0204]; the coded response would be either to ignore that
failure, or to invoke the processing to CREATE the missing TABLE and then
retry the request. In an SQL TRIGGER, a CONTINUE HANDLER for the sqlstate
that correlates to the -204 normally would be used to deal with that issue.
The sqlcode -204 [SQL0204] on a DESCRIBE TABLE is what I recall as
typically being done to test explicitly for existence, *outside of* whatever
processing really needs to be done; e.g. instead of allowing the
UPDATE\INSERT\DELETE DML requests to fail with the -204. If using the
DESCRIBE, then the attributes of the columns also can be verified to match
what is required, additionally, when that request is successful vs failed
per -204.
Or a CREATE TABLE can be issued, if that is going to be the response
anyhow, to the missing TABLE; i.e. the sqlcode -601 [SQL0601] for that
CREATE request [to be clear, *not* using the OR REPLACE syntax] indicates
that the DML should not fail also with the -204. So the table is created if
not there, and the -601 is just ignored when the table is there.
There is also the possibility for use of a PREPARE of a SELECT statement
referring to that table in a table-reference. If the TABLE is very
/complex/ then a prepared statement defining a query that does not require
an effective DESCRIBE, such as a 'SELECT COUNT(*) FROM TABLE_NAME' [for
which no columns are referenced] could outperform the DESCRIBE, even though
the groundwork for a DESCRIBE is being done for that PREPARE anyhow; just no
copying of the data for the record format into the SQL area as the effect of
that DESCRIBE-like work.
Yet, if the DML statements are dynamic due to the /variable/ file-naming,
then those DML statements can serve the same purpose; i.e.
the PREPARE of the INSERT, UPDATE, or DELETE would give the -204 error, just
as would a SELECT statement.
--
Regards, Chuck
--
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.
midrange.com
