query

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)

name

blockfactor

cachedafter

cachedwithin

dataSource

dbtype

debug

maxRows

password

result

timeout

username

sql

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

queryService.setName("myName");

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