Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Writing a Derby Stored procedure for paginatng records

 
Ravi Sree
Ranch Hand
Posts: 64
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


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

StudentStoredProcs.java


I created the database,table and even populated it data.
I installed the jar file(pagsortex.jar) without any error using this:


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


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
Sheriff
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ravi Sree wrote:

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
Ranch Hand
Posts: 64
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Pie
Posts: 20753
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 64
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Got it.

Since it is called by the derby engine itself, is it initialised already, right.?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic