BackFirst PageNext

Database Integration

Database integration is an integral part of any Web based application. And so to demonstrate just how easy it is to write ColdFusion code, let's take a look at a simple example of database integration -- dynamically generating data driven page content.

Take a look at the code snippet below. The CFQUERY tag is the ColdFusion SQL integration tag. Here it is being used to send an SQL SELECT statement to a database, but it can be used to execute any SQL statement. The DATASOURCE attribute specifies the ODBC datasource to retrieve the data from and the NAME attribute specifies the name to be used to refer to the query result set.

<CFQUERY DATASOURCE="catalog" NAME="prods">
    SELECT product_id, product_description
    FROM products  ORDER BY product_description
</CFQUERY>

As soon as ColdFusion reaches the end of the SQL query (the closing CFQUERY tag) it executes the SQL statement, and stores the results in an array named by whatever is specified in the NAME attribute. This example retrieves two fields from a product's table. To display the retrieved data you'd use the CFOUTPUT tag. CFOUTPUT lets you mark a block of code to be processed for each row retrieved by the CFQUERY tag. The code snippet below creates an HTML unordered list (the UL tag), but instead of hard coding the list items, a CFOUTPUT tag is used to dynamically generate the needed LI tags.

<H1>Product List</H1>
<UL>

<CFOUTPUT QUERY= "prods">
<LI>#product_description# (#product_id#)</LI><BR>
</CFOUTPUT>

</UL>

Assuming our database table had just three records, this is what the generated HTML would look like:

Product List

And that's all there is to it.

Of course, you could create far more complex and sophisticated SQL statements if required. ColdFusion will even let you dynamically construct SQL statements using variables, IF statements, loops, and other forms of conditional processing. ColdFusion fully supports any and all SQL syntax, including joins, unions, sub-queries, aliases, aggregate functions, and stored procedures. And ColdFusion supports almost every database out there.


BackFirst PageNext

allaire

AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.