Hi,
I'm trying to detect changes in our application schema and update the
corresponding database schema automatically, but only when the table in
question is actually needed (i.e. a sort of on-demand schema update).
The problem I'm having is when other statements are open when this
occurs, in which case I seem to be getting a "SQL logic error or missing
database" error when I try to run CREATE TABLE.
For example, imagine I have a statement that I'm iterating. One of the
results requires that I retrieve additional info from another table
(e.g. for a dependent or child object). Before I do that, I determine
that the schema for the application object stored in that second table
has changed. So I call:
ALTER TABLE foo RENAME TO _foo;
CREATE TABLE foo;
INSERT INTO foo SELECT [automatic generated column list for conversion]
FROM _foo;
DROP TABLE _foo;
But I get the above-mentioned error when CREATE TABLE is executed. Note
that I call BEGIN IMMEDIATE before preparing the original statement that
I'm iterating, so all of this is happening inside a transaction.
Is there a hard restriction on creating tables while statements are
open? If not, can anyone shed any light on the specific circumstances
which might be causing this error?
Thanks in advance,
Matt
