Java Database Connectivity (JDBC)

I am getting this error when I try to close a connection after running a query.

If I commit the transaction after the query, I don't get the error. As far as I am aware queries are not part of transactions.

Is there any way around not committing a query?

Here is my code that generated the error.

/*
 * Main.java
 *
 * Created on December 21, 2006, 1:27 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 *
 * This class relies on the methods in JDBCManagerV3.java for
 * simple actions such as opening and closing connections.
 */

package ch07v1;

import java.sql.*;

/**
 *
 * @author Administrator
 */
public class Main {
    static Connection conn = null;
    static PreparedStatement pstmt = null;
    static ResultSet rset = null;
    
    static String sqlInsert     = "insert into COUNTRIES "
                                + "(COUNTRY, COUNTRY_IS0_CODE, REGION) "
                                + "values (?, ?, ?)";
    static String sqlQuery      = "select * from COUNTRIES";
    
    
        
    static String url = "my database address";
    static String username = "app";
    static String password = "app";
    
    /** Creates a new instance of Main */
    public Main() {
    }
    
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
 
        
        try {
            conn = JDBCManagerV3.getConnection(url, username, password, false);
            pstmt = conn.prepareStatement(sqlInsert);
            
            // transaction begins here
            cleanupTable();
            doEUBatch();
            doNABatch();
            System.out.println("\nChecking the table before commit or rollback:");
            doQuery();
            
            // pretend we need to roll back
            JDBCManagerV3.rollback(conn);
            System.out.println("\n\nChecking the table after rollback:");
            doQuery();
            JDBCManagerV3.close(pstmt);
            
            pstmt = conn.prepareStatement(sqlInsert);
            doEUBatch();
            doNABatch();
            conn.commit();
            System.out.println("\nChecking the table after the commit:");
            doQuery();
            // conn.commit(); // WHEN THIS IS COMMENTED OUT, I GET AN ERROR.
            
        } catch (SQLException e) {
            // an exception means something failed, so do a rollback
            JDBCManagerV3.rollback(conn);
            e.printStackTrace();
        } finally {
            JDBCManagerV3.close(rset);
            JDBCManagerV3.close(pstmt);
            JDBCManagerV3.close(conn);
        }
    }
    
    public static void cleanupTable() {
        // if there is data in the COUNTRIES table from previous examples
        // then delete the old data
        
        Statement stmt = null;
        
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("delete from COUNTRIES");
            conn.commit();
            System.out.println("Database has been cleaned");
        } catch (Exception e) {
            JDBCManagerV3.rollback(conn);
        } finally {
            JDBCManagerV3.close(stmt);
        }
    }

    
    public static void doEUBatch() throws SQLException {
        pstmt.setString(1, "Kyrgyzstan");
        pstmt.setString(2, "KG");
        pstmt.setString(3, "Asia");
        pstmt.addBatch();
        
        pstmt.setString(1, "Great Britain");
        pstmt.setString(2, "GB");
        pstmt.setString(3, "Europe");
        pstmt.addBatch();
        
        pstmt.setString(1, "France");
        pstmt.setString(2, "FR");
        pstmt.addBatch();
        
        pstmt.executeBatch();
    }
    
    public static void doNABatch() throws SQLException {
        pstmt.setString(1, "United States");
        pstmt.setString(2, "US");
        pstmt.setString(3, "North America");
        pstmt.addBatch();
        
        pstmt.setString(1, "Canada");
        pstmt.setString(2, "CA");
        pstmt.addBatch();
        
        pstmt.executeBatch();
    }
    
    public static void doQuery() throws SQLException {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            rset = stmt.executeQuery(sqlQuery);
            int rownum = 1;
            if (rset.next()) {
                do {
                    System.out.print("\nRow " + rownum++ + " is ");
                    System.out.print(rset.getString(1) + ", ");
                    System.out.print(rset.getString(2) + ", ");
                    System.out.print(rset.getString(3));
                } while (rset.next());
            } else {
                System.out.println("NO RESULTS IN TABLE");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCManagerV3.close(rset);
            JDBCManagerV3.close(stmt);
        }
    }
}

Here is the output

Database has been cleaned

Checking the table before commit or rollback:

Row 1 is Kyrgyzstan, KG, Asia
Row 2 is Great Britain, GB, Europe
Row 3 is France, FR, Europe
Row 4 is United States, US, North America
Row 5 is Canada, CA, North America

Checking the table after rollback:
NO RESULTS IN TABLE

Checking the table after the commit:

Row 1 is Kyrgyzstan, KG, Asia
Row 2 is Great Britain, GB, Europe
Row 3 is France, FR, Europe
Row 4 is United States, US, North America
org.apache.derby.client.am.SqlException: java.sql.Connection.close() requested while a transaction is in progress on the connection.The transaction remains active, and the connection cannot be closed.
        at org.apache.derby.client.am.Connection.checkForTransactionInProgress(Unknown Source)
        at org.apache.derby.client.am.Connection.closeResourcesX(Unknown Source)
        at org.apache.derby.client.am.Connection.closeX(Unknown Source)
        at org.apache.derby.client.am.Connection.close(Unknown Source)
        at ch07v1.JDBCManagerV3.close(JDBCManagerV3.java:97)
        at ch07v1.Main.main(Main.java:76)
Row 5 is Canada, CA, North America

Message was edited by:
klintonray

Message was edited by:
klintonray