I've done this in Oracle 8i and I would assume it can be done in SQLServer
and mySQL. Here's what I did with Oracle:
<CFSET TableName = "foo">
<CFQUERY Name="GetData" ... >
select *
from #TableName#
where 1 = 0
</CFQUERY>
Columns for <CFOUTPUT>#TableName#</CFOUTPUT>
<CFLOOP LIST="#GetData.ColumnList#" INDEX="ColName">
<CFQUERY NAME="GetType" ... >
select data_type, data_length, nullable from
user_tab_columns
where table_name = '#UCase(TableName)#'
and column_name = '#UCase(ColName)#'
</CFQUERY>
<CFSET TempDetail = "#GetType.data_type#(#GetType.data_length#)">
<CFIF GetType.nullable IS "Y">
<CFSET TempDetail = TempDetail & " Null">
</CFIF>
<CFOUTPUT><BR>#ColName# : #TempDetail#</CFOUTPUT>
</CFLOOP>
There may be more efficient ways to do this rather than loop through the
columns and querying on each loop, but this was my quick hack!
Chris Lofback
Sr. Web Developer
TRX Integration
28051 US 19 N., Ste. C
Clearwater, FL 33761
www.trxi.com
-----Original Message-----
From: Jason Miller [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 04, 2002 3:54 PM
To: CF-Talk
Subject: Re: Querying a table for its columns
This came at a perfect time. - I am building a flash application with
coldfusion that queries a database, creates forms on the fly in flash so I
can have
1 mini app that can be used as a database updater (to merely update data in
existing columns) to any database - specifically for now access.
Anyone now of a good tutorial or someone who has done this in just cf and
html so I can check it out?
I am looking for similair variables that may be availabel along with
ColumnList - like - can I see what type of information it is? Or if it is a
drop
down list in Access? If i can sniff that out - so I can create a drop down
so it matches etc etc.
Hope this is clear - any good resources would be a great help.
thanks,
jason miller
Chris Lofback wrote:
> Here's one way:
>
> <CFQUERY Name="GetData" ... >
> select *
> from table
> where 1 = 0
> </CFQUERY>
>
> (Use WHERE 1 = 0 so you get no records, but the ColumnList variable will
> still be populated.)
>
> <CFLOOP LIST="#GetData.ColumnList#" INDEX="Column">
> #Column#
> </CFLOOP>
>
> Chris Lofback
> Sr. Web Developer
>
> TRX Integration
> 28051 US 19 N., Ste. C
> Clearwater, FL 33761
> www.trxi.com
>
> -----Original Message-----
> From: Eric Dawson [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 04, 2002 3:15 PM
> To: CF-Talk
> Subject: Querying a table for its columns
>
> Querying a table for its columns
>
> I can't remember the syntax for querying a table for its columns. I think
it
>
> is a mySQL table on CF 4.0x box.
>
> Eric
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
