New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Current statement has no data to return. [JDBC605] #636
Comments
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. |
I added a test asserting the existing behaviour. |
I'm going to leave the existing behaviour as is. |
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.
}
The text was updated successfully, but these errors were encountered: