User Guide Cancel

QueryNew

Last updated on May 30, 2024 | Also applies to ColdFusion

ColdFusion

Open app

CFML Reference User Guide
ColdFusion functions
ColdFusion Tags
1. ColdFusion tag summary
2. ColdFusion tags by category
  1. Application framework tags
    1. cfapplication
    2. cfassociate
    3. cferror
    4. cfimport
    5. cfinterface
    6. cflock
    7. cfscript
    8. cfthread
  2. Communications tags
  3. Database manipulation tags
  4. Data output tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcol
    5. cfcontent
    6. cfdocument
    7. cfdocumentitem
    8. cfdocumentsection
    9. cfflush
    10. cfheader
    11. cflog
    12. cfoutput
    13. cfpresentation
    14. cfpresentationslide
    15. cfpresenter
    16. cfprocessingdirective
    17. cfprint
    18. cfreport
    19. cfreportparam
    20. cfsilent
    21. cftable
  5. Debugging tags
    1. cfdump
    2. cftimer
    3. cftrace
  6. Exception handling tags
    1. cfcatch
    2. cferror
    3. cffinally
    4. cfrethrow
    5. cfthrow
    6. cftry
  7. Extensibility tags
    1. cfchart
    2. cfchartdata
    3. cfchartseries
    4. cfcollection
    5. cfcomponent
    6. cfexecute
    7. cfftp
    8. function
    9. cfindex
    10. cfinterface
    11. cfinvoke
    12. cfinvokeargument
    13. cfobject
    14. cfproperty
    15. cfreport
    16. cfreportparam
    17. cfreturn
    18. cfsearch
    19. cfsharepoint
    20. cfspreadsheet
    21. cfwddx
    22. cfxml
  8. File management tags
    1. cfdirectory
    2. cffile
    3. cffileupload
    4. cfftp
    5. cfzip
    6. cfzipparam
  9. Flow-control tags
    1. cfabort
    2. cfbreak
    3. cfcase
    4. cfcontinue
    5. cfdefaultcase
    6. cfelse
    7. cfelseif
    8. cfexecute
    9. cfexit
    10. cfif
    11. cfinclude
    12. cflocation
    13. cfloop
    14. cfrethrow
    15. cfswitch
    16. cfthrow
    17. cftry
  10. Forms tags
    1. cfapplet
    2. cfcalendar
    3. cffileupload
    4. cfform
    5. cfformgroup
    6. cfformitem
    7. cfgrid
    8. cfgridcolumn
    9. cfgridrow
    10. cfgridupdate
    11. cfinput
    12. cfpdf
    13. cfpdfform
    14. cfpdfformparam
    15. cfpdfparam
    16. cfpdfsubform
    17. cfselect
    18. cfslider
    19. cftextarea
    20. cftree
    21. cftreeitem
  11. Internet Protocol tags
    1. cfajaximport
    2. cfajaxproxy
    3. cfftp
    4. cffeed
    5. cfimap
    6. cfhttp
    7. cfhttpparam
    8. cfldap
    9. cfmail
    10. cfmailparam
    11. cfmailpart
    12. cfpop
    13. cfsprydataset
  12. Page processing tags
    1. cfcache
    2. cfcontent
    3. cfflush
    4. cfheader
    5. cfhtmlhead
    6. cfinclude
    7. cfprocessingdirective
    8. cfsetting
    9. cfsilent
  13. Security tags
  14. Variable manipulation tags
    1. cfcookie
    2. cfdump
    3. cfparam
    4. cfregistry
    5. cfsavecontent
    6. cfschedule
    7. cfset
    8. cfsetting
  15. Other tags
    1. cfimage
    2. cflog
    3. cfregistry
3. Tags a-b
4. Tags c
  1. cfcache
  2. cfcalendar
  3. cfcase
  4. cfcatch
  5. cfchart
  6. cfchartdata
  7. cfchartseries
  8. cfclient
  9. cfclientsettings
  10. cfcol
  11. cfcollection
  12. cfcomponent
  13. cfcontent
  14. cfcontinue
  15. cfcookie
  16. Tags d-e
5. Tags f
6. Tags g-h
  1. cfgraph
  2. cfgraphdata
  3. cfgrid
  4. cfgridcolumn
  5. cfgridrow
  6. cfgridupdate
  7. cfheader
  8. cfhtmlhead
  9. cfhtmltopdf
  10. cfhtmltopdfitem
  11. cfhttp
  12. cfhttpparam
