Full HTML for

Basic foilset Java Blend and Cold Fusion High Level Database Interfaces

Given by Chao Wei Ou at CPS616 Web Technologies on Spring 98. Foils prepared May 3 1998
Outside Index Summary of Material

In this talk are presented two high-level technologies for making web pages that are linked to a relational database.
We give a brief overview of the new JavaBlend. This system allows you to program the web user interface in Java, where Java data objects are directly linked to the database. These can be used for database access without explicitly using JDBC or SQL.
We describe in more detail the older ColdFusion. This is a server-side markup language, which allows you to program SQL database queries in a tag language. This tag language is an extension of HTML and also makes HTML pages to show the results of database operations. Several examples are given in this talk, illustrating the tags used for database operations.
  • ColdFusion also has tags for other server-side services, not discussed here.

Table of Contents for full HTML of Java Blend and Cold Fusion High Level Database Interfaces

 Denote Foils where Image Critical
Denote Foils where Image has important information
Denote Foils where HTML is sufficient
1 JavaBlend and Cold Fusion High-level Web-Linked Database Software
2 Abstract
3 Introduction - I
4 Introduction - II
5 Java Blend
6 Java Blend Features - I
7 Java Blend Features - II
8 Title of foil 8
9 PPT Slide
10 PPT Slide
11 Overview of Cold Fusion- I
12 Overview - II
13 Cold Fusion Application Server Features - I
14 Cold Fusion Application Server Features - II
15 Cold Fusion Markup Language (CFML) - I
16 Cold Fusion Markup Language (CFML) - II
17 Cold Fusion Markup Language (CFML) - III
18 Web Server Compatibility
19 Database Support
20 How Cold Fusion Works - I
21 How Cold Fusion Works - II
22 Sample Database Used In Examples
23 Creating a Page to Select Data
24 Displaying the Results of a Selection
25 A Simple HTML page
26 A Simple CF Application page
27 HTML Output From CFOUTPUT
28 Variables Used in Example
29 Using Dynamic Parameters in SQL Statements
30 A More Complicated HTML page
31 A More Complicated CF Page (employeesearch.cfm)
32 Presenting Query Output in a Table
33 Using CFREPORT to Add Crystal Reports Output
34 Nested CFOUTPUT and Grouping
35 Output for Nested CFOUTPUT and Grouping
36 Record Number Query Attributes
37 Returning Partial Recordsets
38 Inserting Data
39 Creating Forms with the CFFORM Tag
40 Creating an HTML Insert Form
41 Creating an Insert Page with CFINSERT
42 Creating an Insert Page with CFQUERY
43 Updating Data
44 Creating an Update Form
45 Complete Update Page Using CFUPDATE
46 Creating an Update Page with CFQUERY
47 Deleting Data
48 Data Input Validation
49 Data Input Validation Types
50 Dynamic HTML Forms - I
51 Dynamic HTML Forms - II
52 Dynamic HTML Forms - III
53 Dynamic SQL - I
54 Dynamic SQL - II
55 Transaction Processing
56 More Features in Cold Fusion - I
57 More Features in Cold Fusion - II
58 Conclusions

Outside Index Summary of Material

HTML version of Basic Foils prepared May 3 1998

Foil 1 JavaBlend and Cold Fusion High-level Web-Linked Database Software

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Geoffrey Fox, Nancy McCracken, and Chao-Wei Ou
Northeast Parallel Architectures Center
at
Syracuse University
May 6, 1998
Tutorial: http://leto.npac.syr.edu/CFDOCS/user/index.htm
Foils: http://www.npac.syr.edu/users/gcf/cps616coldfusionapril98
HTML version of Basic Foils prepared May 3 1998

Foil 2 Abstract

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
In this talk are presented two high-level technologies for making web pages that are linked to a relational database.
We give a brief overview of the new JavaBlend. This system allows you to program the web user interface in Java, where Java data objects are directly linked to the database. These can be used for database access without explicitly using JDBC or SQL.
We describe in more detail the older ColdFusion. This is a server-side markup language, which allows you to program SQL database queries in a tag language. This tag language is an extension of HTML and also makes HTML pages to show the results of database operations. Several examples are given in this talk, illustrating the tags used for database operations.
  • ColdFusion also has tags for other server-side services, not discussed here.
HTML version of Basic Foils prepared May 3 1998

