Stored Procedure Calls through MySQL/H2 that return result sets

[iv...@vonnagy.net]

We use MySQL for production and H2 for unit testing. There are several stored procedures that return result sets for data consumptions. We would like to use jOOQ to help map the procedure calls to actual java code and POJOs. 

I have seen information in this group in regards to trying to return result sets from stored procs, and they mostly state that you can't do it except when using Oracle. Any insight would be helpful on how do do this with the use of a simple fetchMany("call myProc(?)"). We would really like to use the code generation tool to create concrete java code.

Example MySQL procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS getUsers; $$

CREATE DEFINER=`company`@`%` PROCEDURE `getUsers`(accountName VARCHAR(255))

BEGIN

SELECT a.id

FROM tblUsers a

JOIN tblAccounts b

ON b.id = a.accountId

WHERE b.name = accountName;

END$$

DELIMITER ;

[Lukas Eder]

Hi,

Indeed, we currently do not support MySQL procedures that return cursors, because (to our knowledge), MySQL's INFORMATION_SCHEMA does not provide any such information to the code generator. We may be wrong about that, of course, in case of which we would be more than happy to add this kind of support to jOOQ...

What you can do, of course, is:

- Base your source code generation on H2 instead of MySQL (I personally wouldn't recommend that, though - the databases are too different...)

- Implement your own MySQLDatabase in jOOQ-Meta, that provides / patches the relevant procedures with cursor results

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sharon Snyder]

Hi,

Is this still the case?  I have a MySQL stored procedure that I would like to return a cursor on (since the number of records returned could be potentially large) vs an array. If this has been fixed - since which version (so I can make sure we are using it in our project).

Thanks,

Sharon

[Lukas Eder]

Hello,

Cursors returned from stored procedures are supported (e.g. in SQL Server and in MySQL). But we currently don't return org.jooq.Cursor types for lazy fetching. The Routine.getResults() method ( http://www.jooq.org/javadoc/latest/org/jooq/Routine.html#getResults--) returns a List<Result<Record>> type. This has been implemented in jOOQ 3.5:

https://github.com/jOOQ/jOOQ/issues/3681

I have registered an issue for your particular use-case:

https://github.com/jOOQ/jOOQ/issues/4472

This use-case will certainly need more thinking, thanks for pointing this out to us!

Best Regards,

Lukas

[ssnyd...@gmail.com]

Thanks!  I will give it a shot with the result set in the meantime.  I will also watch the issue to see when it is fixed.

Sharon

[Lukas Eder]

Great, thank you. Also, let us know if you encounter additional issues. I believe that the MySQL - stored procedures - multiple cursors feature isn't used that often in the wild. Any real-world experience is very interesting for us.

Cheers,

Lukas