Re: CLOSE and FREE

>From: kerry@kcbbs.gen.nz (Kerry Sainsbury) >Subject: Re: CLOSE and FREE >Date: Sun, 08 Jan 1995 00:31:27 +1200 >X-Informix-List-Id: <news.10657> > >In article <D203p4.FGq@cix.compulink.co.uk>, >akent@cix.compulink.co.uk ("Andy Kent") wrote: >> Can anyone offer an explanation of what the CLOSE and FREE statements >> actually do, in terms of what each one frees up. > >... and why I should use them, >... and why there isn't a FREE imbedded inside CLOSE (if FREE is a Good Thing) >... and what happens if I FREE without CLOSEing first. >... and how much resource (memory?) does FREE free? > >I asked all these questions of Informix NZ about 6 months ago, but the guy >really had no idea what the answers were. Neither do I Well, maybe the info below needs to be edited into the FAQ, but the issues seem fairly clear to me... When you OPEN a cursor and FETCH data, you may start applying locks to the table on which it is based, depending on your engine type, isolation level, transaction status, whether it is a FOR UPDATE cursor, WITH HOLD cursor, SCROLL cursor etc. You may also grab disk space for temporary tables, and so on. The CLOSE statement is used to indicate that all the temporary resources allocated for handling the cursor can be released. This may or may not release the locks -- in general, COMMIT WORK releases the locks. Once you have closed the cursor, you can re-open it. If the cursor is parameterized (if the WHERE clause of the SELECT has a program variable in it), then re-opening the cursor may collect different data. With a MODE ANSI database, opening an already open cursor will generate an error, so you must CLOSE the cursor before opening it again. With other database modes, you can (probably, generally) get away with re-opening an already open cursor, but it is bad practice -- definitely discouraged. If you open something, you should close it too. That applies to cursors and forms and windows and ... in my view. So, CLOSE indicates that the cursor is not needed for the time being. However, the Engine retains the query plan for the cursor so that it can be reused. To indicate that you have no further interest in the cursor at all, you FREE the cursor. This releases all the resources used by the cursor in the Engine, thus reducing the memory required. Note that you can also FREE prepared statements as well as cursors. Version dependencies: * In Version 4.1x and prior releases of ESQL/C (and I4GL, etc), you should release either the prepared statement ID or the cursor ID, but not both. * With version 5.00 or later releases of ESQL/C (and hence I4GL 6.00 or later, etc), you need to release both the statement ID and the cursor ID. >... and why I should use them, To reduce the amount of memory in use in both the application and the engine. >... and why there isn't a FREE imbedded inside CLOSE (if FREE is a Good Thing) Because you may want to reuse the cursor without having to re-declare it. >... and what happens if I FREE without CLOSEing first. The FREE works, closing the cursor. You get an error -404 when you try to reuse the cursor without redeclaring it again. >... and how much resource (memory?) does FREE free? Depends on the statement. In the application, typically a few (under 10) kilobytes. In the engine, I'm not sure, but probably more. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>