Ask Ben: Testing The Existence Of A Query Column In CFScript
Posted October 2, 2006 at 4:36 PM by Ben Nadel
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...
The answer is so easy that I wonder if that's what the original author actually meant, and that perhaps Mr. Mejia is on the right track. However, the original author did ask for a CFSCRIPT solution, which would require running a query in a CFSCRIPT block. This is easy in CF9 with the Query.cfc object, but not so easy in earlier versions:
http://www.bennadel.com/blog/186-Running-ColdFusion-Queries-Inside-CFSript-Tags.htm
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!
@Cesar,
Glad you got what you were looking for.
