Getting multiple result sets from a stored procedure.

Hi,

I am trying to get 2 result sets from a stored procdure (using an Oracle Database). I am using a callable statement to access the result sets, the code is below.

CallableStatement stmt = con.prepareCall("{call newextract(?)}");

rs2.next();
int max_tran = rs2.getInt("max_tran"));
stmt.getMoreResults();
ResultSet rs = stmt.getResultSet();
while (rs.next())
{
     // Get the fields from the database.

        // Store database fields in a structure.

       String pan = rs.getString("pan");
       int state = rs.getInt("state");
}

The problem is it does not recognise the field names e.g. pan. I can select them fine by column number but I would prefer to use column name for readability and maintanability of the code.

Hope you can help.

Darren.
isondAsked:
Who is Participating?
 
NeutronConnect With a Mentor Commented:

What do you get with rs.getMetaData().getColumnName( 1 )

Greetings,
    Ntr:)
0
 
poluruCommented:
hi,
sorry to ask this question...how will we get two resultsets in oracle ?
can u please show me the StoredPocedure.
why iam asking is, i thought getting more than one resultset is possible only in SQL SERVER not in oracle
(but u can if u use packages and refcursors)
0
 
isondAuthor Commented:
Poluru,


Sorry my mistake, I am using SQL server.

Darren.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
poluruConnect With a Mentor Commented:
here iam giving the code i used before for getting more than one resultset

     public static Vector getMainsiteIndexContents()
     {
              Connection con=null;
               CallableStatement cst=null;
               ResultSet rs=null;
               Vector contents=new Vector();

               try{
                    con=DataBase.getConnection();
                    cst=con.prepareCall("{ call GetMainsiteIndexContentsSp }");

                         cst.execute();

                         rs=cst.getResultSet();

                    do
                    {
                         Vector temp=new Vector();
                         while(rs.next())
                         {
                                   temp.add(rs.getString(1));
                                   temp.add(rs.getString(2));
                         }

                         contents.add(temp);

                    }
                    while(cst.getMoreResults());

                    return contents;

              }
            catch(Exception e)
              {
                System.out.println("in helper "+e);
                return contents;
            }
            finally
            {
                       try
                       {
                            if(rs!=null)  rs.close();
                          if(cst!=null)  cst.close();
                          if(con!=null) con.close();
                         }
                     catch(Exception e)
                         {
                           System.out.println(e);
                     }
            }

     }

0
 
isondAuthor Commented:
I am doing a similar thing but I have a slight difference. Firstly the two recordsets are completely different (The first has one field, the second recordset has a lot more different fields).

That should not make a difference, however instead of using rs.getString(1) I want to use rs.getString("pan").

This does not work, any ideas why.

Darren.
0
 
NeutronCommented:
There was one strange behaviour of MSSQL with JdbcOdbc bridge that you had to retrieve fields of a row in order that they were declared in sql statement which created resultset...

Greetungs,
    Ntr:)
0
 
isondAuthor Commented:
Ntr,

I have retrieved the fields in the same order that they are declared in the select statement in the stored procedure, it seems there is a problem with the retrieving of the second recordset.
When I use rs.getString("pan") it claims that it can't find the column (pan is the first field in the resultset).

Darren.
0
 
isondAuthor Commented:
Ntr,

Using rs.getMetaData().getColumnName( 1 ) I get 'pan' which is the name of my field.

I have even tried the following line.

rs.getString(rs.getMetaData().getColumnName(1))

I get the error column not found but surely if it can find a column name then the column must exist!

Is this a bug in JAVA or the ODBC driver?

Darren.


0
 
NeutronCommented:
Maybe your driver got confused bc of callable statement that should return resultset(s) and escape sequence is not formed like that. Add ?= in front of call escape, like this:

CallableStatement stmt = con.prepareCall( "{?=call newextract(?)}" );

Greetings,
    Ntr:)
0
 
isondAuthor Commented:
Ntr,

If I use con.prepareCall( "{?=call newextract(?)}")

do I have to register the first parameter as an output parameter, if I don't I get COUNT field incorrect.

If I have to register it what type to I register it as, java.sql.OTHER and java.sql.JAVA_OBJECT give me Invalid SQL data type.

Darren.
0
 
NeutronCommented:

Try registering your output parameter as Types.INTEGER

Greetings,
    Ntr:)
0
 
isondAuthor Commented:
Ntr,

That works as far as setting the output parameter type is concerned but I still get column not found as before.


Darren.
0
 
NeutronCommented:

It is not a normal behaviour and it is not Java bug.

Can you post your driver loading line of code, also first part of your select statement (before where clause), and which servicepack you have installed.

Greetings,
    Ntr:)
0
 
isondAuthor Commented:
This is how I get the connection to the database through the DSN, an SQL server driver is used.

String url = "jdbc:odbc:postilion"; // DSN to connect to database

con = DriverManager.getConnection url, "sa", "");


The client machine is Windows 2000 service pack 2.

The server is running NT server with service pack 6 and SQL server 7 with service pack 2.

Darren.
0
 
NeutronCommented:

I dont't have this problem with identical server, only clients are NT workstations...

Can you test this from some NT client?

Anyway, it is obviously a strange behaviour, just like this one that I have when I don't retrieve fields in the original order.

As a workaround, you can write a simple function which would use metadata getColumnName and getColumnCount to give you index of a field with specified name :-(

Greetings,
    Ntr:)
0
 
isondAuthor Commented:
Ntr,

Thanks for your help, I will try to isolate the problem and try to find out what is causing it. In the meantime I will use a work around using the column number. I will post anything I find out here.

Darren.
0
 
NeutronCommented:

Ok, if you find the exact reason i'm interested to hear about it ('will remain subscribed).

I wish I could help but at least you know that you're doing it in the right way, but resultsets behave strange.

Best wishes,
    Ntr:)
0
 
sudhakar_koundinyaCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:


[Split points between poluru and Neutron]


Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
sudhakar_koundinya
EE Cleanup Volunteer
---------------------
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post your concern in THIS thread.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.