Statement stmt = conn.createStatement();
stmt.execute("lock table test in exclusive mode");
PreparedStatement pst = conn.prepareStatement("insert into test (name, datafield) values (?, ?)");
The following is the error I receive.
com.sybase.jdbc2.jdbc.SybSQLException: A LOCK TABLE command is not allowed outside the scope of a transaction on table 'test' in the database 'master'.
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(Resu ltGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybS tatement.java:204)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybS tatement.java:187)
at com.sybase.jdbc2.jdbc.SybStatement.executeLoop(Syb Statement.java:1670)
at com.sybase.jdbc2.jdbc.SybStatement.execute(SybStat ement.java:1662)
at com.sybase.jdbc2.jdbc.SybStatement.execute(SybStat ement.java:774)
If I call the following
before I perform the lock, then things seem to work. According to the JDBC docs, setting autocommit to false is supposed to start transactions by itself. Plus doing this kind of a thing breaks this same code when run against different databases.
If you could suggest anything, that would be great.
Originally posted by DrWh0
I havn't tried to contact sybase yet. I am currently only using their developer version of their product in order to see if our product can work with it easily.
I will try to see if they can help.
I suggest posting this in the jConnect group at news://forums.sybase.com - you'll probably come across a Sybase engineer or two in that group, and they are likely to help you. The groups can also be accessed via http, though I forger the URL at the moment.
I found a work around suggested by Joe on the Sybase forums. Here's the details in case anyone else has had this problem.
> Hi. This is a DBMS problem. The setAutoCommit(false) doesn't
> start a transaction. It sets the session so that the first thing
> you do will start a transaction. What is odd is that if you were
> to do practically anything else, such as 'select 1', which causes
> zero locking in the DBMS, it would start a transaction and your
> lock table would succeed. The driver isn't at fault.
> Joe Weinstein at BEA
Instead of just locking the table, I am now first executing a dummy 'SELECT 1' statement as he suggested and it works fine.