'Crudding' with ColdFusion

cortegesmashInternet και Εφαρμογές Web

10 Νοε 2013 (πριν από 3 χρόνια και 11 μήνες)

117 εμφανίσεις

PL

Crudding with ColdFusion

i


PL

Crudding with ColdFusion

i


















'Crudding' with ColdFusion






by



Peter Lake and Hugh Lafferty

PL

Crudding with ColdFusion

ii


PL

Crudding with ColdFusion

ii




Table of Contents


1.

Introduction

................................
................................
................................
................................
....

1

1.1.

Assumptions about the reader

................................
................................
................................
...

2

1.2.

How to use these

notes

................................
................................
................................
.............

2

1.3.

Recordset

................................
................................
................................
................................
..

2

1.4.

Database Connections

................................
................................
................................
...............

3

1.5.

Data Source Names (DSNs) and opening connections

................................
.............................

3

1.6.

Executing SQL

................................
................................
................................
.........................

3

2.

Hello world

................................
................................
................................
................................
......

4

3.

A first go at dynamic ColdFusion Markup Language

................................
................................
.

5

4.

Passing Parameters to URLs

................................
................................
................................
.........

7

5.

Using the CFForm

................................
................................
................................
........................

10

6.

Using the CFGrid in Read Only mode

................................
................................
........................

12

7.

Using the CFGrid to delete from the database

................................
................................
...........

14

8.

Conditional processing

................................
................................
................................
.................

16

9.

Back to updating from a Grid

................................
................................
................................
.....

17

10.

References

................................
................................
................................
................................
.

19

10.1.

Books

................................
................................
................................
................................
..

19

10.2.

Web
-
sites

................................
................................
................................
............................

19

11.

Self
-
Test

................................
................................
................................
................................
.....

20

12.

Solutions to b Activities

................................
................................
................................
............

21

13.

Appendix A

................................
................................
................................
................................

25







PL

Crudding with ColdFusion

1


PL

Crudding with ColdFusion

1

1.

Introduction


ColdFusion acts as an extension to a Web Server, in much the same that ASP works. As we shall see
the platform is irrelevant as the ColdFusion server is an in
-
RAM service whi
ch can be set up to
communicate via APIs with whichever Web Server is running on the physical server.


The job of the Web Server, such as Apache or IIS, is to return HTML to a client as a result of a URL
request from that client. ColdFusion is a tag
-
based
language that can be incorporated into an HTML
page. When the webserver sees a <CF> tag it calls on the CodFusion server and asks it to act on the
tag. CF will return some HTML which the webserver can then parcel up with any other HTML on the
URL template
and return to the requester. Actually the term ColdFusion server is rather misleading as
the functionality is actually contained in a DLL that is called as a module by Apache.


In this way, ColdFusion can act as an intermediary between the Webserver and a
database. For
example the <CFQuery> tag that we shall use extensively in what follows gets passed to ColdFusion
Server to deal with. ColdFusion will then query the datasource and return the output. That output is
then available as output in HTML format.


I
n what follows we use a text editor and an FTP client. There are other tools available. Dreamweaver,
for example, offers a friendly IDE for the entire website development cycle. We use the word
Put

to
mean "use FTP to send to the server". If you do not hav
e Dreamweaver you could just as easily use a)
TextPad (switch line numbering on), and b) WSFTP. If you do not know how to use an FTP client,
then look at the accompanying "How to FTP" notes.


'Crudding'

is a shorthand for "Creating Updating and Deleting" a
nd we include Browsing into
crudding. So, we then move on to show how to Browse a file, Create records in a file, Update records
in a file and Delete records from a file.


Although Coldfusion MX now makes some tasks simpler for the user, we think the prin
ciples involved
are best exemplified by writing, or at least cutting
-
and
-
pasting code so that you can understand what is
going on behind the scenes. In this way your skills should be more transferable to other database
"glues".


In the examples in this doc
ument we will be using the SHU Oracle instance, which is running on a
database server called Ivy, and the CMS application server, which is called
Otho
. Refer to separate
notes on using Ivy.


The Oracle database and RDBMS (Relational Database Management Sys
tem) is a database engine
which allows many users to have accounts which are referred to as schema. The owners of accounts
can store data in tables in their schema. They can grant access to the tables to other users. They can
create new tables, insert
data into their tables, update (change) the data, and they can delete the tables
or the data therein.


Registered students at SHU can create an account for themselves on the Oracle Database used for
teaching. More information about using Oracle at SHU (i
ncluding how to create your account) can be
seen at:
-

http://students.shu.ac.uk/cis/documents/oracle/getstarted/index.html



PL

Crudding with ColdFusion

2


PL

Crudding with ColdFusion

2


1.1.

Assumptions about the reader


We have made a

number of assumptions about the reader of these notes, and these are




You are already a competent programmer (so we will not bother explaining what a variable is)



You know what a Web server is. If not please refer to the accompanying "Web Server" notes



Yo
u know how to FTP. If not please refer to the accompanying "How to FTP" notes




1.2.

How to use these notes


Many of the activities are numbered with an
a

or a
b

extension e.g. Activity 2a.


If these notes are delivered in
face
-
to
-
face mode

with students who

