cfquery

<cfquery 
   name = "query_name"
   dataSource = "ds_name"
   dbtype = "query"
   username = "username"
   password = "password"
   maxRows = "number"
   blockFactor = "blocksize"
   timeout = "seconds"
   cachedAfter = "date" 
   cachedWithin = "timespan" 

   Either of the following:
      debug = "Yes" or "No"
   or:
      debug
>
   SQL statement(s)
</cfquery>

SELECT [count] FROM MYTABLE. 

<!--- This example shows the use of CreateTimeSpan with CFQUERY ------>
<!--- define startrow and maxrows to facilitate 'next N' style browsing ---->
<cfparam name="MaxRows" default="10">
<cfparam name="StartRow" default="1">
<!--------------------------------------------------------------------
Query database for information if cached database information has
not been updated in the last six hours; otherwise, use cached data.
--------------------------------------------------------------------->
<cfquery 
   name="GetParks" datasource="cfsnippets" 
   cachedwithin="#CreateTimeSpan(0, 0, 6, 0)#">
   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="index.cfm?startrow=
            <cfoutput>               #Evaluate(StartRow + MaxRows)#            </cfoutput>
            ">See next <cfoutput>#MaxRows#</cfoutput> rows</a> 
      </cfif>
      </td>
   </tr>
</table>

Comments

jrunrandy said on Nov 25, 2003 at 11:23 AM :

The datasource parameter is required unless dbtype="query".

If dbtype="query" you cannot specify the datasource parameter.

This is doc bug 53785.

Der Nickname said on Dec 2, 2003 at 6:44 AM :

Is it possible to define the current datasource within the cfm dokument?
I mean whithout defining it into the administration on CF. So that I could dynamically create databases in MySQL (per example) and immediately use them.
Or is there a Funktion that can add these new databases into the datasource part of the administration?

I Ask, because I'm about to whrite a tool that needs to handel, create and use new databases without any "post-creation- administration".
I know how it works in PHP, but unfortunatly PHP is not allowed for this project, so I have to do it in CF.

Summary: I don't want to use the CF-Administration to define a datasource, but I want to use datasources. How could I handle it?

jrunrandy said on Dec 2, 2003 at 6:51 AM :

Unfortunately, there is no API for defining a datasource outside of the ColdFusion MX Administrator.

f i g i t a l said on Jan 9, 2004 at 10:10 AM :

Depending on your platform you should be able to access datasource names with the CFRegistry tag. Create a datasource in the CFAdministrator, then check in your registry to find the location and format of the information

MarkZet said on Mar 8, 2004 at 3:28 AM :

String variables that are used within a CFQUERY tag temporarily have doubled single quotes. This causes the following code to break up the string differently:

<CFSET sText = "it's weird">
<CFOUTPUT>#Mid(sText, 1, 4)#,#Mid(sText, 6, 5)#</CFOUTPUT>
<CFQUERY name="qBug" datasource="testDSN">
INSERT INTO testTable VALUES ('#Mid(sText, 1, 4)#','#Mid(sText, 6, 5)#')
</CFQUERY>

Mendolis said on Mar 8, 2004 at 7:35 PM :

When I display the contents of query_name.columnlist, the names are in alphabetical order not field ordinal number. Why? Why? Why?

No screen name said on Mar 16, 2004 at 3:01 PM :

you can use character \ (slash) to unmask the quotes in the text...
for example: if the text was "it\'s weird" then it will work. hope this helps.

String variables that are used within a CFQUERY tag temporarily have doubled single quotes. This causes the following code to break up the string differently:

<CFSET sText = "it's weird">
<CFOUTPUT>#Mid(sText, 1, 4)#,#Mid(sText, 6, 5)#</CFOUTPUT>
<CFQUERY name="qBug" datasource="testDSN">
INSERT INTO testTable VALUES ('#Mid(sText, 1, 4)#','#Mid(sText, 6, 5)#')
</CFQUERY>

Sarge said on Mar 20, 2004 at 10:03 AM :

