Transaction problem with DDL commands

[jwray]

Hi,

I'm having a strange problem with some current code I'm working on and would be grateful if anyone can point me in the direction of what I'm missing.

I'm using the Spring jdbc support to issue some drop table and create table commands. This all works. I have a higher level method (service layer if you call it that) that combines multiple of these drop/create methods. These service level methods are transactioned using and transaction interceptors. I'm using this approach elsewhere with great results. The annotation on these methods:

@@DefaultTransactionAttribute(TransactionDefinitio n.PROPAGATION_REQUIRED)

Now, when I run the code with an error in one of my create table sql commands I get, in the logs, what I would expect:

- transaction created.
- drop
- drop
- create
- create
- create fails due to Sql error.
- transaction rollback invoked due to sql error.

But, the first two tables created with the successful create commands are still there after this. The rollback should take care of this, shouldn't it.

Oh, I've tryed it with both DBCP and C3PO database connection providers.

Any pointers?
thanks
Jonny

[irbouho]

jwray,
Conventional transactions do not span DDL, they only span DML. This issue has nothing to do with Spring / DBCP/ C3P0 / JDBC ...
What RDBMS are you using? if you are using Oracle, you can consider using CREATE SCHEMA

[jwray]

Omar,

Thanks for your reply. I did not know that about DDL commands not being part of transactions. First time I've ever had to use them programmatically and will need the transaction capabilities. Explains why I couldn't see a problem with the transaction code.

I'll look into create schema, thanks

Jonny

jwray,
Conventional transactions do not span DDL, they only span DML. This issue has nothing to do with Spring / DBCP/ C3P0 / JDBC ...
What RDBMS are you using? if you are using Oracle, you can consider using CREATE SCHEMA