• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

handle query in java that returns mutiple recordsets

The query is for sybase database.
when i run
sp__revtable 'tablename'

in isql it opens up 2 panes. How can i handle this within JAVA. I want to retrieve records from the second pane.
0
PearlJamFanatic
Asked:
PearlJamFanatic
2 Solutions
 
CEHJCommented:
You should normally use a join query. Why does it return more than one record set?
0
 
PearlJamFanaticAuthor Commented:
this could be a procedure. Can anyone tell how to handle stuff returned by the stored procedure in java.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
CEHJCommented:
That would depend on what the procedure returns. If it's a ResultSet, you'd handle it like any other
0
 
for_yanCommented:

This is an example of callable procedure returning more than one ResultSet from:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=/rzaha/callable.htm

It is for DB2 but JDBC should be more or less the same:
import java.sql.*;
import java.util.Properties;

public class CallableStatementExample1 {

    public static void main(java.lang.String[] args) {

        // Register the Native JDBC driver.  If we cannot
        // register the driver, the test cannot continue.
        try {
            Class.forName("com.ibm.db2.jdbc.app.DB2Driver");

            // Create the connection properties
            Properties properties = new Properties ();
            properties.put ("user", "userid");
            properties.put ("password", "password");

            // Connect to the local server database
            Connection c = DriverManager.getConnection("jdbc:db2://*local", properties);

            Statement s = c.createStatement();

            // Create a procedure with multiple ResultSets.
            String sql = "CREATE PROCEDURE MYLIBRARY.SQLSPEX1 " +
                         "RESULT SET 2 LANGUAGE SQL READS SQL DATA SPECIFIC MYLIBRARY.SQLSPEX1 " +
                         "EX1: BEGIN " +
                         "   DECLARE C1 CURSOR FOR SELECT * FROM QSYS2.SYSPROCS " +
                         "               WHERE SPECIFIC_SCHEMA = 'MYLIBRARY';  " +
                         "   DECLARE C2 CURSOR FOR SELECT * FROM QSYS2.SYSPARMS  " +
                         "               WHERE SPECIFIC_SCHEMA = 'MYLIBRARY';  " +
                         "   OPEN C1; " +
                         "   OPEN C2; " +
                         "   SET RESULT SETS CURSOR C1, CURSOR C2; " +
                         "END EX1 ";

            try {
                s.executeUpdate(sql);
                } catch (SQLException e) {
                // NOTE:  We are ignoring the error here.  We are making
                //        the assumption that the only reason this fails
                //        is because the procedure already exists. Other
                //        reasons that it could fail are because the C compiler
                //        is not found to compile the procedure or because
                //        collection MYLIBRARY does not exist on the system.
            }
            s.close();

            // Now use JDBC to run the procedure and get the results back. In
            // this case we are going to get information about 'MYLIBRARY's stored
            // procedures (which is also where we created this procedure, thereby
            // ensuring that there is something to get.
            CallableStatement cs = c.prepareCall("CALL MYLIBRARY.SQLSPEX1");

            ResultSet rs = cs.executeQuery();

            // We now have the first ResultSet object that the stored procedure
            // left open.  Use it.
            int i = 1;
            while (rs.next()) {
                System.out.println("MYLIBRARY stored procedure
                                   " + i + " is " + rs.getString(1) + "." +
                                   rs.getString(2));
                i++;
            }
            System.out.println("");

            // Now get the next ResultSet object from the system - the previous
            // one is automatically closed.
            if (!cs.getMoreResults()) {
                System.out.println("Something went wrong.  There should have
                                    been another ResultSet, exiting.");
                System.exit(0);
            }
            rs = cs.getResultSet();

             // We now have the second ResultSet object that the stored procedure
            // left open.  Use that one.
            i = 1;
            while (rs.next()) {
                System.out.println("MYLIBRARY procedure " + rs.getString(1)
                                    + "." + rs.getString(2) +
                                   " parameter:  " + rs.getInt(3) + " direction:
                                   " + rs.getString(4) +
                                   " data type: " + rs.getString(5));
                i++;
            }

            if (i == 1) {
                System.out.println("None of the stored procedures have any parameters.");
            }

            if (cs.getMoreResults()) {
                System.out.println("Something went wrong,
                                    there should not be another ResultSet.");
                System.exit(0);
            }

            cs.close();  // close the CallableStatement object
            c.close();   // close the Connection object.

        } catch (Exception e) {
            System.out.println("Something failed..");
            System.out.println("Reason: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Open in new window

0
 
objectsCommented:
That example just repeats what I had already suggested
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now