• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

CallableStatement can't fetch values

 
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 1210
25
Android Python PHP C++ Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Python PHP C++ Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 Python PHP C++ Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
Well THAT's new! Comfort me, reliable tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic