What is the equivalent of Oracle’s REF CURSOR in MySQL when using JDBC?

In Oracle I can declare a reference cursor...

TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE;

...and use it to pass a cursor as the return value...

FUNCTION end_spool
    RETURN t_spool
    AS
    v_spool t_spool;
    BEGIN
        COMMIT;
        OPEN v_spool FOR
            SELECT
                *
            FROM
                spool
            WHERE
                key = g_spool_key
            ORDER BY
                seq;
        RETURN v_spool;
    END end_spool;

...and then capture it as a result set using JDBC...

private Connection conn;
private CallableStatement stmt;
private OracleResultSet rset;
[...clip...]
stmt = conn.prepareCall("{ ? = call " + call + "}");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();
rset = (OracleResultSet)stmt.getObject(1);

What is the equivalent in MySQL?

From stackoverflow

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

  dacracot : It is definitely outside of the JDBC spec. Oracle and to some degree PostgreSQL have implemented the extension. It is very useful and I had hoped for the same from MySQL.

  Spencer K : How much more useful is it than just iterating over a ResultSet?

• 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 DATA
  BEGIN
    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();
  

• This did not work. I did not understand what the problem is?

  dacracot : Which answer did not work?

• fill a temporary table in a procedure and just read the temporary table... :)