Re: Prepare, Free statement id's

}Date: Sat, 25 Jan 97 11:51:03 MET }From: Marco Greco <marcog@linux.ctonline.it> }Subject: Re: Prepare, Free statement id's }To: informix-list@rmy.emory.edu, johnl@informix.com } }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? Correct. }Now, this surely applies to embedded language products, but not to other }tools, which *haven't* had 5ish releases. Well, it depends on what you mean... I4GL 4.1x and ISQL 4.1x use 4.12 ESQL/C, so if you are using 4.1x I4GL, the pre-5.0 rules apply (freeing a statement frees the cursor, and vice versa). I4GL 6.0x and ISQL 6.0x use 6.00 ESQL/C, so if you are using 6.0x I4GL, the post-5 rules apply (you free both the statement and the cursor). NewEra 1.0x was based on 5.0x ESQL/C, so the post-5 rules apply to all versions of NewEra. If you're using some other tool, you'll need to know which version of ESQL/C it was built with, and that will determine which set of rules apply. }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. If the original question was 4.1x I4GL running against 5.0x Engine, then the pre-5 rules do indeed apply. However, since the question mentions the Version 6 SQL Reference Manual (see the first line of Douglas' text), I think the post-5 rules apply to his situation. }Please correct me if I'm wrong. You're correct. I couched my answer in terms of ESQL/C because that is what determines which rules apply. You've given a nice exposition on the implications of which rules apply to which tools, which I hope I've reinforced. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }--- 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----------------- }