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
- 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">
</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>
|
