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.