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.
midrange.com
