Temporary Tables Structure, how to get it ?

Posted in 2012

Summary

Marcos asked how to get the column names and data types of a temporary table, since temp tables don't appear in the system catalogs. John Miller explained that temp tables are session-local and their structure only exists in the creating session's memory. Jonathan Leffler suggested preparing 'SELECT * FROM temptable' and using DESCRIBE from an API such as ODBC, JDBC or ESQL/C; however, Marcos was using FourJ's 4GL, which lacks the dynamic SQL support needed, so you must already know the column types when creating the table. A later post pointed to the IIUG 'find_tmp_tbls' script, which lists temp tables per session via onstat. No in-4GL solution was found.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Data Types & Schema Design

MARCOS CASSINERIO — Mon, 01 Oct 2012 21:31:46 — source: IIUG Forums & Mailing Lists

Hello, is there any sql to get the structure of a temporary table,like colunmn names and data types?. I couldn't find this information in the sysmaster tables or in the sys*tables, and neither googling. Thanks in advance Marcos Cassinerio

↪ replying to MARCOS CASSINERIO

John Miller iii — Mon, 01 Oct 2012 22:01:36 — source: IIUG Forums & Mailing Lists

Temp tables are local to the user who creates the temp table, and the only information about the structure of the temp table is stored in the users memory who created them. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/01/2012 02:31:58 PM: > From: "MARCOS CASSINERIO" <m_cassinerio@hotmail.com> > To: ids@iiug.org, > Date: 10/01/2012 02:32 PM > Subject: Temporary Tables Structure, how to get it ? [28410] > Sent by: ids-bounces@iiug.org > > Hello, is there any sql to get the structure of a temporary > table,like colunmn > names and data types?. > > I couldn't find this information in the sysmaster tables or in the > sys*tables, > and neither googling. > > Thanks in advance > > Marcos Cassinerio > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller iii

MARCOS — Tue, 02 Oct 2012 01:31:41 — source: IIUG Forums & Mailing Lists

I know that temp tables are locale to the user sessions, what I need is to get the structure of the temp table, while inside the session, Do you know if it is possible to access this information inside the session ? Thanks Marcos Cassinerio

↪ replying to MARCOS

Jonathan — Tue, 02 Oct 2012 02:03:27 — source: IIUG Forums & Mailing Lists

On Mon, Oct 1, 2012 at 6:31 PM, MARCOS CASSINERIO <m_cassinerio@hotmail.com>wrote: > I know that temp tables are locale to the user sessions, > > What I need is to get the structure of the temp table, while inside the > session. > > Do you know if it is possible to access this information inside the > session ? > If you're using an API like ODBC, JDBC or ESQL/C, you can prepare a 'SELECT * FROM TempTable' statement and use DESCRIBE or its equivalent to find out the columns on the table. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --bcaec554de024dc5cb04cb09ed55

↪ replying to Jonathan

MARCOS — Tue, 02 Oct 2012 02:26:27 — source: IIUG Forums & Mailing Lists

yes i thout about that, and i need something like that, but unfortunately i'm not using odbc, i'm using 4gl ( fourjs 4gl ). Thanks Marcos Cassinerio

↪ replying to MARCOS

Jonathan — Tue, 02 Oct 2012 03:23:41 — source: IIUG Forums & Mailing Lists

On Mon, Oct 1, 2012 at 7:26 PM, MARCOS CASSINERIO <m_cassinerio@hotmail.com>wrote: > Yes I thought about that, and I need something like that, but > unfortunately I'm > not using ODBC; I'm using 4gl ( FourJ's 4gl ). > Please quote enough context to give newcomers to a thread a reasonable chance of guessing what you're discussing. The issue is finding the columns in a temporary table. It has been refined to 'finding the columns in a temporary table in I4GL' (so DESCRIBE is not an option). With I4GL, you need to know the types of the columns before you create the table. You don't have the necessary dynamic SQL support to be able to determine the column types otherwise. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --e89a8f22c70536029004cb0b0c54

↪ replying to John Miller iii

david@smooth1.co.uk — Wed, 31 Oct 2012 11:55:44 — source: IIUG Forums & Mailing Lists

On 01 October 2012 at 23:01 John Miller iii <miller3@us.ibm.com> wrote: > Temp tables are local to the user who creates the temp table, and the > only information about the structure of the temp table is stored in > the users memory who created them. > > John F. Miller III > STSM, Embedability Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > http://www.iiug.org/software/index_all.html find_tmp_tbls Lists temp tables per session in an instance using onstat [AWK, DBA, KSH, MISC] David.