Subject Re: [firebird-support] Creating and filling a backup table for testing purposes (FB2.4 W10 x64)
Author Andrea Raimondi
Post date 2015-12-10T22:20:20Z
Hi Set!

While I appreciate the distinction and tend to apply it obnoxiously (remember: I am still the guy who almost
ripped a new one to a bloke who suggested in this list to use numbers in the order by...), there are some
cases where practicality - in my view - trumps ideology.

Backup tables for testing purposes squarely falls in this camp :)

I do agree it's *incorrect* but it's very handy and practical, especially when the database in
question is one that you do not know well and you're not sure what kind of constraints there may be
which make a blind DDL copy risky. Such an option would entirely avoid the issue by only
copying the structure with no constraints - removing the need to think and get distracted from the
task at hand (which is why the most widely known usability book is titled "Don't make me think").

A


On Thu, Dec 10, 2015 at 10:25 PM, setysvar setysvar@... [firebird-support] <firebird-support@yahoogroups.com> wrote:


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]:
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





--
Mr. Andrea Raimondi
Senior Software Analyst&Developer