Java SQL: Statement.hasResultSet()?

Question

My app uses MySQL on one platform and SQLite on another, as such there are differences, such as that when using query like DELETE FROM USERS:

On MySQL, PreparedStatement.getResultSet() will return null.
On SQLite, PreparedStatement.getResultSet() will throw java.sql.SQLException: no ResultSet available.

This may or may not be a bug in SQLite implementation (I think it is supposed to return null), but I have to deal with this somehow.

I could use a try { ... } catch (SQLException e) { ... } and if the exception message is "no ResultSet available", simply return null manually. This doesn't feel like a right way to do it though.

I could put up an if that makes a check on what JDBC driver is being used and react accordingly, but again, that doesn't feel like a good solution to the problem.

What I would really like is either a method like .hasResultSet() that returns a boolean OR a way to get the SQL command (SELECT, UPDATE, INSERT etc) from a statement that has been executed. I can find neither of the two in SQL API though.

any specific reason why you want to return null for `DELETE` query because `DELETE` query will always return null as resultset — Bhavik Shah, Jan 08 '13 at 11:25
i have a method that executes all queries and with a `DELETE` on MySQL it returns null as you said but on SQLite it throws an exception. — Dreen, Jan 08 '13 at 11:27
I am using this JDBC driver btw https://bitbucket.org/xerial/sqlite-jdbc — Dreen, Jan 08 '13 at 11:28
@BhavikShah: that is only true for the two mentioned DBMS. Other DBMS can indeed return something from a DELETE statement, e.g. `delete ... returning ...`) — , Jan 08 '13 at 12:12
No statement has a result set until you execute it. So execute it. — user207421, Jan 13 '13 at 01:35

score 7 · Accepted Answer · edited Apr 19 '23 at 06:45

When executing a query that returns an unknown amount of results, then you need to use execute(). This method returns a boolean indicating the type of result:

true: result is a ResultSet
false : result is an update count

If the result is true, then you use getResultSet() to retrieve the ResultSet, otherwise getUpdateCount() to retrieve the update count. If the update count is -1 it means there are no more results. Note that the update count will also be -1 when the current result is a ResultSet. It is also good to know that getResultSet() should return null if there are no more results or if the result is an update count, so the behavior of SQL Lite to throw an exception seems to be wrong.

Now if you want to retrieve more results, you call getMoreResults() (or its brother accepting an int parameter). The boolean return value of this method has the same meaning as that of execute(), so false does not mean there are no more results!

There are only no more results if the getMoreResults() returns false and getUpdateCount() returns -1 (as also documented in the Javadoc)

Essentially this means that if you want to correctly process all results you need to do something like:

PreparedStatement pstmt = connection.prepareStatement(...);
// ...
boolean result = pstmt.execute();
while(true) {
    if (result) {
        ResultSet rs = pstmt.getResultSet();
        // Do something with resultset ...
    } else {
        int updateCount = pstmt.getUpdateCount();
        if (updateCount == -1) {
            // no more results
            break;
        }
        // Do something with update count ...
    }
    result = pstmt.getMoreResults();
}

score 2 · Answer 2 · answered Jan 08 '13 at 11:36

2

The problem is that you use invalid method to perform delete operation. Instead of using getResultSet you should use Statement#execute(String)

IMHO the Exeption in SQLite implementation is more valid than null for MySQL. As delete do not return the set but a scalar value of delted rows.

answered Jan 08 '13 at 11:36

Damian Leszczyński - Vash

30,365
9
60
95

My impression was that you have to use `execute()` anyway even for stuff like `SELECT` before you use a `getResultSet()`? Because for `SELECT` it seems to work fine if I do both... – Dreen Jan 08 '13 at 11:40
Even if I'm wrong in the previous comment my question remains; how do I programatically find out whether to use an `execute()` or a `getResultSet()`? – Dreen Jan 08 '13 at 11:44
3

@Dreen Use `executeQuery` for queries that produce 1 and only 1 resultset, `executeUpdate` for queries that only modify (and produce a single updatecount) and `execute` for all other things (the existence and amount of update counts and/or resultsets is either unknown in advance or more than 1. – Mark Rotteveel Jan 08 '13 at 11:58
so I accepted this because I concluded there is no way to determine this programatically. instead, i am going to check for the first word in a query string and use appropriate method as detailed by @MarkRotteveel – Dreen Jan 08 '13 at 12:15
@Dreen There is a way to detect this programmatically, but I don't have time now to type up the full answer. – Mark Rotteveel Jan 08 '13 at 13:12
@MarkRotteveel, thanks for your and everyone else's help anyway, and if you do find the time ill change my accepted answer to yours. – Dreen Jan 08 '13 at 15:00

Java SQL: Statement.hasResultSet()?

2 Answers2

Linked