9550
   According to JDBC, "a ResultSet is automatically closed by the Statement
   that generated it when that Statement is closed, re-excuted, or is used
   to retrive the next result from a sequence of multiple results".
   This test program re-excutes a preparedStatement, which generates a
   ResultSet, without closing previous ResultSet, then we get a exception.
   If we explicitly close the ResultSet, then it runs fine.

//package test1.research;

/**
 * SimpleInc22.class test preparedStatement execution with JdbcOdbc bridge,
 * It shows that JdbcOdbc has a JDBC 1.* compliant problem.
 *
 * According to JDBC, "a ResultSet is automatically closed by the Statement
 * that generated it when that Statement is closed, re-excuted, or is used
 * to retrive the next result from a sequence of multiple results".
 * This test program re-excutes a preparedStatement, which generates a
 * ResultSet, without closing previous ResultSet, then we get a exception.
 * If we explicitly close the ResultSet, then it runs fine.
 *
 */

import java.io.*;
import java.util.*;
import java.sql.*;

public class SimpleInc22 {

    static String dbURL = "jdbc:odbc:PMB2";
    static boolean tranCommitMode = true;
    
    static String driverClassName =
// "com.ibm.ejs.jts.txjdbc.odbc.TxJdbcOdbcDriver";
    // "COM.ibm.db2.jdbc.app.DB2Driver";
// "com.ibm.db2.jdbc.app.DB2Driver";
"sun.jdbc.odbc.JdbcOdbcDriver";

    static Connection con = null;

final static String createTableStmt = "CREATE table IncTbl (primaryKey VARCHAR(32), theValue int)";
final static String drobTableStmt = "DROP table IncTbl";

    final static String createString = "INSERT INTO IncTbl(primaryKey, theValue) VALUES( ? , ? )";
    final static String loadString = "SELECT * FROM IncTbl WHERE primaryKey = ?";
    final static String storeString = "UPDATE IncTbl SET theValue = ? WHERE primaryKey = ?";
    final static String removeString = "DELETE FROM IncTbl WHERE primaryKey = ?";
  

    public static void main(String args[]) {
        String chkaccountURL;
        int i=0;

initConnection(driverClassName, dbURL);

updateDataBase();

readDataBase();

cleanup();

System.out.println("#### am I here?");
}
   

    private static void initConnection(String driverClassName, String dbURL) {
try {
Class.forName(driverClassName);
}
catch(Exception e) {
System.err.println("Failed to load: " + driverClassName);
e.printStackTrace();
return;
}

//
// add a trace file for jdbcodbc bridge
//
/*
try {
File outfile = new File("c:/clitrc/jdbc-driver.trace");
FileOutputStream out = new FileOutputStream(outfile);
DriverManager.setLogStream(new java.io.PrintStream(out));
} catch (Exception e){
System.err.println("Failed to setLogStream ");
e.printStackTrace();
return;
}
*/
try {
con = DriverManager.getConnection(dbURL, "pmb", "coalport");
// con.setAutoCommit(false);
}
catch(Exception e) {
System.err.println("Unable to get connection for url: "
+ dbURL);
e.printStackTrace();
System.exit(2);
}
    }
    

    private static void readDataBase()
    {
PreparedStatement pstmt = null;
ResultSet rs;

System.out.println("#### in readDataBase");
for (int i = 0; i < 3; i++) {
System.out.println("#### Iteration " + i + " in readDataBase");
if (i == 0) {
try {
pstmt = con.prepareStatement(loadString);
}
catch (SQLException e) {
System.err.println("create prepareStatement error");
e.printStackTrace();
}
}

try {
pstmt.setString(1, "The Count");
rs = pstmt.executeQuery();
 
dispResultSet(rs);

//
// if We close ResultSet explicitly, then everything is ok.
//
// rs.close();

// con.commit();
}
catch (SQLException e) {
System.err.println("SQLException : " + e);
e.printStackTrace();
System.exit(1);
}
catch (Exception e){
System.err.println("Exception : " + e);
e.printStackTrace();
System.exit(1);
}
} // end of for()
    }


    private static void updateDataBase()
    {
PreparedStatement pstmt = null;

//
// Create the table
//
try {
Statement stmt = con.createStatement();
stmt.executeUpdate( createTableStmt );
stmt.close();
con.commit();
System.out.println( "table created" );
}
catch( Exception e) {
System.out.println( "Create failed with:" );
e.printStackTrace();
}


try {
for (int count = 1; count < 5; count++) {
if (count == 1) {
try {
//pstmt = con.prepareStatement(storeString); //pmb
pstmt = con.prepareStatement(createString); //pmb
}
catch (SQLException e) {
System.err.println("create prepareStatement error");
e.printStackTrace();
}
}
System.out.println("#### Iteration " + count);

//pstmt.setInt(1, count+1);
//pstmt.setString(2, "The Count");
pstmt.setString(1, "The Count");
pstmt.setInt(2, count+1);

pstmt.executeUpdate();

con.commit();
}
}
catch (SQLException e) {
System.err.println("SQLException : " + e);
e.printStackTrace();
System.exit(1);
}
catch (Exception e){
System.err.println("Exception : " + e);
e.printStackTrace();
System.exit(1);
}
    }


    private static void dispResultSet(ResultSet rs)
        throws SQLException
    {
        System.out.println("#### in dispResultSet");
     ResultSetMetaData rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();

for(int i = 1; i <= numCols; i++) {
if (i > 1) System.out.print(",");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");

while (rs.next()) {
for (int i = 1; i <= numCols; i++) {
if (i > 1) System.out.print(",");
System.out.print(rs.getString(i));
}
System.out.println("");
}
    }


    private static void cleanup()
    {
//
// Drop the table
//
try {
Statement stmt = con.createStatement();
stmt.executeUpdate( drobTableStmt );
stmt.close();
con.commit();
System.out.println( "table dropped" );
}
catch( Exception e ) {
System.out.println( "table dump failed with:" );
e.printStackTrace();
}


try {
con.close();
}
catch (SQLException e) {
System.err.println("Error closing connection for: " + dbURL);
e.printStackTrace();
}
    }
}