2

Is there any condition under which a SQL INSERT statement executed with the method executeUpdate on a PreparedStatement object would return 0 without throwing an SQLException?

For example:

String query = "INSERT INTO mytable (column1,column2) VALUES (5,6)";
PreparedStatement preparedStatement = connection.prepareStatement(query);
if(preparedStatement.executeUpdate()!=1){
// A strange error has occurred
} else{
// do what ever
}

Why do I ask? I currently always check to ensure the number of rows returned is equal to 1 but I wonder if that is overkill if it should never return anything but 1.

Usman Mutawakil
  • 4,993
  • 9
  • 43
  • 80
  • 1
    I believe an `INSERT INTO Table SELECT FROM Table` on an empty table might return 0. – Sotirios Delimanolis Aug 15 '13 at 18:12
  • Wouldn't that throw an SQL exception first? By empty table do you mean one that doesn't exist? – Usman Mutawakil Aug 15 '13 at 18:26
  • No, a table with no rows in it. Like this http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from – Sotirios Delimanolis Aug 15 '13 at 18:26
  • O I just reread your query/subquery. I didn't see the "SELECT". That makes sense. – Usman Mutawakil Aug 15 '13 at 18:28
  • You would have to test it because I don't know. It's also very probable you never use such a query in your application. – Sotirios Delimanolis Aug 15 '13 at 18:28
  • Retrieve the count if this is meaningful to you. Otherwise use a `try-catch`. Even if you were using the `INSERT-SELECT` statement @SotiriosDelimanolis is talking about, how would you know that a return of `0` is incorrect? This seems like a wrong approach to detect an erroneous condition. – c.s. Aug 15 '13 at 18:48

4 Answers4

10

So I tried it and, yes, you can get a 0 return value with an INSERT statement. This can happen if you SELECT * FROM a table without rows INTO another table. For example, say domain and domain2 have the same schema and domain2 is empty, doing the following

Connection con = dataSource.getConnection();
PreparedStatement ps = con.prepareStatement("INSERT INTO domain SELECT * FROM domain2");
System.out.println(ps.executeUpdate());

prints 0.

The only time I've ever used the return value of executeUpdate is with a DELETE statement on one row to make sure it existed before and was deleted. I don't think you should use it with INSERT.

Sotirios Delimanolis
  • 274,122
  • 60
  • 696
  • 724
3

From the documentation of the executeUpdate() method:

Returns: either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

0 seems like a perfectly natural result (i.e. not an error).

Jerome
  • 1,429
  • 11
  • 13
2

For the example SQL you provided or for any SQL where a single row is to be inserted from parameters, the result would always be 1 if no SQLException occurs.

Michael Edgar
  • 350
  • 3
  • 12
-1

Read again:

Returns: either (A) the row count for SQL Data Manipulation Language (DML) statements or (B) 0 for SQL statements that return nothing

Rodrigo
  • 1