 |
|
|
|
Next: Protecting email with PHP
|
| Author |
Message |
External
Since: Feb 09, 2005
Posts: 2
|
(Msg. 1) Posted: Wed Feb 09, 2005 9:19 pm
Post subject: Sybase, JDBC, AutoCommit, DDL IN TRAN
Archived from groups: comp>lang>java>databases (more info?)
|
|
|
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. |
|
| Back to top |
|
 | |
External
Since: Oct 24, 2003
Posts: 232
|
(Msg. 2) Posted: Wed Feb 09, 2005 11:28 pm
Post subject: Re: Sybase, JDBC, AutoCommit, DDL IN TRAN [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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.<!-- ~MESSAGE_AFTER~ --> |
|
| Back to top |
|
| |
External
Since: Feb 10, 2005
Posts: 3
|
(Msg. 3) Posted: Thu Feb 10, 2005 4:40 am
Post subject: Re: Sybase, JDBC, AutoCommit, DDL IN TRAN [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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<!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Sybase, JDBC, AutoCommit, DDL IN TRAN |
|
| Back to top |
|
| |
External
Since: Feb 09, 2005
Posts: 2
|
(Msg. 4) Posted: Thu Feb 10, 2005 2:08 pm
Post subject: Re: Sybase, JDBC, AutoCommit, DDL IN TRAN [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Hi All,
Thank you for your input. I suspected that there wasn't a way
around this with my current JDBC and Sybase setup.
I was hoping that maybe there was an update to the J-Connect jar
provided by sybase or a command that I could issue to the sybase
driver that would stop the JDBC driver issuing the BEGIN TRAN
statement at the start of every connection that has autocommit set to
false.
Thanks again and kind regards,
Richie.
Karsten Baumgarten <invalid RemoveThis @spam.net> wrote in message news:<cuf5no$cer$04$1@news.t-online.com>...
> 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.<!-- ~MESSAGE_AFTER~ --> >> Stay informed about: Sybase, JDBC, AutoCommit, DDL IN TRAN |
|
| Back to top |
|
| |
| Related Topics: | jdbc - Hi. Is it possible to use java/jdbc to lengthen columns in, say, ms sql tables? Please note (sorry) I already asked this in an ms sql group... it is not quite a cross post, but an after thought that I might get some good information here. Thanks Jeff
JDBC - Hi,i am total newbie in java programming.I want help in connecting Oracle 9i database and inserting data in a table there.How can i do this?Can anyone explain me step-by-step or point me to any link where i can learn this?I am using tomcat 6.0.16 and..
Newbie help needed JDBC - Hi, I have grown up with VB6 and moved onto perl / php. I have got JBOSS running under suse 9.2, and have netbeans as well. I can understand the mechanism for java applets and played with a few. The next step i need help with is 1) what is a class 2)...
JDBC test suite - Do you know of any good JDBC test suite? I developes an java DB and I want to check it compliant with the JDBC standard and SQL 92. Thanks
JDBC and Multiple record sets - Can JDBC handle multiple record sets in a single callable statement. Thanks in advance. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Profile
Private Messages
Log in
