Ask Ben: Finding The SQL Data Type Of A ColdFusion Query Column : Kinky Solutions : A Student's Perspective by Ben Nadel

Ask Ben: Finding The SQL Data Type Of A ColdFusion Query Column

Posted October 2, 2006 at 3:19 PM

How can I find out the query column data type of an existing query object?

NOTE: As Ray Camden pointed out in the comments below I made this WAAAY too complicated. Just dump out the Meta Data of the query and that gives you everything you need:

Launch code in new window » Download code as text file »

<cfdump var="#GetMetaData( qGirls )#" />

All that follows, while perhaps interesting, is not needed.

The ColdFusion query is a modified Java SQL result set (as far as I can guess) and it happens to allow access to the query column types through its Meta Data. Before we get into this though, it is important to talk about one thing: You can only get the data type of a column if it has been set. But isn't it always set? No. It is set when you get a query from the database and it is set when you create a query via QueryNew() AND send column data types. If, however, you create a query using QueryNew() but do NOT send in data types:

Launch code in new window » Download code as text file »

<cfset qGirls = QueryNew(
"id, name"
) />

... then the data types are not available. This makes sense though, I mean what would they be any way? If you try to get the data types from this type of query, an error will be thrown (or rather I think it comes back as NULL which ColdFusion tends to not like so much).

That being covered, let's look at how to get the data types from a query. I will start off by building a query using QueryNew() and explicitly setting data types during query initialization. This should mimic the data that comes back from a standard SQL database call:

Launch code in new window » Download code as text file »

<cfset qGirls = QueryNew(
"name, hotness, is_curvey, birthday",
"VARCHAR, INTEGER, BIT, DATE"
) />

Now, when getting the column data types, we have to use the query meta data. Pretty much every method call regarding columns uses the index of the column, NOT the name of the column. If you don't care which column you are getting, we can simply loop over the columns and output the data type. In this example, I will output the names and the data type so you can see it in action:

Launch code in new window » Download code as text file »

<cfset lstColumns = qGirls.ColumnList />
<!---
Loop over the columns in the query. We don't care about
the names, merely the number.
--->
<cfloop
index="intColumn"
from="1"
to="#ListLen( lstColumns )#"
step="1">
#qGirls.GetMetaData().GetColumnName(
JavaCast( 'int', intColumn )
)#
:
#qGirls.GetMetaData().GetColumnTypeName(
JavaCast( 'int', intColumn )
)#
<br />
</cfloop>

Notice that I am Java casting all values sent to the Java method GetColumnName() and GetColumnTypeName(). That is so ColdFusion does not have to guess the type conversion from a typeless language (ColdFusion) to a strongly typed language (Java) and we don't throw any errors. Also, again, let me stress that the columns are being looked up by INDEX (hence the Java cast to INT), not by name. This will give us:

name : VARCHAR
hotness : INTEGER
is_curvey : BIT
birthday : DATE

If you look up the Java result set, you will notice that there is also a method GetColumnType(). Be careful when using this one; it returns an integer representation of the data type. Since the name means more to us, we use the GetColumnTypeName() method. However, when it comes to comparisons programmatically, you might want to use the GetColumnType() method.

Also, in times, the Name is more descriptive. If you have an INT column, the column type for integer is 4. This is the same whether or not he column is an identity column. However, if you dump out the TypeName of that column, one will output "int", the other will output "int identity"... so, that's pretty nifty.

Now, we have talked about looping over columns, but what happens if you want one column in particular? Well, you can use the query object to tell you the index of a given column using the FindColumn() method. In this next example, we will loop over the column list itself and output the data types based on column name, NOT index:

Launch code in new window » Download code as text file »

<cfset lstColumns = qGirls.ColumnList />
<!---
Loop over the columns in the query. This time, we are
looping over the list items themselves.
--->
<cfloop
index="strColumnName"
list="#lstColumns#"
delimiters=",">
<cfset intColumn = qGirls.FindColumn(
JavaCast( "string", strColumnName )
) />
#strColumnName#
:
<!---
Output the column data type name based on the index
we got searching for the column name.
--->
#qGirls.GetMetaData().GetColumnTypeName(
JavaCast( 'int', intColumn )
)#
<br />
</cfloop>

Notice that we are getting the index based on the column name. This gives us the output:

BIRTHDAY : DATE
HOTNESS : INTEGER
IS_CURVEY : BIT
NAME : VARCHAR

The only difference here is that the column list is alphabetical since that's how the ColumnList value is returned. That's all there is to it. Not sure that this is officially documented, but have fun with it.

Download Code Snippet ZIP File