468,278 Members | 1,582 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,278 developers. It's quick & easy.

SQL Server Stored Proc Issue with Java

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



Expand|Select|Wrap|Line Numbers
  1. 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
  1. 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
Apr 27 '07 #1
1 3929
Well it turns out I needed to call the procedure with the fully qualified 4 part name. I wish the error was a little more descriptive, I thought it was a permissions issue, yet stumbled on the answer by accident.
Apr 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Kent Lewandowski | last post: by
3 posts views Thread by jynxxxed | last post: by
reply views Thread by zattat | last post: by
1 post views Thread by MrBee | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.