| Subject | Re: [firebird-support] Creating and filling a backup table for testing purposes (FB2.4 W10 x64) |
|---|---|
| Author | setysvar |
| Post date | 2015-12-10T21:25:46Z |
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 andrea.raimondi@... [firebird-support]:
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 andrea.raimondi@... [firebird-support]:
I need to update several records in a table.Hello!
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