Sybase, JDBC, AutoCommit, DDL IN TRAN

From: Richad Fallon (richard_fallon_at_ureach.com)
Date: 02/10/05 

Date: 9 Feb 2005 20:19:28 -0800

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)
  • Re: Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... transaction unless ddl_in_tran is set. ... > having executing Sysbase stored procedures via JDBC. ... > stored procedure which contains DDL and with AutoCommit set to false I ... > I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Re: Records lost in an ADOStoredProc
    ... Use a thread to fire off the stored procedures so that your application ... Let's suppose it updates ... > the CacheSize is set to 1; it is worse when I increase the CacheSize. ... > I don't need to show records, only to execute the store procedure. ...
    (borland.public.delphi.database.ado)
  • 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)