Foil 3 Introduction - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Supposed you are planning to publish an audio-tape catalog through your Web site, and you want to list tapes by their themes as well as present an alphabetical index of their titles. If you decide to store your tape catalog as flat HTML-formatted text files, then whenever a new tape is added to your catalog, you have to create a link for that tape on both the theme and the index pages. This link-maintenance task becomes an even more tedious if you further decide to arrange the tapes by their singers, by their release dates, and even by their ranking on Cash Box. However, by organizing the tape catalog in a database and then dynamically linking that database with your Web site, you can reduce the challenge to a simple matter of adding the new tape and its related attributes into the database only once. Granted, this sounds like a great option, but it requires you to venture into the database territory and learn the Web-linked database linking techniques.
HTML version of Basic Foils prepared May 3 1998

Foil 4 Introduction - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
The Web-linked database combination has proven that this wish list can be turned into a practical reality. The database technology not only benefits from the Web's universal appeal, but the regular sites on the Web can also utilize a database back-end to automate their information maintenance and presentation tasks.
Currently, there are hundreds (maybe thousands) of Web-Database products in the world. (e.g., Cold Fusion, HahtSite, NetDynamics, WebObjects, TalentSoft Web+, WebBase, SQLWeb, Java Blend, etc.) This list is not complete but should be enough to show the popularity of Web-Linked Database. Then we will have a brief descriptions about Java Blend and focus on presenting Cold Fusion.
HTML version of Basic Foils prepared May 3 1998

Foil 5 Java Blend

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Java Blend
  • Java Blend is a powerful tool and runtime environment that makes it easy for developers to build business applications that integrate Java objects with enterprise data.
  • Java Blend automatically generates Java objects that correspond to data in a database. Java Blend can either use your existing database schema or automatically generate a new database schema to store instances of Java classes.
Status:
  • Java Blend currently is at Beta version and will be available later this year.
HTML version of Basic Foils prepared May 3 1998

Foil 6 Java Blend Features - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Single Data Model. Java Blend provides a single data model to access both database information and program data.
Customizable Mapping. Java Blend allows a model designer to customize the mapping between Java and the database.
Scalable. Java Blend provides sophisticated caching, a separate query processor, multi-threading, and an advanced transaction concurrency mechanism designed to provide high performance in an enterprise environment.
Database Independent. Because Java Blend is implemented on top of JDBC, it can be used with any DBMS for which JDBCtm or ODBC compliant drivers have been defined. Java Blend implements ODMG on top of JDBC, bridging these higher-level and lower-level standards.
HTML version of Basic Foils prepared May 3 1998

Foil 7 Java Blend Features - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Built Around the Java Object Model. Building an entire business application around the Java object model greatly simplifies development efforts.
Legacy Compatible. Java Blend is designed to work with existing database schemas. Applications developed with Java Blend can co-exist with existing database applications
Supports Multiple Architectures. Java Blend can be used in a two-tier client-server, but is also designed for use in the middle-tier server of a three-tier architecture with a thin client.
Conforms to ODMG Standards. Because the Java Blend programmer's interface is specifically designed to conform to the ODMG standard for object/relational mappings and object databases, it is easy to port to various DBMS products.
HTML version of Basic Foils prepared May 3 1998

Foil 8 Title of foil 8

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
HTML version of Basic Foils prepared May 3 1998

Foil 9 PPT Slide

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
HTML version of Basic Foils prepared May 3 1998

Foil 10 PPT Slide

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
HTML version of Basic Foils prepared May 3 1998

Foil 11 Overview of Cold Fusion- I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
What is the Cold Fusion Application Server?
Cold Fusion Application Server is the component of the Cold Fusion rapid application development system that provides the foundation for integrating browser, server, and database technologies into powerful Web applications. The Application Server offers comprehensive support for deploying scalable Web applications and tightly integrates with Cold Fusion Studio, the visual development environment for Cold Fusion.
You build Cold Fusion applications by creating pages that combine the server-side Cold Fusion Markup Language (CFML) with HTML. These pages are deployed with the high-performance Cold Fusion Application Server, which integrates with all the major Web servers and a wide range of other server technologies.
HTML version of Basic Foils prepared May 3 1998

Foil 12 Overview - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
What are People Building with Cold Fusion?
Electronic Commerce
  • Online stores and catalogs, Supply chain management, Business to business electronic commerce, One to one marketing and Web site personalization
Collaborative Computing
  • Online discussion groups, Project management, Groupware systems, Workflow applications, Web based support
Interactive Publishing
  • Online information services, Agent technology, Dynamic Web publications, Internal corporate newsletters, Interactive training
