PreparedStatement.execute() returns True for DML/DDL queries #797
Description
Describe the bug, including details regarding any error messages, version, and platform.
In Apache Arrow Flight SQL Prepared Statement execution, result set generating queries should be executed via CommandPreparedStatementQuery and queries that return only the number of affected rows should be executed via CommandPreparedStatementUpdate
In JDBC, PreparedStatement.execute() allows for running generic queries, including SELECT, DML and DDL. For DML/DDL, this method should return False and provide an update count, for SELECT it should return True and result set should be provided.
The cause of the issue is that the driver always follows the Arrow Flight SQL communications path for result set generating queries, meaning, the query is always executed via CommandPreparedStatementQuery and never via CommandPreparedStatementUpdate This means that the driver tries to execute DML/DDL as if they were SELECT statements, which can lead to incorrect return values of PreparedStatement.execute()
Code snippet to reproduce:
// connection is a previously established Connection instance to a Flight SQL server
String insertPreparedStatementSingleRow = "INSERT INTO my_table (name) VALUES (?)";
try (PreparedStatement pstmt = connection.prepareStatement(insertPreparedStatementSingleRow)) {
pstmt.setString(1, "My name");
boolean hasResultSet = pstmt.execute();
if (hasResultSet) {
System.out.println("Unexpected: INSERT returned a ResultSet");
try (ResultSet rs = pstmt.getResultSet()) {
// Handle unexpected ResultSet
System.out.println("Unexpected ResultSet...");
}
} else {
// false = Update count available
int updateCount = pstmt.getUpdateCount();
System.out.println("Update count: " + updateCount + " row(s) affected");
}
}
This was tested with a Dremio Arrow Flight SQL server, but looking at the driver's code seems to be an issue that affects all servers.