Skip to content
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

Closed
firebird-automations opened this issue Dec 10, 2019 · 5 comments
Closed

Current statement has no data to return. [JDBC605] #636

firebird-automations opened this issue Dec 10, 2019 · 5 comments

Comments

@firebird-automations
Copy link

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.
}

@firebird-automations
Copy link
Author

Modified by: Attila Molnár (e_pluribus_unum)

priority: Major [ 3 ] => Minor [ 4 ]

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

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.

@mrotteveel
Copy link
Member

I added a test asserting the existing behaviour.

@mrotteveel
Copy link
Member

I'm going to leave the existing behaviour as is.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants