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.

CallableStatement can't fetch values

John Francis Ochotorina

Greenhorn

Posts: 16

posted 8 months ago

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.

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

posted 8 months ago

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!

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.

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.

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?

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'.

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.