query

Search
ColdFusion CFML Reference User Guide
Applies to: ColdFusion

在中国购买的 Creative Cloud 产品不含 CC 桌面应用程序中的 Creative Cloud 移动应用程序和在线服务及相关功能。

Description

Used to execute a query passing SQL statements to a data source using CFScript.

Syntax

Mode

Syntax

Creating the service

new query() or createObject("component", "query")

Initializing the attributes

Any one of the following:

  • queryService=new query(attribute-value_pair)
  • queryService.setAttributes(attribute-value_pair)
  • queryService.set_AttributeName_(attribute_value)
  • queryService.execute(attribute-value_pair)

Executing the service action

queryService.execute(attribute-value_pair)

Properties

name

blockfactor

cachedafter

cachedwithin

dataSource

dbtype

debug

maxRows

password

result

timeout

username

sql

 

 

 

All attributes supported by the tag cfquery can be used as attribute-value pairs. For example,

<cfquery Name="myName"> </cfquery>

can be used as

queryService.setName("myName");

See also

cfquery, Function summary

History

ColdFusion 9: Added this function.

Methods

  • addParam

    Description

    Used in CFScript to add cfqueryparam tags to:

     

  • Verify the data type of a query parameter

  • For DBMSs that support bind variables, to enable ColdFusion to use bind variables in the SQL statement

    Syntax

    serviceName.addParam(attribute-value pair)

    Returns

    Nothing

    Arguments

    All attributes supported by cfqueryparam tag can be used as attribute-value pairs.

     

  • execute

    Description

    Used to execute SQL statements.

    Returns

    A component with the following properties set:

     

  • Result: For SQL queries that return a result set, for example, a "SELECT" SQL query.

  • Prefix: Equivalent to the result attribute for the cfquerytag.

    Syntax

    queryService.execute(attribute-value pair)

    Arguments

    All attributes supported by the cfquery tag.

     

  • setAttributes

    Description

    Sets attributes for the query function.

    Returns

    Nothing

    Syntax

    queryService.setAttributes (attribute-value pair)

    Arguments

    All attributes supported by the cfquery tag.

     

  • getAttributes

    Description

    Gets attributes that were set for the query function.

    Returns

    Returns a struct with all or some of the attribute values.

    Syntax

    queryService.get_Attributes_ (attributelist)

    Arguments

    A comma-separated list of attributes. If no list is specified, all defined attributes are returned.

     

  • clearAttributes

    Description

    Removes all attributes added for the query function.

    Returns

    Nothing

    Syntax

    queryService.clearAttributes(attribute_list)

    Arguments

    A comma-separated list of attributes.

     

  • clearParams

    Description

    Removes queryparams that were added using the addParam method.

    Returns

    Nothing

    Syntax

    queryService.clearParams()

    Arguments

    None

     

  • clear

    Description

    Removes all attributes and queryparms that were added using the addParam method.

    Returns

    Nothing

    Syntax

    queryService.clear()

    Arguments

    None

     

Usage

This function corresponds to the cfquery tag. For usage information, see Usage details for cfquery.

Example

<cfscript>
/*
This example shows how to create a query service in cfscript, set/get attributes using implict setters/getters, and also
how to execute the query and access the resultset
*/
param MaxRows="10";
param StartRow="1";
/*
Query database for information if cached database information has
not been updated in the last six hours; otherwise, use cached data.
*/
/* create a quey service */
queryService = new query();
/* set properties using implict setters */
queryService.setDatasource("cfdocexamples");
queryService.setName("GetParks");
queryService.setcachedwithin(CreateTimeSpan(0, 6, 0, 0));
/* Add sql queryparams using named and positional notation */
queryService.addParam(name="state",value="MD",cfsqltype="cf_sql_varchar");
queryService.addParam(value="National Capital Region",cfsqltype="cf_sql_varchar");
/* invoke execute() on the query object to execute the query and return a component with properties result and prefix (which can be accessed as implcit getters) */
result = queryService.execute(sql="SELECT PARKNAME, REGION, STATE FROM Parks WHERE STATE = :state and REGION = ? ORDER BY ParkName, State ");
GetParks = result.getResult();
/* getPrefix() returns information like recordcount,sql etc (typically whatever one gets if one uses the result attribute of the cfquery tag */
metaInfo = result.getPrefix();
</cfscript>
<cfoutput>
<h4>Found #metaInfo.recordcount# records for '#metainfo.sqlparameters[2]#' in the state '#metainfo.sqlparameters[1]#' </h4>
</cfoutput>
<!--- 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>

 

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy