Re: Sybase, JDBC, AutoCommit, DDL IN TRAN

From: Joe Weinstein (joeNOSPAM_at_bea.com)
Date: 02/10/05 

Date: Wed, 09 Feb 2005 22:28:45 -0800
To: Richad Fallon <richard_fallon@ureach.com>

The short answer is that what you want to do is impossible.
If a procedure contains DDL it cannot be part of a larger
transaction unless ddl_in_tran is set. Setting autoCommit(false)
means that the first thing you do with that connection will
start a transaction.

Joe Weinstein at BEA

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.

Relevant Pages

  • Re: Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... > having executing Sysbase stored procedures via JDBC. ... > stored procedure which contains DDL and with AutoCommit set to false I ... > The explanation for this behaviour I have found is that the JDBC ... > I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... having executing Sysbase stored procedures via JDBC. ... stored procedure which contains DDL and with AutoCommit set to false I ... The explanation for this behaviour I have found is that the JDBC ... I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: most drivers share error variable for sth/dbh handles?
    ... > AutoCommit which requires extra statements to be executed to simulate ... execute (assuming you could have looked at $sth->err which you can't in ... achieve the effect of AutoCommit in a database that doesn't auto-commit ... >> be great except for the fact we use multiple DBDs. ...
    (perl.dbi.users)
  • Re: most drivers share error variable for sth/dbh handles?
    ... > AutoCommit which requires extra statements to be executed to ... that is, the commit, unless the prepare or execute raised an error, in which case it would reflect that error. ... Even though sql has been executed on a statement handle (under a connection handle) I no longer have access to the statement handle since it has gone out of scope BUT I do have access to the connection handle and hope to see the last error from the now out of scope statement in the connection handle. ... >> be great except for the fact we use multiple DBDs. ...
    (perl.dbi.users)
  • Re: Transactions: DBI vs. SQL
    ... I am able to enact transaction processing in sybase with the DBI/DBD::Sybase combination with autocommit set to on. ... "Enable transactions (by turning AutoCommit off) until the next call to commit or rollback. ...
    (perl.dbi.users)