如何在 java/jsp 中执行 MS SQL Server 存储过程，返回表数据? [英] How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?

问题描述

我在从 Java/jsp 执行 MS SQL Server 存储过程时遇到困难.我想返回一个表集数据；存储过程的最后一行是一个表中的常规 select 语句.

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.

(从这一点来看，在PHP 中执行存储过程是轻而易举的.)

(From this point, executing a stored procedure is a cinch in PHP.)

我查看了这些网站以寻求帮助:
www.2netave.com
www.stackoverflow.com

I took a look at these sites for help:
www.2netave.com
www.stackoverflow.com

我没有意识到有一个仅用于存储过程的函数，因为我使用的是 createStatement().

I didn't realize there's a function just for stored procedures, as I was using createStatement() instead.

现在，请理解存储过程在 SQL Server Management Studio 中完美执行，我在使用 createStatement() 在 jsp/java 中执行临时查询没有问题.

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

这是我的jsp页面中的代码:

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.

我试过了:

java.sql.ResultSet ResultSet = cs.executeQuery();

还有这个:

java.sql.CallableStatement cs = conn.prepareCall("{execute sp_test}");

还有这个:

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.

我在这里做错了什么?

谢谢.

更新:

检查了服务器日志 (IIS) 和我的 HTTP 代理、fiddler，它没有报告任何内容.但是，IIS 使用 tomcat 作为 jsp 页面的 servlet 引擎.并且 tomcat 日志文件报告了以下内容:

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: 

我尝试将以上更改为:

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'.

所以，我发现我必须GRANT EXECUTE 给用户.另一个问题是从存储过程返回表数据.

So, I have figured out I have to GRANT EXECUTE to the user. The other issue is returning table data from a stored procedure.

如果我有这样的程序:

CREATE PROCEDURE sp_test2 AS
BEGIN
SELECT * FROM TABLE
END

在jsp中如何操作表格数据?ResultSet 是否可以工作，还是仅适用于临时查询，而不是使用 createStatement() 执行查询的存储过程?

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?

谢谢.

更新 2:

解决方案:

为了操作表格数据，我不得不使用这个:

In order to manipulate table data, I had to use this:

java.sql.ResultSet RS = cs.executeQuery();

它在 execute() 上失败并且在命名 ResultSet 对象ResultSet"时失败.过去它从来没有用 createStatement() 抱怨过这个问题.但是出于某种原因，对于存储过程，它不喜欢这种命名约定.

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.

谢谢.

推荐答案

我们的服务器像这样从 Java 调用存储过程 - 适用于 SQL Server 2000 &2008 年:

Our server calls stored procs from Java like so - works on both SQL Server 2000 & 2008:

String SPsql = "EXEC <sp_name> ?,?";   // for stored proc taking 2 parameters
Connection con = SmartPoolFactory.getConnection();   // java.sql.Connection
PreparedStatement ps = con.prepareStatement(SPsql);
ps.setEscapeProcessing(true);
ps.setQueryTimeout(<timeout value>);
ps.setString(1, <param1>);
ps.setString(2, <param2>);
ResultSet rs = ps.executeQuery();

这篇关于如何在 java/jsp 中执行 MS SQL Server 存储过程，返回表数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！