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 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.
answered 20 Jul '13, 12:16
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.
answered 19 Jul '13, 15:30