Posted In: Database, JDBC

DB2 : Java Accessing the resultset returned from Stored Procedure

Taking sample example of Countries, we will return the list of countries from stored procedure matching the required criteria

Creating Table


CREATE TABLE COUNTRY(COUNTRY_CODE VARCHAR(20), COUNTRY_NAME VARCHAR(250));

 

Inserting Sample Values:


INSERT IN TO COUNTRY VALUES("IND", "India");
INSERT IN TO COUNTRY VALUES("USA", "United States of America");
INSERT IN TO COUNTRY VALUES("UK", "United Kingdom");

 

Writing Stored Procedure: 


CREATE OR REPLACE PROCEDURE SEARCH_COUNTRY (IN searchKey varchar(30)) 
 DYNAMIC RESULT SETS 1 
 LANGUAGE SQL 
 BEGIN 
 DECLARE result_set_1 CURSOR WITH RETURN TO CLIENT FOR
 SELECT * FROM country WHERE country_name LIKE searchKey; 
 OPEN result_set_1;
 END;

Instead of fetching rows in a loop by the fetch statement, you can return a complete result  set to the calling client program.

  • the stored procedure must be specified with the DYNAMIC RESULT SETS clause
  • a cursor must be declared with the WITH RETURN clause
  • the cursor must be opened by the OPEN statement to return the result set
  • don’t close the cursor for the client application

It’s possible to return multiple result sets. Then the number of result sets must be increased in the DYNAMIC RESULT SET clause and for each result set a cursor must be declared.

 

Java code to access the Procedure:


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;

public class TestSQLProcedureCursor {
    public static void main(String[] args) {
        if (args.length < 1) {
            System.out.println("Usage: searchKey"); 
            System.exit(1);
        }
       try {
           // load the DB2 Driver
           Class.forName("com.ibm.db2.jcc.DB2Driver");
           // establish a connection to DB2
           Connection db2Conn = DriverManager.getConnection("jdbc:db2://HOSTNAME:PORTNO/DATABASENAME","DBUSER","DBPASSWORD");
           // use a statement to gather data from the database
           CallableStatement cs = db2Conn.prepareCall( "{call SEARCH_COUNTRY(?)}");
           // Set the value for the IN parameter
           cs.setString(1, args[0]);
           // Execute the stored procedure
           // Returns true if it returns result set or false for count 
           boolean result = cs.execute();
           if (result) {
               ResultSet rs = cs.getResultSet();
               if (rs != null) {
                  System.out.println("CODE\tNAME");
                  while(rs.next()) {
                        System.out.println(rs.getString(1)+ "\t" + rs.getString(2));
                  }
               }
            }
         } catch (ClassNotFoundException cnfe) {
            cnfe.printStackTrace();
         } catch (SQLException sqle) {
            sqle.printStackTrace();
         } catch (Exception e) {
            e.printStackTrace();
         }
      }
}

 

by , on April 19th, 2018

  • Categories