have a computer in front of them
then we would expect the lecturer to demonstrate the
a
Activity and highlight any teaching points, and
then ask the students to simply copy that exercise. Having these notes in electronic form makes
copying very easy. Howe
ver, this could lead to students simply copying and not understanding. That is
why the
b

activities are included. If time permits the students should then practice the ideas in the
associated
b

Activity with the lecturer going round helping. If time does n
ot permit then the students
should, in their own time, do the
b

activities. Before the next lecture students should do the Tutorial
and in the next lecture the lecturer could run a quiz based on the Self
-
Test section.


If these notes are used in
distance
-
l
earning mode
then simply go through them in order, first of all
copy each
a
Activity and note carefully the teaching points, then try out the

b

Activity. Then do the
Tutorial, and then try the Self
-
Test. When you come back to these notes read the Summary f
irst to see
if you have remembered all the points in there.



1.3.

Recordset


One important concept in these notes is the idea of a
Recordset
, which is a copy of a table or results of
a query

which will, in the first instance, reside in the RAM of the server
.
It is best thought of as a
rectangular structure with rows corresponding to records and columns corresponding to fields,
exemplified below


Customer

CustomerNo

CustomerTitle

CustFirstName

CustSurname
















ColdFusion takes care of Recordse
ts for you. They are automatically created as a result of a
CFQUERY. You can either display the resultant recordset using a vanilla <CFOUTPUT> tag, or
manipulate it by adding attributes like MAXROWS and STARTROW to the <CFOUTPUT> tag. More
of that later.



Record 1

Record 2

Record 3

PL

Crudding with ColdFusion

3


PL

Crudding with ColdFusion

3


1.4.

Database Connections


In order to create a recordset ColdFusion must first be able to connect to a database.


Otho

has IIS webserver installed, with the ColdFusion module enabled. This means that you can store
files on
Otho

with a
.CFM

extension and inc
lude ColdFusion tags in those documents.
Otho

also
already has a
data source

set up, pointing at the SHU
10g

Oracle instance on Ivy.


Once you have created your templates locally you will need to upload, using FTP, into your area on

Otho
. If you were using dldb1, as all the following examples do, once you have ftp’ed your files to
Otho

you can get to them from the web by pointing to a URL like:

http://otho.cm
s.shu.ac.uk/dbstudents/AhmadBadawi/Opening.cfm


The upside is that your data source is already set up. The downside is that you need to provide your
Oracle login and password to ColdFusion to give it access to your schema objects. Not the most secure
way
of doing it, but it serves to demonstrate how the technology hangs together.





1.5.

Data Source Names (DSNs) and opening connections


A Data Source Name (
DSN
) is a pointer to a database. This pointer is an ODBC System DSN and must
have been set up by a system

administrator and can point to a database anywhere in the world.


At Sheffield Hallam University there are a number of servers and
<
s are often provided space for their
CFM pages on a server called
Otho

and space for Oracle databases on a server called I
vy, and so we
have this set up














1.6.

Executing SQL



We now have the important elements of a ColdFusion database query in place. There just remains
the question of how we query the database. This is done using a standard SQL string. The
<
CFQUERY> tag has a number of attributes (see example below), amongst them are the ODBC
datasource we will be using, our Oracle username and password, and then, the actual SQL query.









Otho


Ivy

cfm
pages


DSN
s
s

Oracle


<cfquery name="emplist" datasource="shu
10g
" username="cmspl4" pas
sword="murph">

select * from emp

</cfquery>

Don’t forget to use
your own 佲慣汥l
us敲nam攠and
p慳aword 楮s瑥慤
of cmsp水

PL

Crudding with ColdFusion

4


PL

Crudding with ColdFusion

4


2.

Hello world


ACTIVITY 1a
. The objective of this exercise is to
make sure that you can get a simple, static
CodFusion page to work. It will be deeply uninteresting if you are used to writing HTML. The main
objective is to demonstrate that ColdFusion documents MUST contain HTML.



Using a text editor to construct the fo
llowing, and save as
welcome.cfm. Put
it to a server and then
call it in a browser using something like


http://
o
tho
.cms.shu.ac.uk/
dbstudent
s
/
Badawi
/Hello.cfm



















We have used the CFSET tag to declare a string variable. In order to displa
y CF Variables, you need to
display from within a CFOUTPUT tag pair. Note the use of the # around CF variables.


ACTIVITY 2b.
Create a file called
MyMessage.cfm

that displays on the client a message other than
this one. Save it to a server and call it from

a browser. You should thus have practiced a) creating a
cfm

page on a development machine b) FTP
-
ing the
cfm

page to a server c) Calling the
cfm

page from a
browser on a client.


You should rehearse in your mind what the sequence of events is when you ty
pe something like
http://otho.cms.shu.ac.uk/dbstudent
s
/Badawi/Hello.cfm

in a browser.




The
teaching points

from those exercises were




cfm pages are written inside HTML pages



cfm code is interpreted by a cfm extension to a web server (e.g. IIS, Apache,
Tomcat)



the cfm extension simply passes back any HTML (and anything inside <SCRIPT> tags) back
to the web server


However, as we shall see in a minute, if the extension sees an SQL statement then it has to
converse with the database using whatever drivers

that it has been told to use, and eventually send
back dynamically created HTML to the web server.


Use your own
username

<
HTML>


<HEAD>


<TITLE> CF Page </TITLE>


</HEAD>


<BODY>

<cfset welcomemess = "This Welcome come to you from inside a CFOUTPUT tag">

<cfoutput>

#welcomemess#

</cfoutput>



</BODY>

</HTML>



PL

Crudding with ColdFusion

5


PL

Crudding with ColdFusion

5


3.

A first go at dynamic ColdFusion Markup Language


The example in 1.6 is a good start. With it we can create a recordset which in this case would look
som
ething like this output from sqlplus:





ACTIVITY 3a.

The objective of this activity is to
List the contents of one column in a table

on the
server
.


We will use a very similar query to 1.6, with the main difference that we restrict the select to one
co
lumn; ename. We will then simply display that column from the resultant recordset. To try this out,
cut and paste this code into a new file, using an editor like TextPad. Save the file as emp1.cfm. Then
PUT

the file onto your
Otho

account using FTP.



















<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery name="emplist" datasource
="shu
10g
" username="cmspl4" password="murph">

select ename from
emp

</cfquery>

<html>

<head>


<title>Employees</title>

</head>

<body bgcolor="silver">

<CFOUTPUT query="emplist">

#ename#

</CFOUTPUT>

</body>

</html>

ColdFusion Tags

Ordinary
HTML
tags

Again, don’t forget
瑯 us攠your own
佲慣汥lus敲nam攠
慮d p慳aword
楮s瑥慤 of cmsp水

PL

Crudding with ColdFusion

6


PL

Crudding with ColdFusion

6



Now the exciting bit. Launch your browser and point it at your URL on
Otho
. And, with a bit of luck,
we will have our first dynamically created web page, looking like this:





So, OK, the format isn't much to write home about,
but just think what has happened here. If another
user adds to, or deletes from the EMP table and you revisit the URL, the list will automatically alter to
match the database.



ACTIVITY 3b.
Write a CFM page that displays, in a list, the distinct job titl
es that exist in the EMP
table. They should be centred on the screen. Hint: use the <DIV align=center> HTML tag within the
CFOUTPUT .



The
teaching points

from those exercises were


o

To specify what data should be retrieved into a recordset, use a SQL sel
ect within the
CFQUERY tag

o

When ColdFusion is passed this tag it sends the SQL string to the indicated datasource. The
results are not displayed automatically, but come back as a recordset

o

The column names are returned as variables that you can use how you

like

o

To output field names from the recordset use the format: #
columnname
#

o

Actually, the hashes are used to enclose any CF Variable. You will use variables later, but in
this case ColdFusion has automatically declared the columnnames as variables.

o

#varia
blename# only gets translated into its value if placed within a CFOUTPUT


PL

Crudding with ColdFusion

7


PL

Crudding with ColdFusion

7


4.

Passing Parameters to URLs


ACTIVITY 4a.

The objective of this activity is to
pass information to a second form and allow
ColdFusion to access that data to refine a SELECT.



So f
ar one page, and one bit of information. Now let's see if we can let the user select one of the listed
employees from 3a, and take them to a second page which contains details about where that employee
works. We will use a standard HTML anchor, and pass th
e required employee number to the second
page through the URL.


Paste the following code i
nto a file called Act4
a.cfm, alter username and password, and
PUT

it onto
Otho
.


























<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery
name="emplist" datasource="shu10g
" username="cmspl4" password="murph">

s
elect empno, ename from
emp

</cfquery>

<html>

<head>

<title>Employees</title>

</head>

<body bgcolor="silver">

<b>Please select from the list of Employees:</b>

<P></P>

<cfoutput query="emplist">

<li>

<DIV>

<a href="EmpDetails.cfm?sel_emp=#empno#">

#ename#</
a>

</DIV></li>

</CFOUTPUT>

</body>

</html>

This is a

standard
HTML
anchor, but
note how the
value of
#ename# is
what gets
displayed
because its
within a
CFOUTPUT.

The employee number
for the person selected
by name gets passed as a
URL parameter

Note that we

need the recordset to
include both columns, even though
we are only displaying the name

PL

Crudding with ColdFusion

8


PL

Crudding with ColdFusion

8


Now point your browser at this new URL. It should l
ook something like this:




Now we need to create EmpDetails.cfm.


TIP:

There is a join involved in generating the required recordset. When the SQL becomes complex
you might find it easier to develop and test the SQL, for example in SQLPLUS, first, so t
hat you know
that the SQL string within the CFQUERY will work.















<!DOCTYPE HTML

PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery name="empdet" datasource="shu
10g
" username="cmspl4" password="murph">

select empno, ename, dname, l
oc from emp a,
dept b

where a.empno=#url.sel_emp#

and a.deptno=b.deptno

</cfquery>

<html>

<head>

<t
itle>Employee Details</title>

</head>

<body bgcolor="silver">

<b>Details for selected employee:</b>

<P></P>

<cfoutput query="empdet">

REF: #empno#, <BR>

NAME: #ename#, <BR>

Department: #dname#, <BR>

Based: #loc#

</CFOUTPUT>

