Re: Cannot turn off auto commit?

Just for reference in case it came across a deficiency of hsqldb in
particular, I am not aware of any databases (Oracle, mysql, hsqldb, h2, or
indeed non-relational dbs such as dynamo) that provide transactional DDL.
Data is committed but schemas are definitely not.

Here is a helpful guide to the DDL basics:
https://en.wikipedia.org/wiki/Data_definition_language.

If you are a Java developer then frameworks such as Hibernate can be
configured to check and create tables as they are needed (EG You just
worrying about inserting and deleting rows and if the table isn't there
it'll be created with all the right fields. Equally if the table requires
an change like a new column it can do this too) and are a superb
abstraction from dealing with JDBC datasources directly. Typically it is
not required to be so low level to the mechanism of connection as this and
code will look cleaner if you take advantage of existing solutions.

Thanks kindly,
Daniel Stoner

On 7 September 2016 at 23:00, Fred Toussi <[email protected]>
wrote:

> Non-transactional DDL has always been the case.
>
> If your DDL statements do not contain syntax errors or references to
> non-existent database objects, then they won't fail the first time they
> are executed.
>
> The IF NOT EXISTS clause can be used with all database objects.
>
> You can query the INFORMATION_SCHEMA views to check if a table or other
> object exists, before creating database objects.
>
> You can always drop the whole schema if you want to recreate it.
>
> Fred
>
> On Wed, Sep 7, 2016, at 22:17, [email protected] wrote:
> > Hello!
> >
> > On 2016-09-07T22:08:06 +0100
> > Fred Toussi <[email protected]> wrote:
> > >
> > > A DDL statement is not transactional. A commit is executed implicitly
> > > both before and after the statement execution.
> > >
> > > If you want to avoid the exception when the statement is executed the
> > > second time, use:
> > >
> > > CREATE CACHED TABLE IF NOT EXISTS broken ...
> > >
> > > Statements such as INSERT or DELETE are transactional and will be
> rolled
> > > back when you call rollback() on the connection.
> >
> > That's bizarre. Has this always been the case?
> >
> > I remember a few years back trying to decide between HSQLDB and H2, and
> > picking HSQLDB because DDL statements appeared to be transactional.
> >
> > Without transactional DDL, how do I guarantee that my database is set
> > up correctly and atomically? With implicit commits, any failure means
> > that I end up with a half configured database that has to then be
> > destroyed and created from scratch.
> >
> > M
> > ------------------------------------------------------------
> ------------------
> > _______________________________________________
> > Hsqldb-user mailing list
> > [email protected]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
> > Email had 1 attachment:
> > + Attachment1.2
> >   1k (application/pgp-signature)
>
> ------------------------------------------------------------
> ------------------
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>



-- 
Daniel Stoner | Senior Software Engineer UtopiaIT | Ocado Technology
[email protected] | Ext 7969 | www.ocadotechnology.com

-- 


Notice:  This email is confidential and may contain copyright material of 
members of the Ocado Group. Opinions and views expressed in this message 
may not necessarily reflect the opinions and views of the members of the 
Ocado Group. 

 

If you are not the intended recipient, please notify us immediately and 
delete all copies of this message. Please note that it is your 
responsibility to scan this message for viruses. 

 

Fetch and Sizzle are trading names of Speciality Stores Limited and Fabled 
is a trading name of Marie Claire Beauty Limited, both members of the Ocado 
Group.

 

References to the “Ocado Group” are to Ocado Group plc (registered in 
England and Wales with number 7098618) and its subsidiary undertakings (as 
that expression is defined in the Companies Act 2006) from time to time. 
 The registered office of Ocado Group plc is Titan Court, 3 Bishops Square, 
Hatfield Business Park, Hatfield, Herts. AL10 9NE.

------------------------------------------------------------------------------

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user