Home arrow ColdFusion arrow Page 4 - Databases and Dreamweaver MX 2004
COLDFUSION

Databases and Dreamweaver MX 2004


This article, the first of two parts, looks at the different database applications available for use with ColdFusion. It is excerpted from chapter two of the book ColdFusion Web Development with Dreamweaver MX 2004, written by Jen and Peter de Haan et al. (Apress, 2004; ISBN: 1590592379).

Author Info:
By: Apress Publishing
Rating: 4 stars4 stars4 stars4 stars4 stars / 11
January 05, 2006
TABLE OF CONTENTS:
  1. · Databases and Dreamweaver MX 2004
  2. · Different Kinds of Databases
  3. · Using an Access Database
  4. · Getting Started with SQL Queries

print this article
SEARCH DEVARTICLES

Databases and Dreamweaver MX 2004 - Getting Started with SQL Queries
(Page 4 of 4 )

Typically, incorporating a database with ColdFusion requires the following:

  • A database
  • A data source
  • SQL queries

SQL queries allow you to select, insert, update, and delete data from a database. A data source is a name that you assign to a database in the ColdFusion Administrator. It is probably easiest to think of a data source as being an alias pointing to the database file. You will use this alias when you create Recordsets in Dreamweaver or write your own queries using CFML. To learn how to set up a data source, refer to the “Setting Up a Data Source” section later in this chapter.

You will use SQL to talk to the database. The four most common SQL statements areSELECT,INSERT,UPDATE, andDELETE. Each type of database application has its own “extensions” to SQL, but these are the four basic SQL commands that are commonly supported. Let’s look at these four actions in more detail.

TIP   It is usually good practice to terminate each SQL statement with a semicolon (;). Although not required, it makes SQL statements a bit easier to read, and you are able to separate multiple SQL statements within a single <cfquery> block. You will also see in the SQL examples in this chapter that SQL keywords are commonly written in uppercase. Although the SQL language isn’t case-sensitive, it makes the code a little more readable.

SELECT

SELECTqueries retrieve data from your database, and this type makes up the majority of queries you will encounter in CFML development. You can return data from a single table, multiple tables, all columns in a table, or only rows in a table that match a certain criteria.

The generic syntax forSELECTstatements is:

SELECT [list of columns]
FROM [table name]
WHERE [column] = [value]
ORDER BY [column] [order];

TheWHEREandORDER BYclauses in theSELECT statement are optional, and it is worth mentioning that you can have as manyWHERE clauses as you need, as long as each one is separated byAND orORkeywords (or some other Boolean operator). You will look at a series ofSELECTclauses later in this chapter.

The simplest form of theSELECTstatement would be:

SELECT [list of columns]
FROM [table name];

This will return all the data in the listed columns from the specified table.

The following is a sampleSELECTquery that retrieves a single record from the Employee table:

SELECT Emp_ID, FirstName, LastName, Dept_ID FROM Employee
WHERE Emp_ID = 24;

To view all of the data in a table, use “*” as the column name.

INSERT

INSERTqueries are responsible for adding new records into a table. For example, you could use them to allow new members to register on the web site. You could also post new news articles to a web site. Typically, anINSERTquery will add only a single record into a database, but in Chapter 6 you will learn how to use a singleINSERTquery to add multiple records into a table.

The generic syntax forINSERTstatements is:

INSERT INTO [table name] ( [list of columns])
VALUES ( [corresponding list of values] );

A single record is inserted into the table, and the columns are set to the corresponding values. You can see an example of this here:

INSERT INTO Employee( FirstName, LastName, Dept_ID, StartDate, Salary, Contract ) VALUES ( 'test', 'employee', 2, '2-28-2003', 1000, 'No' );

You can see in the preceding code that you must enclose all alphanumeric strings in single quotes; numeric values do not need quotes.

UPDATE

UPDATEqueries are similar toINSERTqueries, except that they update existing records in a table.UPDATE queries can be very dangerous unless you exercise caution, because by default they update all records in a table unless you explicitly specify which records you wish to update (by using aWHEREclause).

The generic syntax forUPDATE statements is:

UPDATE [table name]
SET [column] = [value]
WHERE [column] = [value];

Records in the table are updated to the values specified in theSETlineWHERE the column has a particular value. You can use this behavior a couple of ways. You can update a single record where the table’s primary key has a particular value, like so:

UPDATE Employee
SET Dept_ID = 3
WHERE Emp_ID = 21;

You can also update all records within a table where a column has a particular value. For example, say you wanted to move all employees from one department to another, as follows:

UPDATE Employee
SET Dept_ID = 4
WHERE Dept_ID = 2;

TheUPDATE statement can update multiple columns at the same time as well. Using the following code, you can modify both the salary and department of an employee in a singleUPDATEstatement:

UPDATE Employee
SET Dept_ID = 4,
       
Salary = 100000
WHERE Emp_ID = 20;

If you accidentally forget theWHERE clause in anUPDATEstatement, SQL assumes that you want to update every record in the table, which usually produces undesirable results. For this reason, always keep backups of your current data; it is all too easy to make a simple mistake in your SQL statement and lose a large amount of data.

DELETE

DELETEqueries remove records from a table.DELETEwill remove all records from a table unless you explicitly specify which records to delete, so be very careful when writing these queries.

The generic syntax forDELETEstatements is:

DELETE FROM [table name]
WHERE [column] = [value];

Every row is deleted from the specified tableWHEREa column contains a certain value. There are a few different ways to use this tag. You could delete every record from the Employee table where the Emp_ID equals 6 (where 6 is the value of the primary key), causing one row to be deleted from the database:

DELETE FROM Employee
WHERE Emp_ID = 6;

You could also use the precedingDELETEstatement to delete all records from the Employee table where the employees belong to a certain department. An example of this is shown here:

DELETE FROM Employee
WHERE Dept_ID = 1;

Exercise caution here, just as you did when using theUPDATEstatement; if you accidentally forget theWHEREclause in aDELETEstatement, SQL will assume that you want to delete every record in the table. It is also worth mentioning that SQL is not deleting columns or fields in the table, but entire records! To clear the contents of a column or field, you must use anUPDATEstatement and specify the particular columns you are interested in.

We discuss SQL in more detail later in this chapter and in Chapter 6.


DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

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