QueryAddColumn

Description

Adds a column to a query and populates its rows with the contents of a one-dimensional array. Pads query columns, if necessary, to ensure that all columns have the same number of rows.

Returns

The number of the column that was added.

Category

Query functions

Function syntax

QueryAddColumn(query, column-name [, datatype], array-name)

See also

QueryNew, QueryAddRow, QuerySetCell; Managing data types for columns in the Developing ColdFusion Applications

History

ColdFusion MX 7: Added the datatype parameter.

ColdFusion MX: Changed behavior: if a user attempts to add a column whose name is invalid, ColdFusion throws an error. (In earlier releases, ColdFusion permitted the add operation, but the user could not reference the column after adding it.)

Parameters

Parameter

Description

query

Name of a query object.

column-name

Name of the new column.

datatype

(Optional) Column data type. ColdFusion generates an error if data you add to the column is not of this type, or if it cannot convert the data to this type. The following data types are valid:

  • Integer: 32-bit integer

  • BigInt: 64-bit integer

  • Double: 64-bit decimal number

  • Decimal: Variable length decimal, as specified by java.math.BigDecimal

  • VarChar: String

  • Binary: Byte array

  • Bit: Boolean (1=True, 0=False)

  • Time: Time

  • Date: Date (can include time information)

array-name

Name of an array whose elements populate the new column.

Usage

You can add columns to query objects, such as queries retrieved with the cfquery tag or queries created with the QueryNew function. You cannot use the QueryAddColumn function on a cached query. This function is useful for generating a query object from the arrays of output parameters that Oracle stored procedures can generate.

Adobe recommends that you use the optional datatype parameter. Without this parameter, ColdFusion must try to determine the column’s data type when it uses the query object in a query of queries. Determining the data type requires additional processing, and can result in errors if ColdFusion does not guess the type correctly.

Example

The following example creates a query object, uses the QueryAddColumn function to add three columns to the object, and displays the results. Because two of the arrays that provide the data are shorter than the third, QueryAddColumn adds padding to the corresponding columns in the query.

<!--- Make a query. ---> 
<cfset myQuery = QueryNew("")> 
 
<!--- Create an array. ---> 
<cfset FastFoodArray = ArrayNew(1)> 
<cfset FastFoodArray[1] = "French Fries"> 
<cfset FastFoodArray[2] = "Hot Dogs"> 
<cfset FastFoodArray[3] = "Fried Clams"> 
<cfset FastFoodArray[4] = "Thick Shakes"> 
<!--- Use the array to add a column to the query. ---> 
<cfset nColumnNumber = QueryAddColumn(myQuery, "FastFood", "VarChar", 
            FastFoodArray)> 
 
<!--- Create a second array. ---> 
<cfset FineCuisineArray = ArrayNew(1)> 
<cfset FineCuisineArray[1] = "Lobster"> 
<cfset FineCuisineArray[2] = "Flambe"> 
<!--- Use the array to add a second column to the query. ---> 
<cfset nColumnNumber2 = QueryAddColumn(myQuery, "FineCuisine", "VarChar", 
             FineCuisineArray)> 
 
<!--- Create a third array. ---> 
<cfset HealthFoodArray = ArrayNew(1)> 
<cfset HealthFoodArray[1] = "Bean Curd"> 
<cfset HealthFoodArray[2] = "Yogurt"> 
<cfset HealthFoodArray[3] = "Tofu"> 
<!--- Use the array to add a third column to the query. ---> 
<cfset nColumnNumber3 = QueryAddColumn(myQuery, "HealthFood", "VarChar", 
             HealthFoodArray)> 
 
<!--- Display the results. ---> 
<table cellspacing = "2" cellpadding = "2" border = "0"> 
<tr> 
<th align = "left">Fast Food</th> 
<th align = "left">Fine Cuisine</th> 
<th align = "left">Health Food</th> 
</tr> 
<cfoutput query = "myQuery"> 
<tr> 
<td>#FastFood#</td> 
<td>#FineCuisine#</td> 
<td>#HealthFood#</td> 
</tr> 
</cfoutput> 
</table>