Behavior of SELECT query using executeUpdate

Question

I have come across a strange behavior while executing a SELECT query using Statement#executeUpdate() by mistake. While the Javadoc clearly states that executeUpdate() throws SQLException if the given SQL statement produces a ResultSet object. But when I'm executing SELECT * from TABLE_NAME, I don't get any exception. Instead I'm getting an return value which is same as the no. of rows selected, if no. is less than or equal to 10. If the no. is more than 10, the return value is always 10.

Connection conn;
Statement stmt;
try {
    conn = getConnection();
    stmt = conn.createStatement();
    int count = stmt.executeUpdate("SELECT * from TABLE_NAME");
    log.info("row count: " + count);
} catch (SQLException e) {
    log.error(e);
    // handle exception
} finally {
    DbUtils.closeQuietly(stmt);
    DbUtils.closeQuietly(conn);
}

I am using Oracle 10g.

Am I missing something here or is it up to the drivers to define their own behavior?

Be aware that the javadoc specifies the expected behavior, not necessarily the actual behavior as implemented in the specific driver! — Mark Rotteveel
– Mark Rotteveel, Commented Dec 19, 2012 at 12:35

Flanfl · Accepted Answer · 2012-12-19 07:46:09Z

4

This behaviour is definetely contradicts Statement.executeUpdate API. What's interesting, java.sql.Driver.jdbcCompliant API says "A driver may only report true here if it passes the JDBC compliance tests". I tested oracle.jdbc.OracleDriver.jdbcCompliant - it returns true. I also tested com.mysql.jdbc.Driver.jdbcCompliant - it returns false. But in the same situation as you describe it throws

Exception in thread "main" java.sql.SQLException: Can not issue SELECT via executeUpdate().

It seems that JDBC drivers are unpredictable.

edited Dec 19, 2012 at 7:46

Flanfl

52410 silver badges30 bronze badges

answered Dec 19, 2012 at 7:23

Evgeniy Dorofeev

137k31 gold badges209 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bowmore Over a year ago

What specifications state and what vendors deliver are still 2 different things it seems.

mancini0 Over a year ago

A comment in this related question explains the strange behavior for Oracle drivers. stackoverflow.com/questions/4756094/…

Andremoniy · Accepted Answer · 2019-08-28 05:32:55Z

3

According to the specifications Statement.executeUpdate() method returns the row count for SQL Data Manipulation Language (DML).

UPD: I attempted to make an assumption about the returned result (which is always <=10). It seems, that the oracle statement's implementation returns here a number of a such called premature batch count (according to the decompiled sources OraclePreparedStatement class). This is somehow linked to the update statements. May be this value equals 10 by default.

UPD-2: According to this: Performance Extensions: The premature batch flush count is summed to the return value of the next executeUpdate() or sendBatch() method.

edited Aug 28, 2019 at 5:32

answered Dec 19, 2012 at 7:04

Andremoniy

35.2k25 gold badges143 silver badges259 bronze badges

Comments

shazin · Accepted Answer · 2012-12-19 07:01:36Z

2

The query you are using doesn't produce a ResultSet but affects Rows obviously. That's why you don't get an SQLException but a count of the no of rows affected. The mystery is why it doesn't go beyond 10. May it is Oracle JDBC Driver Implementation Specific.

answered Dec 19, 2012 at 7:01

shazin

22k3 gold badges58 silver badges72 bronze badges

Comments

jlordo · Accepted Answer · 2012-12-19 07:07:05Z

1

Your sql query is to retrieve all rows from table_name. So, you can use execute() method instead of executeUpdate() method. Because later method generally use when your task is related database manipulating language like update query.

edited Dec 19, 2012 at 7:07

jlordo

37.9k7 gold badges63 silver badges84 bronze badges

answered Dec 19, 2012 at 7:05

ved

9092 gold badges17 silver badges43 bronze badges

1 Comment

user207421 Over a year ago

Doesnt' answer the question.

abhishek · Accepted Answer · 2012-12-28 14:09:53Z

1

use

int count = stmt.executeQuery("SELECT * from TABLE_NAME");

instead of

int count = stmt.executeUpdate("SELECT * from TABLE_NAME");

for getting total no. of rows.

answered Dec 28, 2012 at 14:09

abhishek

375 bronze badges

Collectives™ on Stack Overflow

Behavior of SELECT query using executeUpdate

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related