Re: Cannot turn off auto commit?

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.

Fred

On Wed, Sep 7, 2016, at 21:18, [email protected] wrote:
> Hello.
> 
> The following trivial example seems like it should not commit anything
> to the database, but it does.
> 
>   public static void main(
>     final String[] args)
>     throws Exception
>   {
>     final Properties props = new Properties();
>     props.setProperty("user", "SA");
>     props.setProperty("password", "");
>     props.setProperty("create", "true");
>     props.setProperty("shutdown", "false");
>     props.setProperty("sql.enforce_types", "true");
>     props.setProperty("sql.enforce_names", "true");
>     props.setProperty("sql.regular_names", "true");
>     props.setProperty("sql.enforce_refs", "true");
>     props.setProperty("sql.enforce_size", "true");
>     props.setProperty("sql.enforce_types", "true");
>     props.setProperty("sql.enforce_types", "true");
>     props.setProperty("hsqldb.applog", "3");
>     props.setProperty("hsqldb.sqllog", "3");
> 
>     final JDBCDataSource ds = new JDBCDataSource();
>     ds.setUrl("jdbc:hsqldb:file:/tmp/example");
>     ds.setProperties(props);
> 
>     try (final Connection conn = ds.getConnection()) {
>       conn.setAutoCommit(false);
> 
>       try (final PreparedStatement st =
>              conn.prepareStatement("CREATE CACHED TABLE broken (x
>              INTEGER)")) { 
>         st.execute(); 
>       }
>     }
>   }
> 
> I can add explicit calls to conn.rollback(), but the commit still
> occurs. If I run the program twice, the second execution throws 
> an exception because the "broken" table exists. It should not!
> 
> What am I doing wrong?
> 
> 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)

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