SQL Server Stored Proc Problem

[jynxxxed]

I've been reviewing some of the threads here and applying some of the advice and still getting the same error.

15:50:26,935 INFO  [STDOUT] java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'getName'.

I'm basically taking a variable coming to my servlet through a JSP page and using it as my search criteria. I know the stored proc will execute through query analyzer, I can execute a regular prepared statement through my servlet, so i know I'm connecting okay, but I'm still baffled. I have included my servlet and SQL Server procedure:

Servlet:

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
public class VoidMemberServlet extends HttpServlet {
 
    private static final long serialVersionUID = 1L;
 
    Connection conn = null;
    public VoidMemberServlet() {        
        super();
 
    }
 
    public void destroy() {
        super.destroy();
    }
 
    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {    
 
        String state = (String)request.getSession().getAttribute("state");
        if(state != "" &&state!=null)
            callProcedure(state);
    }
 
 
    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
 
    }
    private void callProcedure(String state)
    {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
            conn = DriverManager.getConnection(
            "jdbc:microsoft:sqlserver://SERVER1:1433;database=test;user=sa;password=password");
 
 
            //PreparedStatement statement = conn.prepareStatement("select * from SERVER1.test.dbo.allUsersinSystem");
            //ResultSet set = statement.executeQuery();
 
            CallableStatement cstmt = conn.prepareCall("{Call getName(?)}");
            cstmt.setString(1,state);
 
            cstmt.execute();
            ResultSet set = cstmt.getResultSet();
 
            cstmt.close();            
            conn.close();
 
            while(set.next())
            {                
                System.out.println(set.getString("uname"));
            }            
 
        } catch (ClassNotFoundException e) {
 
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
 
    }
 
    public void init() throws ServletException {
 
    }
 
}

There are appropriate execute permissions on all the users and the sproc looks something like:

CREATE PROCEDURE getName (@State varchar(2))
as
Select uname
FROM allUsersinSystem
WHERE state=@State
GO

I dont know how I can execute a prepared statement on a table in the same database, but I can't execute the sproc.

Any suggestions would be great,

Thanks,

-Dave

[dmjpro]

welcome jynxxxed,

actually i m from oracle ... there is a synonym and grand funda.

i m not sure is there any funda in MicroSoft sqlserver.

so u better to forward this post to the database forums.


and i thing i saw in ur code ... that u r accessing the resultset object after closing statement and connection objects.

u must use the resultset object before closing the statement object.

best of luck jynxxxed.

[jynxxxed]

Well,

That's true, I am accessing the result set object after closing the connection and Callable statement, but I thought that since I set the ResultSet while they're still open, I could access it without worrying about the connection.

But I am still unsure why I can't execute the callable statement though, and why I would get the error, "Cannot Find Stored Procedure 'getName' ".. I'm definitely baffled.

Sorry for posting in the wrong section, I will move this thread over to the Database section.

Thanks,

-Dave

[dmjpro]

the resultset object is associated with statement object and the statement object is associated with connection object.