Re: Embedded SQL counterpart to IF %OPEN()

On 17/07/2008, at 2:08 AM, Buck wrote:

I'd like to be able to test if an SQL cursor is OPEN so I can issue a
CLOSE cleanly. My searches through the SQL manuals didn't turn up any
SQL equivalent to RPG's %open() BIF.

I have a query program that makes extensive use of dynamically created
SQL statements (PREPARE, OPEN, FETCH) and the question of CLOSE has come
up. I can do a prophylactic CLOSE just before the OPEN, but the first
cycle puts a message into the job log to the effect that the cursor
isn't open.

I can always set a flag at OPEN time and test that flag before doing a
CLOSE, but I was sort of hoping for an SQL way.

No "SQL way" (that I know of) but the JOBI0900 format of the QUSRJOBI API will give you cursor information for a job. Not too difficult to build a procedure around that to test something like:

if isCursorOpen( cursorName )

Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists

http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------