|
Refresh |
April 2019 |
|
Views |
8.5k time |
How do we return a cursor from MySQL Stored Procedure (Routines). Or another approach to return a list of values back to a Java program from the MySQL Proc. We are trying to implement the similar functionality as Sys Ref Cursor in Oracle and looks like there is similar thing in MySQL. But wondering as this a very common usecase, what should be a the way to get a list of values returned by a proc in mysql.
Version - MySQL-server-5.5.15
"Return The list of values back to Java Program". MySql (and Connector/J) supports multiple output parameter for stored procedures. If this is not what you want, and you want a result set instead, you can do a SELECT inside your stored procedure and this SP returns a result set, that you can read e.g with CallableStatement.executeQuery()
MySQL:
create procedure get_data(in param1 date)
begin
select * from mytable where mydate=param1;
end;
Java:
CallableStatement cstmt = MyConnection.prepareCall("{call mydb.get_data(?)}");
cstmt.setString(1, '2012-01-01');
ResultSet rs = cstmt.executeQuery();
int nF = rs.getMetaData().getColumnCount();
rs.last();
String[][] out = new String[rs.getRow()][nF];
for (int i=0; i<nF; i++) {
rs.beforeFirst();
int n=0;
while (rs.next()) {
out[n][i]=rs.getString(i+1);
n++;
}
}
cstmt.close();
return out;
I hope that helps: What is the equivalent of Oracle’s REF CURSOR in MySQL when using JDBC?
Here is the latest version of mentioned in thread above reference: http://dev.mysql.com/doc/refman/5.6/en/connector-j-reference-implementation-notes.html
It still says that "MySQL does not support SQL cursors, and the JDBC driver doesn't emulate them, so "setCursorName()" has no effect."