Business Systems
  • HR applications, Sales/order entry, Business process automation, Company directory, Financial information applications, Customer asset management
HTML version of Basic Foils prepared May 3 1998

Foil 13 Cold Fusion Application Server Features - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Scalable Application Delivery
  • Multi-threaded Service
  • Web Server API Support
  • Database Connection Pooling
  • Page Compilation and Caching
  • Scheduling and Static Page Publishing
Complete Platform Integration
  • Email Servers
  • Web Servers
  • Directory Servers
  • File Servers
  • Search Engine
HTML version of Basic Foils prepared May 3 1998

Foil 14 Cold Fusion Application Server Features - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Critical Application Services
  • Security
  • Application Designation
  • Client and Session State
  • Process Scheduling
  • Encryption
  • Debugging
  • Custom Error Messages
  • Web Based Administration
Open Extensible Architecture
  • COM/DCOM
  • Cold Fusion API
HTML version of Basic Foils prepared May 3 1998

Foil 15 Cold Fusion Markup Language (CFML) - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
The Cold Fusion Markup Language (CFML) is a complete fourth generation language (4GL) for scripting Web applications. Based on tags, CFML provides a comprehensive, server-side programming environment with an easy-to-learn syntax that cleanly integrates with HTML. CFML accelerates development by encapsulating complex processes like connecting to databases or email servers with straightforward tags. CFML provides the ultimate environment for leveraging the features of the Cold Fusion Application Server to create sophisticated Web applications.
HTML version of Basic Foils prepared May 3 1998

Foil 16 Cold Fusion Markup Language (CFML) - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Powerful Scripting Environment
  • Process Encapsulation
  • Variable Manipulation
  • Date, Time, Math, List, Query, and String Functions
  • Arrays
  • Automatic International Formatting
  • Dynamic Page Flow Control
  • Code Re-use
  • HTTP Environment Variables
  • Content Type Control
  • Component Architecture
HTML version of Basic Foils prepared May 3 1998

Foil 17 Cold Fusion Markup Language (CFML) - III

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Extensive Database Support
  • Complete Database Connectivity
  • Advanced SQL
  • Database Transaction Processing
  • Data Entry Validation
  • Flexible Reporting
  • Crystal Reports
Dynamic Java Forms
  • Client-side Validation
  • Tree Control
  • Text Box Control
  • Updateable Grid Control
  • Slider Control
  • Module Extensions
HTML version of Basic Foils prepared May 3 1998

Foil 18 Web Server Compatibility

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Windows 95 and NT
  • Server API
    • O'Reilly WebSite 1.1e or Pro
    • Netscape Enterprise and FastTrack Servers
    • Microsoft IIS Peer/Personal Web Server
  • Common Gateway Interface (CGI)
    • EMWAC HTTPS
    • Process Purveyor
    • Internet Factory System
    • Spry Safety Web Server
    • CSM Alibaba
    • Luckman's Web Commander
    • Netscape Commerce and Communications Servers
    • WebSite 1.1
Solaris
  • Netscape FastTrack 2.01
  • Netscape Enterprise 2.01
  • Netscape SuiteSpot 3.0
  • Apache 1.2
HTML version of Basic Foils prepared May 3 1998

Foil 19 Database Support

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Microsoft ODBC Database Support
Microsoft
  • SQL Server, Access
  • FoxPro 2.0, 2.5, and 2.6
  • Excel
Oracle
  • Oracle 7.x
Borland
  • Paradox 3.x and 4.x dBase III and dBase IV
Plain text files
HTML version of Basic Foils prepared May 3 1998

Foil 20 How Cold Fusion Works - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
HTML version of Basic Foils prepared May 3 1998

Foil 21 How Cold Fusion Works - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
1. When a user requests a page in a Cold Fusion application by submitting a form or clicking a hyperlink, the user's Web browser sends an HTTP request to the Web server via the Internet or Intranet.
2. The Web server passes the data submitted by the client and the requested page to the Cold Fusion Application Server either through a server API or CGI. Cold Fusion pages are automatically compiled and cached in memory, so processing is very fast and scaleable even under high loads.
3. Cold Fusion reads the data from the client and processes the CFML used in the page. Based on the CFML, the Cold Fusion Application Server executes the application logic and interacts with a wide range of server technologies.
4. Cold Fusion dynamically generates an HTML page and returns it to the Web server.
5. The Web server returns the page to the user's Web browser.
HTML version of Basic Foils prepared May 3 1998