Suggest adding the following two lines to the cachedAfter description:
Takes effect only if query caching is enabled in the Administrator.
To use cached data, the current query must use the same SQL statement, data source, query name, user name, and password.

Sarge said on Mar 21, 2004 at 5:57 AM :

Suggest adding the following under the ColdFusion MX changes:
New query object variable, cfquery.ExecutionTime

CFMX no longer supports Native Drivers. It use JDBC (and ODBC-JDBC bridge) for database connnectivity.

Sarge said on Mar 21, 2004 at 5:59 AM :

Suggest changing this text:
Because the timeout parameter only the maximum time for each sub-operation of a query, the cumulative time may exceed its value. To set a timeout for a page that might get a very large result set, set the Administrator > Server Settings > Timeout Requests option to an appropriate value.

To:
Because the timeout parameter only affects the maximum time for each sub-operation of a query, the cumulative time may exceed its value. To set a timeout for a page that might get a very large result set, set the Administrator > Server Settings > Timeout Requests option to an appropriate value;
or use the RequestTimeout attribute of the CFSETTING tag: <cfsetting requestTimeout="300">.

Sarge said on Mar 21, 2004 at 7:07 AM :

Suggest removing the following block as it really refers to differences in date/time results from ODBC and Native drivers:
Database query results for date and time values can vary in sequence and formatting, unless you use functions to format the results. To ensure that customers using your ColdFusion application are not confused by the display, Macromedia recommends that you use the DateFormat and TimeFormat functions to format values from queries. For more information and examples, see TechNote 22183, "ColdFusion Server (5 and 4.5.x) with Oracle: Formatting Date and Time Query Results," at www.coldfusion.com/Support/KnowledgeBase/SearchForm.cfm.

Sarge said on Mar 21, 2004 at 7:19 AM :

We are working to improve the examples in the ColdFusion reference pages. We propose to replace the current example on this page with the the following example. If you have any comments on this example, add them to this page.

<!--- This example shows the use of CreateTimeSpan with CFQUERY to cache a record set. ------>
<!--- define startrow and maxrows to facilitate 'next N' style browsing ---->
<cfparam name="MaxRows" default="10">
<cfparam name="StartRow" default="1">
<!--------------------------------------------------------------------
Query database for information if cached database information has
not been updated in the last six hours; otherwise, use cached data.
--------------------------------------------------------------------->
<cfquery
name="GetParks" datasource="cfsnippets"
cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">
SELECT PARKNAME, REGION, STATE
FROM Parks
ORDER BY ParkName, State
</cfquery>
<!---- build HTML table to display query ------------------------->
<table cellpadding="1" cellspacing="1">
<tr>
<td bgcolor="f0f0f0">
&nbsp;
</td>
<td 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>
<cfoutput><a href="#CGI.SCRIPT_NAME#?startrow=#Evaluate(StartRow + MaxRows)#">
See next #MaxRows# rows</a></cfoutput>
</cfif>
</td>
</tr>
</table>

MarkZet said on Mar 24, 2004 at 5:18 AM :

It would be very helpful if there was a property CFQUERY.sql that contains the generated SQL that was sent to the database.

Showfax TJ said on Mar 24, 2004 at 1:14 PM :

MarkZet

I would just use a tool like SQL Profiler. If you are using SQL 7.0 or SQL 2000, then you can use this tool to monitor lots of cool stuff going on in your SQL Server, the least of which is monitor what commands are being sent to the database.

Corey Gilmore said on Apr 23, 2004 at 6:54 AM :

--
Don't you think this page, of all pages, needs to include a note that any strings returned from functions are _NOT_ escaped when the function call is placed inside quotes?!
--
I assume this is by design. It is up to you, the developer, to clean your input before processing it. MM does provide cfqueryparam which does this, the only downside is that it does not allow you to use cachedWithin or cachedAfter.

http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm

Mr S Harris said on May 11, 2004 at 6:46 PM :

Regarding the quoting dilemma, you could also try a more simple approach.

