Re: Difference between CLOSE CURSOR, FREE CURSOR

>|> In ESQL/C for Windows 5.01.WC1 >|> >|> What exactly is the difference between >|> >|> EXEC SQL CLOSE CURSOR :CursorName; >|> >|> and >|> >|> EXEC SQL FREE CURSOR :CursorName; >|> >|> What are the actions taken on client side and as well as on server >|> from the point of resources etc. I wrote this up a few years ago. It applied to 4.x and 5.0 4GL & ESQL/C; no guarantees, but it should apply for the most part to ESQL/C for Windows, too. Jonathan, if you see anything that requires clarification or is inaccurate, please let me know. June ---- June Tong Informix Asia/Pacific ---- ---- On-Loan Engineer Singapore ---- ---- junet@informix.com (65) 298-1716 ---- CURSORS and MEMORY ------------------ There are two types of cursors: static and dynamic. A static cursor is declared directly from an SQL statement: e.g. DECLARE cursorname FOR SELECT columns FROM tablename ... A dynamic cursor is declared from a PREPARE'd statement: e.g. PREPARE stmt_id FROM sql_stmt DECLARE cursorname FOR stmt_id STATIC CURSORS: -------------- DECLARE When the cursor is declared, a cursor block in the front-end is used to store the SQL statement and flags. This cursor block is statically allocated by the pre-processor. OPEN When the cursor is opened, the SQL statement is copied to the engine and parsed. A buffer is created in memory by the front-end to store the rows sent by the engine. The size of this buffer is the greater of 1 KB or 2*Rowsize. Data structures are allocated in the engine to store the parsed statement, the optimizer path, and pointers to temp tables (if any). CLOSE Closing the cursor does not free the memory held by the cursor. The buffer is left in the event of the cursor being re-opened. FREE Freeing the cursor frees the memory buffer and all the structures in the engine. It does not free the cursor block. DYNAMIC CURSORS: --------------- PREPARE When the statement is prepared, the SQL statement and statement ID are stored in a cursor block, and the SQL statement and statement ID are copied to the engine and parsed. Data structures are allocated in the engine to store the parsed statement, the optimizer path, and pointers to temp tables (if any). The cursor block is statically allocated by the pre-processor. DECLARE The cursor name is associated with the statement ID. No memory is allocated. OPEN When the cursor is opened, a buffer is created in memory by the front-end to store the rows sent by the engine. The size of this buffer is the greater of 1 KB or 2*Rowsize. CLOSE Closing the cursor does not free the memory held by the cursor. The buffer is left in the event of the cursor being re-opened. FREE Freeing the cursor frees the memory buffer and all the structures in the engine. It does not free the cursor block. In 4.10, the cursor ID and the statement ID have the same memory address, so freeing either has the same effect. ________________________________________________________________________ Cursors are closed when: 1. The "CLOSE cursorname" statement is executed. 2. The transaction is committed or rolled back (unless the cursor was declared "WITH HOLD"). 3. The cursor was opened with a FOREACH statement, and the last row has been processed and the FOREACH exited normally or with the EXIT FOREACH statement. 4. The currently active database is closed. This can be accomplished by: a. Executing the "CLOSE databasename" statement. b. Opening another database with the "DATABASE dbname" statement. Cursors are freed when: 1. The "FREE stmt_id" statement is executed (dynamic cursors), or the "FREE cursorname" statement is executed (dynamic or static cursors). 2. The application is terminated. In 4.10, cursor ID's and statement ID's had the same memory address. Therefore, FREE'ing one would free both. In 5.00, they have separate memory addresses, so that statements can be freed without freeing the associated cursor. Cursor blocks are allocated dynamically at run-time and completely freed on FREE. The statement ID has to be freed separately. Because the cursor blocks are allocated dynamically in 5.0, local cursors had to be implemented by adding a prefix to the cursor ID. There was no other way to keep them from being accessible in other modules. Similarly, because cursor blocks were static in 4.x, they only had module scope and could not be accessed in other modules of the program.