java 使用带有空结果集的 JDBC 的临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4728495/
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
Temporary tables using JDBC with null ResultSet
提问by Koekiebox
I am executing a stored procedure via standard JDBC Connection using MS SQL Driver version 3.0.
我正在使用 MS SQL Driver version 3.0通过标准JDBC Connection执行存储过程。
I have found that when I create and insert data into a temporary table the stored procedure doesn't execute properly.
我发现当我创建数据并将数据插入到临时表中时，存储过程没有正确执行。
The Java code won't throw a exception, but the javax.sql.ResultSetwill be null.
Java 代码不会抛出异常，但javax.sql.ResultSet会抛出null.
The point of failure in the stored procedure is when I un-comment the INSERT INTO #TBL CLM_NAME VALUES('VAL')
存储过程中的故障点是当我取消注释 INSERT INTO #TBL CLM_NAME VALUES('VAL')
When I execute the statement using SQL Studio Managerit executes without hassle and the data as expected.
当我使用SQL Studio Manager执行该语句时，它会按预期执行而不会出现麻烦和数据。
Has anyone come across this or know why its the case?
有没有人遇到过这个或知道为什么会这样？
Initially I thought its because of the SQL driver and I still think it is?
最初我认为是因为 SQL 驱动程序，我仍然认为是吗？
Thanks.
谢谢。
回答by Radu Caprescu
Maybe this will help:
也许这会有所帮助：
It is recommended that if you want to use temp tables, you should not call "prepareStatement". You can directly execute the query from the statement object.
For example:
String sql = "select uuid, name from Component"; Statement stmt = dbCon.createStatement(); ResultSet rs = stmt.executeQuery(sql);If you must call "prepareStatement", then you will need to create a real table and, if needed, delete the table afterwards.
建议如果要使用临时表，不要调用“prepareStatement”。您可以直接从语句对象执行查询。
例如：
String sql = "select uuid, name from Component"; Statement stmt = dbCon.createStatement(); ResultSet rs = stmt.executeQuery(sql);如果您必须调用“prepareStatement”，那么您将需要创建一个真正的表，如果需要，随后删除该表。
回答by Prakhyat
The executeQuery() is intended to be used with queries that return a ResultSet, typically SELECT statements.
executeQuery() 旨在与返回 ResultSet 的查询一起使用，通常是 SELECT 语句。
The executeUpdate() is intended for INSERT, UPDATE, DELETE or DDL statements that return update counts.
executeUpdate() 用于返回更新计数的 INSERT、UPDATE、DELETE 或 DDL 语句。
Both of the above (ResultSets and update counts) are considered by JDBC to be as "results". For queries that return multiple results it requires we invoke execute().
以上两个（ResultSets 和更新计数）都被 JDBC 视为“结果”。对于返回多个结果的查询，它需要我们调用 execute()。
If stored procedure makes use of temp tables, probably it returns an update count first, followed by a ResultSet. You should use execute() to run query, call getMoreResults() to skip the update count and then call getResultSet() to obtain the ResultSet you want. The problem here is we have to do trial and error by calling getMoreResults() multiple times to fetch result set we want by calling getResultSet().
如果存储过程使用临时表，它可能首先返回一个更新计数，然后是一个 ResultSet。您应该使用 execute() 运行查询，调用 getMoreResults() 跳过更新计数，然后调用 getResultSet()获取您想要的ResultSet。这里的问题是我们必须通过多次调用 getMoreResults() 来进行反复试验，以通过调用 getResultSet() 获取我们想要的结果集。
Instead of above way of trial and error, you can suppress all the extra "query results" by specifying "SET NOCOUNT ON".
您可以通过指定“ SET NOCOUNT ON”来抑制所有额外的“查询结果”，而不是上述反复试验的方式。
Changes required,
需要改变，
Put your logic in SP "SPCHILD". This Stored procedure will have logic along with temp table creation.
Create a SP "SPPARENT" as below,
CREATE PROCEDURE [dbo].[SPPARENT] @Id int = NULL AS BEGIN SET NOCOUNT ON; EXEC(' SPCHILD @Id = ' + @Id) ENDFrom parent SP "SPPARENT" you have to call your actual SP i.e. "SPCHILD".
From your JDBC code make a SP call to "SPPARENT".
将您的逻辑放在 SP“SPCHILD”中。此存储过程将具有逻辑以及临时表创建。
创建一个 SP“SPPARENT”，如下所示，
CREATE PROCEDURE [dbo].[SPPARENT] @Id int = NULL AS BEGIN SET NOCOUNT ON; EXEC(' SPCHILD @Id = ' + @Id) END从父 SP“SPPARENT”，您必须调用您的实际 SP，即“SPCHILD”。
从您的 JDBC 代码对“SPPARENT”进行 SP 调用。
回答by Chirag Nirmal
I am also facing the same issue. In order to fix this, I am going setup a trace on my sql server and see all the statements that are being executive by the MS JDBC driver before executing the actual SP call. This should help me clear things up. Unfortunately our DBA is out today so I will have to do it tomorrow with her help. I will let u know what happens and what is the fix.
我也面临同样的问题。为了解决这个问题，我将在我的 sql 服务器上设置一个跟踪，并在执行实际的 SP 调用之前查看 MS JDBC 驱动程序正在执行的所有语句。这应该可以帮助我理清事情。不幸的是，我们的 DBA 今天不在，所以我明天必须在她的帮助下完成。我会让你知道会发生什么以及解决方法是什么。
