CODEWITHSUNDEEP
javajdbcplsql
spiderman
spiderman

Reputation: 11112

calling PLSQL procedure from Java

Below is my Java program. I am calling a PLSQL procedure to update the Employee name. I turned off the commit in the PLSQL code so that I can do the commit and rollback from Java code. But even after I turned off the auto commit and doing explicit rollback, still the details are updated in the table. How? I have no idea, please help.

Here's my Java code. In PLSQL, it just read the value and does an update statement . No commits.

public class TestCommit {
    public static void main(String[] args) throws SQLException, IOException {
        CallableStatement callableStatement = null;
        Connection conn = null;
        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            conn = DriverManager
                    .getConnection("jdbc:oracle:thin:testuser/testpwd@//testdb:1521/testbx");
            conn.setAutoCommit(false);
            String sql = "{call testpkg.saveemployee(?,?)}";
            callableStatement = conn.prepareCall(sql);
            callableStatement.setString("name", "spider");
            callableStatement.setString("id", "A101");
            callableStatement.executeQuery();
            conn.rollback();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            // Close the statement
            callableStatement.close();
            // Close the connection
            conn.close();
        }
    }

}

edit: PLSQL

CREATE OR REPLACE PROCEDURE saveemployee(
       name IN employee.ename%TYPE,
       id IN employee.eid%TYPE)
IS
BEGIN

  UPDATE employee SET ename = name WHERE eid = id;

END;

Upvotes: 2

Views: 2394

Answers (1)

spiderman
spiderman

Reputation: 11112

My bad, I was calling a wrong procedure, there were two versions of the same procedure in two different packages, one has the commit , the other one doesn't have the commit. I was calling the one that had the commit. Now that the commit is removed from both procedures, my code seems to work now.

Upvotes: 1

Related Questions