Re: Prepare, Free statement id's

Now I am confused. The whole point here is that prior to rel 5.0x a "free prepared-statement-id" freed both engine and application resources, whereas following that you need both a free cursor (to free engine resources) and a free prepared-statement-id (to free tool/application resources), am I right? Now, this surely applies to embedded language products, but not to other tools, which *haven't* had 5ish releases. Take 4gl (which the original poster was referring to, I think), for instance, running against a 5.x engine. In this case, if you free a prepared statement for which a cursor has been declared, and later you try to use the cursor itself, you will most certainly hit error -412 or -400 (command pointer is null or fetch attempted on an unopen cursor), because of the pre 5.x approach of 4gl to freeing resources. Please correct me if I'm wrong. Ciao, Marco ____________________________________________________________________________ rem radioterapia, which I immeritately manage, seldom agrees with what I say marco greco (Catania, Italy) Work: marcog@ctonline.it rem radioterapia 39 95 447828 fax 446558 (was mar.greco@agora.stm.it) Achea 39 95 503117 --- On Thu, 23 Jan 1997 Jonathan Leffler <johnl@informix.com> wrote: >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> -----------------End of Original Message-----------------