Re: Prepare, Free statement id's

>From: Douglas Wilson <dgwilson@gte.net> >Date: Wed, 22 Jan 1997 22:54:36 -0800 >X-Informix-List-Id: <news.32903> > >In the V.6 Sql Reference manual, under the description of the "FREE" >command, it says (among other things) that after you free a prepared >statement id, you can't open a cursor that was prepared with that >statement id. > >I've just run across some code (while looking for some mysterious >error) that has alot of prepares in the beginning like so: > >let sel_stmt="select ...." >prepare sel_stmt_id from sel_str >declare sel_cursor cursor for sel_stmt >free sel_stmt_id > >and then later the cursors are used. There aren't any obvious >errors, but I was wondering if either the manual was hogwash (not the >whole thing, just the referenced part), or if there should be an >error generated when opening the cursor, or if this might be the >cause of some not so obvious error. I called Informix tech support, >he didn't know, and I was wondering if anyone did know. > >I was also wondering if there was any benefit to freeing the >statement id; the modules not very large, there's only about 50 >select statements in the whole thing, I'd think that you could leave >them all as open cursors if you wanted to. Somebody's walked off with my copy of the 6.0 Informix Guide to SQL: Syntax book, so I can't quote chapter and verse from it. I don't think the behaviour changed between 6.0 and 7.1, and I do have the 7.1 manual from which to quote. And I learned about this behaviour via the 5.0 version of the manual, so I am fairly sure that, unless there was a documentation error in the 6.0 version, the same behaviour was documented. It is different from the pre-5.0 behaviour. In the 7.1 Syntax manual, it says (p1-276 and 1-277): Freeing a statement If you declared a cursor for a prepared statement, FREE statement-id releases only the resources in the application development tool; the cursor can still be used. The resources in the database server are released only when you free the cursor. After freeing a statement, you cannot execute it or declare a cursor for it until you prepare it again. Freeing a cursor If you declared a cursor for a prepared statement, freeing the cursor releases only the resources in the database server. To release the resources for the statement in the application development tool, use FREE statement-id. After a cursor is freed, it cannot be opened until it is declared again. It is recommended that the cursor be explicitly closed before it is freed. The code you show is perfectly correct; it frees the prepared statement after a cursor was declared for it, so the cursor can still be used. As to whether there is any benefit to freeing the statement in a small application -- it is somewhat debatable, but generally it is a good idea (good housekeeping) to release resources as soon as they are no longer needed, so it is a good idea to free the statement immediately after declaring the cursor. No, the house won't fall down if you don't (probably:-), but the discipline in a small program is good practice for larger programs. It is also reassuring that the programmers had read the manual (and come to the correct conclusion) and were prepared to do that; it suggest the application has a chance of being of good quality. If they were not reasonably familiar with the newer ESQL/C, they might never free the statement -- in pre-5.0 ESQL/C, freeing the cursor also freed the statement! Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>