如何在 java/jsp 中执行 MS SQL Server 存储过程并返回表数据？
我在从 Java/jsp 执行 MS SQL Server 存储过程时遇到困难。我希望返回一组数据；存储过程的最后一行是来自表的常规选择语句。
（从这一点来看，在 PHP 中执行存储过程是轻而易举的事。）
我查看了这些站点以寻求帮助：
www.2netave.com
www.stackoverflow.com
我没有意识到有一个函数专门用于存储过程，因为我使用的是 createStatement() 。
现在，请了解存储过程在 SQL Server Management Studio 中完美执行，并且我使用 createStatement() 在 jsp/java 中执行即席查询没有任何问题。
我创建了一个不带参数的简单存储过程，只是为了缩小问题范围：
CREATE PROCEDURE sp_test AS
BEGIN
PRINT 'HELLO WORLD'
END
这是我的 jsp 页面中的代码：
Class.forName("net.sourceforge.jtds.jdbc.Driver");
java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://MySQLServer:1433/test", "user", "pass");
java.sql.CallableStatement cs = conn.prepareCall("{call sp_test}");
java.sql.ResultSet ResultSet = cs.execute();
浏览器告诉我该页面无法显示，因为发生了内部服务器错误。我知道这意味着上面的代码有问题。
我尝试了这个：
java.sql.ResultSet ResultSet = cs.executeQuery();
还有这个：
java.sql.CallableStatement cs = conn.prepareCall("{execute sp_test}");
还有这个：
java.sql.CallableStatement cs = conn.prepareCall("{exec sp_test}");
但没有任何效果。一旦我可以让它工作，那么我就可以运行一个实际的存储过程，从 select 语句返回表数据。但我什至无法让这个虚拟存储过程工作。
我在这里做错了什么？
谢谢。
更新：
检查了服务器日志 (IIS) 和我的 HTTP 代理、fiddler，它没有报告任何内容。然而，IIS 使用 tomcat 作为 jsp 页面的 servlet 引擎。 tomcat 日志文件报告了以下内容：
An error occurred at line: 20 in the jsp file: /test.jsp
Type mismatch: cannot convert from boolean to ResultSet
17:
18: java.sql.CallableStatement cs = conn.prepareCall("{call sp_test}");
19:
20: java.sql.ResultSet ResultSet = cs.execute();
21:
22: // java.sql.ResultSet ResultSet = state.executeQuery(SQL);
23:
我尝试将上面的内容更改为：
cs.execute();
并且日志文件报告：
- Servlet.service() for servlet jsp threw exception
java.sql.SQLException: The EXECUTE permission was denied on the object 'sp_test', database 'test', schema 'dbo'.
因此，我发现我必须将 EXECUTE 授予用户。另一个问题是从存储过程返回表数据。
如果我有这样的程序：
CREATE PROCEDURE sp_test2 AS
BEGIN
SELECT * FROM TABLE
END
如何在jsp中操作表数据？ ResultSet 是否有效，或者仅适用于即席查询，而不是存储过程，在存储过程中，人们会使用 createStatement() 来执行查询？
谢谢。
Update2：
解决方案：
为了操作表数据，我必须使用这个：
java.sql.ResultSet RS = cs.executeQuery();
它在 execute() 上失败，并且在命名 ResultSet 上失败> 对象“结果集”。过去它从未使用 createStatement() 抱怨过这一点。但由于某种原因，对于存储过程，它不喜欢这种命名约定。
谢谢。
I am having difficulty executing a MS SQL Server stored procedure from Java/jsp. I wish to return a table set of data; the last line of the stored procedure is a regular select statement from a table.
(From this point, executing a stored procedure is a cinch in PHP.)
I took a look at these sites for help:
www.2netave.com
www.stackoverflow.com
I didn't realize there's a function just for stored procedures, as I was using createStatement() instead.
Now, please understand the stored procedure executes perfectly in SQL Server Management Studio and I have had no problems executing ad-hoc queries in jsp/java with createStatement().
I created a simple stored procedure that takes no arguments, just to narrow down the problem:
CREATE PROCEDURE sp_test AS
BEGIN
PRINT 'HELLO WORLD'
END
Here is the code in my jsp page:
Class.forName("net.sourceforge.jtds.jdbc.Driver");
java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:jtds:sqlserver://MySQLServer:1433/test", "user", "pass");
java.sql.CallableStatement cs = conn.prepareCall("{call sp_test}");
java.sql.ResultSet ResultSet = cs.execute();
The browser is telling me that the page cannot be displayed because an interal server error has occurred. I know this means there is an issue with the code above.
I tried this:
java.sql.ResultSet ResultSet = cs.executeQuery();
And this:
java.sql.CallableStatement cs = conn.prepareCall("{execute sp_test}");
And this:
java.sql.CallableStatement cs = conn.prepareCall("{exec sp_test}");
And nothing worked. Once I can get this working, then I can run an actual stored procedure that returns table data from a select statement. But I can't even get this dummy stored procedure to work.
What am I doing wrong here?
Thank you.
Update:
Checked the server logs (IIS) and my HTTP proxy, fiddler, and it doesn't report anything. However, the IIS is using tomcat as the servlet engine for jsp pages. And tomcat log file reported the following:
An error occurred at line: 20 in the jsp file: /test.jsp
Type mismatch: cannot convert from boolean to ResultSet
17:
18: java.sql.CallableStatement cs = conn.prepareCall("{call sp_test}");
19:
20: java.sql.ResultSet ResultSet = cs.execute();
21:
22: // java.sql.ResultSet ResultSet = state.executeQuery(SQL);
23:
I tried changing the above to:
cs.execute();
And the log files reported:
- Servlet.service() for servlet jsp threw exception
java.sql.SQLException: The EXECUTE permission was denied on the object 'sp_test', database 'test', schema 'dbo'.
So, I have figured out I have to GRANT EXECUTE to the user. The other issue is returning table data from a stored procedure.
If I have a procedure like this:
CREATE PROCEDURE sp_test2 AS
BEGIN
SELECT * FROM TABLE
END
How do I manipulate the table data in jsp? Would ResultSet work or is that only for ad-hoc queries, as opposed to stored procedures, where one would use createStatement() to execute a query?
Thank you.
Update2:
Solution:
In order to manipulate table data, I had to use this:
java.sql.ResultSet RS = cs.executeQuery();
It failed on execute() and it failed on naming the ResultSet object "ResultSet". It never complained about this in the past with createStatement(). But for some reason, with stored procedures, it didn't like this naming convention.
Thank you.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我们的服务器像这样从 Java 调用存储过程 - 适用于 SQL Server 2000 和 SQL Server 2000。 2008年：
Our server calls stored procs from Java like so - works on both SQL Server 2000 & 2008:
感谢布莱恩提供代码。我尝试使用
{call spname(?,?)}连接到 sql 服务器，但出现错误，但是当我将代码更改为exec sp...它运作得很好。我发布我的代码希望这可以帮助其他人解决像我这样的问题：
Thank to Brian for the code. I was trying to connect to the sql server with
{call spname(?,?)}and I got errors, but when I change my code toexec sp...it works very well.I post my code in hope this helps others with problems like mine:
我们经常与其他 Java 程序员一起处理创建这些存储过程的工作。
我们不想乱搞它。
但您可能会得到这些 exec 示例返回 0 的结果集（几乎存储过程调用返回零）。
检查这个示例：
经过几天的尝试和错误，我找到了这个解决方案，谷歌搜索并感到困惑；）
它在存储过程下面执行：
右键单击数据库中的存储过程，您将获得“java SQL 代码”。
像这样的事情：
检查我已经完成的查询字符串，那是你的作业；）
很抱歉回答这么久，
这是我几周前注册以获得答案以来的第一个答案。
Frequently we deal with other fellow java programmers work which create these Stored Procedure.
and we do not want to mess around with it.
but there is possibility you get the result set where these exec sample return 0 (almost Stored procedure call returning zero).
check this sample :
I came to this solutions after few days trial and error, googling and get confused ;)
it execute below Stored Procedure :
you will get the "java SQL Code" by right click on stored procedure in your database.
something like this :
check the query String I've done, that is your homework ;)
so sorry answering this long,
this is my first answer since I register few weeks ago to get answer.
FWIW，sp_test 除了一个整数之外不会返回任何内容（所有 SQL Server 存储过程只返回一个整数）并且不会返回任何结果集（因为没有 SELECT 语句）。要获取 PRINT 语句的输出，通常在 ADO.NET 中的连接（而不是命令）上使用 InfoMessage 事件。
FWIW, sp_test will not be returning anything but an integer (all SQL Server stored procs just return an integer) and no result sets on the wire (since no SELECT statements). To get the output of the PRINT statements, you normally use the InfoMessage event on the connection (not the command) in ADO.NET.