"waitfor delay" not working as expected....

On Sybase 11.9.2, running on AIX.
Here's what I'm experiencing, and I'm not sure why.  Placed the following in a stp:

print 'WE START.'
waitfor delay '00:02:00'
print 'WE END.'
return 0

When I execute this, I'm was expecting to see "WE START", then have a two minute pause, and then see "WE END"....but instead, the two minute pause appears to occur FIRST, and THEN I see "WE START" "WE END" print one after another with no delay in between....  Can someone expain why that is?   Is the print "WE START" actually running prior to the pause, but not actually printing until after?  If so, how can I make it work as I'm expecting?
derdleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the print does indeed happen correctly with 2 minutes intervals, you can check that by adding the current time to the prints.
however, the output is buffered, and only shown when the full batch is completed.

I know the same from SQL Server, you might try to use SELECT instead of PRINT.
0
grant300Commented:
Use the command

SET FLUSHMESSAGE ON

This tells ASE to push messages out to the client as they occur instead of waiting for the buffer to fill.

Regards,
Bill
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
derdleAuthor Commented:
Bill-  Just the solution I was looking for!  That works, thanks!  Although it's interesting that you refer to this, and the solution relates to "waiting for the buffer to fill".  I say this because in reading the defined action of "waitfor delay" which indicates that it "suspends the session....and communications", I thought that maybe becuase I had the "print WE START" directly before the "waitfor delay", the waitfor was suspending the session (and thus the output) before the output from the print on the previous line had a chance to display.  ....which to me seemed almost impossible, given the fractional amount of time we talking about between the execution of the two line....BUT....when I tried the following, it did resolve the problem, and appears to support my thoughts, since all I am doing is adding a small amount of time between the print and the waitfor.  I did this, and this also worked (but is abviously much more painful than your correct solution).   ...but my question is, even though I am not printing anything while performing the pause loop below, is that adding something to the buffer than would make this solution valid as well....if indeed it is a matter of the buffer being full or not - (which again, I totally believe, as the  your solution works...and make more sense).

 print 'WE START.'
   select @next = 1
   while (@next < 15)
   begin
      select @next = @next + 1
   end
   waitfor delay '00:02:00'
print 'WE END.'
return 0

This works as I was describing as well, althoug obviously the wait is a tick longer than 2:00....but the print statement happens before the wait...where if I take that loop out, it does not.  

I'll be marking your answer as the solution....but thought I continue with this thought, since it puzzles me a bit.  =))  



0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

grant300Commented:
Actually, what you are doing is stuffing a bunch of entries in the buffer; you just don't realize it.

SELECT statements, even if they don't return any results, do return a "rows affected" message.  You are using your loop to put 15 rows affected messages into the buffer which is, evidently, enough to get it to flush.

I am willing to bet that if you put a "SET NOCOUNT ON" statement at the beginning of your code, your trick no longer works.

Let us know what happens.

Regards,
Bill
0
derdleAuthor Commented:
Well, oddly enough, the addition of the SET NOCOUNT ON statement seems to have no affect on the process.  The trick does still work even with it included.  Any other thoughts on why that might be?  Now I'm sucked in and want to understand exactly what is happening here, and how this works...  =))
0
grant300Commented:
The only way to know for sure is to delve into the internals of Sybase.  Since we don't have the source code, that is a bit tough.

I still suspect that the buffer is getting stuffed with the Rows Affected messages and that the SET NOCOUNT ON probably sets a flag in the Open Server that prevents them from being sent to the client, probably where the buffer is translated into TDS packets.  That way, there would be only one place in the entire code base where that function would have to operate.

Since your conjecture is that it is a delay prior to the waitfor that is causing/allowing the buffer to be flushed, substitute something that does not produce a bunch of messages.  With SET NOCOUNT ON, you could SELECT * INTO #TMP FROM master.dbo.sysobjects.  That is likely to take longer than a loop with 15 set statements.

Regards,
Bill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.