• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL exception -4470

 
Lars Otto Haugen
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am getting the following error when running a trigger which is executing a select statement in SQL.
The trigger is run every night and this error happens occasionally. Database is DB2.

com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.64.106] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

The failing statement is String[] row = cursor.next(); from the method listed below

private void generateReport(BufferedWriter writer, GenericReportQuery query) throws GenericPwhException, IOException {
SimpleGenericReportCursor cursor = null;
Connection connection = null;
try {
connection = getConnection();
dao.setConnection(connection);
cursor = dao.runQuery(query);
String[] row = cursor.next();
int numRows = 0;
while (row != null) {
writer.write(getRowText(row));
row = cursor.next();
numRows++;
}

log.debug("Writing " + numRows + " number of rows to report");
} finally {
closeEverything(cursor, connection);
}
}
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does the code in dao.runQuery(query) look like?
 
Lars Otto Haugen
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
public SimpleGenericReportCursor runQuery(GenericReportQuery query) throws GenericPwhException {
SimpleGenericReportCursor cursor = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = getConnection().prepareStatement(query.getSqlToRun(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pstmt.setString(1, (String) query.getParameters()[0]);
pstmt.setTimestamp(2, new Timestamp(((Date) query.getParameters()[1]).getTime()));
pstmt.setTimestamp(3, new Timestamp(((Date) query.getParameters()[2]).getTime()));
rs = pstmt.executeQuery();
cursor = new SimpleGenericReportCursor(rs, pstmt, query.getNbColumnsRetrieved());
} catch (Exception e) {
throw new GenericPwhException(e);
}
return cursor;
}

The SQL looks something like this:

<Statement>
<LogicName>SELECT_TRANSACTION_DETAIL_REPORTING_DATA</LogicName>
<SQL>
select xxx

from TR
left outer join
R1 on TR.col1 = R1.col1 and R1.col2 = 'aaa'
left outer join
R2 on TR.col1 = R2.col1 and R2.col2 = 'bbb'
left outer join
R3 on TR.col1 = R3.col1 and R3.col2 = 'ccc'
left outer join
R4 on TR.col1 = R4.col1 and R4.col2 = 'ddd'
left outer join
R5 on TR.col1 = R5.col1 and R5.col2 = 'eee'
left outer join
R6 on TR.col1 = R6.col1 and R6.col2 = 'fff'
left outer join
P1 on TR.col1 = P1.col1 and P1.col2 = 'ggg'
left outer join
P2 on TR.col1 = P2.col1 and P2.col2 = 'hhh'
left outer join
P3 on TR.col1 = P3.col1 and P3.col2 = 'iii'
left outer join
P4 on TR.col1 = P4.col1 and P4.col2 = 'jjj'
left outer join
P5 on TR.col1 = P5.col1 and P5.col2 = 'kkk'
left outer join
P6 on TR.col1 = P6.col1 and P6.col2 = 'lll'
left outer join
PM on TR.col3 = PM.col4
left outer join
PF on PM.col5 = PF.col6

where TR.col7 = ?
and TR.col8 between ? and ?
and TR.col9 in ('EX','RL','CA')
</SQL>
</Statement>

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic