Question 21162 in Mysql Question posted in
The official MySQL documentation can be found here.

Proper way to "do while" SQL in Eclipse – worked prior to SSH connection to MySQL

                    Class.forName("com.mysql.cj.jdbc.Driver");
                    Connection connection = DriverManager.getConnection(
                            "jdbc:mysql://127.0.0.1:3306/wp_dashort", "dashort",
                            "gt9wkk6r1TPnkgrY");

                    PreparedStatement ps = null;

                    ps = connection.prepareStatement(mark_complete);
                    System.out.println("dateset");
                    dateof = dateof + " 00:00";
                    // ps.setString(1, dateof);

                    connection.createStatement();
                    ResultSet rs = ps.executeQuery();

                    Sheet sheet = workbook.createSheet("Security_Completions");
                    org.apache.poi.ss.usermodel.Row rowhead = sheet.createRow((short) 0);
                    System.out.println("Sheet created");
                    rowhead.createCell((short) 0).setCellValue("Column 1");
                    rowhead.createCell((short) 1).setCellValue("Column 2");

                    int size = 0;
                    rs.last();
                    size = rs.getRow();
                    rs.beforeFirst();

                    System.out.println("Number of records =  " + size);

                    // Running thru the ResultSet

                    int index = 0;
                    int r = 0;
                    rs.next();

                    while (r < size) {

                        index = index + 1;
                        Row row = sheet.createRow((short) index);

                        for (int i = 0; i < 1; ++i) {
                            String value = rs.getString(1);
                            row.createCell(i).setCellValue(value);
                            row.createCell(1).setCellValue(StringUtils.left((rs.getString(2)), 16));
                            rs.next();
                        }
                        ++r;
                    }

So this code worked fine just last week. This week I have been forced to connect to the MySQL server using an SSH tunnel session.

I get the SQLException "Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY" It seems that when I get the last row and then try to iterate through to write a spreadsheet, the error is thrown.

Tags:

2

Answers


  1. 0

    It’s possible that the default ResultSet type is different depending on the type of connection. Specifically, the default ResultSet type when connecting to a database via JDBC may be different when connecting via an SSH tunnel.

    In some cases, the default ResultSet type when connecting to a database via JDBC may be set to forward-only, which means that the cursor can only move forward through the ResultSet and cannot be moved back to previous rows. However, when connecting via an SSH tunnel, the default ResultSet type may be different and may allow for scrolling through the ResultSet in both directions.
    The default ResultSet type could also depend on the specific JDBC driver being used. Some drivers may have a default ResultSet type of forward-only, while others may have a default type of scrollable.

    You can check the default ResultSet type of your JDBC driver by calling the method getResultSetType() on the Statement object, before creating a ResultSet with it.

    You could try explicitly set the ResultSet type when creating the Statement object, by passing the constant ResultSet.TYPE_SCROLL_INSENSITIVE as the first argument.

    Login or Signup to reply.
  2. 0

    You can refashion your outer loop into a do…while style using ResultSet#next() to advance through the ResultSet rows and ResultSet#isLast() to know when to stop.

    Your for loop that adds spreadsheet rows only runs once per ResultSet row, but you’re already set up to loop through the ResultSet rows, so you don’t need to do an additional loop there.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search