By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,647 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,647 IT Pros & Developers. It's quick & easy.

SQL Server Stored Proc Issue with Java

P: 5
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
Share this Question
Share on Google+
1 Reply


P: 5
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.