Replacing oracle's "OracleTypes.CURSOR" to an equivalent in mysql when using registerOutParameter function

Question

Dear stackoverflow community

I am trying upgrade a system developed in JAVA from using oracle DB to mysql DB.

I got to a point where to make use of a storaged procedure the java code makes use of a specific oracle structure - OracleTypes.CURSOR. Please see the code below.

private int getUserID(String username) {
    int UserID = -1;
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
        con = this.getConnection();
        cs = con.prepareCall("{call getUserID(?,?)}");
        cs.setString(1, username);
        cs.registerOutParameter(2, OracleTypes.CURSOR);
        cs.execute();
        rs = (ResultSet) cs.getObject(2);

        if (rs.next()) {
            UserID = rs.getInt(1);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            cs.close();
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return UserID;
    }
}

What I am trying to do is to convert the use of OracleTypes.CURSOR to something mysql friendly. I did read that it would be posssible to do this in the newer version of mysql so I even tried replacing OracleTypes.CURSOR by java.sql.Types.INTEGER without any result.

Can you help me?

note: I have following previous information here on stack overflow and also other places like: http://bugs.mysql.com/bug.php?id=17898

Answer 1

MySql unfortunately doesn't implement ref cursors.

However, stored procedures in MySql can return a resultset to the client, see this link: http://dev.mysql.com/doc/refman/5.6/en/faqs-stored-procs.html#qandaitem-B-4-1-14

B.4.14: Can MySQL 5.6 stored routines return result sets?
Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client.

Just run an ordinary SELECT - and a resultset is returned to the client.

The resulset is a very similar concept to the ref cursor in Oracle. The most significant difference is that in MySql the resultset is completely retrieved from the server to the client and stored in memory on the client side (while Oracle cursors retrieves rows in chunks).
However, MySql can simulate this behaviour (with some limitations), see this link: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html

ResultSet
By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

How it works - an example.

Say we have an oracle procedure that we want to migrate to MySql:

create or replace procedure getUserId( p_name varchar2, p_ref OUT sys_refcursor )
is
begin
  open p_ref for
     select id, username from users
     where username = p_name;
end;
/

In MySql this procedure might look like this:

delimiter %%
drop procedure if exists getUserId %%
create procedure getUserId( p_name varchar(100) )
begin
  SELECT id, username FROM users
  WHERE username = p_name;
end;%%
delimiter ;

and code in Java - based on an example from documentation: http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-statements-callable.html#connector-j-examples-retrieving-results-params

      cs = conn.prepareCall("{call getUserID(?)}");
      cs.setString(1, "user1");
      boolean hasResultSet =  cs.execute();
      if( hasResultSet ){
            rs = cs.getResultSet();
            if (rs.next()) {
                userId = rs.getInt(1);
            }
            System.out.println( "Userid = " + userId );
      }