Foil 22 Sample Database Used In Examples

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Employees Table
Employee_ID FirstName LastName EmailName Phone Department
1 Carolynn Peterson CPETERSON (612)832-7654 Sales
2 Dave Heartsdale FHEARTSDALE (612)832-7201 Accounting
3 Linda Stewart LSTEWART (612)832-7478 Aministration
4 Aaron Smith ASMITH (612)832-7201 Accounting
5 Peter Barken PBARKEN (612)832-7023 Engineering
6 Linda Jennings LJENNINGS (612)832-7026 Engineering
7 Peter Jacobson PJACOBSON (612)832-7652 Sales
8 Richard Frankin RFRANKLIN (612)832-7672 Sales
:
:
HTML version of Basic Foils prepared May 3 1998

Foil 23 Creating a Page to Select Data

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Syntax
<CFQUERY NAME="query_name"
DATASOURCE="ds_name"
USERNAME="username"
PASSWORD="password"
MAXROWS="number"
TIMEOUT="milliseconds"
DEBUG="yes/no">
SQL statements
</CFQUERY>
Example: (EmployeeSearch.cfm)
<CFQUERY NAME="EmployeeList" DATASOURCE=
"CF 3.0 Examples">
SELECT * FROM Employees
</CFQUERY>
CFML query tag (in a .cfm file on the server):
HTML version of Basic Foils prepared May 3 1998

