What is the equivalent of Oracle’s REF CURSOR in MySQL when using JDBC?
Mysql has an implicit cursor that you can magically return from a stored procedure if you issue a select.
Here's an example:
CREATE PROCEDURE `TEST`()MODIFIES SQL DATABEGIN SELECT * FROM test_table;END;and in your java code:
String query = "{CALL TEST()}";CallableStatement cs = con.prepareCall(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);ResultSet rs = cs.executeQuery();
Googling on cursors in MySQL, it doesn't seem like you can actually return a Cursor from a Proc or Function. Additionally, I found the following in the MySQL JDBC manual:
"MySQL does not support SQL cursors, and the JDBC driver doesn't emulate them, so "setCursorName()" has no effect."
In general, I believe Oracle's implementation here breaks JDBC, and is not used elsewhere (MySQL, MSSQL, etc). You should be returning your results as a select statement and iterating over the JDBC ResultSet, as is standard (and intended) practice when using JDBC.
fill a temporary table in a procedure and just read the temporary table... :)