SQL Server Stored Proc Issue with Java

Question posted by: jynxxxed (Newbie) on April 27th, 2007 02:57 PM

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







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.SQLServerDriver").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:






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

Quote | Subscribe