Oninit cdi / Archive
Talk to an expert →

Re: To get the column information from SELECT Query.

Posted in 2006

Topics: SQL Development & Query Writing, Stored Procedures & SPL, Error Codes & Troubleshooting, Connectivity: ESQL/C, 4GL & Embedded SQL

Doug Lawry — — source: Usenet: comp.databases.informix

"SW" <wagh.shirish@gmail.com> wrote in message news:1138077701.917637.54550@g44g2000cwa.googlegroups.com... >I want to find out the Column Information from an SELECT Query. Above > operation is possible in the SQL SERVER. U can see the code for that on > the link, > > "http://groups.google.co.in/group/microsoft.public.sqlserver.programming/ > browse_thread/thread/c4b4be733a6b4cba/37c68c72b00996b%2337c68c7 > 2b00996b?sa=X&oi=groupsr&start=1&num=3" > > Stored procedure written in above link does a wonderful job. Given any > SQL SELECT Query as parameter it will get the column informations about > all the rows which are returned by this SELECT query. > > I can not figure out what they are doing actually in that procedure. > > Does anybody know how to accomplish the same thing in Informix 4GL? > > Thanks in advance, > Shirish Wagh Have a look at InfoCenter/Developing/Guide to SQL Syntax/DESCRIBE: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.doc/sqls984.htm If your 4GL version/variant does not support it, use an ESQL/C function: http://publibfp.boulder.ibm.com/epubs/pdf/ct1uhna.pdf If you are not using c4gl, download Client SDK from: http://www14.software.ibm.com/webapp/download/search.jsp?rs=ifxdl The example below shows a simple DESCRIBE application. -- Regards, Doug Lawry www.douglawry.webhop.org _____________ ___| describe.ec |______________________________________________________ // Get tab-delimited column headings for a select statement // Doug Lawry, 24/01/2006 #include "sqlca.h" #define CHECK_STATUS if (check_status(result)) return(result) #define MAX_COL_LEN 256 char *describe_select(database, sql) $char *database, *sql; { $char *describe = "describe", *delimit = "\\t", colname[MAX_COL_LEN]; $int ncols, colno, len, max_col = 256; $static char result[512] = ""; $DATABASE $database; CHECK_STATUS; $ALLOCATE DESCRIPTOR $describe WITH MAX $max_col; CHECK_STATUS; $PREPARE id FROM $sql; CHECK_STATUS; $DESCRIBE id USING SQL DESCRIPTOR $describe; CHECK_STATUS; $GET DESCRIPTOR $describe $ncols = COUNT; CHECK_STATUS; for (colno = 1; colno <= ncols; colno++) { $GET DESCRIPTOR $describe VALUE $colno $colname = NAME; for (len = 0; len < MAX_COL_LEN && colname[len] != ' '; len++) { if (colname[len] == '_') colname[len] = ' '; if (len == 0 || colname[len-1] == ' ') colname[len] = toupper(colname[len]); } strncat(result, colname, len); strcat(result, delimit); } $DEALLOCATE DESCRIPTOR $describe; $FREE id; result[strlen(result)-1] = '\\0'; return(result); } check_status(char *result) { if (sqlca.sqlcode) sprintf(result, "SQL error %d", sqlca.sqlcode); return(sqlca.sqlcode); } main(int argc, char *argv[]) { if (argc != 3) printf("Usage: %s database select-statement\\n", argv[0]); else printf("%s\\n", describe_select(argv[1], argv[2])); }

↪ replying to Doug Lawry
SW — — source: Usenet: comp.databases.informix

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. 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?

Glossary