ColdFusion Class Notes for 11/22/1999
Setting up the
Datasource Properties for a Database
When creating a new database for use on the web server, it is
necessary to set up the datasource properties on the CF server. This is accomplished by doing the following:
1.
Go
to the following URL http://207.196.87.5/CFIDE/Administrator/index.cfm
2.
Type
in the password which is “password”.
3.
Click
on the “ODBC” link under the Server category.
4.
In
the “Datasource Name” field, type the name that you want to use to reference
your datasource.
5.
Click
on the ADD button.
6.
Type
in a description of your database.
7.
Click
on the browse key next to the “Database File” field in order to find database
the datasource will reference (usually it is located under the C: drive).
8.
Click
on the "CF Settings" button located in the lower, right-hand corner
of the page.
9.
Make
sure the “Maintain database connections
- Check this” box is NOT checked. (This will make it easier to FTP new MDB
files to the server).
Incrementing
Unique ID’s after Inserting Data into a Database
1.
Using
an AUTONUMBER field for the ID. This
data type allows the ID to be automatically assigned or incremented when a new
record is inserted into that particular table.
Therefore, one can simply insert the new values for the other fields in
the record to be added using an INSERT INTO query as follows:
<CFQUERY datasource = “databasename” name = “queryname”>
INSERT INTO tablename (fieldname1, fieldname2. . . fieldnameN)
VALUES (value1, value2 . . . valueN)
</CFQUERY>
Although the ID in this case will not be
explicitly assigned, MS Access will automatically assign a new ID to this
additional record. The main problem
with using autonumber fields for ColdFusion applications (and database
applications in general) is that it is hard to find the value of the new ID
which is automatically assigned to the new record(s). Therefore, it is generally better to use the approach outlined in
number 2 below when assigning ID values to new records.
2.
Using
a Number field for the ID. In this case
the programmer or user must explicitly assign a new, unique ID to the added
record.
<CFQUERY datasource = “databasename” name = “queryname”>
SELECT MAX(idfield) + 1 AS newid FROM tablename
</CFQUERY>
<CFQUERY datasource = “databasename” name = “queryname”>
INSERT INTO tablename (idfield, fieldname1, . . . fieldnameN)
VALUES (#queryname.newid#, value1, . . . valueN)
</CFQUERY>
This example selects the current maximum value of the
ID field and set the value of the new ID to one greater than that maximum. The second query then inserts this new
record with the newly assigned ID value into the table. The problem with this scenario, is that it
fails when no records exist in the table initially. In such a case, the maximum value for the ID field would be Null,
and hence the new ID value would likewise be set to Null, rendering the INSERT
query impossible. To avoid this
problem, a CFIF statement can be used to check the maximum ID value, and to set
it to zero if there are no records in the table. This is shown in the code below:
<CFQUERY datasource = “databasename” name = “queryname”>
SELECT MAX(idfield) + 1 AS newid FROM
tablename
</CFQUERY>
<CFIF queryname.recordcount
is 0>
<CFSET newid2 = 0>
<CFELSE>
<CFSET newid2 = queryname.newid >
</CFIF>
<CFQUERY datasource = “databasename” name = “queryname”>
INSERT INTO tablename (idfield, fieldname1, . . . fieldnameN)
VALUES (#newid2#, value1, . . . valueN)
</CFQUERY>
In a production site you may also want to use
<CFTRANSACTION> arround the above code or put both SQL statements in a
stored procedure.
Using the
<CFINCLUDE> Tag
This tag references another .html, .txt, or .cfm file which
contains text, bitmaps, or other HTML code that will be incorporated in the
current page where the tag resides. It
is advantageous to use such a tag to reference a file which contains items that
will be included on multiple pages within a web site. Often the template file contains header, footer, bitmap, or
background information which needs to appear on multiple pages. Therefore, if the background or company logo
needs to be updated or modified this can be done only once in the template
file, and all the pages referencing the file in a <CFINCLUDE> tag will
automatically be updated. An example of
using this tag is shown below, where “header.cfm”
is a file containing header information to be contained on all the web pages
for a particular project, and similarly, “footer.cfm“
contains footer information to be carried across a group of pages.
<HTML>
<BODY>
<CFINCLUDE TEMPLATE = “header.cfm”>
additional
HTML code for the contents of this page . . .
<CFINCLUDE TEMPLATE = “footer.cfm“>
</BODY>
</HTML>
Setting up Web
Page Security
When developing web pages, one can turn the “debug” feature on
in order to view details about the processing of the web page. One of the pieces of information displayed
is the IP number of the computer from which you are working. This number is found under the “REMOTE_HOST”
listing from the debug information for a web page you have developed and can be
used to provide security for your web pages.
Below are three examples of different ways to handle security using this
IP address:
1.
Only
allowing entry to the page from a particular computer. In this instance one uses the exact and full
IP number for the computer which can access the page.
a)
In
this first case, when someone from a different computer, and therefore a
different IP number, tries to access the page, the text “Private Page! Access is denied.” is displayed and the page
stops loading (<CFABORT>).
<CFIF cgi.remote_addr is not “IPnumber”>
Private
Page! Access is denied.
<CFABORT>
</CFIF>
b) In this second case, rather than displaying a
warning message and then aborting the page, the user is redirected to a
different page (<CFLOCATION>).
Furthermore, the user will not be able to use the ‘Back’ button to try
to return to the private page either.
<CFIF cgi.remote_addr is not “IPnumber”>
<CFLOCATION url="webaddress">
</CFIF>
2.
One
can also allow only IP numbers containing a certain sequence of numbers to
access a page. This would allow users
at a particular location, but perhaps working from different computers to gain
access to the web page. This is
accomplished by using the keyword, “contains” as shown below:
<CFIF cgi.remote_addr CONTAINS “partofIPnumber”>
<CFLOCATION url="webaddress">
</CFIF>
<CFIF cgi.remote_addr CONTAINS “partofIPnumber”>
Private
Page! Access is denied.
<CFABORT>
</CFIF>
Using a Delete
Confirmation Page
This page is simply used to confirm that a user wants to delete
a certain record which they have selected from another page, before actually
performing the delete. It acts
similarly to a dialogue box that asks the user to confirm their decision to
delete a file. In this case, rather
than having a link from a select page which directly points to another page
which performs a DELETE query, the link from the select page points to the
confirmation page, which then displays the record the user has chosen to
delete. The user can then confirm their
decision by clicking on a link which goes to a page where the DELETE query is
performed, or can cancel their decision and return to the select page.
Note: for this intermediate page to work correctly it is
necessary to both pass the unique id of the record to be deleted to the page
and for the intermediate page to in turn pass this id to the action page that
does the SQL delete on the record. This is because web pages are stateless and
don't know variables unless you pass them in!
The following code
demonstrates how this works:
Select Page – displays records in table, and a link to a
delete confirmation page for each record
<CFQUERY datasource="databasename" name="queryname">
SELECT IDfieldname, fieldname1, fieldname2,
… fieldnameN
FROM tablename;
</CFQUERY>
<CFOUTPUT>
<center> The tablename contains the following
records: </center>
<br><br>
<table
align="center">
<tr>
<td> fieldname1 </td>
<td> fieldname2 </td>
….
<td> fieldnameN </td>
<td> Delete Link
</td></tr>
</table
<br><br>
<CFLOOP query="queryname">
<table
align="center">
<tr>
<td
align="left"># fieldname1#</td>
<td align="left"># fieldname2#</td>
….
<td align="left">#fieldnameN#</td>
<td> <a HREF = ”deleteconfirmationpage.cfm?linkvariablename=#IDfieldname#"> Delete:
Click Here to Delete this Record </a></td>
</tr>
</table>
</CFLOOP>
</CFOUTPUT>
Delete Confirmation Page – displays record from select page
which the user chose to delete
Are you sure you want to delete the record shown
below?
<CFQUERY datasource="databasename" name="queryname">
SELECT IDfieldname, fieldname1, fieldname2,
… fieldnameN
FROM tablename
WHERE IDfieldname=#url.linkvariablname#";
</CFQUERY>
<CFOUTPUT>
Field1: #queryname.fieldname1#
Field2: #queryname.fieldname2#
….
FieldN: #queryname.fieldnameN#
<br><br>
<a href="deletepage.cfm?linkvariablename=#queryname.IDfieldname#"> Yes </a> <br>
<a href="selectpage.cfm">
No </a>
</CFOUTPUT>
Delete Confirmation Page – deletes the record
<CFQUERY datasource = “databasename” name = “queryname”>
DELETE
* FROM tablename
WHERE IDfieldname=#url.linkvariablname#";
</CFQUERY>
TOP