</body>

</html>

Yes, we can do
joins!

Note how we
use the
sel_emp
variable as
passed in the
URL

PL

Crudding with ColdFusion

9


PL

Crudding with ColdFusion

9



ACTIVITY 4b.
Build upon your Activity3b work and create a CFM page that displays, in a list, the
distinct job titles that exist in the EMP table as anchors. When the user c
licks one, it should move to
another page that lists all the employees who work in that role.


Hint: If you are using a string in your select, the database will expect to see a single quote around the
value, for example:
where ename='#sel_name#'.
You must
note put quotes round numbers, however.




The
teaching points

from those exercises were


o

The SQL string can contain complex SQL statements

o

If a value has been passed to a page through the URL, ColdFusion gives you access to it like
any other variable by
prefixing "URL." in front of the variable name.

o

When you use the URL prefix, you are specifying the scope of the variable. More of this later

o

You can pass multiple URL variables in this way.

o

We will find that this sort of prefix is used often. Amongst othe
rs, we can declare variables as:

o

cookie.variablename

o

form.variablename

o

session.variablename

o

client.variablename


o

These are all ways of dealing with the stateless problem inherent in using web pages. Cookies
store information client
-
side. Form variables ar
e passed to the called form from the calling
form. The other two store information server
-
side. In fact, ColdFusion deals with client
management, by default, using the registry. To make applications more scaleable, this can be
changed to use an ODBC databa
se.



PL

Crudding with ColdFusion

10


PL

Crudding with ColdFusion

10


5.

Using the CFForm


ACTIVITY 5a.

The objective of this activity is to
improve the look and feel of the user interface
using CFFORM


We have thus far only listed our data out using traditional HTML code. ColdFusion comes with some
data presentation t
ags which can really improve the look of a template. If you are familiar with an
HTML Form, the usage will look familiar, with ACTION indicating where the template should move
to when the user presses SUBMIT.


From within CFForm tag pair we will have acce
ss to other display control tags. In this example we use
a CFSELECT to provide the user with a rather smarter list than a mere set of anchors. This is what we
are aiming for:



PL

Crudding with ColdFusion

11


PL

Crudding with ColdFusion

11


And here is the code:




































Make sure
you understand what the other attributes of the CFSELECT do by experimenting with this
code. Note that we have altered EmpDetails.cfm that we created in Activity 4a to accept a value from a
Form scoped variable, instead of a URL. The change is a line that
now reads:



where a.empno=
#form.selemployee#

instead of:


where a.empno=#url.sel_emp#


Other input tags include CFTEXTINPUT, CFSLIDER, and as we shall see later, CFGRID.



ACTIVITY 5b.
Build upon your Activity4b work and create a CFM page

which

uses a CF
S
elect

instead of anchors.


The
teaching points

from those exercises were


o

The CFFORm provides a developer with enhanced display capabilities

o

CFSELECT allows the user to select from a list

o

Variables can be pas
sed between CFFORM

<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery

name="emplist" datasource="shu10g
" username="cmspl4" password="murph">

select empno, ename from
emp

</cfquery>

<html>

<head>


<title>Employees</title>

</head>


<body bgcolor="silver">


<P align=center><FONT size=5><b>The Dacotta Company Employee List</b></FONT></P>

<P align=center><FONT size=5><B><IMG alt="" border=0 height=129

src="dacotta.jpg" style="HEIGHT: 140px; WIDTH: 240px"

width=153></B></FONT></P>

<P>


<HR width=500>


<P></P
>

<P align=center><STRONG>The following employees work for The Dacotta Co.:</STRONG></P>

<P></P>

<P align=center>

<cfform action="EmpDetails2.cfm" method="POST" enablecab="Yes">

<cfselect name="selemployee"


SIZE="#emplist.recordcount#"



message="You must select an employee to continue"


query="emplist"


value="empno"


display="ename"


required="Yes">

</cfselect>

<P align=center><input type="Submit" value="View Details"></p>

</cfform>

</P>

</body>


</htm
l>

CFSELECT
within a
CFFORM

As with an HTML Form, the action
indicates the template to move to when
the user SUBMITS


What value
in the
recordset to
pass as a
variable to
the next
Form when
submitted

Note the automatically created cf variabl
e which
tells you how many records are in the recordset

PL

Crudding with ColdFusion

12


PL

Crudding with ColdFusion

12

6.

Using the CFGrid in Read Only mode


ACTIVITY 6a.

The objective of this activity is to
display recordset data in a grid using CFGRID


The output we are aiming for is something like a spreadsheet. We will use a grid to both display data,

and to allow the user to select and update data presented therein.


The easiest type of grid is one that is used only for Browsing, in Read Only mode. In the example
below the user can navigate around the grid. They cannot modify any data, and if the CFFO
RM
containing the grid is submitted, no Grid data is passed on. In the example below we present the user
with the
Department
details within a grid.


You should be aware, and you should make sure your users are aware, that these tags are actually Java
Apple
ts and, as such, when they are used for the first time on a client machine, there will be an
automatic applet download. On a slow modem this could take 20+ minutes.










































<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery name="depts" datasource="shu
10g
" username="cmspl4" password="murph1">


select deptno, dname, loc


