Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2010 (Minneapolis, MN) with:

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

By Ben Nadel on

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:

  • <!--- Dump meta data to see query column types. --->
    <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:

  • <!--- Create query without column data types. --->
  • <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:

  • <!--- Create a query setting column names and data types. --->
  • <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:

  • <!--- Get the column list. --->
  • <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">

  • <!--- Output the column name. --->
  • #qGirls.GetMetaData().GetColumnName(
  • JavaCast( 'int', intColumn )
  • )#

  • :

  • <!--- Output the column data type name. --->
  • #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:

  • <!--- Get the column list. --->
  • <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=",">

  • <!--- Get the index of the column. --->
  • <cfset intColumn = qGirls.FindColumn(
  • JavaCast( "string", strColumnName )
  • ) />

  • <!--- Output the column name from the list. --->
  • #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.




Reader Comments

Ray, 'cause clearly I'm smoking crack :) I sweeeear I tried that and it didn't work! But I just tried it again and it totally does work.

I am gonna go hide in a corner and hope no body sees me now :)

Reply to this Comment

Glad you did this the hard way.
Needed the examples to do a lazy report from a table. Just looping through a result, grabbin' column types as they appear in the table (getColumnList) and then doing getMetaData().getColumnType(FindColumn(getColumnListIndex)))

Reply to this Comment

Hi Ben,

This solved a major problem we had with an app. We were using the cfdbInfo tag to get just the column names and dataTypes. It was taking (for some, unknown to me, reason) 2-3 minutes per table. This idea shortened the time to 20-30 millisecs. Wonderful performance improvement!!! Thx.

Reply to this Comment

@Ralph,

That's really odd that grabbing the DB info takes such a long time. I'm glad this was a work around, but I'm curious as to what was going on?? Very strange.

Reply to this Comment

You never fail me, Ben. Whenever I come across some weird CF behavior, I find that you've already blogged about it. Thanks so much!

Reply to this Comment

  • <!--- Create a query setting column names and data types. --->
  • <cfset qVirginTechies = QueryNew(
  • "name, dick_size, is_a_tool, last_had_sex_with_a_lady",
  • "VARCHAR, INTEGER, BIT, DATE"
  • ) />

xoxoxoxoxoxo

Reply to this Comment

Thank you so much guys, you're both great. I've taken this a step further and added the ability to list all of the tables in the database with their columns and data types neatly laid out on the page. I didn't use the cfdump command because I wanted to be able to use it in a shared hosting environment. Here is the code I ended up with:

  • <title>Database Column List</title>
  • Here is a list of the tables, columns and their data types used in your application:<br />
  • <cfset badlist="CDATA,CGLOBAL">
  • <cfdbinfo datasource="#Application.Datasource#" type="tables" name="tableresult">
  • <cfquery dbtype="query" name="qTableList">
  • SELECT TABLE_NAME FROM tableresult
  • WHERE TABLE_TYPE='TABLE'
  • </cfquery>
  • <cfloop query="qTableList">
  • <cfif not listfind(badlist, table_name)>
  • <h3><cfoutput>#qTableList.TABLE_NAME#</cfoutput></h3>
  • <cfquery name="qGetMetadata" datasource="#Application.Datasource#">
  • SELECT *
  • FROM #qTableList.TABLE_NAME#
  • </cfquery>
  • <cfoutput>
  • <cfset lstColumns=qGetMetadata.ColumnList />
  • <cfloop
  • index="intColumn"
  • from="1"
  • to="#ListLen(lstColumns)#"
  • step="1">
  • #qGetMetadata.GetMetaData().GetColumnName(
  • JavaCast('int', intColumn)
  • )#
  • :
  • #qGetMetadata.GetMetaData().GetColumnTypeName(
  • JavaCast('int', intColumn)
  • )#
  • <br />
  • </cfloop>
  • </cfoutput>
  • </cfif>
  • </cfloop>

Reply to this Comment

Oh, I almost forgot. I included a badlist of the tables CDATA and CGLOBAL because I didn't want them displayed. You can either remove them from the badlist to display them or you can add other tables that you don't want to display.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.