ColdFusionChapter 6: Advanced Database Features

Chapter 6

Advanced Database Features

Overview

Chapter 6 introduces advanced database skills that will enhance the student’s ability to create more intricate and organized output. The integration of many SQL commands such as aggregate functions, as well as the Group/Order By attribute, will help the students to create more structured and useful views of data. Within a group of queries that may be interdependent, if not all queries are able to successfully update the database; the <cftransaction> tag can be used to prevent any of the changes from being committed to the database.

Adding to the repertoire of building modular applications, the chapter continues by visiting stored procedures in both server based database programs as well as creating and using stored procedures in file based applications such as MS Access. Creation of dynamic SQL statements is demonstrated in an example that allows the user to enter the criteria which is subsequently used within a query to return records matching the value that was specified. The final part of the chapter explains when and how to use the date conversion functions such as CreateODBCDate so that the expected format will be used when maintaining date values in a database.

TeachingTipsandStrategies

At this point, the students will have the skills required to create a complete database maintenance application that will allow them to practice creating dynamic SQL, setting up datasources, and reinforce skills learned in previous chapters. Using either a simple database that you create, or a database the students create that will have one table to contain information such as databases or a collection of some sort – CDs or Books for example.

Have the students create the following templates:

  • Main Template – lists records from a table (Book title for example)
  • Next to each record, create links for editing and deleting the record. You will have to send a URL parameter containing the key field value to the action page so that your query can modify or delete only the corresponding record as well as a URL variable to specify the action requested (“Edit” or “Delete”).
  • Be sure to create an Add link to this page as well which will not send any URL parameters.
  • Add/Edit/Delete Form Template
  • If no URL parameters are sent, create empty local variables for each of the form fields (this means the user is adding a new record).
  • If Edit or Delete is specified in the URL parameter, run a query to retrieve the corresponding record using dynamic SQL and set local variables to contain these values.
  • Be sure to create a local variable in each of the three cases that stores the button text for the submission of the form. You will want to make sure to make each one unique so that you can differentiate what action should be performed on the action page.
  • Create the form using the local variables in the Value parameter of the form field.
  • Create a hidden input field that stores the primary key sent from the main page if edit or delete.
  • Action Template
  • Retrieve the value of the submit button created on the form page and determine if the value is “Add”, “Edit”, or “Delete”
  • If Add – create SQL INSERT to insert form values to database
  • If Edit – create SQL UPDATE to update values for the corresponding record in the database
  • If Delete – create SQL DELETE to delete the corresponding record in the database

Once the appropriate action is completed, have the students browse the main page again to view changes to the data.

LectureNotes

I.SQL Aggregate Functions

A.Count – returns the number of rows in a column

B.Min – returns the smallest value

C.Max – returns the largest value

D.Avg – returns the average of values in a column

E.Sum – adds all the values in a column

F.Use an alias to access the results of an aggregate function

II.SQL Group By/Order By commands

A.Group By sorts the results on one or more fields in many server-based database systems. Order By gives the same result in MS Access.

B.In MS Access, Group By works only in conjunction with an aggregate function.

III.Transaction Processing and Database Security

A.The <cftransaction> tag is used in ColdFusion to:

1.Keep the database from being updated unless all queries can be completed.

2.Provide a locking mechanism for the records in the table.

B.<cftransaction> uses the following attributes:

1.Action

a)Begin – marks the starting point

b)Commit – commits pending queries

c)RollBack – rolls back pending queries

2.Isolation

a)Serializable – exclusive locking allowed

b)Repeatable Read – allows ability to insert data by other SQL statements

c)Read Committed – both read and insert data

d)Read Uncommitted – readers can see changes prior to commit

IV.Stored Procedures – a query created previously and called when necessary.

A.Database access is minimized which speeds up processing time.

B.Allows the same query to be used in many different applications.

C.Promotes Modular and Team programming.

V.Dynamic SQL

A.Create parameter-based queries by allowing the user to supply the value that will be used as the criterion value in the query.

VI.Working with Dates

A.CreateODBCDateTime() ColdFusion function may be used to convert dates entered by the user into a format that is understand by the database.

Review Questions

Answers:

1.These SQL functions are performed on a column in a table and the result returned is a single value for each function. The method of using Aliases for field names or Column names can come in very handy especially when the field names in question are cryptic or lengthy. Once you create an alias, you will be able to use the alias instead of the original field name. For example, in your output tag you could say

<cfoutput #minPrice#>

where the alias minPrice stands for the minimum value in the column UnitPrice

2.Aggregate functions are performed on a column.

3.Any function returns one result.

4. The SQL Group By command allows you to organize and categorize records in the table based on a certain criterion (value in a column). Grouping enables the presentation of data returned by the query in an organized manner. Group By works well with server-based database systems like ORACLE or SQL SERVER

5. Transaction processing is the process of grouping together a set of queries sent to the database via the server. This process serves two main purposes:

  1. To ensure that a group of queries succeed or fail in totality. The benefit here is that the database will not be altered until all steps required to be completed are completed, thus maintaining the integrity of the database.
  2. To provide a mechanism for locking the records of the table when the table is being used, thus not allowing multiple users to manipulate and modify the data at the same time.

6. CFTRANSACTION tag has two optional attributes:

  1. Action (this attribute has three options)
  2. Begin (is the default value, marks the starting point of the block of code to be executed in sequence)
  3. Commit (commits the set of pending queries or code segments to be executed)
  4. RollBack (restores the state that existed before the set of pending queries or code segments were executed)
  5. Isolation (this attribute supports four levels)
  6. Serializable (highest level – allows exclusive locking, nobody other than the current user can modify the data being read)
  7. Repeatable Read (allows ability to insert data by other SQL statements)
  8. Read Committed (allows ability to both read and insert data)
  9. Read Uncommitted (similar to SQL commit, but readers can see the changes prior to commit)

7. ColdFusion cannot directly lock a database, but it provides a method via the CFTRANSACTION’s isolation attribute. This attribute supports four levels:

  1. Serializable (highest level – allows exclusive locking, nobody other than the current user can modify the data being read)
  2. Repeatable Read (allows ability to insert data by other SQL statements)
  3. Read Committed (allows ability to both read and insert data)
  4. Read Uncommitted (similar to SQL commit, but readers can see the changes prior to commit)

8.No, All ColdFusion tags within a transaction must use the same datasource.

9.The Cascade DELETE option must be enabled in MS Access on the relationships between tables to ensure that all related records in different tables do get deleted when you issue a DELETE SQL command via ColdFusion.

10.A Stored Procedure is just a query created earlier and called when necessary. Stored procedures have a number of benefits such as:

  1. Avoid traffic via the network and provide speed of execution: since the query is already built and saved in the database, database access is minimized, thus the processing time speeds up.
  2. Enables Reuse: once created, stored procedures allow for the concept of reuse which is a very crucial component of efficient and good programming.
  3. Promote Modular and Team programming: since stored procedures can be thought of as individual blocks, they can be developed as modules where each module can be developed by different members of the team thus benefiting from the advantages of modular and team programming concepts.

11.The SQL command sent to the server is said to be dynamic because the database will not know the SQL statement being sent to it until runtime. This is an important concept in all web based programming technologies such as ColdFusion, ASP, PHP etc. because these technologies allow the user to send queries to the sever on the fly and get the results on the browser.

12.An electronic shopping cart is an excellent example because the application must check the quantity in stock each time a user requests a product by sending a query to the database, and depending on the result returned by the query, the customer is allowed to proceed or gets a message “out of stock.”

True/False

Answers:

1.False

2.False

3.True

4. True

5. True

Programming Exercises

Answers:

1.See CH06_EX01A-SOL.CFM for the action page and CH06_EX01F-SOL.CFM for the form page

2.See CH06_EX02-SOL.CFM

3.See CH06_EX03-SOL.CFM

4.See CH06_EX04-SOL.CFM

Project Building Exercises

Answers:

1.See ShoppingCart.cfm template

CF_IM06-1