Instead of predetermining the part of the WHERE clause and inserting it in the query like this:
*snip*
WHERE #variables.criteria#
*snip*
Just put CFIF (and whatever else you reqired) statements within the CFQUERY tag. Not as modular and graceful, but easy and intuitive.
*snip*
<CFQUERY ...>
SELECT * FROM THINGS
WHERE SYSDATE BETWEEN START_DATE AND END_DATE
<CFIF IsDefined("session.something")>
AND j.something = '#session.something#'
</CFIF>
</CFQUERY>
*snip*

jrunrandy said on Jul 22, 2004 at 5:04 AM :

In general, ColdFusion is just passing the SELECT statement to the DBMS, so it supports everything your DBMS supports.

In your case, I believe the problem is that you are using * instead of % for your LIKE predicate.

mtayter said on Jul 26, 2004 at 11:25 AM :

at the top it says, Macromedia recommends that you use the cfqueryparam tag within every cfquery tag.

however, if you use cfqueryparam, then you can't use cachedWithin or cachedAfter. so then the line at the top implies that Macromedia recommends not using cachedWithin or cachedAfter.

basically, if we want secure cached queries, we have to write our own version of cfqueryparam, correct? will this be fixed in the future?

jrunrandy said on Jul 27, 2004 at 5:12 AM :

To No nickname:
It was before my time on the ColdFusion team, but I believe the connectstring parameter was removed when moving from native/ODBC data sources to JDBC data sources.

You might try posting to the online forums to see if other customers have developed workarounds: http://webforums.macromedia.com.

No screen name said on Aug 4, 2004 at 11:08 AM :

Someone should mention that there's a bug with cfquery and single quotes in 6.1. CF automatically turns single quotes evaluated from a string expression into two single quotes. To work around this, call PreserveSingleQuotes with the query string as an argument.

Or tell your sysadmin to get the fix: http://www.macromedia.com/support/coldfusion/ts/documents/quotes_hotfix.htm

No screen name said on Aug 11, 2004 at 7:11 AM :

I have a little interesting question about the CFQUERY tag:

Consider the folowing:

<cfset myStr = "Select name as EntryName from myDb where status ='Y' ">

<cfquery name="test" datasource="myDtSrc">
#mystr#
</cfquery>

<table>
<cfoutput query = "test"><tr>#EntryName#</tr></cfoutput>
</table>


Information:

name: an attribute that is a string in the myDb table
status: an attribute that is a char of value in the myDb table


Output:

An error is displayed stating the following:

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Y'.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (8:1) to (8:44).
Date/Time: 08/11/04 08:57:57
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.0.3705; .NET CLR 1.1.4322)
Remote Address: 10.16.10.32


Is there a way to go around the quotes.

Thanks,

Prosper

No screen name said on Aug 11, 2004 at 7:17 AM :

Thanks to Macromedia, my question was answered before

BlueSpline said on Sep 2, 2004 at 1:35 AM :

Three comments:

1) The point made by Mendolis on March 8th about the alphabetical ordering of the columns returned by CFQuery is a pain. I'm trying to write a general purpose routine to view queries stored in an Access DB.

2) Is it possible to use escape characters with the AS clause of a query so that I can create columns aliases _including_ spaces to make them more readable? I've tried single quotes, double quotes and square brackets.

3) Generally, why isn't the CF feedback threaded so that it's easier to identify questions and their answers?

jrunrandy said on Sep 2, 2004 at 3:17 AM :

BlueSpline. I think you're confusing LiveDocs with the online Forums (which are threaded). LiveDocs is intended for documentation comments, is reviewed by Technical Writers only, and does not receive enough traffic to make a threaded discussion meaningful. Questions like Mendolis's and yours would be more quickly answered on the online forums: http://webforums.macromedia.com/coldfusion/

No screen name said on Sep 2, 2004 at 3:49 PM :

An example on how to use the variable Names such as query_name, cfquery with a cftransaction would be greatly appreciated. Currently I am trying to Do UPDATE, SELECT, INSERT transactions and need to verify if transaction successful.

extdw_doc said on Sep 3, 2004 at 10:39 AM :

Please see the cftransaction page in this document at:
http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-c15.htm#wp1104164
Some comments on that page also point you to
addtional information that might be useful.

