Re: To get the column information from SELECT Query.
A poster building a simple query analyser in Informix 4GL wanted to retrieve column information (names/types) for an arbitrary SELECT, including results dumped INTO TEMP, but his 4GL didn't support DESCRIBE. Replies: DESCRIBE of 'SELECT * FROM table' is the only way to get column info (temp or permanent), and there's no CREATE TABLE LIKE / CREATE TABLE AS SELECT in Informix (a feature request existed). The suggested workaround was to write an ESQL/C function, callable from 4GL, that PREPAREs and DESCRIBEs the statement and returns the statement name plus an array of column names (wrapped in a structure if arrays can't be returned); alternatively build it into a DataBlade. The poster didn't confirm whether this worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
SW wrote: > Thank Doug for your response. > > Just tell me one more thing, can we accomplish the same thing by use of > Temp tables. > > I will retrive all the rows from the SELECT statment inside an Temp > table using INTO TEMP clause. You can describe 'SELECT * FROM temp_table' - and you'll get the information. In fact, it's the only way to do it (and it works for permanent tables too). > But now the problem is, How to find the column information for the Temp > Tables? > > Is it possible to create a Table dynamically from the Temp Table? If you mean CREATE {PERMANENT} TABLE LIKE temp_table; the answer is No. If you mean CREATE {PERMANENT} TABLE LIKE SELECT * FROM WhereEver; the answer is also No. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Well Thank you again. So we can not create the table directly from Temp table or for that matter any other table. Do you know how to get the column information of the Temp Tables using some SQL Query?
"SW" <wagh.shirish@gmail.com> wrote in message news:1138182078.451365.191170@z14g2000cwz.googlegroups.com... > Well Thank you again. > > So we can not create the table directly from Temp table or for that > matter any other table. Yes, you could in ESQL/C by assigning a character array variable with the CREATE TABLE statement string constructed from the DESCRIBE results, then using PREPARE and EXECUTE. > Do you know how to get the column information of the Temp Tables using > some SQL Query? If you want all this to be available in SQL, you could build the above into a datablade. Note that the syntax for cloning a permanent table is provided in SQL Server. Feature request, David?! -- Regards, Doug Lawry www.douglawry.webhop.org
Well Doug thanks again. But again the same problem persists. The 4GL i am using is not supporting the DESCRIBE statement. I am just trying to build a simple Query Analyser for Informix in 4GL. I can get back the rows using a PREPARE statement, But i am not able to retrive the column information. Do u know any other method to achieve this?
SW wrote: > Well Doug thanks again. > > But again the same problem persists. The 4GL i am using is not > supporting the DESCRIBE statement. But 4GL DOES support ESQL/C functions and you could write one to PREPARE and DESCRIBE the statement and return the name of the prepared statement so that the DECLARE CURSOR statement can access it and place the column names into an array for 4GL to look at. Not hard. The 4Gl side would look something like: DECLARE colnames array[100] of char(128), stmtname char(20) CALL prepfunc( sql_stmt_string ) RETURNING stmtname, colnames DECLARE stmt_curs CURSOR FOR stmtname ... Just don't remember if you can return an array directly in a 4GL function (or C/ESQL-C equivalent). If not you can wrap the array in a structure and return (ie push) the structure. Art S. Kagel > I am just trying to build a simple Query Analyser for Informix in 4GL. > I can get back the rows using a PREPARE statement, But i am not able to > retrive the column information. > > Do u know any other method to achieve this? >
"Note that the syntax for cloning a permanent table is provided in SQL Server. Feature request, David?! " Yes it is at www.smooth1.co.uk and apparently has been noticed by IBM. Does anyone want to add their support for this feature request? Thats ok Nibbles:- http://www.lawry.nildram.co.uk/home/images/Nibbles%20at%20New%20Year%202.jpg
> > "Note that the syntax for cloning a permanent table is provided in SQL > Server. > Feature request, David?! " > > Yes it is at www.smooth1.co.uk and apparently has been noticed by IBM. > Does anyone want to add their support for this feature request? > > Thats ok Nibbles:- > http://www.lawry.nildram.co.uk/home/images/Nibbles%20at%20New% > 20Year%202.jpg Yes but which one is Doug ? Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 GO FURTHER with DB2 GET THERE FASTER with Informix. Attend the IDUG 2006 North America Conference. Tampa, Florida, USA. 7-11 May 2006. Visit http://www.iiug.org/conf for more information.>
:-) "Paul Watson" <paul@oninit.com> wrote in message news:016901c62203$5aacfb10$11202952@Arnold... >> >> "Note that the syntax for cloning a permanent table is provided in SQL >> Server. >> Feature request, David?! " >> >> Yes it is at www.smooth1.co.uk and apparently has been noticed by IBM. >> Does anyone want to add their support for this feature request? >> >> Thats ok Nibbles:- >> http://www.lawry.nildram.co.uk/home/images/Nibbles%20at%20New% >> 20Year%202.jpg > > > Yes but which one is Doug ? > > Paul Watson > Tel: +44 1414161772 > Mob: +44 7818003457 > > GO FURTHER with DB2 > GET THERE FASTER with Informix. > Attend the IDUG 2006 North America Conference. > Tampa, Florida, USA. 7-11 May 2006. > Visit http://www.iiug.org/conf for more information.>