Re: cursors question

In article <EAAKGB.7yp@nonexistent.com>, Jacob Salomon <jake@apparel.net> writes >Oscar Goldes wrote: >> >> Hello,=20 >> a simple question about cursors in informix online 7.2 >> using powerbuilder as client. >> >> If a stored procedure uses an implicit cursor such as >> foreach >> select ...... >> return.... with resume >> end foreach>> >> The client uses a corresponding loop to fetch results. >> >> My (very simple) question is: >> >> When the procedure is executed, how are the results handled, i.e: >> are they sent to the client at once in a single operation, stored >> there somewhere, and retrieved localy by the fetch loop in the >> application? in this case, where are they stored? >> are they kept on the server and returned to the client on each >> (client) fetch iteration? in this case, where are they kept? >> is each iteration in the foreach executed only as the client executes >> a fetch? > >Oscar, > >as I recall it, the engine fetches each row in a procedure as it would >in in a regular query - a single row is returned to the user as a time. >(If the user asks for bufferloads for a scroll cursor, the engine >fetches more in order ot fill the buffer.) As the user repeatedly >executes the FETCH command, the engine itself fetches another row and >sends it to the user. > >(Corrections requested on the following point:) >The engine does not store rows before sending to the user; it worries >about stale data and the locking business. This is the behavior in both >ordinary queries and stored procedures. If it is a SCROLL cursor i.e. you can move backwards in the list of rows then the engine builds a 'hidden' temporary table to sort the results. You cannot aess this 'hidden' temp table directly. Somehow it handles the locking and stale data problem... -- David Williams