Introduction
ColdFusion offers developers powerful and flexible tools to work with data. One of these is the ability to create and manipulate queries directly in the code without a database, which can be helpful in various scenarios such as testing or building dynamic data sets for an application. The QueryNew function allows developers to create an empty query (or query object) and then programmatically add rows and columns to it. This is particularly useful for scenarios where data needs to be handled on the server-side without relying on database operations.
In this tutorial, we will explore how to use the QueryNew function to create an empty query, populate it with data, and view the result. This can serve as the foundation for dynamic data manipulation within your ColdFusion application.
Creating an Empty Query Object
The first step in the code is the creation of an empty query object using ColdFusion’s QueryNew function. The QueryNew function allows you to define the structure of the query, specifying the columns and their corresponding data types. In this example, the query object qEmployee is created with three columns: EmployeeID, FirstName, and LastName. The data types for these columns are defined as Integer for EmployeeID and VarChar for both FirstName and LastName.
This step establishes the framework for a query object, similar to creating a new table in a database where you specify the fields and data types, but here it’s handled entirely within ColdFusion.
Adding a Row to the Query
After creating the empty query, the next task is to add a row to it. This is accomplished using the QueryAddRow function. In this example, a single row is added to the query. The QueryAddRow function accepts the query object as its first parameter and the number of rows to add as the second parameter. In this case, we are adding just one row to qEmployee.
At this point, the query object has a structure and an empty row but no data in the cells. This is where the QuerySetCell function comes into play.
Setting Values in the Query Cells
Once a row is added, the next step is to populate it with actual data. ColdFusion’s QuerySetCell function allows you to set the values for individual cells within the query. The function takes four parameters: the query object, the column name, the value to insert, and the row number where the data should be placed.
In the provided example, the QuerySetCell function is used three times—once for each column in the qEmployee query. The first call sets the EmployeeID to 1, the second call sets the FirstName to "Jenny", and the third call sets the LastName to "Jones". The data is inserted into the first row, as specified by the row number parameter.
Displaying the Query
After creating the query and populating it with data, the last step is to display the contents of the query. This is done using the <cfdump> tag, which is a debugging tool in ColdFusion that displays the structure and contents of a variable in a human-readable format. In this example, the query qEmployee is passed to the <cfdump> tag, allowing you to see the columns and their corresponding values.
The output shows the query with the three columns (EmployeeID, FirstName, and LastName) and their respective data for the row that was added.
Conclusion
The QueryNew function is a versatile tool in ColdFusion for creating query objects on the fly without needing a database connection. Combined with functions like QueryAddRow and QuerySetCell, it allows for dynamic creation and manipulation of data, making it a powerful feature for scenarios where database access is either unavailable or unnecessary. By utilizing these functions, developers can easily work with data in-memory, providing a flexible and efficient way to handle data within ColdFusion applications.
This tutorial demonstrated how to create an empty query, add a row, populate the query with data, and display the result. Understanding how to manipulate query objects in ColdFusion is essential for building dynamic and robust applications.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>QueryNew function example: how to create an empty query (query object)</title>
</head>
<body>
<h2 style="color:HotPink">QueryNew Function Example</h2>
<cfset qEmployee=QueryNew("EmployeeID, FirstName, LastName","Integer, VarChar, VarChar")>
<cfset Temp=QueryAddRow(qEmployee,1)>
<cfset Temp=QuerySetCell(qEmployee,"EmployeeID",1,1)>
<cfset Temp=QuerySetCell(qEmployee,"FirstName","Jenny",1)>
<cfset Temp=QuerySetCell(qEmployee,"LastName","Jones",1)>
<cfdump var="#qEmployee#" label="empty query">
</body>
</html>
- QueryAddColumn() - add a column to a query with its value
- QueryAddRow() - add a specified number of empty rows to a query
- QuerySetCell() - set a cell to a value in a query object
- QuotedValueList() - get a delimited List from an executed query
- cfquery - select data
- cfdump - dump query
- cfquery - delete data
- cfquery - insert data
- cfquery - result attribute