SMao said on Sep 16, 2004 at 6:39 AM :

Hello,
I use the tag cfquery with the attribute cachedwithin and I have a problem if the result of the query is an error for example a time out error, the next time I try to use that query, CF return the same error, this seems to be happening because CF is storing that error query in the cache, then when it tries to retrieve it, the variable is coming back the error.

Mao

No screen name said on Sep 22, 2004 at 12:04 PM :

I'm trying to dynamically run SQL queries. I've created an input box where the user can enter the SQL string and that is passed to a processing page. I can get the query to run, get the column list into an array, get the number of columns and even get the column names to populate a table as headers. What I can't seem to do is find a way to populate the table with the results for each column. I want to use the entries in the column list array as my calls to the query, but I can't seem to get it to use them that way.

#SQLCol[loopcount]# - returns the name of a field, I can't seem to find a way to use this to return the field contents:
#UserQuery.SQLCol[loopcount]# is what I hoped would work but it doesn't, neither does #UserQuery.#SQLCol[loopcount]##

Any ideas?

BlueSpline said on Oct 12, 2004 at 6:13 AM :

Is it possible to use a Subquery in a Query-of-queries?

Mike

jrunrandy said on Oct 12, 2004 at 9:46 AM :

Sorry. Subqueries aren't supported by Query of Queries.

bensauer said on Oct 28, 2004 at 11:52 AM :

I have a database with a field called Incident # on it. How can I access this. I can't seem to escape the pound character.

jrunrandy said on Oct 29, 2004 at 12:54 PM :

This is explained on http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/elemen26.htm

mindtrap said on Nov 19, 2004 at 7:43 AM :

We've discovered a major bug with the TIMEOUT attribute of the cfquery tag. I don't know if this is a CFMX issue, a driver issue or a JVM issue.

But what we have experience on CFMX Enterprise is that using this attribute extents the time it takes to run a query by about 40-100ms. It also make CFMX real unstable. We've has reveral problem with the JVM/CFMX running out of memory.

We were using this attribute everywhere. We've since removed it and things are very much table now.

jrunrandy said on Nov 19, 2004 at 4:16 PM :

mindtrap,
ColdFusion just passes the timeout number to the JDBC driver - so if you are seeing something unstable, it might be related to the JDBC driver.

As far as I know, there are no reported problems like this with the DataDirect drivers that ship with CF.

One thought is to diagnose and fix the "running out of memory" error.
Once memory is exhausted, lots of other unrelated things can go awry and lead to wild goose chases.

mindtrap said on Dec 1, 2004 at 12:35 PM :

For those interested, my comment above in regards to the timeout issue was experienced with the MSSQL 2000 server.

justin.alpino said on Dec 15, 2004 at 7:43 AM :

During performance tuning for a project I am working on, we discovered that passing a username and password via the <cfquery> tag opens a new connection to the datasource. Keep in mind that the 'new' connection is only opened once during the request, not for each instance of <cfquery>. By removing the username and password attributes we were able to shave off a few tenths of a second.

r.sesser said on Jan 17, 2005 at 7:56 AM :

I'm running a query on another query and the results of two columns are getting converted to a timestamp automatically. This only happens under certain circumstances, like when the string is something like 9-2 or 9-3. How can I prevent the query from converting a string to a timestamp? When I create the original query, can I define the columns as varchar? The original query is being built from a webservice call.

No screen name said on Jan 24, 2005 at 3:41 PM :

Is there a way I can query from 2 datasources? I am splitting my database into two seperate databases for more stability and I dont want to have to write the pages all over again when I need to use tables from both datasources.

ASandstrom said on Feb 4, 2005 at 11:30 AM :

The situation described by Jonathan, above, illustrates why it's a good idea to put your queries in CFCs. Thanks to Jonathan for providing a read-world example that illustrates the value of CFCs.

sanaullah said on Feb 18, 2005 at 2:26 AM :

I think there is a bug in query of query if we try this

select top 20 cms_id,cms_name from application.contents

the error comes up that [ from keyword is missing from select statment]

teletypeturtle said on Feb 18, 2005 at 12:15 PM :

