Example usage for java.sql BatchUpdateException getSQLState

Introduction

In this page you can find the example usage for java.sql BatchUpdateException getSQLState.

Prototype

public String getSQLState() 

Source Link

Document

Retrieves the SQLState for this SQLException object.

Usage

From source file:TestBatchUpdate.java

public static void main(String args[]) {
    Connection conn = null;//from  w  w w.  j  a va  2  s  .com
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = getConnection();
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        conn.setAutoCommit(false);
        stmt.addBatch("INSERT INTO batch_table(id, name) VALUES('11', 'A')");
        stmt.addBatch("INSERT INTO batch_table(id, name) VALUES('22', 'B')");
        stmt.addBatch("INSERT INTO batch_table(id, name) VALUES('33', 'C')");
        int[] updateCounts = stmt.executeBatch();
        conn.commit();

        rs = stmt.executeQuery("SELECT * FROM batch_table");
        while (rs.next()) {
            String id = rs.getString("id");
            String name = rs.getString("name");
            System.out.println("id=" + id + "  name=" + name);
        }

    } catch (BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor error code: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }
    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor error code: " + ex.getErrorCode());
    } catch (Exception e) {
        System.err.println("Exception: " + e.getMessage());
    } finally {
        try {
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception ignore) {
        }
    }
}

---

From source file:BatchUpdate.java

public static void main(String args[]) {
    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;//from   w  w w .  j  a va2s.c  om
    Statement stmt;

    try {
        Class.forName("myDriver.ClassName");
    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        con.setAutoCommit(false);
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
        int[] updateCounts = stmt.executeBatch();
        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
        System.out.println("Table COFFEES after insertion:");

        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + " " + id + " " + price);
            System.out.println(" " + sales + " " + total);
        }
        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor: " + ex.getErrorCode());
    }
}

---

From source file:BatchUpdate.java

public static void main(String args[]) throws SQLException {

    ResultSet rs = null;//from   w  w w .j  a  v  a2 s . c  om
    PreparedStatement ps = null;

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;
    Statement stmt;
    try {

        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        con.setAutoCommit(false);

        stmt = con.createStatement();

        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

        int[] updateCounts = stmt.executeBatch();
        con.commit();
        con.setAutoCommit(true);

        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

        System.out.println("Table COFFEES after insertion:");
        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + "   " + id + "   " + price);
            System.out.println("   " + sales + "   " + total);
        }

        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("-----SQLException-----");
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

---

From source file:CreateRef.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;//w ww  .j a va 2  s .c o m
    Statement stmt;
    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        String createManagers = "CREATE TABLE MANAGERS OF MANAGER "
                + "(OID REF(MANAGER) VALUES ARE SYSTEM GENERATED)";

        String insertManager1 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES "
                + "(000001, 'MONTOYA', 'ALFREDO', '8317225600')";

        String insertManager2 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES "
                + "(000002, 'HASKINS', 'MARGARET', '4084355600')";

        String insertManager3 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES "
                + "(000003, 'CHEN', 'HELEN', '4153785600')";

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();
        stmt.executeUpdate(createManagers);

        con.setAutoCommit(false);

        stmt.addBatch(insertManager1);
        stmt.addBatch(insertManager2);
        stmt.addBatch(insertManager3);
        int[] updateCounts = stmt.executeBatch();

        con.commit();

        System.out.println("Update count for:  ");
        for (int i = 0; i < updateCounts.length; i++) {
            System.out.print("    command " + (i + 1) + " = ");
            System.out.println(updateCounts[i]);
        }

        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("Message:  " + b.getMessage());
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts for successful commands:  ");
        int[] rowsUpdated = b.getUpdateCounts();
        for (int i = 0; i < rowsUpdated.length; i++) {
            System.err.print(rowsUpdated[i] + "   ");
        }
        System.err.println("");
    } catch (SQLException ex) {
        System.err.println("------SQLException------");
        System.err.println("Error message:  " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

---

From source file:InsertStores.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;/*from w ww  . j a v a 2s  .  c o m*/
    Statement stmt;
    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();
        con.setAutoCommit(false);

        String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, "
                + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore1);

        String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, "
                + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore2);

        String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, "
                + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore3);

        String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, "
                + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore4);

        String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, "
                + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))";

        stmt.addBatch(insertStore5);

        int[] updateCounts = stmt.executeBatch();

        ResultSet rs = stmt.executeQuery("SELECT * FROM STORES");

        System.out.println("Table STORES after insertion:");
        System.out.println("STORE_NO  LOCATION          COF_TYPE     MGR");
        while (rs.next()) {
            int storeNo = rs.getInt("STORE_NO");
            Struct location = (Struct) rs.getObject("LOCATION");
            Object[] locAttrs = location.getAttributes();
            Array coffeeTypes = rs.getArray("COF_TYPE");
            String[] cofTypes = (String[]) coffeeTypes.getArray();

            Ref managerRef = rs.getRef("MGR");
            PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?");
            pstmt.setRef(1, managerRef);
            ResultSet rs2 = pstmt.executeQuery();
            rs2.next();
            Struct manager = (Struct) rs2.getObject("MANAGER");
            Object[] manAttrs = manager.getAttributes();

            System.out.print(storeNo + "   ");
            System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + "  "
                    + locAttrs[4] + " ");
            for (int i = 0; i < cofTypes.length; i++)
                System.out.print(cofTypes[i] + " ");
            System.out.println(manAttrs[1] + ", " + manAttrs[2]);

            rs2.close();
            pstmt.close();
        }

        rs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

---

From source file:com.oracle.tutorial.jdbc.JDBCTutorialUtilities.java

public static void printBatchUpdateException(BatchUpdateException b) {
    System.err.println("----BatchUpdateException----");
    System.err.println("SQLState:  " + b.getSQLState());
    System.err.println("Message:  " + b.getMessage());
    System.err.println("Vendor:  " + b.getErrorCode());
    System.err.print("Update counts:  ");
    int[] updateCounts = b.getUpdateCounts();
    for (int i = 0; i < updateCounts.length; i++) {
        System.err.print(updateCounts[i] + "   ");
    }//from  w ww.j  a  v  a 2s  .co m
}

---

From source file:com.dattack.dbcopy.engine.InsertOperationContext.java

private int executeBatch() throws SQLException {

    int insertedRows = 0;
    try {/* w w  w  .  ja va2s. c om*/
        final int[] batchResult = getPreparedStatement().executeBatch();

        for (int i = 0; i < batchResult.length; i++) {
            if (batchResult[i] > 0) {
                insertedRows += batchResult[i];
            } else if (batchResult[i] == Statement.SUCCESS_NO_INFO) {
                insertedRows++;
            }
        }

    } catch (final BatchUpdateException e) {
        LOGGER.warn("Batch operation failed: {} (SQLSTATE: {}, Error code: {}, Executed statements: {})",
                e.getMessage(), e.getSQLState(), e.getErrorCode(), e.getUpdateCounts().length);
    }

    getConnection().commit();
    return insertedRows;
}

---

From source file:jade.domain.DFDBKB.java

/**
 * Builds an error message for a <code>BatchUpdateException</code>
 *///from  w  w w. ja v  a 2s. co m
private String getBatchUpdateErroMsg(BatchUpdateException e) {
    StringBuffer msg = new StringBuffer("SQLException: " + e.getMessage() + "\n");
    msg.append("SQLState:  " + e.getSQLState() + "\n");
    msg.append("Message:  " + e.getMessage() + "\n");
    msg.append("Vendor:  " + e.getErrorCode() + "\n");
    msg.append("Update counts: ");

    int[] updateCounts = e.getUpdateCounts();
    for (int i = 0; i < updateCounts.length; i++) {
        msg.append(updateCounts[i] + "   ");
    }
    return msg.toString();
}

---

From source file:org.wso2.andes.store.rdbms.RDBMSStoreUtils.java

/**
 * Throws an {@link AndesBatchUpdateException} with specified
 * information. and rollback the tried batch operation.
 * //  w w  w  . j av  a 2 s . c om
 * @param dataList
 *            data objects will was used for batch operation.
 * @param connection
 *            SQL connection used.
 * @param bue
 *            the original SQL batch update exception.
 * @param task
 *            the string indicating the task tried out (in failed batch
 *            update operation)
 * @throws AndesBatchUpdateException
 *             the error.
 */
public <T> void raiseBatchUpdateException(List<T> dataList, Connection connection, BatchUpdateException bue,
        String task) throws AndesBatchUpdateException {

    int[] updateCountsOfFailedBatch = bue.getUpdateCounts();
    List<T> failed = new ArrayList<T>();
    List<T> succeded = new ArrayList<T>();

    for (int i = 0; i < updateCountsOfFailedBatch.length; i++) {
        T msgPart = dataList.get(i);
        if (Statement.EXECUTE_FAILED == updateCountsOfFailedBatch[i]) {
            failed.add(msgPart);
        } else {
            succeded.add(msgPart);
        }
    }

    rollback(connection, task); // try to rollback the batch operation.

    AndesBatchUpdateException insertEx = new AndesBatchUpdateException(task + " failed", bue.getSQLState(), bue,
            failed, succeded);
    throw insertEx;
}

---