Using cursor fetch does not work in some cases
Posted by: Ulf Weiland
Date: April 07, 2020 10:19AM

Hi!

I'm wondering if certain SQL statements cannot be used in combination with cursor fetch? The reason for asking is that running the SQL "show engines" results in
"java.sql.SQLException: The statement (1) has no open cursor".

The following simple Java program exemplifies this. See the main.
I'm running with: mysql-connector-java-8.0.19 towards a server MySQL 8.0.19.

package db;


import java.sql.*;
import java.util.Properties;

public class CursorFetchNotWorking {

private static void runQuery(String query, boolean cursorFetch) {
try {
String myDriver = "com.mysql.jdbc.Driver";
String myUrl = "jdbc:mysql://localhost:3307/";
Properties connectionProps = new Properties();
connectionProps.put("user", "dbvis");
connectionProps.put("password", "dbvis");
connectionProps.put("defaultFetchSize", "5000");
if (cursorFetch) {
connectionProps.put("useCursorFetch", "true");
}
Class.forName(myDriver);
Connection conn = DriverManager.getConnection(myUrl, connectionProps);
Statement st = conn.createStatement();
System.out.println("Connected:" + conn.getMetaData().getDriverVersion());
ResultSet rs = st.executeQuery(query);

while (rs.next()) {
String col1 = rs.getString(1);
System.out.println(col1);
}
st.close();
}
catch (Exception e) {
System.out.println("Got exception: " + e.getMessage());
e.printStackTrace();
}
}

public static void main(String[] args) {
System.out.println("=== Working");
runQuery("Select 1" , true);

System.out.println("=== NOT Working");
runQuery("show engines" , true);

System.out.println("=== Working");
runQuery("show engines" , false);
}
}

Options: ReplyQuote


Subject
Written By
Posted
Using cursor fetch does not work in some cases
April 07, 2020 10:19AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.