sanaullah -- Query of Query is not sympathetic to reserved words being used anywhere in your statement. For example -- a column name that happens to be a reserved word caused me some headaches in a QoQ. My workaround was to return that column's results under a different name in the source query (SELECT form AS myform FROM thistable).

Try a workaround that removes the word "application" from "application.contents". That may help.

jrunrandy said on Feb 24, 2005 at 6:34 AM :

What version are you moving from? I talked to a developer and he said CF hasn't supported "top" since the base CFMX release, but I can't find any documentation anywhere that says CF ever supported this keyword.

In any case, I'm sorry to say that CFMX 7 does not support "top."

savage10 said on Mar 2, 2005 at 10:15 PM :

Is ther going to be fix to the cfquery aphabetical ordering so that the results may be returned Ordinally?

This current sorting is very hard to work with and around. I almost never have any use for sorting columns aphabeticly, most of the time I need them returned in the same order that I requested them. And the only way around it is to alias every column to match cfquery obsurd automatic sorting.

jrunrandy said on Mar 7, 2005 at 4:57 PM :

My understanding is that the order in which DBMSs return rows is implementation-specific, so there is no guarantee that CF can return them in the order you added them.

backprop said on Mar 9, 2005 at 8:24 AM :

He's asking about sorting COLUMNS, not rows.

Can the columnList please be returned as passed using <cfquery>, and NOT alphabetically?

Mike@Jobscience said on Mar 11, 2005 at 12:12 PM :

I think I've found an inconsistent behaviour with the CFQUERY tag. I'm not sure if it's a problem with CFQUERY or components, but here goes. Consider the following table, component and code:

SQL ->create table temp_object ( textField varchar(50) NULL )

Component:
<cfcomponent name="testObject">
<cfset variables.text = "" />

<cffunction name="init">
<cfreturn this />
</cffunction>

<cffunction name="setText" returntype="void" access="public" output="no">
<cfargument name="text" required="yes" type="string">
<cfset variables.text = arguments.text />
</cffunction>

<cffunction name="getText" returntype="string" access="public" output="no">
<cfreturn variables.text />
</cffunction>
</cfcomponent>

Code:
<cfset testObj = createObject( "component", "test.component_test.testObject" ).init() />

<cfset myText = "Today's my birthday." />

<cfset testObj.setText( myText ) />

<cfquery datasource="#application.settings.dsn#">
insert temp_object ( textField )
values ( '#myText#' )
</cfquery>

<cfquery datasource="#application.settings.dsn#">
insert temp_object ( textField )
values ( '#testObj.getText()#' )
</cfquery>


The second insert doesn't escape the quote correctly. Is this intentional?

jrunrandy said on Mar 18, 2005 at 12:50 PM :

backprop and savage10,
I have entered enhancement request 59954, asking that cfquery maintain column order when the SELECT statement specifies columns.

travmak said on Jul 15, 2005 at 5:56 AM :

I have the following CFQuery line:
<cfquery name = "myQuery" datasource = "#ds#">

I ran into an instance where "ds" had no value so datasource = ""

The error that returned was
name can't be empty

The value for name is static however when I finished trouble shooting I found datasource was the problem.

Is this an issue with the error thrown? I do not have a copy of MX 7 to test on. Is this behavior the same for MX 7?

Running CF Version: 6,1,0,63958

Thank you
-T

ASandstrom said on Jul 15, 2005 at 1:41 PM :

In response to travmak:
I tested the example you cited in ColdFusion MX 7. It generates the error:
Data source could not be found.

Speedy said on Apr 4, 2006 at 9:35 AM :

For those asking how to bring back the column list of a resultset in a non-alphabetical order, here is an easy solution. Enjoy.

<cfoutput>
<cfset colHeaderNames = ArrayToList(results.getColumnList()) />
#colHeaderNames#
</cfoutput>

carehart said on Mar 13, 2007 at 11:01 PM :

Where this document says "Setting this value to 0 disables query caching", that is not true. According to this technote, http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_19406, 0 instead means prevent "unlimited query caching"--a dramatically different result.