0

We are currently using MariaDB v10.1.19 to as a database. We wanted to know if this particular version supports the refcursor concept within store procedure like Oracle DB.

Tried to look at some of the sites but could not get a final answer.

We would be using Java8 using below jdbc connector

<dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>2.0.1</version>
        </dependency>
halfer
  • 19,824
  • 17
  • 99
  • 186
Shantanoo K
  • 765
  • 5
  • 15
  • 43
  • No, MySQL (and MariaDB) still [doesn't support refcursors](https://stackoverflow.com/questions/7155790/what-is-the-equivalent-of-oracle-s-ref-cursor-in-mysql). The usual workaround is a temporary table. – Solarflare Jun 26 '17 at 09:15
  • @Solarflare - Do you have any example of using temporary table, does it work the same way as ref-cursor – Shantanoo K Jun 26 '17 at 13:20
  • It doesn't work exactly the same way, is slower and less flexible than a refcursor. But depending on what you are trying to do will have a similar result. So maybe describe your intention. Oftentimes, a refcursor is used to pass or return a resultset from procedure `a` to another procedure `b`. Instead of passing a refcursor, in procedure `a` you can insert rows into a table (a temporary table is session specific, but you can use a real table too). Then call procedure b. In procedure b, query that table (usually with a known name). You can use a cursor (declared in procedure b!) to do so. – Solarflare Jun 26 '17 at 14:20
  • @Solarflare in a stored procedure we are doing select on multiple tables and we want that data to be sent to Java layer in a ref cursor (1 per table) so that we can read all the rows within that table using that refcursor object – Shantanoo K Jun 26 '17 at 14:25
  • In this case, it's actually easier: [Queries returning multiple result sets](https://stackoverflow.com/questions/9696572/queries-returning-multiple-result-sets). In your procedure, just use several `select`s (into seemingly nothing), they will be sent to the client and can be read (just another procedure in mysql itself cannot use that result). But you first have to read all rows of first select, then 2nd select and so on. Or you can use temporary tables here too and read these in java (with several queries), then you can access the different results independently of each other. – Solarflare Jun 26 '17 at 16:06
  • Please step back from "refcursors" and explain the intent. Describe it without reference to a specific implementation (refcursors); there may be some other (perhaps simpler) solution. – Rick James Jul 05 '17 at 22:21
  • @RickJames - You are absolutely rite I should have mentioned the intent, we basically wanted to read the resultset from stored procedure in MariaDB and we made it work with slightly different implementation than the way we used to do with Oracle – Shantanoo K Jul 08 '17 at 03:50

0 Answers0