I have a custom operation binding with customSQL that issues an UPDATE to the database. In some cases the update results in no records affected, which is a legitimate condition, but the operation fails with a "cursor state not valid" message. Here is an example from the log.
Our db server is an IBM iSeries so I researched the error and found that this error occurs when you don't have a resultset (e.g. when you run an update statement or a stored proc that does not return a resultset), but you call rs.next()
I can run the same SQL statement directly and get no error. So it seems that the SQLDataSource code is calling rs.next() and getting the error. Is there another approach I could take that might avoid this?
Code:
update IPPOHDR set HSTS='R', HRNO=HRNO+1 where HONO in ( select HONO from ProductionOrder join ProductionLine on ProductionOrder.id=ProductionLine.parentId join ProductionDelivery on ProductionLine.id=ProductionDelivery.parentId join ProductionDestination on ProductionDelivery.id=ProductionDestination.parentId join IPPOHDR on ProductionDestination.controlNum = HONO where ProductionOrder.id = 34 and HSTS in ('R','A') ) on db: iptsfili threw exception: java.sql.SQLException: Cursor state not valid. - assuming stale connection and retrying query.
I can run the same SQL statement directly and get no error. So it seems that the SQLDataSource code is calling rs.next() and getting the error. Is there another approach I could take that might avoid this?
Comment