Temporary table

Temporary table

Post by Sauro » Wed, 10 Dec 2003 19:46:41


Hi!

How can I define if defined temp table exists in the current session?
 
 
 

Temporary table

Post by Fernando N » Wed, 10 Dec 2003 20:06:49


Err... "defined temp table"?!
A "CREATE TEMP TABLE..." will create a temporary table. It will exist while the session exists or until you drop it.
You can't share temporary tables between different sessions.

Regards.

 
 
 

Temporary table

Post by Sauro » Wed, 10 Dec 2003 22:03:26

Hi!

while the session exists or until you drop it.

Probably, you didn't undestand me owing to my bad english...:(
How must be to look select statement which will select created temp tables
in a current session?
 
 
 

Temporary table

Post by Ph. Chantr » Wed, 10 Dec 2003 22:06:23


Try to create it (after having set "whenever error continue").
If it fails, the table already exists.
If it works, the table didn't exist.
 
 
 

Temporary table

Post by June C. Hu » Wed, 10 Dec 2003 22:29:30


If I understand what you are asking, a temp table is referenced within a
SELECT the same way that you would reference a regular table.

For example:

CREATE TEMP TABLE test_table
(field_a char(1),
field_b smallint) WITH NO LOG;

SELECT count(*) FROM test_table;

Are you having a specific problem using a temp table, or is this a general
question?
 
 
 

Temporary table

Post by Sauro » Wed, 10 Dec 2003 23:51:34

Hi, All!

:((
I repeat my question by example:

------------------
create procedure _p1()

create temp table _t1 (a int);

insert into _t1 values(1);

end procedure;
.........
.........
.........
create procedure _p2()

if not exists (*) then
call _p1();
end if;

end procedure;
------------------



* - select temp_table_name from ... where sesionid = dbinfo('sessionid') and
temp_table_name = '_t1'


I hope, you will undestand me now...



I know one solution:

-------------
begin
on exception
end exception;

create temp table _t1 (a int);
end;
------------

But must be solution by system tables using.
For example, the following script returns all TEMP tables from instance:

select tn.tabname[1,18] temp_table, tn.dbsname[1,18] db_name, s.name[1,14]
dbspace, tn.owner[1,18]
from sysmaster:systabnames tn, sysmaster:systabinfo ti,
sysmaster:sysdbspaces s
where tn.partnum = ti.ti_partnum
and s.dbsnum = sysmaster:partdbsnum(ti_partnum)
and (sysmaster:bitval(ti_flags,32) = 1 or
sysmaster:bitval(ti_flags,64) = 1)
 
 
 

Temporary table

Post by June C. Hu » Thu, 11 Dec 2003 01:53:46


and

Your example helped clarify the issue. Thank you. Unfortunately, I don't
have an answer for you. There was a similar question asked very recently
(see the thread at http://www.yqcomputer.com/ ). The answers posted got about
as close as you've already managed. Good luck.
 
 
 

Temporary table

Post by Fernando N » Thu, 11 Dec 2003 21:02:59


I'm not sure about this, but I would say that an alternative is to always try to create the table.
You you get an error saying that the table already exists you then know that it existed before.

Regards.
 
 
 

Temporary table

Post by roefer » Thu, 25 Dec 2003 23:34:42

TRY IT:

In a database:
----------------------- spa@dsa2 --------------- Press CTRL-W for Help
--------
create temp table rfo (a int) with no log ;
insert into rfo values (100) ;

NOW, IN OTHER SESSION, IN THE SAME DATABASE OR IN OTHER DATABASE IF IN
THE SAME INSTANCE :
----------------------- dbprod@dsa2 ------------ Press CTRL-W for Help
--------

select * from sysmaster:systabnames
where dbsname = 'spa'
and tabname = 'rfo'

THE RESULT WILL BE:
----------------------- dbprod@dsa2 ------------ Press CTRL-W for Help
--------

partnum 24117537
dbsname spa
owner informix
tabname rfo
collate en_US.819

Best regards, merry christmas ! RFo
 
 
 

Temporary table

Post by curti » Sun, 18 Jan 2004 05:44:21


OK while we are on the subject, how do you know if it is your temp
table and not some other sessions temp table. I can see a temp table
list in onstat -g ses and I know only your session can access your
temp table, but I don't know where to find the temp tables that are
only valid for your session. The application could of course tag them
with your session id as in temp_table_1_for_session_1234 etc. but is
there a table that can give you the session and temp table info.
 
 
 

Temporary table

Post by Art S. Kag » Fri, 23 Jan 2004 01:51:49


<SNIP>

There are several scripts for this in the IIUG Repository.

Art S. Kagel
 
 
 

Temporary table

Post by Ravi Krish » Fri, 23 Jan 2004 02:32:22


There isn't a single script which ties a temp table to a session. Rajib Sarkar
has a script on korn shell/awk etc, but it doesn't work, at least for me.

I have tried this in every possible way, but it looks like that thru sysmaster
it is impossible to link a temp table to a session.

Ravi