471,337 Members | 869 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

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 4024
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.