asked on

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.

poluru

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)

isond

ASKER

Poluru,

Sorry my mistake, I am using SQL server.

Darren.

SOLUTION

poluru

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

isond

ASKER

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.

Neutron

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:)

isond

ASKER

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.

ASKER CERTIFIED SOLUTION

Neutron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

isond

ASKER

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.

Neutron

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:)

isond

ASKER

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.

Neutron

Try registering your output parameter as Types.INTEGER

Greetings,
Ntr:)

isond

ASKER

Ntr,

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

Darren.

Neutron

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:)

isond

ASKER

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.

Neutron

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:)

isond

ASKER

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.

Neutron

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:)

sudhakar_koundinya

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.