Databases
StackOverflow2013
Postsdbo
POAbrupt Resultset behavior in java
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAbrupt Resultset behavior in java
    text
    copied!<p>I am stuck with an issue over past 2-3 days. It seems to be a minor issue but I have not been able to catch it.</p> <p>The issue is with resultset rs in the code. <strong>The while (rs.next()) statement in method mergeTable is behaving abruptly. For the first time, On some occasions it goes inside the while loop, sometimes it doesn’t and on few occasions when it does enter the while loop, it comes out abruptly throwing resultset exhausted exception</strong>. I googled and found that the resultset might have been getting closed by some other thread accessing the code. But this is a simple standalone java application and is not multi-threaded.</p> <p>I am also taking care the other two resultsets, check and checktarget doesn’t interfere with resultset rs. I am closing the statements and resultsets also. Can you plz have a look at the code and see if I am missing out on something.</p> <p>The two database instances being used are 10.180.22.93:1521:V3demo(called source database) and 10.180.22.93:1521:fusiondb(called target database). rs resultset is from the source database.Resultsets check and checktarget are from the target database. So the resultset rs will be from table A from source database and resultsets check and checktarget will be from table A from target database. </p> <pre><code> static String mergeTable() throws Exception { String result = "ERROR"; int error = 0; String tableString = "&lt;table " + tablename + "&gt;"; PreparedStatement preparedSelect = null; PreparedStatement preparedSelectTarget = null; Statement selectSourceStmt = null; ResultSet checkTarget = null; ResultSet rs = null; try { logger.println("===================================================================================="); logger.println("Processing table:" + tablename); System.out.println("===================================================================================="); System.out.println("Processing table:" + tablename); // Create query to fetch records from the source String sourceQuery = "SELECT * FROM " + tablename; if (owner.trim().equals("F1") || owner.trim().equals("C1") || owner.trim().equals("CM")) sourceQuery = sourceQuery + " WHERE OWNER_FLG='" + owner + "'"; // Get the result set selectSourceStmt = source.createStatement(); rs = selectSourceStmt.executeQuery(sourceQuery); System.out.println(sourceQuery); String selectSQL = "SELECT COUNT(*) FROM " + tablename + " WHERE "; String selectSQLTarget = "SELECT * FROM " + tablename + " WHERE "; // ankush ResultSetMetaData metaData = rs.getMetaData(); List list = new ArrayList(); List typesList = new ArrayList(); for (int i = 1; i &lt;= metaData.getColumnCount(); i++) { String columnName = metaData.getColumnName(i); list.add(columnName); // list contains the entire list of columns of the source typesList.add(metaData.getColumnType(i)); } for (int i = 1; i &lt; keys.length; i++) { if (i == 1) { selectSQL = selectSQL + " " + keys[i] + "= ?"; selectSQLTarget = selectSQLTarget + " " + keys[i] + "= ?"; //ankush } else { selectSQL = selectSQL + " AND " + keys[i] + "= ?"; selectSQLTarget = selectSQLTarget + " AND " + keys[i] + "= ?"; //ankush } } logger.println("Select SQL:" + selectSQL); logger.println("selectSQLTarget:" + selectSQLTarget); //ankush preparedSelect = target.prepareStatement(selectSQL); preparedSelectTarget = target.prepareStatement(selectSQLTarget); //ankush int updateCount = 0, insertCount = 0, errorCount = 0; // rs contains the entire table snapshot of source based on the owner flag if (rs != null) { while (rs.next()) { try { int i, count; // check if record exists or not; keys contain the values of primary columns specified in the.lst file for (int j = 1; j &lt; keys.length; j++) { preparedSelect.setObject(j, rs.getObject(keys[j])); // for every single row in source, corresponding rows are fetched from target.Here, where clause is being prepared } ResultSet check = preparedSelect.executeQuery(); // check is the target resultset for the primary key values in current row of source resultset check.next(); count = check.getInt(1); // count gives the row/s fetched from target based on the values in source. check.close(); // check if record exists or not; keys contain the values of primary columns specified in the.lst file for (int j = 1; j &lt; keys.length; j++) { // for every single row in source, corresponding rows are fetched from target.Here, where clause is being prepared preparedSelectTarget.setObject(j, rs.getObject(keys[j])); } // check is the target resultset for the primary key values in current row of source resultset checkTarget = preparedSelectTarget.executeQuery(); checkTarget.next(); // if record exists UPDATE CONDITION if (true) { // if there is a record in target for a row in source, update target String rowString = "&lt;row&gt;"; String rowDiffFlag = "N"; // if merge flag is Y if (mergeFlag.equals("Y")) { String colDiffFlag = ""; String sourceColVal = ""; String targetColVal = ""; // list contains the column names for (i = 0; i &lt; list.size(); i++) { System.out.println("value of i " + i); } i++; // ????? } else { logger.print("Did not update Record:"); } rowString = rowString + "&lt;/row&gt;"; if (rowDiffFlag.equals("Y")) { tableString = tableString + rowString; } } else { // if there is no record in target for a row in source, insert into target String sourceColVal = ""; String rowString = "&lt;row&gt;"; for (i = 0; i &lt; list.size(); i++) { //looping through columns in a row System.out.println("column " + i); } rowString = rowString + "&lt;/row&gt;"; tableString = tableString + rowString; } } catch (Exception e1) { e1.printStackTrace(logger); } } } } catch (Exception e) { e.printStackTrace(logger); } finally { preparedSelect.close(); preparedSelectTarget.close(); selectSourceStmt.close(); checkTarget.close(); rs.close(); } tableString = tableString + "&lt;/table&gt;"; formXmlString(tableString); if (error == 0) result = "SUCCESS"; return result; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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