home Links Articles Books Past Meetings Photos SiteMap
The MDCFUG is sponsored by TeraTech. Visit us at www.TeraTech.com

Please send
comments/questions to

michael@
teratech.com

 

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>

 

 


Home | Links | Articles | Past Meetings | Meeting Photos | Site Map
About MDCFUG | Join | Mailing List |Forums | Directions |Suggestions | Quotes | Newbie Tips
TOP

Copyright © 1997-2017, Maryland Cold Fusion User Group. All rights reserved.
< >