7. Tags i
  1. cfif
  2. cfimage
  3. cfimap
  4. cfimapfilter
  5. cfimpersonate
  6. cfimport
  7. cfinclude
  8. cfindex
  9. cfinput
  10. cfinsert
  11. cfinterface
  12. cfinvoke
  13. cfinvokeargument
8. Tags j-l
9. Tags m-o
10. Tags p-q
  1. cfparam
  2. cfpdf
  3. cfpdfform
  4. cfpdfformparam
  5. cfpdfparam
  6. cfpdfsubform
  7. cfpod
  8. cfpop
  9. cfpresentation
  10. cfpresentationslide
  11. cfpresenter
  12. cfprint
  13. cfprocessingdirective
  14. cfprocparam
  15. cfprocresult
  16. cfprogressbar
  17. cfproperty
  18. cfquery
  19. cfqueryparam
11. Tags r-s
  1. cfregistry
  2. cfreport
  3. cfreportparam
  4. cfrethrow
  5. cfreturn
  6. cfsavecontent
  7. cfschedule
  8. cfscript
  9. cfsearch
  10. cfselect
  11. cfservlet
  12. cfservletparam
  13. cfset
  14. cfsetting
  15. cfsharepoint
  16. cfsilent
  17. cfslider
  18. cfspreadsheet
  19. cfsprydataset
  20. cfstoredproc
  21. cfswitch
12. Tags t
  1. cftable
  2. cftextarea
  3. cftextinput
  4. cfthread
  5. cfthrow
  6. cftimer
  7. cftooltip
  8. cftrace
  9. cftransaction
  10. cftree
  11. cftreeitem
  12. cftry
13. Tags u-z
  1. cfupdate
  2. cfwddx
  3. cfwebsocket
  4. cfwindow
  5. cfxml
  6. cfzip
  7. cfzipparam
CFML Reference
1. Reserved words and variables
2. Ajax JavaScript functions
3. ColdFusion ActionScript functions
4. ColdFusion mobile functions
5. Application.cfc reference
6. Script functions implemented as CFCs
7. ColdFusion Flash Form style reference
8. ColdFusion event gateway reference
9. ColdFusion C++ CFX Reference
10. ColdFusion Java CFX reference
11. WDDX JavaScript Objects
Cloud services

Description

Creates an empty query (query object).

Returns

An empty query with a set of named columns, or an empty query.

Function syntax

QueryNew(columnlist [, columntypelist[, rowData]])

History

ColdFusion (2018 release) Update 5: You can insert rows in a query object as array of structs. For more information, see Enhancements in QueryNew function.

ColdFusion MX 7: Added columntypelist parameter.

ColdFusion 10: Added rowData parameter.

Parameters

Parameter	Description
columnlist	Comma-delimited list of column names, or an empty string.
columntypelist	(Optional) Comma-delimited list specifying column data types. ColdFusion generates an error if the 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: Object: Java Object. This is the default column type. 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 Timestamp: Time and date information
rowData	(Optional) Specifies data to add to the new query. Can be one of: A struct with key/value pairs representing a single row of data, where the key is the column name and the value is the data; an array of structs as per above, for adding multiple rows of data; an array of arrays where each inner array are values for a row of data, in the same order as the columns are specified in the columntypelist parameter. See "Usage" for examples.

Parameter

Description

columnlist

Comma-delimited list of column names, or an empty string.

columntypelist

(Optional) Comma-delimited list specifying column data types. ColdFusion generates an error if the 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:

Object: Java Object. This is the default column type.
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
Timestamp: Time and date information

rowData

(Optional) Specifies data to add to the new query. Can be one of:

A struct with key/value pairs representing a single row of data, where the key is the column name and the value is the data;
an array of structs as per above, for adding multiple rows of data;
an array of arrays where each inner array are values for a row of data, in the same order as the columns are specified in the columntypelist parameter.

See "Usage" for examples.

Usage

If you specify an empty string in the columnlist parameter, use the QueryAddColumn function to add columns to the query .Adobe recommends that you use the optional columntypelist parameter. Without this parameter, ColdFusion must try to determine data types when it uses the query object in a query of queries. Determining data types requires additional processing, and can result in errors if ColdFusion does not guess a type correctly .Enhancements in ColdFusion 10 lets you initialize the query data. You can specify a struct, an array of structs, or arrays with single or multiple dimensions to initialize the query as shown in the following example:

myQuery1 = queryNew("id,name","Integer,Varchar", {id=1,name="One"}); 
myQuery2 = queryNew("id,name","Integer,Varchar", 
[ 
{id=1,name="One"}, 
{id=2,name="Two"}, 
{id=3,name="Three"} 
]); 
myQuery3 = queryNew("id,name","Integer,Varchar", [ [1,"One"], [2,"Two"], [3,"Three"] ]);

Example

The following example creates a query object.

<cfscript>
    myQuery = queryNew("id,name,amount","Integer,Varchar,Integer", 
                [ 
                        {id=1,name="One",amount=15}, 
                        {id=2,name="Two",amount=18}, 
                        {id=3,name="Three",amount=32} 
                ]); 
    writeOutput("The new query is:")
    writeDump(myQuery)
</cfscript>

Output

Example

The following example uses the QueryNew function to create an empty query with three columns. It populates two rows of the query and displays the contents of the query object and its metadata.

<!--- Create a new three-column query, specifying the column data types ---> 
<cfset myQuery = QueryNew("Name, Time, Advanced", "VarChar, Time, Bit")> 

<!--- Make two rows in the query ---> 
<cfset QueryAddRow(MyQuery, 2)> 

<!--- Set the values of the cells in the query ---> 
<cfset QuerySetCell(myQuery, "Name", "The Wonderful World of CMFL", 1)> 
<cfset QuerySetCell(myQuery, "Time", "9:15 AM", 1)> 
<cfset QuerySetCell(myQuery, "Advanced", False, 1)> 
<cfset QuerySetCell(myQuery, "Name", "CFCs for Enterprise Applications", 2)> 
<cfset QuerySetCell(myQuery, "Time", "12:15 PM", 2)> 
<cfset QuerySetCell(myQuery, "Advanced", True, 2)> 

<h4>The query object contents</h4> 
<cfoutput query = "myQuery"> 
#Name# #Time# #Advanced#<br> 
</cfoutput><br> 
<br> 
<h4>Using individual query data values</h4> 
<cfoutput> 
#MyQuery.name[2]# is at #MyQuery.Time[2]#<br> 
</cfoutput><br> 
<br> 
<h4>The query metadata</h4> 
<cfset querymetadata=getMetaData(myQuery)> 
<cfdump var="#querymetadata#">

Enhancements in QueryNew function

In ColdFusion (2018 release) Update 5, there is an enhancement in the function QueryNew, related to how a query object is populated.

In previous versions of ColdFusion, while defining the parameters in the QueryNew function, you had to first define the columns of the query object, the data types of the columns, and finally, the row data.

For example,

<cfscript>
 q=queryNew("foo,bar", "", [{foo="foo"},{bar="bar"},{foo="foo1",bar="bar1"}])
 writedump(q)
</cfscript>

In this update, you can insert rows in a query object as array of structs. The query object interprets the struct keys as column names and assumes the appropriate data type.

For example,

<cfscript> 
 myquery=QueryNew([ 
  {"Id":101,"Name":"John Adams","Paid":FALSE}, 
  {"Id":102,"Name":"Samuel Jackson","Paid":TRUE}, 
  {"Id":103,"Name":"Gal Gadot","Paid":TRUE}, 
  {"Id":104,"Name":"Margot Robbie","Paid":FALSE} 
 ]) 
 writedump(myquery) 
</cfscript>

In the snippet above, you can see that the struct keys Id, Name, and Paid are the columns of the query object and the values are the row values of the query object.

You can also create the query object by using named parameters.

For examples,

Use the parameter data to populate the query object with values as array of structs.

<cfscript>
 mydata = queryNew(data=[
     {"id":1,"title":"Hello World"}, 
     {"id":2,"title":"How are you"}
    ]);
 writeDump(mydata);
</cfscript>

Use the parameter columnNames to create the columns for the query object.

<cfscript>
 data = queryNew(columnNames="Year,Salary")
 writeDump(data);
</cfscript>

Get help faster and easier

New user?

QueryNew

Description

Returns

Category

Function syntax

History

See also

Parameters

Usage

Enhancements in QueryNew function

Get help faster and easier

Ask the Community

Contact Us