I was looking for a way to get the data type of a query column so that I could apply different logic based on the type. I found the solution for this on another blog: http://www.bennadel.com/blog/308-Ask-Ben-Finding-The-SQL-Data-Type-Of-A-ColdFusion-Query-Column.htm

Using the GetMetaData() function, you can pass in a query object and return an array of structures that contains each column in the query and information about that column. Very useful!!

Basically, to use the function, you just pass in the query name:

view plain print about
1<cfset arrMetaData = GetMetaData(myQuery)>

I have expanded on the original blog entry a little bit by creating a function that I placed in my generic Utilities component to be used throughout the application.

view plain print about
1<cffunction name="getColumnType" returntype="string" hint="get the column type based on the table/column name">
2<cfargument name="strTableName" type="string" required="yes">
3<cfargument name="strColumnName" type="string" required="yes">
4<cfargument name="strDataSource" type="string" required="no" default="#application.datasource#">
5
6<cfset strColumnType = "unknown">
7
8<cftry>
9<!--- don't really want to return data, just want to get the column info --->
10<cfquery name="qryColumnType" datasource="#arguments.strDataSource#">
11 select #arguments.strColumnName# from #arguments.strTableName# where 0=1
12</cfquery>
13<cfscript>
14tableMeta = getMetaData(qryColumnType);
15// only getting one column, just get the 1st record in the arary
16strColumnType = tableMeta[1].TypeName;
17</cfscript>
18<cfcatch type="any"><!--- problem - use "unknown" for returned type ---></cfcatch>
19</cftry>
20
21<cfreturn strColumnType>
22</cffunction>

You could also write a similar function to pass in an existing query instead of the table name and the field that you want to get the data type for, then loop over the array elements until you find the column you are looking for:

view plain print about
1<cffunction hint="get the column type based on the table/column name" returntype="string" name="getColumnTypeFromQuery">
2 <cfargument name="myQuery" required="yes" type="query">
3 <cfargument name="strColumnName" required="yes" type="string">
4
5 <cfset strcolumntype="unknown">
6 <cfset arrmetadata="GetMetaData(arguments.myQuery)">
7
8 <cfloop to="#arrayLen(arrMetaData)#" from="1" index="i">
9 <!--- check to see if this is the column we want --->
10 <cfif arrMetaData[i].name eq arguments.strcolumnname>
11 <cfset strcolumntype= arrMetaData[i].TypeName>
12 </cfif>
13 </cfloop>
14
15 <cfreturn strcolumntype>
16</cffunction>

This entry was posted on May 28, 2009 at 1:11 PM and has received 1836 views. There are currently 3 comments. Print this entry.