Re: 4GL Cursors again

afalout@zg.tel.hr (Andrej Falout) wrote: :Hi, : :Sorry for jumping in like these, but I can tell You know more about :cursors then me. : :I am trying to keep compatibility with pre-V 5.0 OnLine server in 4GL :app, by keeping the limit on prepare & declare stmts., but reducing :number of declare stmts. alone did not helped. If I'm not missing :something, it should (?) : :Finderr: : :-257 : :System limit on maximum number of statements exceeded, maximum is :count. : :The database server can handle only a fixed number of prepared SQL :statements for each user. Included in this limit are statements :prepared with the PREPARE statement and cursors declared with DECLARE. :This statement (a PREPARE, DECLARE, or PREPARE IMMEDIATE) exceeds that :limit. The statement is not executed. The FREE statement releases :prepared statements. Review the logic of your program and change it so :that it frees prepared statements when they are no longer needed. :Note: This restriction is removed starting with database servers at :software level 5.0; however, programs that might have to be compatible :with earlier versions should still use FREE to stay under the limit. : :This message is not used by database servers later than Version 5.01. : :but, :I writhed a test program with 70 times (my limit is 64) of: : :declare a1 scroll cursor for x1 open a1 fetch first a1 into p_test.* : ....... :declare a70 scroll cursor for x1 open a70 fetch first a70 into :p_test.* : :and only one prepare (of x1) at the beginning, and it did NOT reported :any errors. So, declare does not count ? What *does* count for -257? I am not shure. :I MUST reduce only prepare stmts.? :What do I need to reduce to avoid max stmts err? : :I know I can re-use ID's in different moduls (up to V6), and I did. :Yes, I know this makes me a bad boy. Yes, but your problem here is that even if you reuse the ID's the engine will see them as different. The compiler will have to use the module name or some other scheme to make the ID's different. So you should *never* do this. It will give you trouble when you eventually upgrade to ver. 6.x of 4GL. :All in all, I have max of 2 prep's with same names in each modul :(total of 15 moduls) and no more then 8 different declare ID names in :any modul. So, only 2 different prep names and 8 different declare :names (ID's) in *all* app! : :No can do. Still I get -257 under pre V5 engines. : :I want to avoid using FREE. I still have to support some V2 4gl :compilers that does not have them. I can use only CLOSE. I think you have hit an absolute limit unless it helps to increase the max. number of files each process can have open. Otherwise the only solution I can see given your limits (not using free) is to create a function you can call that will create the different statements in a char variable and use the same prepare/declare to create the cursor as you need it. In this case you can open the same cursor (one with the same name) in different functions and in reality have totaly unrelated sql statements behind it. It will *not* be nice (very bad programming practice), it will be slow, but it should work. Of course you will have to be extremely carefull not calling this common function while a cursor based on it is open. You may need more than one such function to be able to avoide that. Another thing we did when we had this problem was to replace several cursors with singleton sql statements where that was possible. It's slow, but again it made the application work. I hope your customers will soon be able to upgrade to some later engine. The world will be very much easier for you and them when they can. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org