Databases
StackOverflow2013
Postsdbo
POUpdating a large Resultset with JDBC and MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdating a large Resultset with JDBC and MySQL
    primarykey
    data
    text
    <p>I am trying update a large set of rows (around 5M). I first came across the heap overflow issue of having so many rows fetched in a resultset. Since I don't want to raise my heap size on this machine I was wondering if there is an effective way of doing this. </p> <p>I tried setting the <code>setFetchSize(Integer.MIN_VALUE)</code> but then when I call the update function I get this error:</p> <p>Streaming result set <code>com.mysql.jdbc.RowDataDynamic@2087c268</code> is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called <code>.close()</code> on any active streaming result sets before attempting more queries.</p> <p>If I call <code>close()</code> on the result set I cannot update it of course. Here is my code</p> <pre><code>public void getRows() { Statement stmt = null; ResultSet rs = null; int countSpecialChars = 0; int upper = 0, lower = 0, digits =0; String pass = null; int id = 0; char thisChar; String query = "select id,pass from datatable"; try { this.conn.setAutoCommit(false); stmt = this.conn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); rs = stmt.executeQuery(query); while (rs.next()) { pass = rs.getString(2).trim(); id = rs.getInt(1); for (int i=0; i&lt;=pass.length()-1; i++) { thisChar= pass.charAt(i); if (thisChar &gt;= 65 &amp;&amp; thisChar &lt;= 90) { upper++; } else if (thisChar &gt;= 97 &amp;&amp; thisChar &lt;= 122) { lower++; } else if( thisChar &gt;= 48 &amp;&amp; thisChar &lt;= 57) { digits++; } else {countSpecialChars++;} } Entropy entropy = new Entropy(); double naiveEntropy = entropy.naiveEntropy(pass); NumberFormat formatter = new DecimalFormat("#0.00"); this.updateRow(id, pass.length(), digits, upper, lower, countSpecialChars, Double.parseDouble(formatter.format(naiveEntropy))); countSpecialChars = 0; upper=digits=0; lower = 0; } rs.close(); } catch (SQLException e) ... } public void updateRow(int id, int length, int numbers, int upper, int lower, int specialChars, double naiveEntropy ) { PreparedStatement updatePassEntry = null; String updateString = "update cwlCompatiblePassUnique " + "set length = ?, numbers = ?, upper = ?, lower = ?, specialChars = ?, ShannonEntropy = ? where id = ?"; try { this.conn.setAutoCommit(false); updatePassEntry = this.conn.prepareStatement(updateString); updatePassEntry.setInt(1, length); ... updatePassEntry.setInt(7, id); updatePassEntry.executeUpdate(); this.conn.commit(); } catch (SQLException e) ... } </code></pre> <p>Any ideas on what can be done?</p> <p>Thanks</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USoliholz
    1. USidipous
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUpdating a large Resultset with JDBC and MySQL
      1. USSuraj Chandran
      1. VTFavorite
    2. VO
      singulars
      1. POUpdating a large Resultset with JDBC and MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUpdating a large Resultset with JDBC and MySQL
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COtry to create a new connection and use that one to update to database. hope this will help.
      singulars
      1. POUpdating a large Resultset with JDBC and MySQL
      1. USAsad Rasheed
    2. COThis is the correct answer. MySQL wire protocol does not support multiple statement execution on the same connection at the same time. So if one really needs 2 commands either the full result of the first command must be completely read (this is "not streaming" mode), or you can "stream" the result with one connection, and update via second one.
      singulars
      1. POUpdating a large Resultset with JDBC and MySQL
      1. USVladislav Vaintroub
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload