5
Expand|Select|Wrap|Line Numbers
- 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:
package com.caregain.web;
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.SQLSer verDriver").newInstance();
conn = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://Server1:1433;database=test;user=sa;password=passwo rd");
//Connection conn = DriverManager.getConnection(
// "jdbc:microsoft:sqlserver://Server1:1433;database=Server1.test;user=sa;passwor d=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();
while(set.next())
{
System.out.println(set.getString("uname"));
}
cstmt.close();
conn.close();
} 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:
Expand|Select|Wrap|Line Numbers
- 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
