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 CFUNITED 2008 (Washington, D.C.) with:

Ask Ben: Testing The Existence Of A Query Column In CFScript

By Ben Nadel on

When I am in a script block, how can I test for the existence of a column?

Testing for existence of column in a query (I assume you mean a query column) is easy inside of a CFScript block in ColdFusion. Actually, it's easy anywhere: all you have to do is check to see if it is in the ColdFusion query's ColumnList attribute:

  • <!--- Check to see if column exists. --->
  • <cfif ListFindNoCase( qQueryObject.ColumnList, "column_name" )>
  •  
  • <!--- Column found! --->
  • Sweeet! #column_name# is available in the query.
  •  
  • </cfif>

This uses searches through the column list of the query. I am never sure what case this list comes back in (upper, lower, mixed) and so I do a NoCase find method call. This should return the index of the matching item. If there is no matching item, then it returns zero which ColdFusion will interpret as False.

You can also reference a ColdFusion query object like it was a struct with keys. These keys, among other things, contain the columns. Therefore, you can also check for key existence:

  • <!--- Check to see if column KEY exists. --->
  • <cfif StructKeyExists( qQueryObject, "column_name" )>
  •  
  • <!--- Column found! --->
  • Sweeet! #column_name# is available in the query.
  •  
  • </cfif>

Now, you asked about doing this inside of ColdFusion CFScript tags. These are just simple list and key checking operations and can be easily translated to CFScript:

  • // Check to see if column exists.
  • if (ListFindNoCase( qQueryObject.ColumnList, "column_name" )){
  •  
  • // Column found!
  • WriteOutput(
  • "Sweeet! #column_name# is available in the query."
  • );
  •  
  • }
  •  
  •  
  • // Check to see if column KEY exists.
  • if (StructKeyExists( qQueryObject, "column_name" )){
  •  
  • // Column found!
  • WriteOutput(
  • "Sweeet! #column_name# is available in the query."
  • );
  •  
  • }

You would of course replace qQueryObject with name of your query and column_name with the name of your query. Good luck!



Reader Comments

hi!

Actually, if what you are looking for is to loop over a list of columns and, for example, if a column does not exist, alter the table, you could do it this way:

<cfquery name="opciones_pp_venta" datasource="#request.app.dsn#">select * from opciones_pp_venta where empresa_id like '#elusuario.empresa#'</cfquery>

<cfloop query="opciones_pp_venta">
<cftry>
<cfquery name="#opciones_pp_venta.des_op_1#_hay" datasource="#request.app.dsn#">
select #opciones_pp_venta.des_op_1# from pp_venta_#request.empresa#
</cfquery>
<cfcatch type="database">
<cfswitch expression="#opciones_pp_venta.tipo_opcion#">
<cfcase value="1">
<cfquery datasource="#request.app.dsn#">ALTER TABLE `pp_venta_#request.empresa#` ADD `#opciones_pp_venta.des_op_1#` VARCHAR( 255 ) NOT NULL;</cfquery>
</cfcase>
<cfcase value="5">

</cfcase>
<cfcase value="3,4" delimiters=",">
<cfquery datasource="#request.app.dsn#">ALTER TABLE `pp_venta_#request.empresa#` ADD `#opciones_pp_venta.des_op_1#` TINYINT NOT NULL;</cfquery>
</cfcase>
</cfswitch>
</cfcatch>
</cftry>
</cfloop>

First I loop over a table that has all the posible columns ( a column could be a varchar, tinyint, int, etc...I have a column for that...) then I loop over a cftry cfcatch; if the column does not exist, I alter the table, depending on the attribute needed the process enters a cfswitch where every case has a particular type...with this I could manage for each client to have particular sets of columns, depending on their needs..hope you might find any other applications...

Reply to this Comment

Actually I'm back here because I have the right answer: BOTH , Ben's and Mine's are right just to keep in mind the following: what are you looking for a DB check before the query? Mine's good; don't know and don't care if you have to alter table, or don't want to alter the table, just want to know if the column exists or not: Ben's the script for it! Just if you we're wondering about both methods...

thank you!

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.