from
dept

</cfquery>


<html>

<head>

<title>
Employee Details</title>

</head>


<body bgcolor="silver">

<b>Dacotta Company Departments</b>

<P></P>


<cfform action="handle_grid.cfm" method="POST" enablecab="Yes">

<!
---

CFGRID with all setting as default
---
>


<cfgrid name="" align="MIDDLE" query="depts
" insert="No" delete="No" sort="Yes"




colheaders="Yes" colheaderalign="LEFT" colheaderbold="Yes" selectmode="BROWSE"

>


</cfgrid>

</cfform>

</body>

</html>

Provi
de the user
with buttons to
sort the data

PL

Crudding with ColdFusion

13


PL

Crudding with ColdFusion

13

The user should see something like this:



Not
e how the column widths have defaulted to just wide enough to display the column headings.
Within a CFGRID, you can take control of the Rows and Columns with the CFGRIDROW and
CFGRIDCOLUMN tags.


Here we have fixed the column width. Measured in pixels. Not
e also that we are displaying Deptno. It
is not always the case that we want to display all columns to the user. Sometimes we still need to return
the value because we want to pass the row data to the next form, particularly if that value is a primary
key,

but want the user to be unaware of its value. Finally, we have Selectmode set as ROW, which
means that clicking on the row highlights it and makes the values available in the form variable
#gridname.columname#

















Everything else as before……


<cfgrid name="" align="MIDDLE" query="depts" insert="No" delete="No" sort="Yes"




colheaders="Yes" colheaderalign="LEFT" colheaderbold="Yes" selectmode="ROW"
>




<CFGRIDCOLUMN NAME="dname" HEADERBOLD="Yes" Width="100">



<CFGRIDCOLUMN NAME="loc" HEADERBOLD="Yes" HEADER="Location" Width="80">



<CFGRIDCOLUMN NAME="deptno" HEADERBOLD="Yes" Display="No">


</cfgrid>


Everything else as before……

PL

Crudding with ColdFusion

14


PL

Crudding with ColdFusion

14



ACTIVITY 6b(i)

Experimen
t with the attributes in both CFGRID, CFGRIDCOLUMN and
CFGRIDROW.

ACTIVITY 6b(ii)

You have now had experience of using CFGRID and CFSELECT. Use the
online documentation to see what other CFFORMS related tags there are. Try some out.




The
teaching points

from those exercises were


o

CFGrid has much built
-
in functionality to enable users to view, select and amend records

o

Browse = "Edit" is required if you want let the users insert, update or delete

o

To enable users to delete, set the delete attribute to "YES
" and the selectmode to "Edit".

o

BE AWARE, users edit only the entries in the grid, which is a recordset, probably in their
RAM. You will need to process their changes if you want them reflecting in the database.

o

As this functionality is enabled using Java
applets, be aware that a target client machine must
be Java enabled, and be prepared for a lengthy download the first time they use one of the
controls.





7.

Using the CFGrid to delete from the database


ACTIVITY 7a.

The objective of this activity is to re
flect

changes made to the locally held recordset
displayed in a CFGRID back to the database.



Remember that what is being displayed in a grid is only a copy of data from a database. Even if we set
the Selectmode to EDIT and have both INSERT and DELETE =
"YES", we are only allowing the user
to amend the their recordset. We need to find some way to reflect back any changes to the database.


One simple way is a tag called CFUPDATEGRID. However, this has several limitations for all but
simple, single table sc
enarios, so we will go straight to using CFQUERY to do this, as we will then be
able to cope with any eventuality in the future.


The basic principle is that when a CFGRID gets submitted, the receiving template has access to 3
arrays:




#Form.gridname.colum
nName[rowindex]# holds the new values for a particular column



#Form.gridname.Orininal.columnName[rowindex]# holds the old values for a particular column



#Form.gridname.RowStatus.Action[rowindex]#

stores the type of change to a row. U, for Update,
D for
Delete, I for insert.



With this information we can determine exactly what has been changed and then write a SQL statement
to reflect those changes back to the database.


For this exercise you need to create your own table in your schema. Scripts for crea
ting sample tables
with some data are appended to these notes.

PL

Crudding with ColdFusion

15


PL

Crudding with ColdFusion

15

To start with we will simply write some code to delete a record that the user has selected. This is not
very useful, but illustrates the principles involved. In the next section we discover ho
w to use
conditional processing in ColdFusion, and when we have those skills we will revisit our grid update
requirements.


Building on the work we did in Activity 6a, we here have added an HTML submit button.





























Now we need to

create the template to handle the submitted data. Paste this into Act7a2.cfm and PUT
it onto
Otho
:


















Of course this is a very simple application. It is one table, and we are sure that we can uniquely identify
the record to be deleted bec
ause we included the primary key in the recordset. But the CFQUERY
could be any legitimate SQL, as we will see later.
<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfqu
ery name="CDs" datasource="shu10g
" username="cmspl4" password="murph1"
>


select Catalogue_Number, Title, Daily_Cost


from CD

</cfquery>


<html>

<head>

<title>CD Masterfile</title>

</head>

<body bgcolor="silver">

<b>All CDs in the Collection</b>

<P></P>


