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