This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
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

CallableStatement can't fetch values

 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I created a Stored Procedure where I can fetch all my data that I inserted in my following textfields. How can fetch all of this data by calling my Callable Statement? I think this is the easiest way than Batch Statement based on what I read. I only drag and drop this following components just a practice purposes.

Stored Procedure



I used OUT parameter to retrieve values using getXXX() methods. What DYNAMIC RESULT SETS 1 mean? I'm just little bit confuse since this is my first time to use Stored Procedure in derby.

Source Code



After I insert in Search textfields it throws me a error NullPointerExeption. I follow Derby Reference Manual so I can have a guide writing a proper Stored Procedure. This code is mine most of the part. Guide if I missed something wrong. Feel free to comment thanks.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wow, i haven't really looked at DB2 in years. Anyway, Returning result sets from SQL procedures explains that "cursors...can also be used to return result sets to the calling program." of which the first step is "Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement". So, it means the SP will be returning a cursor to the caller.

How you get that returned cursor into a variable is not something that i know.

 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:Wow, i haven't really looked at DB2 in years. Anyway, Returning result sets from SQL procedures explains that "cursors...can also be used to return result sets to the calling program." of which the first step is "Specify the DYNAMIC RESULT SETS clause in the CREATE PROCEDURE statement". So, it means the SP will be returning a cursor to the caller.

How you get that returned cursor into a variable is not something that i know.



I used derby in this application project. How can I eliminate this using derby? Thanks bro!
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:I used derby in this application project. How can I eliminate this using derby?

Apache Derby is based on DB2, so, if you want a cursor returned from the SP, you must use that statement.
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
John Francis Ochotorina wrote:I used derby in this application project. How can I eliminate this using derby?

Apache Derby is based on DB2, so, if you want a cursor returned from the SP, you must use that statement.



So will I call CURSOR WITH RETURN TO CLIENT. So I can retrieve the value using ResultSet?
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:So will I call CURSOR WITH RETURN TO CLIENT. So I can retrieve the value using ResultSet?


Unfortunately, i do not know. I have not touched DB2 in nearly a decade. I suggest you search for examples. If searching for Derby examples comes up short, try DB2 instead, the answers might be the same.
 
Karthik Shiraly
Bartender
Posts: 1210
25
Android C++ Java Linux PHP Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I don't think the database related code is giving the null pointer exception. This will throw one for example:

You don't really need an array of ResultSet's for the code you have posted. Change it to a single reference instead of array.

It always helps if you show us the full stack trace of any exception.
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Karthik Shiraly wrote:I don't think the database related code is giving the null pointer exception. This will throw one for example:

You don't really need an array of ResultSet's for the code you have posted. Change it to a single reference instead of array.

It always helps if you show us the full stack trace of any exception.


This is the exception that I received.

 
Paul Clapham
Sheriff
Posts: 21581
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at Frame.searchButtonActionPerformed(Frame.java:156)


But this stack trace refers to a class named Frame and a method named searchButtonActionPerformed. It's unrelated to anything that you've posted so far. So have a look at line 156 in that method and see what variable is being misused there.
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at Frame.searchButtonActionPerformed(Frame.java:156)


But this stack trace refers to a class named Frame and a method named searchButtonActionPerformed. It's unrelated to anything that you've posted so far. So have a look at line 156 in that method and see what variable is being misused there.


This is my line 156. is this the correct term should I use when using ResultSet?
 
Karthik Shiraly
Bartender
Posts: 1210
25
Android C++ Java Linux PHP Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First, 'data' is currently a null array reference. Change it to a single reference.

Second, data actually seems totally unnecessary here. You are executing the "Select * from sampleonly" query, but not using its resultset at all anywhere. Looks to me like you don't even need the query or 'data'.

 
Karthik Shiraly
Bartender
Posts: 1210
25
Android C++ Java Linux PHP Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I understand now what you tried to do. You tried to follow this guide https://wiki.apache.org/db-derby/DerbySQLroutines#Creating_Procedures but missed out some important details

Your java stored procedure should be in its own class and method, packaged in its own JAR, and declared to derby. Read the articles under https://db.apache.org/derby/docs/10.12/devguide/cdevdeploy23812.html on how to do this.

Derby then executes this java method when CALLed, passing it a pre-created ResultSet data[] array. You just have to run your query and copy its resultset into that array.

Currently, what you seem to have done is included the code of the stored proc too into the client app's action listener. That's not how Derby stored proc works. Follow that guide carefully, and you should be good to go.

But before all that, ask yourself if you really need a stored proc at all for such a simple query.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic