cloned connection do`t in manual transaction

Can't start a cloned connection while in manual transaction mode

在尝试打开一个克隆连接，有两种解决办法： 每个数据库的连接在执行完毕后，关闭，第二，在连接时，判断是否已经有连接存在，有则使用现成的连接。你在DatabaseURL字符串中加入SelectMethod=Cursor;如下

今天为了保证多个sql全部执行成功后再提交，加上了语句

conn.setAutoCommit(false);

//执行语句sql1,sql2

conn.commit();

结果出现Can't start a cloned connection while in manual transaction mode错误，从网上找到原因及解决办法如下：

原因一般是当你在一个SQL SERVER的JDBC连接上执行多个STATEMENTS的操作，或者是手动事务状态（AutoCommit=false) 并且使用默认的模式. direct (SelectMethod=direct) 模式.

解决办法

当你使用手动事务模式时，必须把SelectMethod 属性的值设置为 Cursor, 或者是确保在你的连接只有一个STATEMENT操作。

修改url

加入SelectMethod=cursor即可

微软提供详细原因如下：

SYMPTOMS

While using the Microsoft SQL Server 2000 Driver for JDBC, you may experience the following exception:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Can't start a cloned connection while in manual transaction mode.

500)this.width=500" border="0"> Back to the top

CAUSE

This error occurs when you try to execute multiple statements against a SQL Server database with the JDBC driver while in manual transaction mode (AutoCommit=false) and while using the direct (SelectMethod=direct) mode. Direct mode is the default mode for the driver.

500)this.width=500" border="0"> Back to the top

RESOLUTION

When you use manual transaction mode, you must set the SelectMethod property of the driver to Cursor, or make sure that you use only one active statement on each connection as specified in the "More Information" section of this article.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce the Behavior

Use the following code to reproduce the error:

NOTE: See the comments in the code for information on the code changes that are required to resolve the problem.

import java.sql.*;

import java.io.*;

public class Repro{

public static void main(String args[])

{

try {

Connection con;

Statement s1 = null;

ResultSet r1 = null;

Statement s2 = null;

ResultSet r2 = null;

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

con = DriverManager.getConnection(

"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs;SelectMethod=Direct;User=User;Password=Password");

//fix 1

//"jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs;SelectMethod=Cursor;User=User;Password=Password");

con.setAutoCommit(false);

try {

s1 = con.createStatement();

r1 = s1.executeQuery("SELECT * FROM authors");

//fix 2

//r1.close();

//s1.close();

s2 = con.createStatement();

r2 = s2.executeQuery("SELECT * FROM publishers");

}

catch (SQLException ex)

{

System.out.println(ex);

}

}

catch (Exception e)

{

e.printStackTrace();

}

}