Re: Sybase, JDBC, AutoCommit, DDL IN TRAN
From: Karsten Baumgarten (invalid_at_spam.net)
Date: 02/10/05
- Next message: leni: "ResultSet has a null value..."
- Previous message: Bjorn Abelli: "Re: simple sql query, but don't get it ..."
- In reply to: Richad Fallon: "Sybase, JDBC, AutoCommit, DDL IN TRAN"
- Next in thread: Richad Fallon: "Re: Sybase, JDBC, AutoCommit, DDL IN TRAN"
- Reply: Richad Fallon: "Re: Sybase, JDBC, AutoCommit, DDL IN TRAN"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Thu, 10 Feb 2005 09:24:36 +0100
Richad Fallon wrote:
> Hi All,
> I am hoping someone can help me with the following problem I'm
> having executing Sysbase stored procedures via JDBC. When I execute a
> stored procedure which contains DDL and with AutoCommit set to false I
> get the following exception.
>
> com.sybase.jdbc2.jdbc.SybSQLException: The 'CREATE TABLE' command is
> not allowed within a multi-statement transaction in the 'tempdb'
> database.
>
> at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2796)
> at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:2126)
> at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:220)
> at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:203)
> at com.sybase.jdbc2.jdbc.SybStatement.queryLoop(SybStatement.java:1578)
> at com.sybase.jdbc2.jdbc.SybCallableStatement.executeQuery(SybCallableStatement.java:102)
> at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery
>
> When I set AutoCommit to true the exception is not thrown and the
> procedure executes as expected.
>
> The explanation for this behaviour I have found is that the JDBC
> driver issues a BEGIN TRAN statement implicitly when AutoCommit is set
> to false.
>
> I need to be able to set AutoCommit to false and execute multiple
> stored procedures or sql statements in the one transaction so
> unfortunately I can't use the work around of setting AutoCommit to
> true.
>
> One suggestion I have found is to set the sybase database parameter
> "DDL IN TRAN" to true but again I have been instructed not to modify
> the stored procedures I am using or change any database settings so
> this is also out.
>
> I have found that the JDBC drivers change certain session settings
> when AutoCommit is changed eg. When AutoCommit is set to false the
> session mode is implicitly changed to "CHAINED ON" and if you wanted
> to work with AutoCommit false and in a "CHAINED OFF" mode you would
> have to execute a statement containing the text "SET CHAINED OFF".
>
> Does anyone know of a way to execute sybase stored procedures which
> contain DDL via JDBC with AutoCommit set to false without modifying
> the stored procedure or the sybase database setting "DDL IN TRAN".
>
> Kind Regards,
> Richie.
Using DDL in longer transactions could cause your whole system to come
to a halt. DDL commands hold locks on system tables, hence if the
transaction is running too long (where "too long" would be determined by
the system load, etc.) it might render the DBMS unusable. Therefore by
default DDL_IN_TRANS is disabled and should not be used at all (and
NEVER on tempdb). My suggestion would be to separate the DDL from the
DML statements if ever possible. That is, running the all the DML stuff
in a single transaction and the DDL with auto commit enabled.
-- Regards, Karsten
- Next message: leni: "ResultSet has a null value..."
- Previous message: Bjorn Abelli: "Re: simple sql query, but don't get it ..."
- In reply to: Richad Fallon: "Sybase, JDBC, AutoCommit, DDL IN TRAN"
- Next in thread: Richad Fallon: "Re: Sybase, JDBC, AutoCommit, DDL IN TRAN"
- Reply: Richad Fallon: "Re: Sybase, JDBC, AutoCommit, DDL IN TRAN"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
