<CFQUERY> Tag - ColdFusion


>> Return to ColdFusion Tag Index

The <CFQUERY> tag allows you perform database operations (Queries/Inserts/Updates) on various databases with which you have set up DSN (Data Source Names) in the ColdFusion control panel.

Syntax:
 <CFQUERY
       NAME="queryName"
       DATASOURCE="dataSourceName"
       [DBTYPE="query"]
       [USERNAME="userName"]
       [PASSWORD="password"]
       [MAXROWS="max_row_num"]
       [BLOCKFACTOR = "block_size"]
       [TIMEOUT = "seconds"]
       [CACHEDAFTER = ""]
       [CACHEDWITHIN = ""]
Either of the following:
       [DEBUG = "Yes" or "No"]
or
       [DEBUG]

Attributes:

ATTRIBUTE DESCRIPTION COMMENTS
NAME The Name of the Query. Required.
DATASOURCE The Name of the ColdFusion Datasource being used. Required.
DBTYPE The default value is "query". This value specifies the results of a query as input. Optional.
USERNAME Overrides the username in data source setup. Optional.
PASSWORD Overrides the password in data source setup. Optional.
MAXROWS Maximum number of rows to return in the record set. Optional. (-1 = All Records)
BLOCKFACTOR Maximum rows to get at a time from server. Range: 1 - 100. Some database systems do not support this. Optional. (Default value = 1)

Examples:


  <CFQUERY> Comments from Macromedia LiveDocsLiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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>

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
In response to travmak:
I tested the example you cited in ColdFusion MX 7. It generates the error:
Data source could not be found.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
backprop and savage10,
I have entered enhancement request 59954, asking that cfquery maintain column order when the SELECT statement specifies columns.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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?

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
He's asking about sorting COLUMNS, not rows.

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

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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."

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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]

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
For those interested, my comment above in regards to the timeout issue was experienced with the MSSQL 2000 server.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
This is explained on http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/elemen26.htm

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
Sorry. Subqueries aren't supported by Query of Queries.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
Is it possible to use a Subquery in a Query-of-queries?

Mike

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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?

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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.

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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/

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
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?

LiveDocs Comments - coldfusion - 6.1 - htmldocs - tags-b19.htm
Thanks to Macromedia, my question was answered before



[Top]


Recommended Books

[Top]

Web Sites

[Top]

Copyright © 2003- 2008, Orville Paul Chomer, All Rights Reserved