Automating DROP TABLE

If you're a fan of script files for CREATE TABLE and other SQL statements, like I am, you've probably written your fair share of DROP TABLE statements. Here's the format I like; it just drops the table, and if the table doesn't exist it ignores the exception and carries on with the CREATE TABLE:

BEGIN
   DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t ( ...

One problem with that code is when some other connection is holding a schema lock on that table: the DROP TABLE fails because the table is in use, you don't see the error because of the empty EXCEPTION WHEN OTHERS THEN clause, and then the CREATE TABLE fails because the table exists.

This can happen quite frequently if you are developing web services that use HTTP sessions as described here. The problem is when you start an HTTP session, a long-lasting database connection is started, and that connection may have a schema lock on the table you're trying to drop.

These HTTP session connections may have no visible client process. Even if you shut down the browser, these connections persist; here is an example where service "s1" has started connection number 3:



The solution is to drop these HTTP session connections before executing the DROP TABLE. Here's the CREATE GLOBAL TEMPORARY TABLE from HTTP Sessions in SQL Anywhere with the additional code to do the DROP CONNECTION and DROP TABLE statements. The FOR loop gets the "@conn" connection number for each connection where the connection name is the same as the web service that started the connection, and the EXECUTE IMMEDIATE drops that connection.

FOR f_drop_conn AS c_drop_conn NO SCROLL CURSOR FOR
SELECT conn.Number AS @conn
  FROM sa_conn_info() AS conn
 WHERE conn.Name IN ( 's1', 's2' ) 
 ORDER BY conn.Number
FOR READ ONLY
DO
   EXECUTE IMMEDIATE 
      STRING ( 'DROP CONNECTION ', @conn );
END FOR;

BEGIN
   DROP TABLE state;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE GLOBAL TEMPORARY TABLE state (
   id    INTEGER NOT NULL DEFAULT 1 
            PRIMARY KEY CHECK ( id = 1 ),
   value VARCHAR ( 100 ) NOT NULL )
   ON COMMIT PRESERVE ROWS;

Astute readers might ask "Why not call sa_locks to find any connections holding schema locks on the table you're trying to drop? That you wouldn't have to code all those web service names in the FOR loop."

The answer seems to be this: sa_locks doesn't show schema locks for a GLOBAL TEMPORARY TABLE in SQL Anywhere 11, at least not yet, so calling sa_conn_info seems to be the kludge of choice... if anyone has a better way, please let me know.