CODEWITHSUNDEEP
javasqloracle-databasejdbc
Tom Stone
Tom Stone

Reputation: 80

statement.executeUpdate() always returns 1

I have a problem with the JDBC executeUpdate() method. It always returns 1 whether it updates a row or not. As far as I understand the method it should return 0 is no rows are altered.

Here is a sample of my code:

try {
conn = pool.getConnection();
        PreparedStatement ps = conn.prepareStatement("{CALL UPDATE_USER (?,?,?)}");
        ps.setString(1, field.toString());
        ps.setString(2, change);
        ps.setString(3, userID);
        int updated = ps.executeUpdate();
        System.out.println(updated);
        if(updated==0){
            throw new NoUserException();
        }
        ps.close();
    } catch (SQLException e) {
        log.error("An error occurred while creating the connection");
        e.printStackTrace();
    } finally {
        pool.returnConnection(conn);
    }

Could this be because I'm using a prepared statement or a stored procedure?

Here is the stored procedure:

create or replace
PROCEDURE UPDATE_USER
(
  updateColumn IN user_tab_columns.column_name%type,
  changeStr IN VARCHAR2,
  unID IN VARCHAR2
)
IS
BEGIN
  EXECUTE IMMEDIATE
    'UPDATE
      users
SET ' || updateColumn || '= :1
WHERE
  uniqueID = :2'
  USING changeStr, unID; 
END;

Upvotes: 1

Views: 2956

Answers (2)

vijayaragavan
vijayaragavan

Reputation: 176

You can use Callable statement a procedure or function. And return the no of rows affected as a return parameter

Upvotes: 0

igr
igr

Reputation: 3499

It can't get info from stored proc execution. If you want to get row count (or anything else) switch PROCEDURE to FUNCTION, add return clause in this function, and change your call to something like ? = CALL ... Test with sql%rowcount inside function to get impacted row count.

Upvotes: 2

Related Questions