Querying SQL 2000 Server from ColdFusion - Querying pubs database with a ColdFusion query
(Page 3 of 4 )
Sample query implemented
We will be implementing the following query, which will be run on the MS SQL Server's Query Analyzer tool, in our ColdFusion application:
The <cfquery/> tag
ColdFusion application's interaction with databases is through the <cfquery/> tags. The generic <cfquery/> tag is as follows from Macromedia's documentation:
<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>
In this tag, except for the name parameter, everything else is optional, assuming that the data source has been configured with the correct authentication information.
For starters we will be looking at the simplest query, whose sample run in the query Analyzer was shown above. Using the <cfquery/> tags, the above query can be implemented in ColdFusion as shown in the code that follows. The query has a name, "GetAuthors" and the rows (result set) returned are accessed by GetAuthors.CurrentRow. Of course, the query is run against the source created earlier, CFSQL.
<cfquery name="GetAuthors" datasource="CFSQL">
select au_lname, au_fname, phone, city, zip from authors
order by au_lname
</cfquery>
<table>
<tr>
<!--Column headers-->
<td>Last Name</td>
<td>First Name</td>
<td>Telephone</td>
<td>City</td>
<td>Zip Code</td>
</tr>
<!--output of query-->
<cfoutput query="GetAuthors">
<tr>
<td>#GetAuthors.CurrentRow#</td>
<td>#au_lname#</td>
<td>#au_fname#</td>
<td>#phone#</td>
<td>#city#</td>
<td>#zip#</td>
</tr>
</cfoutput>
When this code is displayed in the web browser, we display the following. You may notice that for the six columns returned, there are only five columns of headers. It is an error that can be corrected by adding another header column, such as "Row number."
Next: Modifying the query and improving the display >>
More ColdFusion Articles
More By Jayaram Krishnaswamy
