Faking Create Or Replace Table

January 20, 2015
Ted Holt

The new CREATE OR REPLACE feature of SQL has been most helpful to me. It works for aliases, functions, masks, permissions, procedures, sequences, triggers, variables and views. It would be nice if it worked for tables, especially when I’m developing a new application. Here’s a workaround.

One of the things that dynamic compound statements let you do is include conditional logic, which is what we need. We need a way to drop a table if it exists, and not error out if the table does not exist.

The dynamic compound statement is the begin-end block that spans the second through the eighth lines. I’ve defined a continue handler to run an empty begin-end block when an exception occurs. The continue handler takes control when a drop table ends abnormally.

This technique is not exactly like Create or Replace. There is one big difference. When you delete an object, the system discards all object authority for that object. Create or Replace retains object authority.

Share this:

RDB Connect gives developers full SQL access to other IBM i databases and to remote databases from an RPG, COBOL or CL program on the IBM i.

The newest release features a menu-driven interface, improved functionality and seamless error handling. RDB Connect 4.0 is available on the ProData website and can be downloaded and used for 30 days at no charge.