Solved

Getting multiple result sets from a stored procedure.

Posted on 2001-07-04
18
440 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:isond
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 1

Expert Comment

by:poluru
ID: 6252099
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
 

Author Comment

by:isond
ID: 6252124
Poluru,


Sorry my mistake, I am using SQL server.

Darren.
0
 
LVL 1

Assisted Solution

by:poluru
poluru earned 75 total points
ID: 6252416
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
 

Author Comment

by:isond
ID: 6252502
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
 
LVL 4

Expert Comment

by:Neutron
ID: 6252765
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
 

Author Comment

by:isond
ID: 6253041
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
 
LVL 4

Accepted Solution

by:
Neutron earned 75 total points
ID: 6253081

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

Greetings,
    Ntr:)
0
 

Author Comment

by:isond
ID: 6253115
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
 
LVL 4

Expert Comment

by:Neutron
ID: 6253193
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:isond
ID: 6253231
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
 
LVL 4

Expert Comment

by:Neutron
ID: 6253471

Try registering your output parameter as Types.INTEGER

Greetings,
    Ntr:)
0
 

Author Comment

by:isond
ID: 6254700
Ntr,

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


Darren.
0
 
LVL 4

Expert Comment

by:Neutron
ID: 6254740

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
 

Author Comment

by:isond
ID: 6254773
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
 
LVL 4

Expert Comment

by:Neutron
ID: 6254812

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
 

Author Comment

by:isond
ID: 6254831
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
 
LVL 4

Expert Comment

by:Neutron
ID: 6254973

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
 
LVL 14

Expert Comment

by:sudhakar_koundinya
ID: 9017854
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Updating Java 9 87
countHi2 challenge 7 44
topping1 challenge 7 51
Java and GPO 11 47
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now