Issue Details (XML | Word | Printable)

Key: JDBC-605
Type: Bug Bug
Status: Open Open
Priority: Minor Minor
Assignee: Mark Rotteveel
Reporter: Attila Molnár
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JDBC Driver

Current statement has no data to return.

Created: 10/Dec/19 02:42 PM   Updated: 14/Dec/19 11:04 AM
Component/s: None
Affects Version/s: Jaybird 3.0.8
Fix Version/s: None


 Description  « Hide
Hi Mark!

The "test" proc is selectable, but executed via EXECUTE PROCEDRURE/CallableStatement. Reading out parameter cause "Current statement has no data to return." exception. I think in this case null value should be returned
The resultset is empty beacuse of no suspend is actually called in "test", but it was not executed via SELECT.

try(Connection conn = ds.getConnection()) {
String create = "create or alter procedure test\n" +
"returns (msg varchar(1000))\n" +
"as\n" +
"begin\n" +
" msg = '';\n" +
" if (msg <> '') then suspend;\n" +
"end";
conn.setAutoCommit(false);
conn.setReadOnly(false);
conn.createStatement().execute(create);
conn.commit();
CallableStatement cs = conn.prepareCall("EXECUTE PROCEDURE test (?)");
cs.registerOutParameter(1, java.sql.Types.VARCHAR);
cs.execute();
String msg = cs.getString("MSG");//Current statement has no data to return.
}

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 14/Dec/19 10:55 AM
Jaybird will automatically convert this to `SELECT * FROM test(?)` because the presence of SUSPEND in the code will make the procedure selectable. Using `CallableStatement.getXXX` is not advisable for selectable procedures. This specific case is explicitly handled in the code (see https://github.com/FirebirdSQL/jaybird/blob/master/src/main/org/firebirdsql/jdbc/AbstractCallableStatement.java#L1012), and has been this way for years (eg see in Branch_1_0: https://github.com/FirebirdSQL/jaybird/blob/Branch_1_0/src/main/org/firebirdsql/jdbc/FBCallableStatement.java#L1284)

If you don't want the automatic conversion, you can either specify connection property `ignoreProcedureType=true`, or for this specific case use `cs.unwrap(FirebirdCallableStatement.class).setSelectableProcedure(false);`. This will then work because an `execute procedure` will always have a result.

The first option (`ignoreProcedureType=true`) will have effect for all statements on the connection, and will result in call escapes to also always use `EXECUTE PROCEDURE`, which may not be what you want.

Alternatively, use `executeQuery()` and iterate over the result set.

Mark Rotteveel added a comment - 14/Dec/19 11:04 AM
I am considering expanding the ignoreProcedureType property to allow a little bit more fine-grained control (eg to not perform the transformation of EXECUTE PROCEDURE, but do perform it for the call-escape).

To be honest, I'm not really happy with how stored procedures work in Jaybird currently, but with nearly two decades of history in the current behaviour, I also don't want to break existing applications.