Home arrow ColdFusion arrow Page 3 - Querying SQL 2000 Server from ColdFusion
COLDFUSION

Querying SQL 2000 Server from ColdFusion


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.

Author Info:
By: Jayaram Krishnaswamy
Rating: 5 stars5 stars5 stars5 stars5 stars / 4
February 07, 2006
TABLE OF CONTENTS:
  1. · Querying SQL 2000 Server from ColdFusion
  2. · Using ColdFusion data sources to connect to SQL Server 2000
  3. · Querying pubs database with a ColdFusion query
  4. · Modifying the query and improving the display

print this article
SEARCH DEVARTICLES

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

 


blog comments powered by Disqus
COLDFUSION ARTICLES

- Adobe ColdFusion Just Got More RAD
- How to Access a SQL Anywhere Database with C...
- CFXML: Probing XMLDOM in ColdFusion
- Creating a Web Service with ColdFusion: the ...
- CFAjax: What it is and How to Use it
- Querying SQL 2000 Server from ColdFusion
- Introduction to ColdFusion Markup Language, ...
- Introduction to ColdFusion Markup Language
- Databases and Dreamweaver MX 2004, concluded
- Databases and Dreamweaver MX 2004
- Welcome to Coldfusion MX 6.1, concluded
- Welcome to Coldfusion MX 6.1
- What You Must Know About ColdFusion Flow-Con...
- What You Must Know About Operators in ColdFu...
- Everything You Must Know About ColdFusion Va...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials