Audience

People who need to use IBM DB2 Connect with JDBC

Purpose

Illustrate a sample Java class that uses JDBC with db2Connect.

Sample

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.PrintWriter;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


public class TestCallProcedure {
    public static void main(String[] args) {
        System.out.println("Connection/SQL test starting TestCallProcedure.java...");

        String driver = "com.ibm.db2.jcc.DB2Driver";
        String url = "jdbc:db2:inyodb2y";
        String userid = USER_ID;
        String password = PASSWORD;

        String location = "ucbkdb2y.";
        String schema = "stutest.";
        String procName = "acet327";

        Connection con = null;

        try { // load the driver
            Class.forName(driver);
        } catch (Exception e) {
            System.out.println("error loading driver");
            e.printStackTrace();

            return;
        }

        try {
            con = DriverManager.getConnection(url, userid, password);
            System.out.println("Connection successful!");

            CallableStatement cs = con.prepareCall("{call " + location +
                    schema + procName +
                    "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}",
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            //set inputs
            cs.setString(1, "11006");     //ccn
            cs.setString(2, "11667051");  //sid
            cs.setString(3, "    ");      //term year
            cs.setString(4, " ");         //term cd
            cs.setString(5, " ");         //term sub cd

            //set outputs
            cs.registerOutParameter(6, java.sql.Types.CHAR);
            cs.registerOutParameter(7, java.sql.Types.CHAR);
            cs.registerOutParameter(8, java.sql.Types.CHAR);
            cs.registerOutParameter(9, java.sql.Types.CHAR);
            cs.registerOutParameter(10, java.sql.Types.CHAR);
            cs.registerOutParameter(11, java.sql.Types.CHAR);
            cs.registerOutParameter(12, java.sql.Types.CHAR);
            cs.registerOutParameter(13, java.sql.Types.CHAR);
            cs.registerOutParameter(14, java.sql.Types.CHAR);
            cs.registerOutParameter(15, java.sql.Types.CHAR);
            cs.registerOutParameter(16, java.sql.Types.CHAR);
            cs.registerOutParameter(17, java.sql.Types.CHAR);
            cs.registerOutParameter(18, java.sql.Types.CHAR);
            cs.registerOutParameter(19, java.sql.Types.CHAR);

            // execute
            ResultSet rs = cs.executeQuery();

            // display returned values
            System.out.println("\nreturned parameters...");

            System.out.println("error-cd: "          + cs.getString(6));
            System.out.println("dept-cd: "           + cs.getString(7));
            System.out.println("course-num: "        + cs.getString(8));
            System.out.println("exclude_educ_grp: "  + cs.getString(9));
            System.out.println("section_num: "       + cs.getString(10));
            System.out.println("primary_secondary: " + cs.getString(11));
            System.out.println("format_det: "        + cs.getString(12));
            System.out.println("cancel_flag: "       + cs.getString(13));
            System.out.println("instructor_count: "  + Integer.parseInt(cs.getString(14)));
            System.out.println("open_count: "        + Integer.parseInt(cs.getString(15)));
            System.out.println("reserve_count: "     + Integer.parseInt(cs.getString(16)));
            System.out.println("wait_list_count: "   + Integer.parseInt(cs.getString(17)));
            System.out.println("message_text: "      + cs.getString(18));
            System.out.println("number_of_rows: "    + Integer.parseInt(cs.getString(19)));

            System.out.println("\nresult set...");
            System.out.println(
                "\tccn\tsect_num\tinstr_cnt\topen_cnt\treserve_cnt\twaitlist_cnt");

            int numRows = Integer.parseInt(cs.getString(19));

            boolean b = rs.next();
            for (int i = 0; i < numRows; i++) {
                if (!b) {
                    System.out.println("result set rows does not match output parameter row count");
                    System.exit(3);
                }
                System.out.println("\t"   + rs.getString("ccn") +
                                   "\t"   + rs.getString("sect_num") +
                                   "\t\t" + Integer.parseInt(rs.getString("instr_cnt")) +
                                   "\t\t" + Integer.parseInt(rs.getString("open_cnt")) +
                                   "\t\t" + Integer.parseInt(rs.getString("reserve_cnt")) +
                                   "\t\t" + Integer.parseInt(rs.getString("waitlist_cnt")));
                b = rs.next();
            }

            System.out.println("\nnumber of rows found: " + numRows);
        } catch (SQLException e) {
            System.out.println("Caught SQLException!");
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}