<cfquery name = "query_name" dataSource = "ds_name" dbType = "type" dbServer = "dbms" dbName = "database name" connectString = "connection string" username = "username" password = "password" maxRows = "number" blockFactor = "blocksize" timeout = "milliseconds" cachedAfter = "date" cachedWithin = "timespan" provider = "COMProvider" providerDSN = "datasource" debug> SQL statements </cfquery>
Passes SQL statements to a data source. Not limited to queries.
cfinsert,
cfprocparam,
cfprocresult,
cfqueryparam,
cfstoredproc,
cftransaction,
cfupdate
In addition to returning data from a ColdFusion data source, the cfquery
tag also returns information about the query. cfquery.ExecutionTime
returns the time it took the query to execute in milliseconds.
The cfquery
tag creates a query object, providing information in query variables as described in the following table.
You can cache query results and execute stored procedures. For information about caching cfquery
results, executing stored procedures, and displaying cfquery
output, see Developing ColdFusion Applications.
<!--- This example shows the use of cfquery ---> <html> <head> <title>cfquery Example</title> </head> <body> <H3>cfquery Example</H3> <!--- define startrow and maxrows to facilitate 'next N' style browsing ---> <cfparam name = "MaxRows" default = "10"> <cfparam name = "StartRow" default = "1"> <!--- query database for information ---> <cfquery name = "GetParks" dataSource = "cfsnippets"> SELECT PARKNAME, REGION, STATE FROM Parks ORDER by ParkName, State </cfquery> <!--- build HTML table to display query ---> <table cellpadding = 1 cellspacing = 1> <TR> <TD colspan = 2 bgColor = f0f0f0> <B><I>Park Name</I></B> </TD> <TD bgColor = f0f0f0> <B><I>Region</I></B> </TD> <TD bgColor = f0f0f0> <B><I>State</I></B> </TD> </TR> <!--- Output the query and define the startrow and maxrows parameters. Use the query variable CurrentCount to keep track of the row you are displaying. ---> <cfoutput query = "GetParks" StartRow = "#StartRow#" maxRows = "#MaxRows#"> <TR> <TD valign = top bgColor = ffffed> <B>#GetParks.currentRow#</B> </TD> <TD valign = top> <font size = "-1">#ParkName#</font> </TD> <TD valign = top> <font size = "-1">#Region#</font> </TD> <TD valign = top> <font size = "-1">#State#</font> </TD> </TR> </cfoutput> <!--- If the total number of records is less than or equal to the total number of rows, then offer a link to the same page, with the StartRow value incremented by MaxRows (in the case of this example, incremented by 10) ---> <TR> <TD colspan = 4> <cfif (StartRow + MaxRows) LTE GetParks.recordCount> <a href = "cfquery.cfm?startrow = <cfoutput>#Evaluate(StartRow + MaxRows)#</cfoutput>">See next <cfoutput>#MaxRows#</cfoutput> rows</A> </cfif> </TD> </TR> </table> </body> </html>