Sybase: Flush messages and result sets

If you have a procedure returning multiple result sets or printing messages and running for quite some time, you usually want to get the intermediate result sets and message back on the client as they are produced. ASE has a buffer where they are stored on their way to the client and you thus always have a delay which duration depends on how fast the buffer gets filled.

To prevent this behavior you can switch in the FLUSHMESSAGE option before executing the procedure:

SET FLUSHMESSAGE ON
go

You will see that the messages issued with the print command are returned immediately but there is still a delay for the result sets. FLUSHMESSAGE only has the result set returned to the client when a print is executed. This means that you have to execute a print after the selects for which you want the result back immediately e.g.:

print ''

If you are using the select to return messages e.g.:

SELECT "Everything is fine until now."

you should convert it to a print (or add an empty print afterwards):

print "Everything is fine until now."

The only problem with print is that it handles strings and if you want to just return a number, you have to convert it to a string to print it.

Leave a Reply

Your email address will not be published. Required fields are marked *