Re: Embedded SQL counterpart to IF %OPEN()

[Simon Coulter <shc@xxxxxxxxxxxxxxxxx>]

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  / \
--------------------------------------------------------------------