Foil 24 Displaying the Results of a Selection

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Syntax
<CFOUTPUT QUERY="queryname" MAXROWS=n >
Literal text, HTML tags, and dynamic field references (e.g. #FullName#)
</CFOUTPUT>
Example: (EmployeeSearch.cfm)
<CFOUTPUT QUERY="EmployeeList">
<HR>
#FirstName# #LastName# (Phone: #Phone#)
<BR>
</CFOUTPUT>
CFML Output tag, showing syntax to refer to columns of the
table:
HTML version of Basic Foils prepared May 3 1998

Foil 25 A Simple HTML page

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
<HTML>
<HEAD>
<TITLE>Employee Search</TITLE>
</HEAD>
<FORM ACTION="employeesearch.cfm" METHOD="Post">
Select all employees:
<INPUT TYPE="submit" VALUE="Search">
</FORM>
</HTML>
This HTML page gives the URL of a Cold Fusion page to process its form:
HTML version of Basic Foils prepared May 3 1998

Foil 26 A Simple CF Application page

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
<!--- Query to select customers --->
<CFQUERY NAME="EmployeeList" DATASOURCE="CF 3.0 Examples">
SELECT * FROM Employees
</CFQUERY>
<HTML>
<HEAD> <TITLE>Employee List</TITLE></HEAD>
<H2>Employee List</H2>
<!--- #FirstName#, #LastName#, #Phone# - Employees column names --->
<CFOUTPUT QUERY="EmployeeList">
<HR> #FirstName# #LastName# (Phone: #Phone#) <BR>
</CFOUTPUT>
</HTML>
HTML version of Basic Foils prepared May 3 1998

Foil 27 HTML Output From CFOUTPUT

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
<HTML>
<HEAD><TITLE>Employee Search Results</TITLE></HEAD>
<BODY>
<H2>Organization Search Results</h2>
<P>The Search for in the Accounting returned
these results:</P>
<HR>Dave Heartsdale (Phone: (612) 832-7201) <BR>
<HR>Aaron Smith (Phone: (612) 832-7201) <BR>
<HR>Dominique Branden (Phone: (612) 832-7049) <BR>
</BODY>
</HTML>
HTML version of Basic Foils prepared May 3 1998

Foil 28 Variables Used in Example

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
#LastName# (#Form.LastName#) and #Department# (#Form.Department#) in CFQUERY are inputs from Form in the HTML page.
#Form.LastName# and #Form.Department# in CFOUTPUT are inputs from Form in the HTML page. The default name for the Form is "Form". Inputs from different forms can be obtained by combining form name and input name as FormName.InputName.
#FitstName#, #LastName#, and #Phone# in CFOUTPUT are columns in table Employees.
HTML version of Basic Foils prepared May 3 1998

Foil 29 Using Dynamic Parameters in SQL Statements

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
SQL statement with dynamic parameters:
SELECT * FROM Employees
WHERE LastName = '#Form.LastName#'
If the user entered "Rucker" for LastName, the SQL statement sent to the database would be:
SELECT * FROM Employees
WHERE LastName = 'Rucker'
Syntax for cfm file to refer to parameters from the form:
HTML version of Basic Foils prepared May 3 1998

Foil 30 A More Complicated HTML page

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
<HTML>
<HEAD><TITLE>Employee Search</TITLE></HEAD>
<FORM ACTION="employeesearch.cfm" METHOD="Post">
Last Name: <INPUT TYPE="text" NAME="LastName">
Department:
<SELECT NAME="Department">
<OPTION>Accounting
<OPTION>Administration
<OPTION>Engineering
<OPTION>Sales
</SELECT>
<INPUT TYPE="submit" VALUE="Search">
</FORM>
</HTML>
HTML version of Basic Foils prepared May 3 1998

Foil 31 A More Complicated CF Page (employeesearch.cfm)

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
<CFQUERY NAME="EmployeeList" DATASOURCE="CF 3.0 Examples">
SELECT * FROM Employees WHERE LastName LIKE '%#LastName#%'
AND Department = '#Department#'
</CFQUERY>
<HTML>
<HEAD><TITLE>Employee Search Results</TITLE></HEAD>
<H2>Organization Search Results</h2>
<CFOUTPUT>
<P>The Search for #Form.LastName# in the #Form.Department# returned these results:</P>
</CFOUTPUT>
<CFOUTPUT QUERY="EmployeeList">
<HR>#FirstName# #LastName# (Phone: #Phone#) <BR>
</CFOUTPUT>
</HTML>
HTML version of Basic Foils prepared May 3 1998

Foil 32 Presenting Query Output in a Table

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
CFTABLE tag:
<CFTABLE QUERY="EmployeeList" MAXROWS=10 >
<CFCOL HEADER="First Name" WIDTH=25
TEXT="<I>#FirstName#</I>">
<CFCOL HEADER="Last Name" WIDTH=15 TEXT="#LastName#">
<CFCOL HEADER="Phone" WIDTH=15 ALIGN=RIGHT
TEXT="#Phone#">
</CFTABLE>
HTML version of Basic Foils prepared May 3 1998

Foil 33 Using CFREPORT to Add Crystal Reports Output

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
This example uses a function to determine the application page path. The report displays only those records that match ZIP code and last name criteria entered into an HMTL form:
<CFREPORT REPORT="#GetDirectoryFromPath
(Application PagePath)#myreport.rpt"
{ZipCode} = "#Form.ZipCode#" AND
{LastName} like "#Form.LastName#*"
</CFREPORT>
HTML version of Basic Foils prepared May 3 1998

Foil 34 Nested CFOUTPUT and Grouping

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
<CFQUERY NAME="Courses" DATASOURCE="CF 3.0 Examples">
SELECT * FROM CourseList
WHERE Department_ID = '#Form.Department#'
ORDER BY CourseLevel
</CFQUERY>
<CFOUTPUT QUERY="Courses" GROUP="CourseLevel">
<H4>#CourseLevel#</H4>
<UL>
<CFOUTPUT>
<LI> #CourseNumber# - #CourseName#
</CFOUTPUT>
</UL>
</CFOUTPUT>
HTML version of Basic Foils prepared May 3 1998

Foil 35 Output for Nested CFOUTPUT and Grouping

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Basic
<UL>
<LI> 100 - Physiology
</UL>
Intermediate
<UL>
<LI - Neurobiology
<LI - Plant Biology
</UL>
Advanced
<UL>
<LI - Neurobiology
<LI - Microbiology
</UL>
Basic
  • 100 - Physiology
Intermediate
  • 510 - Neurobiology
  • 500 - Plant Biology
Advanced
  • 820 - Neurobiology
  • 800 - Microbiology
HTML version of Basic Foils prepared May 3 1998

Foil 36 Record Number Query Attributes

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
RecordCount - The total number of records returned by the query.
CurrentRow - The current row of the query being processed by CFOUTPUT.
ColumnList - Returns a comma-delimited list of the query columns.
<CFOUTPUT>
The search returned information on #CustomerList.RecordCount# customers.<BR>
Columns queried were #CustomerList.ColumnList#.
</CFOUTPUT>
(Note that this assumes the previous query is to be used.)
HTML version of Basic Foils prepared May 3 1998

Foil 37 Returning Partial Recordsets

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
This example shows returning records 10-20 from the recordset created by the EmployeeList query:
<CFOUTPUT QUERY="EmployeeList" STARTROW="10" MAXROWS="20">
#FirstName# #LastName# #Phone# <BR>
</CFOUTPUT>
The STARTROW and MAXROWS attributes can be used to create an application that displays records in limited sets and then prompts the user to display the next group of records
HTML version of Basic Foils prepared May 3 1998

Foil 38 Inserting Data

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Inserting data into a database is usually done with two application pages:
  • An insert form
  • An insert page
You can create an insert form with CFFORM tags or with standard HTML form tags. When the form is submitted, form variables are passed to a Cold Fusion page that performs an insert operation (and whatever else is called for) on the specified data source. The insert page can contain either a CFINSERT tag or a CFQUERY tag with a SQL insert statement. The insert page should also contain a message for the end user.
HTML version of Basic Foils prepared May 3 1998

Foil 39 Creating Forms with the CFFORM Tag

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
The CFFORM tag allows you to create dynamic forms in CFML and gives you access to a wide range of form controls, such as Java applet-based tree, slider, and grid controls, as well as the standard HTML control types like checkboxes, radiobuttons, text input boxes, and edit boxes.
Using CFFORM to build a table does not preclude the use of HTML form tags in your form. You can use any HTML form tag in a CFFORM-based form. In fact, you could replace your HTML FORM tags with Cold Fusion CFFORM tags and your forms would work fine. Cold Fusion generates HTML forms dynamically from CFFORM tags and passes through to the browser any HTML code it finds in the form.
HTML version of Basic Foils prepared May 3 1998

Foil 40 Creating an HTML Insert Form

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
<FORM ACTION="insdata.cfm" METHOD="Post">
<!-- Data entry fields -->
<PRE>
First Name: <INPUT TYPE="text" NAME="FirstName">
Last Name: <INPUT TYPE="text" NAME="LastName">
Phone: <INPUT TYPE="text" NAME="Phone">
<INPUT TYPE="Submit" VALUE="Enter Information">
</PRE>
</FORM>
HTML version of Basic Foils prepared May 3 1998

Foil 41 Creating an Insert Page with CFINSERT

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
The CFINSERT tag is the easiest way to handle simple inserts from either a CFFORM or an HTML form.
The ODBC data source is named "Employees DB" and the table you want to insert data into is named "Employees." Given this information, the CFINSERT tag would be included in your page as follows:
Example: (insdata.cfm)
<CFINSERT DATASOURCE="CF 3.0 Examples" TABLENAME="Employees">
HTML version of Basic Foils prepared May 3 1998

Foil 42 Creating an Insert Page with CFQUERY

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Basic SQL insert statement is:
INSERT INTO tablename (columnnames) VALUES (values)
The VALUES keyword specifies the values for the columns in the new row. You have to type the values you want to add in the same order as the columns in the columnnames section of the statement.
To insert the form data from the example above with a CFQUERY use this syntax:
<CFQUERY NAME="AddEmp" DATASOURCE="CF 3.0 Examples">
INSERT INTO Employees (FirstName, LastName, Phone)
VALUES ('#Form.FirstName#', '#Form.LastName#',
'#Form.Phone#')
</CFQUERY>
HTML version of Basic Foils prepared May 3 1998

Foil 43 Updating Data

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Updating data in a database is usually done with two pages:
  • An update form
  • An update page
The update form is created with CFFORM tags or HTML form tags. The update form calls an update page which can contain either a CFUPDATE tag or a CFQUERY tag with a SQL update statement. The update page should also contain a message for the end user that reports on the update completion.
HTML version of Basic Foils prepared May 3 1998

Foil 44 Creating an Update Form

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
An update form is similar to an insert form with two key differences. An update form contains a reference to the primary key of the record that is being updated (Employee_ID).
<HTML>
<HEAD><TITLE>Example Update</TITLE></HEAD>
<FORM ACTION="EmployeeUpdate.cfm" METHOD="Post">
<INPUT TYPE="Text" NAME="Employee_ID" VALUE="">
FirstName: <INPUT TYPE="Text" NAME="FirstName" VALUE="">
LastName: <INPUT TYPE="Text" NAME="LastName" VALUE="">
Phone: <INPUT TYPE="Text" NAME="Phone" VALUE=""><BR>
<INPUT TYPE="Submit" VALUE="Update Information">
</FORM>
</HTML>
HTML version of Basic Foils prepared May 3 1998

Foil 45 Complete Update Page Using CFUPDATE

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
EmployeeUpdate.cfm:
<CFUPDATE DATASOURCE="CF 3.0 Examples" TABLENAME="Employees">
<HTML>
<HEAD><TITLE>Thanks!</TITLE></HEAD>
<H2>Thank You!</H2>
<HR><P>Thank you for updating your data in our database - please visit our site often!</P> <HR>
</HTML>
HTML version of Basic Foils prepared May 3 1998

Foil 46 Creating an Update Page with CFQUERY

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
SQL update statement is:
UPDATE tablename SET columnname = value WHERE condition
To update the record with the front end form from the example above using a CFQUERY use this syntax:
<CFQUERY NAME="UpdateEmployee"
DATASOURCE="CF 3.0 Examples">
UPDATE Employees
SET Firstname='#Form.Firstname#',
LastName='#Form.LastName#',
Phone='#Form.Phone#'
WHERE Employee_ID=#Employee_ID#
</CFQUERY>
HTML version of Basic Foils prepared May 3 1998

Foil 47 Deleting Data

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Deleting data in a database can be done with a single delete page. The delete page contains a CFQUERY tag with a SQL delete statement.
SQL delete statement is:
DELETE FROM tablename WHERE condition
<!-- Page to delete single employee record -->
<CFQUERY NAME="DeleteEmployee"
DATASOURCE="CF 3.0 Examples">
DELETE FROM Employees
WHERE Employee_ID = #URL.EmployeeID#
</CFQUERY>
<HTML>
<HEAD><TITLE>Delete Employee Record</TITLE></HEAD>
<H3>The employee record has been deleted.</H3>
</HTML>
HTML version of Basic Foils prepared May 3 1998

Foil 48 Data Input Validation

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Cold Fusion provides a server-side mechanism for requiring users to enter data in fields, using one of a set of special "suffixes".
<INPUT type="hidden" NAME="FirstName_required">
Data validation is also implemented by adding hidden fields to forms
The hidden field suffixes includes _integer, _float, _range, _date, and _eurodate.
<INPUT TYPE="hidden" NAME="Rooms_integer" VALUE="You must enter a number for the Rooms field.">
<INPUT TYPE="hidden" NAME="Guests_range" VALUE="MIN=1 MAX=12">
HTML version of Basic Foils prepared May 3 1998

Foil 49 Data Input Validation Types

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Cold Fusion offers several different types of data input validation.
Client-side - In a CFFORM, you can specify a JavaScript program in the ONVALIDATE attribute of tags like CFINPUT, CFGRID, CFSLIDER, CFTEXTINPUT, and CFTREE to perform input validation.
Server-side - In a CFFORM, you can enable validation in tags that support input validation (like CFINPUT and CFTEXTINPUT) using the VALIDATE attribute.
You can also use hidden fields in HTML forms to require user entries and to validate several common data types.
HTML version of Basic Foils prepared May 3 1998

Foil 50 Dynamic HTML Forms - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
A dynamic form is an HTML form generated by a cfm file that uses elements created with database query results and CFML.
This example shows a select list of employees' names that is created with a query against the employees table.
<CFQUERY NAME="EmpName" DATASOURCE="CF 3.0 Examples">
SELECT Employee_ID, LastName, FirstName
FROM Employees
</CFQUERY>
<FORM ACTION="example.cfm" METHOD="post">
<SELECT NAME="Employee_ID">
<CFOUTPUT QUERY="EmpName">
<OPTION VALUE="#Employee_ID#">#LastName#, #FirstName#
</CFOUTPUT>
</SELECT>
<INPUT TYPE="submit" VALUE="Submit">
</FORM>
HTML version of Basic Foils prepared May 3 1998

Foil 51 Dynamic HTML Forms - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
In the HTML form, select one or more companies to get information on:
<INPUT TYPE="checkbox" NAME="SelectedOrgs" VALUE=5>
Mobil Corporation<BR>
<INPUT TYPE="checkbox" NAME="SelectedOrgs" VALUE=19>
Shapeware, Inc.<BR>
<INPUT TYPE="checkbox" NAME="SelectedOrgs" VALUE=13>
BankBoston<BR>
<INPUT TYPE="hidden" NAME="SelectedOrgs_required"
VALUE="You must select at least one organization.">
The cmf program that processes the form includes:
SELECT * FROM Organizations
WHERE Organization_ID IN ( #SelectedOrgs# )
If the users selects Shapeware and BankBoston, the statement sent to the database would be:
SELECT * FROM Organizations
WHERE Organization_ID IN ( 19, 13 )
HTML version of Basic Foils prepared May 3 1998

Foil 52 Dynamic HTML Forms - III

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Single quotes must be added to the value attributes of the checkboxes.
<INPUT TYPE="checkbox" NAME="SelectedOrgs" VALUE="'Mobil Corp.'">Mobil Corporation<BR>
<INPUT TYPE="checkbox" NAME="SelectedOrgs" VALUE="'Bank of Boston'"> BankBoston<BR>
Cfm includes:
SELECT * FROM Organizations WHERE OrganizationName IN
(#PreserveSingleQuotes(SelectedOrgs)#)
The statement sent to the database would be:
SELECT * FROM Organizations WHERE OrganizationName IN
('Mobil Corp.', 'BankBoston')
HTML version of Basic Foils prepared May 3 1998

Foil 53 Dynamic SQL - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Dynamic SQL allows you to dynamically determine which parts of a SQL statement are sent to the database. So if a user leaves a search field empty, for example, you could simply omit the part of the WHERE clause that refers to that field. Or, if a user does not specify a sort order, the entire ORDER BY clause could be omitted.
Cold Fusion uses CFIF/CFELSEIF/CFELSE tags to control how the SQL statement is constructed.
  • <CFQUERY NAME="queryname" DATASOURCE="datasourcename">
  • ...Base SQL statement
  • <CFIF value operator value >
  • ...additional SQL
  • </CFIF>
  • </CFQUERY>
HTML version of Basic Foils prepared May 3 1998

Foil 54 Dynamic SQL - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
For example, in the following code, a series of CFIF tags determine which SQL statements to append to the base SQL SELECT statement:
<CFQUERY NAME="EmpList" DATASOURCE="CF 3.0 Examples">
SELECT * FROM Employees WHERE 0=0
<CFIF #LastName# is not "">
AND LastName = '#LastName#'
</CFIF>
<CFIF #FirstName# is not "">
AND FirstName LIKE '%#FirstName#%'
</CFIF>
</CFQUERY>
Tip: The WHERE 0=0 clause has no impact on the query submitted to the database. But if none of the conditions is true, it ensures that the WHERE clause does not result in a SQL syntax error.
HTML version of Basic Foils prepared May 3 1998

Foil 55 Transaction Processing

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
CFTRANSACTION tag is treated as a transactional unit. This means that changes made to the database are not committed until all queries in the transaction block execute successfully. If an error occurs in one of the queries, all changes made by previous queries within the transaction block are rolled back.
<CFTRANSACTION>
<CFQUERY NAME="WithdrawCash" DATASOURCE="BankDB">
UPDATE Accounts
SET Balance=Balance - #Amount#
WHERE Account_ID=#AccountFrom#
</CFQUERY>
<CFQUERY NAME="DepositCash" DATASOURCE="BankDB">
UPDATE Accounts
SET Balance=Balance + #Amount#
WHERE Account_ID=#AccountTo#
</CFQUERY>
</CFTRANSACTION>
HTML version of Basic Foils prepared May 3 1998

Foil 56 More Features in Cold Fusion - I

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Integrated support for Internet Technologies
  • Send E-mail (SMTP)- CFMail
  • Retrieve E-mail(POP)- CFPop
  • HTTP Support- CFHTTP
  • LDAP Support- CFLDAP
Dynamic Java Forms
  • Dynamic Java Forms- CFForm, CFTextInput, CFSlider, CFTree, etc.
  • Data Entry Validation- Form Validation, InputValidation
  • Content Type Control- CFContent
HTML version of Basic Foils prepared May 3 1998

Foil 57 More Features in Cold Fusion - II

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Complete Application Framework
Web Server Security Integration
Client State Management - CFCookie
Application Framework - CFApplication, CFParam
Customize Error Messages - CFError
Extensive Debugging Information
Open Extensible Architecture
Custom Tags - CFModule, Encrypting Custom Tags
ActiveX Server Components (COM) - CFObject
Open API - CFXs
HTML version of Basic Foils prepared May 3 1998

Foil 58 Conclusions

From Java Blend and Cold Fusion High Level Database Interfaces CPS616 Web Technologies -- Spring 98. *
Full HTML Index
Cold Fusion provides
  • an easy programming environment for users with experience in developing HTML page and SQL,
  • a powerful way to integrate database applications and Web,
  • many useful features (e.g., mail, file management, etc.) to users
For more information:
For NPAC CPS students:
© Northeast Parallel Architectures Center, Syracuse University, npac@npac.syr.edu

If you have any comments about this server, send e-mail to webmaster@npac.syr.edu.

Page produced by wwwfoil on Sat Nov 28 1998