Writing a Derby Stored procedure for paginatng records

[Ravi Sree]

Hi All,

I have been trying my hand on stored procedures in derby(JavaDB 10.8) with an example for 2 days now.

I need to fetch some records from a table 'tbl_students' using it.
But i keep getting a NulPointerException, I cant figure out why.

This procedure takes mainly 2 parameters – pagenumber & maxRows, both as integers.

I created the stored procedure in derby using this:
CREATE PROCEDURE pageStudents (IN pageNum INT, IN maxRows INT) LANGUAGE JAVA PARAMETER STYLE JAVA DYNAMIC RESULT SETS 1 READS SQL DATA EXTERNAL NAME 'derby.pagsortex.StudentStoredProcs.pageStudents'

I created the class and the method, compiled it and even packed it into a jar file properly without any error.

StudentStoredProcs.java

package derby.pagesortex;

import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class StudentStoredProcs {

  public static String GET_STUDENTS_QRY="SELECT s.roll,s.firstName,s.lastName,"
                                   +"s.dob,s.marks1,s.marks2,s.marks3,s.percent"
                                   +" FROM tbl_students s WHERE s.roll IN (";

  private static Connection getConnection() throws SQLException {
    Connection con=DriverManager.getConnection("jdbc:default:connection");
    con.setAutoCommit(false);
    return con;
  }

  public final static void pageStudents(int pageNum,int maxRows,ResultSet[] rs) throws SQLException {
	int totalRows=-1,pages=-1,searchVal=-1;
	final String countQry="SELECT COUNT(*) FROM tbl_students";
	StringBuffer buff;
	Connection con=null;
	PreparedStatement ps=null;
           rs=null; //if this is commented i get an error saying atleast one variable was left uninitialised
	try {
	  con = getConnection();
	  ps=con.prepareStatement(countQry);
	  rs[0]=ps.executeQuery(); //i get an error here
	  /*
	   Calculate pageNum from totalNumofrows with maxRecs
	   fetch next batch of records into resultset as next page
	   using get_students_qry
	  */
	  while(rs[0].next())
		totalRows=rs[0].getInt(1);
	  rs[0].close();
	  ps.close();
          System.out.println("Total Rows:"+totalRows);
	  //calculate pages and determine from which to which index
	  //is to be searched and prepare the query.
	  pages=totalRows/maxRows;
	  searchVal = pageNum * maxRows;
	  buff=new StringBuffer();
	  buff.append(GET_STUDENTS_QRY);
	  buff.append("?");
	  for(int i=0;i<maxRows;i++) {
	    buff.append(",?");
	  }
	  buff.append(")");
          System.out.println("\nSQL Query:\n"+buff.toString());
	  ps=con.prepareStatement(buff.toString());
	  for(int i=1;i<=maxRows;i++) {
		ps.setInt(i,searchVal);
                System.out.println("searchVal["+i+"]="+searchVal);
		searchVal+=1;
	  }
	  rs[0]=ps.executeQuery();
	} catch (SQLException sqlex) {
	  throw sqlex;
	} finally {
	  try {
	    if(rs != null)
	     rs[0].close();
	    if(ps != null)
	     ps.close();
	    if(con != null)
	     con.close();
	  } catch (SQLException sqlex1) {
		sqlex1.printStackTrace();
	  }
	}
  }

}

I created the database,table and even populated it data.
I installed the jar file(pagsortex.jar) without any error using this:
CALL sqlj.install_jar('<path-to-jar>','APP.pagsortexJar',0); CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','APP.pagsortexJar'); COMMIT;

The class i wrote to call this stored procedure :
StudentStoredProcTest.java

package sptest;

import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.CallableStatement;

public class StudentStoredProcTest {

  void testIt() {
    ResultSet rs=null;
    Connection conn=null;
    CallableStatement cs=null;
    try {
     Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
     conn=DriverManager.getConnection("jdbc:derby:<path-to-derbydbs>/pagesortex","<username>","<password>");
     System.out.println("Connection established successfully!");
     cs=conn.prepareCall("{CALL pageStudents(?,?)}");
     cs.setInt(1,1);
     cs.setInt(2,5);
     cs.executeQuery();
     rs=cs.getResultSet();
     while(rs.next()) {
       System.out.println(
         rs.getInt("roll")+","+
         rs.getString("firstName")+","+
         rs.getString("lastName")+","+
         new java.util.Date(rs.getDate("dob").getTime())+","+
         rs.getDouble("marks1")+","+
         rs.getDouble("marks2")+","+
         rs.getDouble("marks3")+","+
         rs.getDouble("percent")
         );
     }
    } catch (SQLException sqlex) {
      System.err.println("SQL Error:"+sqlex.getMessage());
      sqlex.printStackTrace();
    } catch (Exception ex) {
      System.err.println("Error:"+ex.getMessage());
      ex.printStackTrace();
    } finally {
      try {
        if(rs!=null)
          rs.close();
        if(cs!=null)
          cs.close();
        if(conn!=null)
          conn.close();
      } catch (SQLException ex2) {
        ex2.printStackTrace();
      }
    }
  }

  public static void main(String args[]) {
    StudentStoredProcTest procTest=new StudentStoredProcTest();
    procTest.testIt();
  }

}

The error trace i got is:

java sptest.StudentStoredProcTest
Connection established successfully!
SQL Error:The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
java.sql.SQLException: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedCallableStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown Source)
at sptest.StudentStoredProcTest.testIt(StudentStoredProcTest.java:22)
at sptest.StudentStoredProcTest.main(StudentStoredProcTest.java:58)
Caused by: java.sql.SQLException: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 13 more
Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.javaException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
... 10 more
Caused by: java.lang.NullPointerException
at derby.pagesortex.StudentStoredProcs.pageStudents(StudentStoredProcs.java:32)
at org.apache.derby.exe.acf81e0010x0142xc795x43dex00000167f4380.g0(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source)
at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
... 6 more

Although I did this referring DerbySQLroutine-DB-derby-wiki, this code is mine for most of the part.
So, i know errors are bound to exist

Any input is greatly appreciated.
Sree

PS:I believe this logic may too have mistakes, do point out if that strikes you...

[Rob Spoor]

Ravi Sree wrote:

  public final static void pageStudents(int pageNum,int maxRows,ResultSet[] rs) throws SQLException {
...
           rs=null; //if this is commented i get an error saying atleast one variable was left uninitialised
...
	  rs[0]=ps.executeQuery(); //i get an error here

You explicitly set rs to null, then try to set its first element. Of course that's going to cause a NullPointerException. You should remove that line and fix the error that then occurs. Can you show the exact error message?

[Ravi Sree]

Hi Rob,

I did what you suggested, but shouldn't each variable be initialised?
I once got an error saying 'Atleast one variable was left unintialised' or something...

Well, i did go a bit further than that after that.
Some of the changes i did were-

1. I used java.sql.Statement instead of PreparedStatement in preparing the query.
Since it is inside the backend that shouldn't harm any security principle, I hope.
2. Later on, i figured the reason for the problem -
I was closing the ResultSet in the finally block of Stored Procedure - out of habit
I commented that and only closed the Statement and Connection.
3. I was adding a ',' in the end and forgetting to remove it from the query.
Well I removed that.
4. Also I was calling and executeQuery() instead of execute() for calling the Stored Procedure
So, i replaced that too..

After all these analysis and repair I finally got it working..
I think if eventhough I was able to do it, coderanch being there is a great relief for many like me..

Sree

[Rob Spoor]

Ravi Sree wrote:Hi Rob,

I did what you suggested, but shouldn't each variable be initialised?
I once got an error saying 'Atleast one variable was left unintialised' or something...

rs is an argument, so it's initialized with whatever value you use when you call the method. If you got that error, it was not caused because rs was not assigned inside this method, or it was a local variable before.

[Ravi Sree]

Got it.

Since it is called by the derby engine itself, is it initialised already, right.?