CALL causes block to stop

[Former Member]

Howdy folks, it's been a while. After more than a decade of using Oracle I finally get a project that uses Sybase IQ. It's great to see Breck Carter is still around.

I'm working with a Sybase IQ 16 database and I'm having a problem that seems to be SQL Anywhere related. It's probably a newbie question and I already RTFM...

Why does my code block stop after the call to sp_iqwho?

BEGIN
DECLARE @LOOP_COUNTER BIGINT;  
SET @LOOP_COUNTER = 0;  
WHILE (@LOOP_COUNTER < 100) LOOP  
    WAITFOR DELAY '00:00:03';  
    CALL sp_iqwho;  
    SET @LOOP_COUNTER = @LOOP_COUNTER + 1;  
    MESSAGE 'Nothing to see here';  // This never gets called!  
END LOOP;  
END;

Jim Egan [TeamSybase - Retired]

[Breck_Carter]

Oracle... [shiver]... 🙂

[MarkCulp]

Breck has the right idea. Here is what is going on... and yes this is one of those things that might be difficult to get your mind around?

The basis of what you are seeing is that when a result set is returned from the server to the client the execution of the stored procedure stops until the client consumes the result set and issues a RESUME statement. AND in this case it appears that the client does not display anything until all results sets have been returned to the client.

So in your case it sounds like the client is ISQL (or perhaps dbisql?)... in either case the client appears not to be displaying anything until the execution of the series of result sets has been returned to the client and the stored procedure terminates (and hence tells the client that there are no more results sets). I.e. the client is underneath the covers opening a cursor on the batch statement (BEGIN .... END) and then is retrieving the first result set, issuing a resume and then returning the next result set, issuing another resume and ... Well, you get the idea.

Since your procedure has a delay within it the client is likely thinking that you have a procedure that is taking a really long time so it dutifully waits until it can consume all of the rows from all of the results sets before it shows you what it got. (Perhaps the client could do a better job here and display what it got as it gets it but in the normal case this special display processing is likely not needed?)

To demonstrate what is is going on you could add a MESSAGE ... TO CONSOLE statement within the loop of your procedure and then watch the server console log. What you will see is a message showing up in the console log every three seconds yet the client will appear to not be doing / displaying anything.

HTH

[Breck_Carter]

OK, I upvoted your question just because you're you 🙂

But... it's very much an IQ question and this is a SQL Anywhere forum.

Having said that, let's turn it into a SQL Anywhere 16 ISQL question, and run this slightly modified version... (two loop passes instead of 100, and SELECT instead of CALL):

BEGIN
DECLARE @LOOP_COUNTER BIGINT;  
SET @LOOP_COUNTER = 0;  
WHILE (@LOOP_COUNTER < 2) LOOP  
    WAITFOR DELAY '00:00:03';  
    SELECT CURRENT TIMESTAMP; 
    SET @LOOP_COUNTER = @LOOP_COUNTER + 1;  
    MESSAGE 'Nothing to see here';  // This never gets called!  
END LOOP;  
END;

current timestamp       
----------------------- 
2013-07-19 15:38:52.705

current timestamp       
----------------------- 
2013-07-19 15:38:55.762 
Execution time: 6.093 seconds

Nothing to see here
Nothing to see here

Sure looks like it works! But... [snork]... you had to be there... it took the whole 6 seconds before anything appeared at all... so maybe if you wait for 300 seconds you will see your output too!

Maybe someone else can explain how this works, but I think you know what you have to do.

[Former Member]

I'm feeling the love!

I commented out the line with the CALL and of course it works as expected - mostly. Interactive SQL shows that it is executing the whole time. My message doesn't show up but it was there for debug anyhow so no loss.

So it looks like the CALL is the issue. I vaguely recall something from years ago that the CALL command did odd things in SQL Anywhere. I'll look into getting the current sessions without using the stored procedure.

[Breck_Carter]

The CALL does not do anything weird to SQL Anywhere. In this case it returns a result set which is displayed by ISQL.

It's not the CALL... it behaves the same with SELECT... it doesn't show anything until the loop finishes... it's the fact the statement returns a result set (both the CALL and the SELECT).