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.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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].