Java 在 Hibernate for Sybase DB 的多语句事务中不允许 SELECT INTO 命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21605632/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT INTO command not allowed within multi-statement transaction in Hibernate for Sybase DB
提问by Harsha GNS
I was new to hibernate and trying to execute an procedure from a Java file using hibernate to a Sybase DB. While i am trying to run the application i am getting an error like below
我是 hibernate 的新手，并试图从使用 hibernate 的 Java 文件到 Sybase DB 执行一个过程。当我尝试运行该应用程序时，出现如下错误
Stored procedure 'dbo.p_chklist_test' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode.
存储过程“dbo.p_chklist_test”只能在非链式事务模式下运行。“SET CHAINED OFF”命令将使当前会话使用非链式事务模式。
I have checked in few forums and set the mode as "Any mode" by running below command. sp_procxmode p_chklist_test, "anymode"
我已经检查了几个论坛，并通过运行以下命令将模式设置为“任何模式”。sp_procxmode p_chklist_test, "anymode"
Also i have set the Auto Commit as False in hibernate.
我还在休眠中将自动提交设置为 False。
Now i am getting a different error like below
现在我收到一个不同的错误，如下所示
Caused by: org.hibernate.exception.GenericJDBCException: could not execute native bulk manipulation query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:198)
at org.hibernate.impl.SessionImpl.executeNativeUpdate(SessionImpl.java:1191)
at org.hibernate.impl.SQLQueryImpl.executeUpdate(SQLQueryImpl.java:357)
at com.lcit_release.server.dao.ReleaseItemDao.searchRecordsNew(ReleaseItemDao.java:198)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy8.searchRecordsNew(Unknown Source)
at com.lcit_release.server.logic.ReleaseItemLogic.searchExisting(ReleaseItemLogic.java:147)
at com.lcit_release.server.adapter.ReleaseItemLogicAdapter.search(ReleaseItemLogicAdapter.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:569)
... 41 more
**Caused by: com.sybase.jdbc3.jdbc.SybSQLException: SELECT INTO command not allowed within multi-statement transaction.**
at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.updateLoop(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.executeUpdate(Unknown Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeUpdate(Unknown Source)
at msjava.tools.db.jdbc3.MSDBPreparedStatementImpl.executeUpdate(MSDBPreparedStatementImpl.java:315)
at msjava.tools.db.jdbc3.MSDBPreparedStatement.executeUpdate(MSDBPreparedStatement.java:78)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:189)
... 62 more
I have check few sites for the error SELECT INTO command not allowed within multi-statement transactionand set the parameter "ServerInitiatedTransactions"as false in the configuration xml
我检查了几个站点的错误SELECT INTO command not allowedin multi-statement transaction并在配置 xml中将参数“ServerInitiatedTransactions”设置为 false
**<ConnectProperties>
<Property name="ServerInitiatedTransactions">false</Property>
</ConnectProperties>**
But this even dint resolve the issue and i am getting the same error. Can someone please help me on this.
但这甚至解决了问题，我也遇到了同样的错误。有人可以帮我解决这个问题。
My Code:
我的代码：
String sql3 ="exec dbo.p_chklist_test";
System.out.println("sql 3 is "+sql3);
Query query = sessionFactory.getCurrentSession().createSQLQuery(sql3);
sessionFactory.getCurrentSession().connection().setAutoCommit(false);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
listRelItem = query.list();
Thanks in Advance!
提前致谢！
采纳答案by swateek
Please check your stored procedure for statements like
请检查您的存储过程是否有类似的语句
SELECT id INTO #a FROM students.
SELECT id INTO #a FROM 学生。
This statement is okay from DB perspective but when executed from a Java Program this won't work and give the above error.
从数据库的角度来看，这个语句是可以的，但是当从 Java 程序执行时，这将不起作用并出现上述错误。
First, define the temporary table
一、定义临时表
CREATE TABLE #a( id INT )
创建表 #a( id INT )
INSERT INTO #a SELECT id FROM students
INSERT INTO #a 从学生中选择 ID
The above fix works.
上述修复有效。
回答by Harsha GNS
I have solved the issue by calling the procedure in the below manner. Hope it might help others to figure it out.
我通过以下方式调用程序解决了这个问题。希望它可以帮助其他人弄清楚。
SET CHAINED OFF exec p_qa_existing_items
SET CHAINED OFF exec p_qa_existing_items
回答by Niraj Kulkarni
Probably this is because you should "never" try to use "select into" while a transaction(select @@transaction) is open, call it from any platform Java or any other application.
可能这是因为当事务（select @@transaction）打开时，您应该“永远不要”尝试使用“select into”，从任何平台 Java 或任何其他应用程序调用它。
I believe solution given by @swat is pretty much you should use rather than SET CHAINED OFF. There could be impact due to this as in your code as I understand SET CHAINED was ON for some reason. So there might not be any code in SP when there is need to open a transaction but you have not as SET CHAINED is ON.
我相信@swat 给出的解决方案几乎是您应该使用的，而不是 SET CHAINED OFF。可能会因此产生影响，因为在您的代码中，我知道 SET CHAINED 由于某种原因而处于 ON 状态。因此，当需要打开事务时，SP 中可能没有任何代码，但您没有，因为 SET CHAINED 处于 ON 状态。
You can play with this in Sybase -
你可以在 Sybase 中玩这个 -
create proc testproc as select * into #temp from table return go
create proc testproc as select * into #temp from table return go
begin tran exec test_proc commit tran
开始 tran exec test_proc 提交 tran
begin tran exec test_proc rollback tran
开始 tran exec test_proc 回滚 tran
(I do not have my Sybase environment with me now so cannot check it for myself. but this could definitely help you understand the cause)
（我现在没有 Sybase 环境，所以无法自己检查。但这绝对可以帮助您了解原因）
回答by Wiliam Carvalho
I had the same problem using Jboss, and I couldn't change the procedure because it was provided by the client. In my case the problem stopped after configuring the datasource to use
我在使用 Jboss 时遇到了同样的问题，我无法更改程序，因为它是由客户端提供的。在我的情况下，问题在配置数据源后停止使用
jta="false".