<cfform name="GridForm" action="Act7a2.cfm" method="POST" enablecab="Yes
">

<!
---

CFGRID with all setting as default
---
>


<cfgrid name="CDGrid" align="MIDDLE" query="CDs" insert="No" delete="No" sort="No"




colheaders="Yes" colheaderalign="LEFT" colheaderbold="Yes" selectmode="ROW">




<CFGRIDCOLUMN NAME="Title" HEADERBOLD="Y
es" Width="150">



<CFGRIDCOLUMN NAME="Daily_Cost" HEADERBOLD="Yes" HEADER="Cost per Day"
DataAlign="Right" Width="80">



<CFGRIDCOLUMN NAME="Catalogue_Number" HEADERBOLD="Yes" Display="No">


</cfgrid>

<INPUT TYPE=submit value="Delete Selected
"></p>

Select a CD before pressing Delete

</cfform>

</body>

</html>

The template that will get called when
the user presses the submit button

<HTML>

<HEAD>


<TITLE>Delete the selected CD</TI
TLE>

</HEAD>

<BODY>

<cfoutput>


<H3>Deleting #form.CDGrid.Title# from the database</H3>

</cfoutput>

<cfquer
y name="CDdel" datasource="shu10g
" username="cmspl4" password="murph1">


delete from CD where Catalogue_Number=#form.CDGrid.Catalogue_Number#

</cfque
ry>

---
>

Record Deleted

</BODY>

</HTML>



The values of the
selected row are passed
as CF variables.

PL

Crudding with ColdFusion

16


PL

Crudding with ColdFusion

16

ACTIVITY 7b(i)

Create a table in your schema which will record a CD selection. It should
have one field, called selectedcd in which you ca
n store catalogue_numbers. Call the table
SELECTIONS.


ACTIVITY 7b(ii)

Save Ac7a.cfm as Act7b.cfm and change the button to say "Add to Basket".
Don’t forget to change the Action as you should save Act7a2.cfm as Act7b2.cfm. Make this latter
template issue

a SQL command to insert the selected Catalogue_Number into the SELECTIONS table.




The
teaching points

from those exercises were


o

The Action attribute tells us which template will be used when the user submits.

o

Grid values are passed to the called templ
ate on submission.

o

That called template can be used to interact with the database.

o

In this way, as we will see, we can keep the CFGRID and database in step.






8.

Conditional processing


ACTIVITY 8a.

The objective of this activity is to demonstrate how co
nditional processing is enabled
with CF tags.


The <CFIF> tag allows us to test for some condition an decide how to act dependant upon the value.
These conditions can be expanded using the <CFELSEIF>. The block needs ending with a </CFIF>.
There is also a
<CFSWITCH><CFCASE> construct for more complex conditional processing.


In our first example we can begin to make our forms more robust. In this case we do so by checking
that our recordset has returned some values before we display the CFGRID and button. I
f there are no
records, we display a message to the user:

























Up to here, the same as 7a……


<cfif #CDs
.recordcount# greater than 0>


<!
---

Some values, so make this into a cfform to enable us to use the cfgrid
---
>


<cfform name="GridForm" action="DelForm.cfm" method="POST" enablecab="Yes">

<!
---

CFGRID with all setting as default
---
>


<cfgrid name="CDGr
id" align="MIDDLE" query="CDs" insert="No" delete="No" sort="No"




colheaders="Yes" colheaderalign="LEFT" colheaderbold="Yes" selectmode="ROW">




<CFGRIDCOLUMN NAME="Title" HEADERBOLD="Yes" Width="150">



<CFGRIDCOLUMN NAME="Daily_Cost" HEADERBOLD="Yes"

HEADER="Cost per Day"
DataAlign="Right" Width="80">



<CFGRIDCOLUMN NAME="Catalogue_Number" HEADERBOLD="Yes" Display="No">


</cfgrid>

<INPUT TYPE=submit value="Delete Selected"></p>

Select a CD before pressing Delete

</cfform>


<cfelse>

<!
---

No data return from query
---
>


<font size="+1"><i>No CDs found, contact your administrator</i></font>

</cfif>


…………..from here, the same as 7a

PL

Crudding with ColdFusion

17


PL

Crudding with ColdFusion

17

There are other flow control tags. Check out the
online reference
.


ACTIVITY 8b.
Build up
on your Act4b work to make sure that the list of Jobs only gets displayed the
recordset contains some records. Try the <CFSWITCH> control.


The
teaching points

from those exercises were


o

CFIF structure gives us control over our processing

o

There are other
useful flow control tags




9.

Back to updating from a Grid


ACTIVITY 9a.

The objective of this activity is to demonstrate how to reflect all the changes from a
CFGRID in the underlying database.


We are not using anything here that is all that new, just a b
it more complex. Try and follow the code
and make sure you understand what is happening.


Those of you new to Oracle will be surprised that there is no such thing as an autoincrement field.
Actually, the thing you use is more powerful, and is called a sequ
ence. It’s a schema object that you
will need to create using this syntax:

CREATE SEQUENCE NEWCD INCREMENT BY 1 START WITH 1 ;


We can now call that sequence whenever we need the next number, in effect giving us the same as an
autoincrement.


