Following a brief explanation about the database drivers and the application environment in which the ColdFusion MX Server is installed, this tutorial describes a step-by-step method of creating a ColdFusion data source from the administrative panel of a ColdFusion MX 6.1 Server which derives its data from a MS SQL 2000 server. The tutorial then describes a ColdFusion application for running a database query against this data source.
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:
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
<!--output of query-->
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."