如何在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}");

没有任何效果.一旦可以正常工作，就可以运行实际的存储过程，该存储过程从select语句返回表数据.但是我什至无法使这个虚拟存储过程正常工作.

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: 

我尝试将以上内容更改为:

I tried changing the above to:

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?

谢谢.

Update2:

解决方案:

为了操作表数据，我必须使用此命令:

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调用存储的proc-在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屋！