• Post Reply Bookmark Topic Watch Topic
  • New Topic

Need help executing and reading the results of a Stored Procedure that returns a Table  RSS feed

 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello everyone,

I am new at calling stored procedures. I have the following stored procedure which was given to me :




I want from  my Java code to call this procedure and print all the results.
So I want to print the table of students, and for each student to print the array of the courses that he has taken.

Does any one has any idea in how to achieve this?

 
Stephan van Hulst
Saloon Keeper
Posts: 7737
142
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can get an instance of CallableStatement like this:Then you register your input parameters the same way you would with a PreparedStatement, and you register your output parameters with one of the CallableStatement.registerOutParameter() methods.
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have succeded calling the procedure. I wrote this code:



In the

it throws the following error:
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
where

 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am sorry there is one line missing



where the error is thrown
 
Stephan van Hulst
Saloon Keeper
Posts: 7737
142
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you need the metadata?
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
From the metadata, I get the column names like this:

                

and then I want to get the rest
 
Stephan van Hulst
Saloon Keeper
Posts: 7737
142
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why you want to know the names of the columns? You already know the names of the columns, they were given to you.
 
Dave Tolls
Ranch Hand
Posts: 2839
30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can't get the metadata from an ARRAY ResultSet (as you've seen).
You need to get the StructDescriptor, which holds that info.
See:
Steps for Retrieving Object Metadata
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, so how can i get the information inside ?
I was going to get the column names in order to loop to each column for each row.
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also seems like the Struct Descriptor and all of its methods are deprecated.
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I also wrote the following code:

   

And the result was:



 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am sorry in the for I meant to write

 
Knute Snortum
Sheriff
Posts: 3974
98
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's sample code for how you would ordinarily loop through a resultset where you know the table layout:
 
Dave Tolls
Ranch Hand
Posts: 2839
30
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ioanna Katsanou wrote:I also wrote the following code:


Can you post a bit more than that code snippet.
For example, where is that ResultSet from?

If it's from the Array then the first  "column" is the row number (I think) and the second (the STRUCT) is the actual data.
You can cast that to a java.sql.Struct.
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello !! So I think I am getting into something !! I am posting my whole code here:
Indeed as @Dave Tolls mentioned, I got the number and the Struct object.
Then on the Struct object, I managed to get the information of the first student.

I think I am very close!!
How do I get the information of the rest of the students???

 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry I got confused !!! It only returned the first student because I wrote as an input the user with id 1 !!! So it works !!! Thank you so much.
So just to check again there is no way to get the column names from the result set?
 
Dave Tolls
Ranch Hand
Posts: 2839
30
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For this:

there's no need to check for null, as 'rs' cannot be null at this point.

As for the columns, pre-12 you can cast the Struct to oracle.sql.STRUCT and get the meta data from there.
You can use it in 12 as well, but it's marked deprecated.  There doesn't seem to be a new mechanism (that I can find), so I would just use the old way for now.
 
Ioanna Katsanou
Ranch Hand
Posts: 111
1
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Dave thanks for the tip !!

When I try to use it, it is marked deprecated. Anyway let's wait till a solution comes out for this.

Thank you again for all your help!!
Ioanna
 
Dave Tolls
Ranch Hand
Posts: 2839
30
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"deprecated" doesn't mean it won't work.
It just means it's no longer the current way to use it.
However they are supposed to say (as part of the deprecation) what the proper way is...and they haven't.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!