Managing data types for columns

A Query of Queries requires that every column have metadata that defines the column's data type. All queries that ColdFusion MX creates have metadata. However, a query created with QueryNew function that omits the second parameter does not contain metadata. You use this optional second parameter to define the data type of each column in the query.

To specify column data types in the QueryNew function:

Type a QueryNew function, specifying the column names in the first parameter and the data types in the second parameter, as the following example shows:
```
<cfset qInstruments = queryNew("name, instrument, years_playing", 
"CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_INTEGER")>
```

Tip: To see the metadata for a Query of Queries, use the GetMetaData function.

To specify the column data types in the QueryAddColumn function:

Create a query by specifying the QueryNew function with no parameters.

<!--- Make a query. --->
<cfset myQuery = QueryNew("")>

Add and populate a column with the QueryAddColumn function, specifying the data type in the third parameter:

<!--- 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", "CF_SQL_VARCHAR",
	FastFoodArray)>

If you do not specify the data type, ColdFusion MX examines the first fifty rows of each column to determine the data type when performing conditional expressions.

In some cases, ColdFusion MX can guess a data type that is inappropriate for your application. In particular, if you use columns in a WHERE clause or other conditional expression, the data types must be compatible. If they are not compatible, you must use the CAST function to recast one of the columns to a compatible data type. For more information on casting, see Using the CAST function. For more information on data type compatibility, see Understanding Query of Queries processing.

Note: Specifying the data type in the QueryNew function helps you avoid compatibility issues.

View comments in LiveDocs