|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Structured Query Language (SQL) Overview
|
|
|
|
SQL is the standard language for performing database operations. Its syntax is relatively simple, yet it is powerful enough to handle complex data tasks. SQL commands are encapsulated in the CFQUERY tag to perform data operations in ColdFusion. The simplicity of this mechanism is one of the principal reasons why ColdFusion is so popular with developers of data-driven Web applications.
|
|
|
|
Resources |
|
|
|
While a working knowledge of SQL is an important asset for ColdFusion development, a thorough discussion of SQL syntax and usage is beyond the scope of this user guide. A number of useful resources can be found at the OnLine SQL Meta Reference.
|
|
|
|
SQL syntax overview |
|
|
|
A SQL statement is a command made up of clauses that specify the operation to perform, the data source, and any instructions needed to complete the operation. Each clause must begin with a keyword.
Here's a simple example of a SQL statement enclosed in a ColdFusion query:
<CFQUERY NAME="zip_02140"
DATASOURCE="Customer">
SELECT first_name, last_name, phone
FROM Customer
WHERE zip_code = 02140
ORDER BY last_name
</CFQUERY>
The statement uses the SELECT keyword to search the database. The first line of the statement consists of a keyword followed by the identifiers of the data columns to be retrieved. The second line is a clause that names the data source, in this case the Customer table. The third clause uses the equal sign (=) operator to set a condition that limits the records returned to the specified zip code. The fourth clause sorts the result set by the specified column.
This query returns a sorted list of the first name, last name, and phone number of every record in the Customer table that has the value "02140" in the zip code column. The result set can then be presented in an HTML table using the CFOUTPUT tag.
SQL statements also can be used to manipulate the records in a database, create and remove data objects, and to run administrative tasks such as setting user access to data sources. The complete SQL syntax is available in ColdFusion, though most ColdFusion applications focus on queries and data maintenance operations.
|
|
|
|
Note
|
|
|
Some DBMS vendors use non-standard SQL syntax (known as a dialect) in their products. ColdFusion does not validate the SQL in a CFQUERY, so you are free to use any syntax that is supported by your data source. Check your DBMS documentation for non-standard SQL usage.
|
|
|
|
Syntax elements |
|
|
|
The following sections present brief descriptions of the main SQL command elements.
|
|
|
|
Statements
|
|
|
These keywords identify commonly-used SQL commands:
- SELECT -- Retrieves the specified records.
- INSERT -- Adds a new row.
- UPDATE -- Changes values in the specified rows.
- DELETE -- Removes the specified rows.
|
|
|
|
Statement clauses
|
|
|
These keywords are used to refine SQL statements:
- FROM -- Names the data source for the operation.
- WHERE -- Sets one or more conditions for the operation.
- ORDER BY -- Sorts the result set in the specified order.
- GROUP BY -- Groups the result set by the specified select list items.
|
|
|
|
Operators
|
|
|
These specify conditions and perform numeric functions:
Operator
|
Description
|
AND
|
Both conditions must be met, such as Paris AND Texas
|
OR
|
At least one condition must be met, such as Smith OR Smyth
|
NOT
|
Exclude the condition following, such as Paris NOT France
|
=
|
Equal to
|
<>
|
Not equal to
|
<
|
Less than
|
>
|
Greater than
|
<=
|
Less than or equal to
|
>=
|
Greater than or equal to
|
+
|
Addition
|
-
|
Subtraction
|
/
|
Division
|
*
|
Multiplication
|
|
|
|
|
SQL Extensions |
|
|
|
This section describes the ODBC extensions to SQL, which enable a high degree of code portability across data sources.
Several extensions to SQL grammar are defined by ODBC. These extensions make ODBC SQL more portable across data sources by providing a uniform syntax for features that vary across database systems. The most commonly used ODBC SQL extensions include those defined for date/time specification, scalar functions, and stored procedures.
|
|
|
|
Date/Time Specifications
|
|
|
ODBC defines a universal format for specifying date and time values. If you need to include a literal date or time value inside a SQL statement, always use this format. The formats for date, time, and date/time values are as follows:
Value
|
Format
|
Example
|
Date
|
{ d 'yyyy-mm-dd' }
|
{ d '1995-06-20' }
|
Time
|
{ t 'hh:mm:ss' }
|
{ t '15:34:08' }
|
Date/Time
|
{ ts 'yyyy-mm-dd hh:mm:ss' }
|
{ ts '1995-06-20 15:34:08' }
|
When you use the ColdFusion Insert and Update actions, date fields are automatically validated and converted to the ODBC date format, so you do not need to format the user's input. However, when you submit SQL statements directly using a CFQUERY tag, be sure you properly format all dates and times.
One way to achieve this for dates entered by users is to apply a date validation rule to the appropriate form field. This ensures that a valid date is entered and the date is automatically converted to the ODBC format.
|
|
|
|
Scalar Functions
|
|
|
Many database drivers allow you to call functions within SQL statements. These functions typically perform numeric calculations, string manipulation, date/time manipulation, and the retrieval of system information. Because the names and syntax of these functions are often different across data sources, ODBC defines a set of data source-independent scalar functions.
The syntax for calling ODBC scalar functions is as follows:
{ fn scalar-function }
where scalar-function refers to the function name and its arguments enclosed in parentheses. The following examples illustrate how two commonly used scalar functions are used in SQL statements. The first example illustrates calling CURDATE to obtain the current date:
SELECT *
FROM Orders
WHERE ShipDate>={ fn CURDATE() }
The second example illustrates calling LEFT to get the left-most 5 characters of a postal code entered by a user:
SELECT *
FROM Customers
WHERE PostalCode={ fn LEFT('#Form.PostalCode#', 5) }
The ODBC 3.0 Programmer's Reference is a good source of information on the extensive set of available functions.
|
|
|
  
|
|
|
AllaireDoc@allaire.com
Copyright © 1998, Allaire Corporation. All rights reserved.
|