Let's say you have a well-tested procedure p that (a) does some useful work and then (b) returns a RESULT set... say it's normally called by an HTML web service:
CREATE PROCEDURE p() RESULT ( x LONG VARCHAR ) BEGIN [useful work] SELECT 'Hello, World!' ; END;
Now let's say you're coding a new procedure q where you need to have the useful work performed but you have no need of the result set. Other than wasting some time and resources to produce the result set, is there any further penalty to just calling p and ignoring the result set?
CREATE PROCEDURE q() BEGIN CALL p(); END;
There's no [gasp] memory leak or anything else that would violate the rule "Watcom Does Things The Way They Should Be Done", right?
The result set just gets tossed, right?
asked 09 Jan '11, 21:25
The answer to "The result set just gets tossed, right?" is that yes, it may be tossed, but better yet the result may actually never get generated.
Here is how SQL Anywhere works w.r.t. handling of results sets.
In your simple example the caller of procedure q will be required to consume the result set before q() will continue past the call to procedure p().
So for example, lets say we had
and the caller of q() did not consume the result set generated by p(), for example:
then the message in q() will never be written to the console because procedure p() was terminated at the point that the result set was (or would have been) generated. Any work performed in p() prior to the SELECT 'Hello World!' will be have been performed and will persist provided that the transaction is committed.
Note that SQL Anywhere's behaviour w.r.t. handling of result sets is different than other RDBMSes. Other database products - such as ASE and MSSQL - completely execute all procedure logic and generates all result sets (which may consist of many many rows) and returns these results sets back to the caller (e.g. client application) to deal with. i.e. The client does not continue past the point of the call to the procedure until the procedure has fully executed everything it was to do and has returned (fallen off the bottom of the procedure or executed a RETURN statement).
When a client calls a SQL Anywhere procedure, SA only executes to the point where the first result set gets generated, at which point the client continues past the call and may fetch the rows. When the SA client does a RESUME, the procedure continues executing past the point where the first result set was generated and will pause again when/if the procedure generates a second result set. Eventually either the SA procedure runs off the bottom (of the procedure) or does an explicit return and/or the client closes the cursor over which the procedure was called (at which point the procedure context is closed and cleaned-up).
answered 10 Jan '11, 08:44