How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?

Question

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.

Answer 1

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();

Answer 2

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 to exec sp... it works very well.

I post my code in hope this helps others with problems like mine:

ResultSet rs = null;
PreparedStatement cs=null;
Connection conn=getJNDIConnection();

try {
    cs=conn.prepareStatement("exec sp_name ?,?,?,?,?,?,?");
    cs.setEscapeProcessing(true);
    cs.setQueryTimeout(90);

    cs.setString(1, "valueA");

    cs.setString(2, "valueB");

    cs.setString(3, "0418");

    //commented, because no need to register parameters out!, I got results from the resultset. 
    //cs.registerOutParameter(1, Types.VARCHAR);
    //cs.registerOutParameter(2, Types.VARCHAR);

    rs = cs.executeQuery();
    ArrayList<ObjectX> listaObjectX = new ArrayList<ObjectX>();
    while (rs.next()) {

        ObjectX to = new ObjectX();
        to.setFecha(rs.getString(1));
        to.setRefId(rs.getString(2));
        to.setRefNombre(rs.getString(3));
        to.setUrl(rs.getString(4));

        listaObjectX.add(to);

    }
    return listaObjectX;
     } catch (SQLException se) {
        System.out.println("Error al ejecutar SQL"+ se.getMessage());
        se.printStackTrace();
        throw new IllegalArgumentException("Error al ejecutar SQL: " + se.getMessage());

    } finally {

        try {

            rs.close();
            cs.close();
          con.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }