Initializing A New Query With Data in ColdFusion 10

It's time to take a look at another of the awesome language enhancements in ColdFusion 10.

In CF10, you can populate new user-defined query with data at the point of instantiation. This means that you don't have to perform a series of loops to add a row and set data into each cell within that row. Thanks to this language enhancement you can send your data into the new query from the start and have it populated for you.

** edit **

Thanks to Sam Farmer for reminding me that the enhanced queryNew() method is also available in the ColdFusion 9.0.2 update. I'm silly...

** end **

This is one of my personal favourites as it is such a timesaver and can really streamline your code and workflow if put to use.

In Action

Let's create a new query and populate it with data from the start. In the code below, we are defining the names of the columns and the data type for those columns. Finally, we'll send in an array of structures as the data for the query:

<cfscript>
	qryPeople = queryNew(
					'firstname, lastname, email',
					'varChar, varChar, varChar',
					[
						{
							firstname 	: 	'Matt',
							lastname	:	'Gifford',
							email		:	'[email protected]'
						},
						{
							firstname 	: 	'Dave',
							lastname	:	'Ferguson',
							email		:	'[email protected]'
						},
						{
							firstname 	: 	'Scott',
							lastname	:	'Stroz',
							email		:	'[email protected]'
						}
					]
				);
</cfscript>

<cfscript>
	qryPeople = queryNew(
					'firstname, lastname, email',
					'varChar, varChar, varChar',
					[
						{
							firstname 	: 	'Matt',
							lastname	:	'Gifford',
							email		:	'[email protected]'
						},
						{
							firstname 	: 	'Dave',
							lastname	:	'Ferguson',
							email		:	'[email protected]'
						},
						{
							firstname 	: 	'Scott',
							lastname	:	'Stroz',
							email		:	'[email protected]'
						}
					]
				);
</cfscript>

Running the above code in our browser, we would receive the following query object, populated with our data:

What's great about this addition to the language is how much time it saves compared to creating the query, setting the columns, setting the number of rows, adding rows and setting values of cells within each of those rows... by sending the data through to the function from the start, your recordset is ready instantly. Of course, there will be times when you may need to use the 'old skool' method of creating custom queries on the fly, and there is nothing wrong with that either! You have options, which is always a good thing.

Acceptable Data Formats

When passing the data in to the new query, you have three formats of data organisation to choose from. In the first example (above) we passed in an array of structured information:

We can also pass in a single structure to set the values of one row within the database:

<cfscript>
	qryPeople = queryNew(
					'firstname, lastname, email',
					'varChar, varChar, varChar',
					{
							firstname 	: 	'Dave',
							lastname	:	'Ferguson',
							email		:	'[email protected]'
					}
				);
</cfscript>

Or we can pass in an array of arrays:

<cfscript>
	qryPeople = queryNew(
					'firstname, lastname, email',
					'varChar, varChar, varChar',
					[
						[ 'Matt','Gifford','[email protected]' ],
						[ 'Dave', 'Ferguson', '[email protected]' ],
						[ 'Scott', 'Stroz', '[email protected]' ]
					]
				);
</cfscript>

The array you send in could be single or multi-dimensional.

Order Is Everything

It's important to remember that if sending data by an array, the values in the array will be mapped to the query columns in the order they are sent.

The structures being sent in the first two examples had a name / value pair for their contents (it's what structures do) and so ColdFusion can easily map the value to the specific column by matching the name. I could send the data through in any order as below, but the values will still map to the correct columns by reference:

<cfscript>
	qryPeople = queryNew(
					'firstname, lastname, email',
					'varChar, varChar, varChar',
					[
						{
							firstname 	: 	'Matt',
							email		:	'[email protected]',
							lastname	:	'Gifford'
						},
						{
							lastname	:	'Ferguson',
							firstname 	: 	'Dave',
							email		:	'[email protected]'
						},
						{
							firstname 	: 	'Scott',
							lastname	:	'Stroz',
							email		:	'[email protected]'
						}
					]
				);
</cfscript>

When sending the data as an array, the values will be inserted into the query in the order they are defined in the array. In this example, I've intentionally mixed up the order of the values within each array:

<cfscript>
	qryPeople = queryNew(
					'firstname, lastname, email',
					'varChar, varChar, varChar',
					[
						[ 'Gifford','Matt','[email protected]' ],
						[ '[email protected]', 'Ferguson', 'Dave' ],
						[ 'Scott', 'Stroz', '[email protected]' ]
					]
				);
</cfscript>

The resulting query is stil populated, but as we expected it's not in the right order:

Although I'm sure that people will not have this issue, it's something to remember in case you start seeing strange values in incorrect columns.

ColdFusion 10, continually making developer's lives easier with simple language enhancements that have a lot of power!