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

Question

                    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.

Francisco · Accepted Answer · 2023-01-20 15:22:20Z

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.

nitind · Accepted Answer · 2023-01-20 15:51:57Z

1

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.

answered Jan 20, 2023 at 15:51

nitind

20.1k4 gold badges37 silver badges46 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related