NOT FOUND handler problem (and empty sets)

Database
Enthusiast

NOT FOUND handler problem (and empty sets)

Hello,

I'm attempting to resolve an issue with empty sets 'terminating' my stored procedure. Can anyone shed light on this?

Here is a small sample stored procedure:

REPLACE PROCEDURE ptest()

DYNAMIC RESULT SETS 4 SQL SECURITY INVOKER

BEGIN

    DECLARE c1 CURSOR WITH RETURN ONLY FOR

        SELECT TOP 1 * FROM c_codes ;

    DECLARE c2 CURSOR WITH RETURN ONLY FOR

        SELECT TOP 2 * FROM c_codes ;

    DECLARE c3 CURSOR WITH RETURN ONLY FOR

        SELECT TOP 3 * FROM c_codes WHERE 1=0;

    DECLARE clast CURSOR WITH RETURN ONLY FOR

        SELECT TOP 10 * FROM c_codes ;  

    OPEN c1;

    OPEN c2;

    OPEN c3;

    OPEN clast;

END;

CALL ptest();

The problem is that the 4th returned set is empty!  It appear the 'not found' condition returned by the 3rd cursor is somehow negating continued result sets?

I have moved the relevant WHERE clause around and this behavior is consistent: whenever there is a result set with zero rows, all subsequent results sets are zero.

After looking at the NOT FOUND error handler documentation, I tried adding this:

DECLARE CONTINUE HANDLER

FOR NOT FOUND

BEGIN

END;

at various places in my SP, but to no avail.

Putting it in front of the 1st DECLARE CURSOR statement (after BEGIN) results in an error "Syntax error, expected something like a 'CONTINUE' keyword or an 'EXIT' keyword between the 'DECLARE' keyword and the word 'c1'.'"

Putting it after the last DECLARE CURSOR statement (before END) results in same behavior.

My desired outcome is that a zero-record result set does NOT suspend execution or eliminate subsequent valid result sets.

Any thoughts??

Bk

2 REPLIES
Junior Contributor

Re: NOT FOUND handler problem (and empty sets)

Are you running the SP in SQL Assistant in an ODBC session?

According to SQLA's help there's a known limitation in ODBC:

"If a macro (or execute parallel) returns zero rows from a Select statement, no rows are returned from any later Select statements in that macro or parallel query."

This seems to be true for SPs, too.

You might simply close an empty cursor (then it's not returned), but then you don't know which it was:

    OPEN c1;
IF ACTIVITY_COUNT = 0 THEN CLOSE c1; END IF;

You could do close the cursor in a Handler, too, it must be placed after the last cursor declaration, but I don't know if you can use a single Handler for multiple cursors.

So you probably have to put them in a BEGIN/END block like

...
DECLARE c3 CURSOR WITH RETURN ONLY FOR
SELECT TOP 3 * FROM c_codes WHERE 1=0;
DECLARE clast CURSOR WITH RETURN ONLY FOR
SELECT TOP 10 * FROM c_codes ;

BEGIN
DECLARE EXIT HANDLER
FOR NOT FOUND
BEGIN
CLOSE c1;
END;
OPEN c1;
END;

BEGIN
DECLARE EXIT HANDLER
FOR NOT FOUND
BEGIN
CLOSE c2;
END;
OPEN c2;
END;
...

Or you simply run the CALL in a .Net session, which works as expected without additional coding :-)

Enthusiast

Re: NOT FOUND handler problem (and empty sets)

**THANKS** for the reply Dieter!

And yes/no: yes, I've been developing w/SQLA+ODBC and no, we should be implementing with a little BTEQ scripts and mostly calls from Perl. Unfortunately the Perl is also thru ODBC, so we're at the same juncture.

I believe we're going to architect such that our SPs only return ONE result set. Somehow this seems cleaner in a way and certainly gets around this known limitation.

We may also use IF ACTIVITY_COUNT = 0 to insert a dummy/not-found row into the final output as well, to confirm empty set.

See ya online!

Bk