The process
that Act9a2.cfm goes through is to loop through all the rows passed to it from the
submitting form (Act9a.cfm) and check what activity has been carried out on each row. We then use
our conditional processing to run the appropriate SQL command.


Up to here, the same as 8a……


<cfif #CDs.recordcount# greater than 0>


<!
---

Some values, so make this into a cfform to enable us to use the cfgrid
---
>


<cfform name="GridForm" action="Act9a2.cfm" method="POST" enablecab="Yes">

<!
---

CFGRID with all setting as default
---
>


<cfgrid name="CDGrid" align="MIDDLE" query="CDs" insert
="YES" delete="YES" sort="No"




colheaders="Yes" colheaderalign="LEFT" colheaderbold="Yes" selectmode="EDIT">




<CFGRIDCOLUMN NAME="Title" HEADERBOLD="Yes" Width="150">



<CFGRIDCOLUMN NAME="Daily_Cost" HEADERBOLD="Yes" HEADER="Cost per Day"
DataAlign="
Right" Width="80">



<CFGRIDCOLUMN NAME="Catalogue_Number" HEADERBOLD="Yes" Display="No">


</cfgrid>

<INPUT TYPE=submit value="Commit Changes to Database"></p>

</cfform>


…………..from here, the same as 8a

i整eth攠us敲 do
瑨敩e worst!

PL

Crudding with ColdFusion

18


PL

Crudding with ColdFusion

18


And
then the processing template:










































ACTIVITY 9b

Just spend some time understanding what we have just done! It's quite complex, but
in understanding that we are ready for more or less anything!



The
teaching poin
ts

from those exercises were


o

We can keep the CFGRID and database in step!







<HTML>

<HEAD>


<TITLE>Deal with submitted grid values</TITLE>

</HEAD>

<BODY>


<H3>CD row updates</H3>


<CFIF IsDefined("form.CDgrid.rowstatus.action")>



<CFLOOP INDEX = "Counter" FROM = "1" TO =


#ArrayLen(f
orm.CDGrid.rowstatus.action)#>



<CFOUTPUT>


The row action for #Counter# is:


#form.CDGrid.rowstatus.action[Counter]#


<BR><BR>


</CFOUTPUT>



<CFIF form.CDGrid.rowstatus.action[Counter] IS "D">




<cfquery name="CDDel" datasource="sh
u
10g
" username="cmspl4" password="murph1">



delete from CD where Catalogue_Number=#form.CDGrid.original.Catalogue_Number[Counter]#


</cfquery>




<CFELSEIF form.CDGrid.rowstatus.action[Counter] IS "U">





<cfquer
y name="CDUpd" datasource="shu10g
" user
name="cmspl4" password="murph1">



UPDATE CD



SET Title = '#form.CDGrid.Title[Counter]#' ,



Daily_Cost = #form.CDGrid.Daily_Cost[Counter]#




WHERE Catalogue_Number=#form.CDGrid.original.Catalogue_Number[Counter]#


</cfquery>


<CFELSEIF form.CDGrid.r
owstatus.action[Counter] IS "I">

<!
---

note: requires a sequence called newcd, created thus:CREATE SEQUENCE NEWCD INCREMENT BY 1 START WITH 1 ;
---
>


<cfquer
y name="CDIns" datasource="shu10g
" username="cmspl4" password="murph1">



INSERT into CD



