jaybird
jaybird copied to clipboard
FirebirdSQL
Current statement has no data to return. [JDBC605]
Submitted by: Attila Molnár (e_pluribus_unum)
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. }
Modified by: Attila Molnár (e_pluribus_unum)
priority: Major [ 3 ] => Minor [ 4 ]
Commented by: @mrotteveel
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.
Commented by: @mrotteveel
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.
