Win a copy of TensorFlow 2.0 in Action this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

Writing a Derby Stored procedure for paginatng records

 
Ranch Hand
Posts: 64
MySQL Database PHP Java
  • 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...
 
Sheriff
Posts: 22030
108
Eclipse IDE Spring VI Editor Chrome Java Ubuntu 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
MySQL Database PHP Java
  • 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
Posts: 22030
108
Eclipse IDE Spring VI Editor Chrome Java Ubuntu 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
MySQL Database PHP Java
  • 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.?
 
If tomatoes are a fruit, then ketchup must be a jam. Taste this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic