getting java.lang.NullPointerException while calling sql server stored Procedure using JDBC

Question

i am trying to get stored procedure datas from calling database through JDBC.Here i am getting resultset=null when i execute below code,what is the mistake in my code?Please guide me.

stored procedure

CREATE PROCEDURE getEmpDetails
AS
BEGIN 
SELECT * FROM  Employees;
END

java file

 // JDBC driver name and database URL
   static final String JDBC_DRIVER =   "com.microsoft.sqlserver.jdbc.SQLServerDriver";  
   static final String DB_URL =  "jdbc:sqlserver://localhost;database=JDBCDatabase";

   //  Database credentials
   static final String USER = "hj";
   static final String PASS = "kalpana";

   public static void main(String[] args) {
   Connection con = null;
   ResultSet rs = null;
   CallableStatement stmt = null;

   try{
          //STEP 2: Register JDBC driver
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

          //STEP 3: Open a connection
          System.out.println("Connecting to database...");
          con = DriverManager.getConnection(DB_URL,USER,PASS);

          //STEP 4: Execute a query
          System.out.println("Creating statement...");
         // stmt = conn.createStatement(); 

          stmt = con.prepareCall("{call getEmpDetails}");

          ResultSet resultSet  = stmt.getResultSet();

          System.out.println("resultset"+resultSet);

            if (resultSet.next()) {
                // Then we use a loop to retrieve rows and column data
                // and creates a html coded table output
                System.out.println("<table border='1' >");
                do {
                 System.out.println("<tr>");
                 System.out.print("<td>" + resultSet.getString("id") + "</td>");
                 System.out.print("<td>" + resultSet.getString("first") + "</td>");
                 System.out.println("<td>" + resultSet.getInt("last") + "</td>");
                 System.out.println("<td>" + resultSet.getDouble("age") + "</td>");
                 System.out.println("</tr>");
                } while (resultSet.next());
                System.out.println("</table>");
               }
   }catch(Exception e){
       e.printStackTrace();
   }finally{
       try {
           stmt.close();
           con.close();
          // input.close();
       } catch (SQLException e) {
           e.printStackTrace();
       }
   }

   }
}

You never executed the statement. There is a reason the method you did call is named "prepare". You still need to give values for input parameters (if any) and defined output parameters (if any), then execute the statement, in your case using `executeQuery()`. See http://tutorials.jenkov.com/jdbc/callablestatement.html — Andreas, Jan 11 '16 at 06:56
Vote to reopen: The linked NPE question is not relevant to this question, since the NPE happens inside the JDBC driver caused by incomplete setup, not because OP is using a null value. The NPE should have been an `IllegalStateException`. — Andreas, Jan 11 '16 at 07:05
@Andreas I agree. But how do you vote to reopen? The OP is not asking what is a NullPointer — bmarkham, Jan 11 '16 at 07:12
@bmarkham You need 3000 rep to [vote for reopen](http://stackoverflow.com/help/privileges/close-questions). — Andreas, Jan 11 '16 at 07:15
@Andreas Welp. That's kind of horse crap. I'd suggest opening a new question. — bmarkham, Jan 11 '16 at 07:17
@bmarkham Normally it takes 5 votes to close, and 5 votes to reopen, but Jens seems to be able to do it himself. — Andreas, Jan 11 '16 at 07:21
@Andreas Instead of an `IllegalStateException` it should return `null`, or maybe throw `SQLException`. — Mark Rotteveel, Jan 11 '16 at 10:55

score 1 · Accepted Answer · answered Jan 11 '16 at 09:38

In your code your prepare the call, and then you go and expect Results - but without executing the query. Try changing the accesor on the ResultSet to the actual execution of your query:

   stmt = con.prepareCall("{call getEmpDetails}"); 
   //Do it!
   ResultSet resultSet  = stmt.executeQuery();

getting java.lang.NullPointerException while calling sql server stored Procedure using JDBC

stored procedure

1 Answers1