Re: open cursor ==> error -255/0

There are several problems with this code. Although I am not sure why you get exactly the error you do you might want to rewrite it to see if that works: Jacob Salomon <jake@apparel.net> wrote: :Hi all, : :I am testing a program modification in which I need to do work on every :row in the active set of a user query. The user has entered query data :in a 4GL form, and has been browsing Next/Previous so the primary-key :cursor has been quite active. Now the user presses a menu selection :called "mass-operate". My response is to close the PKey-cursor and :repoen it. Here is a code snippet: : :538 function mass_approve() # Approve all credit-held NSRs for all :orders :539 # in this query's active set. :540 # Uses cursors created in llh_a_construct() :541 :##################################################################### :542 whenever error continue # For debugging :543 :544 close pkey_curs # This was opened in llh_a_construct :545 open pkey_curs # reopen it for fresh business :546 while sqlca.sqlcode = 0 The first time this sqlcode is from the above open statement which is ok. The next time arround it's from the clos lh_cursor just before the end while. That I assume is not the idea. :547 fetch pkey_curs :548 into m_oordrh.ord_num # Just get the primary key value You may use: if sqlca.sqlcode <> 0 then exit while end if to get out of the while loop. :549 open lh_cursor :550 using m_oordrh.ord_num :551 fetch lh_cursor # Fetch again, just to lock the :row :552 into m_oordrh.ord_num :553 call approve_nsrs(m_oordrh.ord_num) :554 close lh_cursor # release the locking cursor :555 end while : As you apperantly have transactions turned on in the database it's strange to program the way you have done. As you are locking each row I assume some kind of update (perhaps including insert/delete) happens somewhere (in the approve_nsrs function). As you have no begin work each update will be treated as a separate transaction. At this time all your cursors will be automatically closed, including your main pkey_curs. This should therefore be declared as a hold cursor. The lh_cursor should be closed before the commit work statement (although it is automatically closed by a commit work,I think an explisit close is a god idea). However it would be *much* better to code the whole thing with explisit begin/commit/rollback work. That would make it much more clear exactly what is happening. You might want to try these changes to see what happens then. You obviously need to check status for every open/fetch/close as well with a whenever error statement arround the whole thing. I assume you have left this out only to minimise the posted code. :IMO, this code looks quite innocent. The close (line 545) comes off :with no problem. The reopen of the pkey_curs cursor goes just fine and :I get real data in the variable when I fetch it. Now I try to open the :locking cursor using the value I just fetched. I get error -255. : :Now error -255 is quite absurd for me to get here. Observe: If you get this error the first time you open the lh_cursor I also don't quite understand why. The second and later times the fetch pkey_curs should give an error that the cursor is no longer open. : :$ finderr 255 :[]-255 Not in transaction. :[]This COMMIT WORK or ROLLBACK WORK statement cannot :[]be executed because no BEGIN WORK was executed to start :[]a transaction. Since no transaction was started, there is no :[]need to end one. Any database modifications that were :[]done are now permanent; they cannot be rolled back but do :[]not need to be committed. Review the sequence of SQL :[]statements to see where the transaction should have started. : :This statement is basically correct; I was not running in a transaction. :However, as y'all can see, I also wasn't running a *&^^%! commit or :rollback to engender this kind error. : :It would appear that I simply got back the wrong error code. Otherwise, :something *has* gone awry in opening the locking cursor. OK, when I :created the cursor, what did it look like? : :225 let scratch # Locking cursor for the row :226 = "select ord_num from orders_tab", :227 " where ord_num = ?", :228 " for update" :229 prepare lh_stmt from scratch :230 declare lh_cursor cursor for lh_stmt : :So when opened and running (heh,heh) it will somply fetch that column :again and lock the row. At least that's the idea. : :Has anyone ever run across such an error opening a cursor? Any clue :what's causing it? 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