Values( n
ewcd.nextval ,'#form.CDGrid.Title[Counter]#' , #form.CDGrid.Daily_Cost[Counter]#)


</cfquery>



</CFIF>


</CFLOOP>

</CFIF>

</BODY>

</HTML>





Useful CF function which returns TRUE if the named
variable exists. In this case the processing wont happen if
you directly call Act9a2.cfm from your browser, because the
grid on the calling
Act9a will not have been defined.

Loop through every altered row submitted

Check whether Delete, Update or Inser
t

PL

Crudding with ColdFusion

19


PL

Crudding with ColdFusion

19


10.

References



10.1.

Books


Danesh & Motlagh, Mastering ColdFusion 4.5, Sybex. This weighty tome has much to commend it.



10.2.

Web
-
sites



ColdFusion Forum

at Macromedia
PL

Crudding with ColdFusion

20


PL

Crudding with ColdFusion

20


11.

Self
-
Test


1.

What is a Recordset?

2.

How do you connect to a server?

3.

How do you create a Recordset?

4.

How do you send the information from a Recordset to a database?

5.

How do you call one cfm page from another cfm pa
ge?

6.

How can values be passed between cfm pages?

7.

Why might using a URL parameter not be the most secure method of answering 6?

8.

What tag do we use to run some SQL against our Oracle database?

9.

Which tags allow us to process conditionally?

10.

When a user deletes
a row is the change reflected automatically in the database?

11.

How do you find out how many rows there are in a recordset?

12.

How do you find out if a CF variable is defined?

13.

What tag would you use to alter the heading of a Grid column?

14.

What is a DSN?

15.

How are
DSNs set up?

16.

Dreamweaver MX makes much easier the production of much of the code that we have produced
here. Discuss whether this lecture has been a waste of time.

PL

Crudding with ColdFusion

21


PL

Crudding with ColdFusion

21



12.

Solutions to b Activities


Solution 2b:












Solution 3b:




















<HTML>


<HEAD>


<TITLE> CF Page </TITLE>


</HEAD>


<BODY>

HELLO WORLD


</BODY>

</HTML>



<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery name="joblist" datasource="shu
10g
" username="cmspl4" password="murph1">

select distinct job
from
emp

</cfquery>

<html>

<head>


<title>Employees</title>

</head>

<body>

<cfoutput query="joblist">

<DIV align=center>#job#

</DIV>

</CFOUTPUT>

</body>

</html>


PL

Crudding with ColdFusion

22


PL

Crudding with ColdFusion

22

Sol
ution 4b1:
























Solution 4b2:
























<!DOCTYPE HTML P
UBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery name="joblist" datasource="shu
10g
" username="cmspl4" password="murph1">

select distinct job from
emp

</cfquery>

<html>

<head>


<title>Employees</title>

</head>

<body>

<cfoutput query="joblist">

<DIV>

<li>

<a href="Act4b2.cfm?sel_job=#job#">

#job#</a>

</DIV></li>

</CFOUTPUT>

</body>

</html>

<!DOCTYPE HTML PUBLIC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfquery nam
e="empdet" datasource="shu
10g
" username="cmspl4" password="murph1">

select empno, ename from
emp

where job='#url.sel_job#'

</cfquery>

<html>

<head>

<title>Employees work as selected job</title>

</head>

<body bgcolor="silver">

<cfoutput>

<!
---

need to use c
foutput to display the value in sel_job
---
>

<b>All employees in the role of #url.sel_job#:</b>

</CFOUTPUT>

<!
---

now the repeated output
---
>

<cfoutput query="empdet">

<P></P>

REF: #empno#, NAME: #ename# <BR>

</CFOUTPUT>

</body>

</html>

PL

Crudding with ColdFusion

23


PL

Crudding with ColdFusion

23



Solution 7bii template one:












































<!DOCTYPE HTML PUBL
IC "
-
//W3C//DTD HTML 4.0 Transitional//EN">

<cfqu
ery name="CDs" datasource="shu10g
" username="cmspl4" password="murph1">


select Catalogue_Number, Title, Daily_Cost


from CD

</cfquery>


<html>

<head>

<title>CD Masterfile</title>

</head>


<body bgcolor="sil
ver">

<b>All CDs in the Collection</b>

<P></P>


<cfform name="GridForm" action="Act7b2.cfm" method="POST" enablecab="Yes">

<!
---

CFGRID with all setting as default
---
>


<cfgrid name="CDGrid" align="MIDDLE" query="CDs" insert="No" delete="No" sort="No"




colheaders="Yes" colheaderalign="LEFT" colheaderbold="Yes"
selectmode="ROW">




<CFGRIDCOLUMN NAME="Title" HEADERBOLD="Yes" Width="150">



<CFGRIDCOLUMN NAME="Daily_Cost" HEADERBOLD="Yes" HEADER="Cost
per Day" DataAlign="Right" Width="80">



<CFGRID
COLUMN NAME="Catalogue_Number" HEADERBOLD="Yes"
Display="No">


</cfgrid>

<INPUT TYPE=submit value="Add to Basket"></p>

Select a CD to add to basket

</cfform>

</body>

</html>

PL

Crudding with ColdFusion

24


PL

Crudding with ColdFusion

24

Solution 7bii template two:























<HTML>

<HEAD>


<TITLE>Delete the selected CD</TITLE>

</HEAD>

<BODY>

The create should look like this:<BR>

create table SELECTIONS (cd_id integer);


<cfoutput>


<H3>Adding

#form.CDGrid.Title# to the selection table</H3>

</cfoutput>

<cfquery name="CDaddto" datasource="shu
10g
" username="cmspl4" password="murph1">


INSERT into SELECTIONS values(#form.CDGrid.Catalogue_Number#)

</cfquery>


</BODY>

</HTML>




PL

Crudding with ColdFusion

25


PL

Crudding with ColdFusion

25



13.

Appendix A


Scripts for creating Oracle table for cha
pter 7 onwards.



1
. Create table:


Create Table CD( Catalogue_Number Integer Primary Key,


Title Char(30) NOT NULL,


Daily_Cost Number(5,2) ,


Constraint CD_cost CHECK (Daily_Cost Between 0 and 100) )
;



2.

Insert Data



INSERT INTO CD Values(22,'Moody Blues Greatest Hits', 1.00) ;

INSERT INTO CD Values(23,'Hits of the 60s',1.20) ;

INSERT INTO CD Values(24,'Hits of the 70s',1.20) ;

INSERT INTO CD Values(25,'Hits of the 80s',1.50) ;

INSERT INTO CD Values(2
6,'Hits of the 90s',1.50) ;

INSERT INTO CD Values(27,'ELO Greatest Hits',1.00) ;

INSERT INTO CD Values(28,'Worlds Greatest Opera Arias',0.75) ;

INSERT INTO CD Values(29,'Rock Anthems',1.50) ;

INSERT INTO CD Values(30,'Solo Piano Greats',1.50) ;

INSERT INTO

CD Values(31,'Dire Straights Greatest Hits',1.25) ;