Re: Ownership of (temp)tables.

Jeff Cauhape writes: ->aland@informix.com (Colonel Panic) writes: -> ->>In article <4755@krafla.rhi.hi.is> gaukur@rhi.hi.is (Kristjan Gaukur Kristjansson) writes: ->>>I'm having a bit of a problem with temptables. ->>>I'm using temptables for storing of select-results to use further later, ->>>and that works just fine. ->>>BUT, if I want to do two things at the same time that use the same temptable ->>>name, and so start the 4gl program in two windows, I get a conflict between ->>>the temptable(s) since in both cases it gets the same owner name, and that, ->>>the good book says, is not allowed. -> ->>This can't be the problem -- all temp tables are strictly local to an ->>engine process. That is, even if two independent sessions by the same user ->>create temp tables of the same name, the names are known only by the engine ->>process; they are not inserted into the system catalogs. -> ->>Are you *sure* that you are creating them as temp tables? -> ->>> Gaukur. (gaukur@rhi.hi.is) ->>Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland -> ->I have the same problem. It appeared right after I upgraded the SE from 4.0 ->to 5.0. The only solution I found that worked was to build the table through ->a prepared statement. I get a unique name by a call to tempnam() and assign ->this to a variable which is then used in making the prepared statement. -> ->BTW, if you try to drop the table before closing ALL cursors opened against ->it, it will tell you that the table can't be dropped because it's opened by ->another user. -> ->---------------------------------------------------------------------------- ->Jeff Cauhape cauhape@TWG.COM 415/962-7147 Room 224 (SneakerNet) -> ->"I haven't lost my mind. It's backed up on tape somewhere." ->---------------------------------------------------------------------------- A client of ours had this, or a very similar, problem in version 4.0 of OnLine and 4GL last year. Except in their case it was caused by different users on different workstations accessing the same bit of 4GL code at the same time. And because locks were being taking on rows in the temporary table (it was a several stage interactive query, which involved marking rows in the first stage to get further details...) the net result was that some sessions would lock up. And yes, Informix Tech. Support (Australia) told us too that the temporary table names are strictly local to a process and that it couldn't be the problem. We never did find out exactly what or where the problem really was. The solution that was finally adopted was to create a _permanent_ intermediate results table for that particular query to use. Not very elegant I know but it seemed the best we could come up with at the time. +--------------------------------------------------------------------+ Ron Lees rlees@pdact.pd.necisa.oz.au NEC Information Systems Australia Pty Ltd Voice: +61-6-2516411 Software Development Centre (Canberra) Fax: +61-6-2516947 PO Box 244, Belconnen ACT 2616, AUSTRALIA +--------------------------------------------------------------------+