Hi Andrea!
Creating a table is DDL (data definition language), inserting into it is
DML (data manipulation language). I NEVER mix DDL and DML in the same
transaction even though it is possible (though I don't think you can use
a table before the transaction that created it is committed).
One thing you can try for fun on a test database, is to use EXECUTE
BLOCK, check if the table exist (SELECT * FROM RDB$RELATIONS WHERE
RDB$RELATION_NAME = 'ANDREA_TABLE'), if not create it using EXECUTE
STATEMENT ... IN AUTONOMOUS TRANSACTION and after that use another
EXECUTE STATEMENT ... IN AUTONOMOUS TRANSACTION for the INSERT (the
table will be created in an autonomous transaction that started after
your main transaction, so it is probably not visible otherwise). I've no
idea whether or not this will work (I don't even know whether
"autonomous transaction" is possible from "execute block"), but I
wouldn't be very surprised if this was a possible way to corrupt your
database - e.g. I've no clue what happens if two transactions try to
create the same table simultaneously.
Why do you want to create the table on the fly? I'd rather create such
tables in advance. A few empty, unused tables doesn't have much impact
on most databases (though, of course, I don't know whether you're
talking about doing this type of create/insert for one or possibly one
million tables, and lots of tables could possibly have an effect on
performance - at least for tools that read all metadata at startup).
Set
Den 10.12.2015 15:33, skrev Andrea Raimondi [email protected]
[firebird-support]:
Hello!
I need to update several records in a table.
Obviously, the idea of doing so without being able to compare to the
previous values does not fill my heart with joy :)
I have found a guide saying that I can insert into a table by
using a SQL statement - and that's fine, I like that.
Alas, the table must already exist, so I am wondering if
someone knows of a way to also create it in the same
statement. It's one of the few things (VERY FEW things) I
miss from Oracle.
Please help :)
Thanks!
A
