Files Databases cfmx SQL CFSQL Opinion INSERT SELECT UPDATE DELETE ->> PROGRAM - VOTERS

Session 4: Web database for opinion polls

Files and databases

In Session 3 we used the tag CFFILE, which permits storing and retrieval of files on disk when needed. However, the files created by CFFILE can only be written , appended , read or deleted . If any operations on individual records, such as conditional modifications or retrieval of records, are needed, the file has to be read into the memory before it can be processed and then re-written. Modern databases can store complex collections of data, and flexible methods for inserting and retrieving data exist.

Databases

This is not a course in databases, but databases are frequently needed in connection with web applications. We shall limit our discussion in this session to how an established database can be used to serve the needs of web applications by means of the SQL language included in CFML . An established database includes one or more named tables . Each table has one or more named columns , and zero or more identifiable rows with column data.

The connection between a web applications and the database we shall use, is through Java Database Connectivity , JDBC , drivers which permit interrogations and updating of a database by means of SQL statements. In some cases, the JDBC driver will also have to cooperate behind the scene with ODBC drivers.

Databases, which can be used as a back-end to ColdFusion applications, are limited to those for which adequate drivers exist for the operation system/hardware platform used. For Windows XP/2000 , on which this course is based, drivers for MS Access , Excel , MySQL , are included. Drivers developed by others exist, however, for a number of other databases/platforms. More efficient native drivers for DB2 , Informix , Oracle and Sybase are available from Macromedia to be used in connection with the CFMX Enterprise version.

In this course, we use a MS Access database ,. The database used has no significance for the functionality discussed. The MySQL database can be downloaded free and is an excellent alternative.

An installed and specified database must be named as a datasource , and registered with the CFMX Administrator as an accessible database. The name is usually some shortcut, and in this course, the datasource name used is db . If the database corresponding to a datasource name exists, the database may be extended with the tables needed. Registering the datasource with the CFMX Administrator means that it has to be listed as a datasource known to the CFMX system. The registering of a datasource should be done with your CFMX Administrator . If you installed CFMX with the standalone web server, the URL of the Administrator is http://localhost:8500/CFIDE/ADMINISTRATOR . If you use another web server, it is the same without the port address " :8500 ".

CFMX SQL

ColdFusion opens for interaction with a data base by means of a special pair of tags, CFQUERY and /CFQUERY , between which the SQL statements are inserted. The tag syntax is:

<CFQUERY NAME="myquery"  DATASOURCE="#session.datasource#" >

SQL statements

</CFQUERY>

The NAME is a useful reference to the output from a database transaction if needed in other places of the current or other templates. We shall see examples later on. The only requirement is that a unique name is chosen. The DATASOURCE parameter specifies which database is relevant, and in our course the name is db, which implies that a <CFSET session.datasource="db"> must be set,usually as part of the Application.cfm template.

Basic SQL elements

We consider 4 SQL statements:

• SELECT
• INSERT
• UPDATE
• DELETE

We shall discuss applications of each type in detail below.

Within each statement, a number of clauses can be specified of which some are restricted to use with only one or two of the SQL commands. The list of the most commonly SQL clauses in alphabetic order is:

• FROM
• GROUPED BY
• HAVING
• INTO
• JOIN
• ORDERED BY
• SET
• VALUES
• WHERE
• UNION

For construction of compound statements , SQL provides also a large set of operators:

• = : equal to
• <> : not equal to
• < : less than
• > : greater than
• <= : less than or equal to
• >= : greater than or equal to
• + : plus
• - : minus
• / : divided by
• * : multiplied by
• AND : both conditions must be true
• OR : one or both conditions must be true
• NOT : ignores a condition
• IS [NOT] NULL : value is [not] null
• IN : value is within a list
• BETWEEN :value is in the range between two values
• LIKE : value is like a % or _ value
• EXISTS : tests for a non-empty set

In addition to the datasource name, you must also know the name of the table , the columns and rows from which you want to retrieve data or add data. Table, column and row specifications will be discussed in the next section.

Opinion polling

A polling institute is the scenario for the service we shall use to explain the SQL statements.

The institute investigates changing opinions among 5 competing brands of a certain commodity. The organization uses a rotating panel of voter who are interviewed about their preferences each week. The panel must include a certain number of voters to provide significant results. For each voter, name, telephone, age and home area is recorded for the establishment of the panel. Each Monday , the panel voters are interviewed about their opinions about the preferred alternative among the 5 competing products. For each interviewed panel voter, name, telephone number, and age may also change and are checked. The updating takes place on Wednesday . Each Thursday , statistics are generated from the records and made available, and each Friday , one fraction of the panel is substituted with new voters. Figure 1 shows the processes in the system.

In this session, we assume that the db datasource has a specified table named Voters The box at the left hand in Figure 1 shows the columns of the table.

A link to a complete demonstration of the application is available at the end of the session. As usual, the application starts with an Application.cfm .

1. <!--- Application.cfm --->

2. <CFAPPLICATION NAME="database"

3. SESSIONMANAGEMENT="yes"

4. SESSIONTIMEOUT=#CreateTimeSpan(0,0,30,0)#>

5 . <CFSET session.datasource="db">

In this Application.cfm the cookies used in the example of the last session, are not needed, but a CFSET tag for setting the session.datasource variable has been added.

A menu, ( Figure 2 ), implemented by the index.cfm template introduces the application. This is an ordinary HTML type of form template with simple hyperlinks for the different options. We could have named the file index.htm as well.

1. <!--- index.cfm --->

2. <h2><font color="Blue">Opinion polls</font></h2> <p>The Opinion polls system is <font color="Red">initialized</font> with a sample of panel voters. Each Monday a <font color="Red">List</font> of panel voters to be interviewed is generated. After the interviews, a table with data for each of the panel voters is <font color="Red">updated</font>. The table is the basis for computing <font color="Red">statistics</font> for the week. At the end of the week, the first panel voter on the list is <font color="Red">deleted</font>, and a new voter <font color="Red">added</font> at the end of the list.</p>

3. <ol>

4. <li><a href="form.cfm">Initialize</a> table of panel voters</li>

5. <li><a href="list.cfm">List</a> panel voters for interviews</li>

6. <li><a href="retrieve.cfm">Update</a> table of panel voters after interviews</li>

7. <li><a href="Compute.cfm">Compute</a> statistics for the week</li>

8. <li><a href="form2.cfm">Delete</a> first and add new panel voter</li>

9. </ol>

and needs no further comments.

INSERT statements

It is not our concern to identify and recruit a representative panel of persons willing to serve on the panel. We simply assume that the required number of persons have been recruited and information have been collected about their names, telephone number, age and area in which they live. Votes will be collected later by interview.

To establish the list of panel voters, a form template will be needed to record and for transcribing collected information to a table in the database. The form can look like Figure 3 and the template like the following:

1. <!--- form.cfm --->

2. <h2><font color="Blue">Form to be used for establishing the interview panel</font></h2>

3. <form action="add.cfm">

4. <p>Family name:<input type="text" name="FamilyName"></p>

5. <p>First name:<input type="text" name="FirstName"></p>

6. <p>Telephone no:<input type="text" name="Telephone"></p>

7. <p>Age(18-100):<input type="text" name="Age"></p>

8. <p>Area (1-10):<input type="text" name="Area"></p>

9. <p><input type="submit" value="Submit new panel voter"></p>

10. </form>

As you see from Line 3 in the template, the form requests a template named add.cfm when it is submitted for further processing. The form.cfm template also uses ordinary HTML tags, and could as well have been named with a .htm extension. However, we use consequently the .cfm extension.

The purpose of the add.cfm template is to append a new row with values to a database table called Voters . The panel voters need to be assigned a unique Id number, and we start by introducing the mechanism needed for this in Lines 2-5. The core in this is the variable application.id which keep track of the last identification number assigned. The qualifier application makes the variable persistent within the application, in other words, the service will keep the number in mind from session to session.

The tag in Line 2 tests if the application has been active before and the application.id variable has been defined. If not, the variable is defined in the next line and assigned value "0". If the application has already been used the control is transferred directly from Line 2 to Line 5, and the value of the identification variable is increased by "1", i.e. the first time the identifier variable is assign the value "1", next time the value "2", and so on.

1. <--- add.cfm --->

2. < CFIF NOT IsDefined("application.id")>

3. < CFSET application.id="0">

4. < /CFIF>

5. < CFSET application.id=#application.id#+1>

6. <CFQUERY NAME="add" DATASOURCE="#session.datasource#">

7. INSERT INTO Voters(Id,FamilyName, FirstName,Telephone,Age,Area,Vote) VALUES('#application.id#','#FamilyName#','#FirstName#','#Telephone#','#Age#','#Area#','-')

8. </CFQUERY>>

9. <CFLOCATION form.cfm>

This template uses in Line 6 the QUERY tag with an INSERT INTO statement which specifies the name of the table, Voters , and all predefined column names of the table following in a parenthesis. It is extremely important that the list contains column names correctly spelled. The next attribute is VALUES with a parenthesis containing all the values which should be saved in a new row of the table.

Note 2 important details. for each specified variable, a value represented by the variable name enclosed by # before and after the name, for example #FamilyName# , is needed. If the variable is a text type, this should be indicated by single quotes before and after the name and the # 's, for example '#FamilyName#' . As you can see, we specify all values as text, because all variables were specified as text in when we defined the table in the database. The last value is '-' . The surrounding single quotes tells us that this is another text variable and the value of the variable Vote is set preliminary to "-" because the person has not yet been interviewed.

After all panel members have been appropriately recorded by means of this form, the polling can start.

SELECT statements

Monday morning the telephone interviewers need a list for calling the panel voters. This list can be retrieved by calling the template " list.cfm " which illustrate the use of the statement SELECT . The CFQUERY tag includes a NAME="list" which will be used below. The SQL statement SELECT is followed by a list of those variable which are required. The attribute FROM specifies from which table, Voters in our application, the variables should be retrieved. As for all SQL statements, the syntax is very strict. Only variable names specified in the named table are accepted.

1. <--- list.cfm ---

2. <CFQUERY NAME="list" DATASOURCE="db">

3. SELECT Id,FamilyName,FirstName,Telephone,Age,Area FROM Voters

4. </CFQUERY>

5. <h2><font color="Blue">List of panel voters<</font></h2>

6. <TABLE >

7. <TR>

8. <TD><b>ID</b></TD>

9. <TD><b>Family Name</b></TD>

10.<TD><b>First Name</b></TD>

11.<TD><b>Telephone</b></TD>

12. <TD><b>Age</b></TD>

13. <TD><b>Area</b></TD>

14. </tr>

15. <CFOUTPUT QUERY="list">

16. <TR>

17. <TD>#id#</TD>

18. <TD>#FamilyName#</TD>

19. <TD>#FirstName#</TD>

20. <TD>#Telephone#</TD>

21. <TD>#Age#</TD>

22. <TD>#Area#</TD>

23. </TR>

24. </CFOUTPUT>

25. </TABLE&

The content of the column Vote is excluded from the listing to avoid that the interviewer reminds the panel voters about their answers last week.

The second part of the template concerns the tabulation of the list. The TABLE tags used are well known from HTML which permits more elaborate tables with background, borders, etc. In this example, we keep it as simple as possible. Note that the results from the CFQUERY can be referred to in the CFOUTPUT tag by its name QUERY="list" . All the retrieved rows are considered as a Query Object with the NAME ="list". By referring to the attribute QUERY="list" in the CFOUTPUT tag, each retrieved row from the table Voters will be listed according to the table specifications. No loops are needed.

Figure 4 illustrates the output from this template

UPDATE statements

The third step is a process which permits updating of the panel table. For this we use the SQL UPDATE statement. Updating requires that all fields of the update form are populated. Since the fields FamilyName , FirstName , Telephone , Age and Area are usually unchanged, a direct use of the insert form used to create the initial table would require a large amount of unnecessary typing of redundant information. The solution is first to retrieve a list of Id's , and then updated with the changes needed.

The first template retrieve.cfm retrieves the unique ID of the current panel voters and produce a list with links to each individual row of the table:

1. <!--- retrieve.cfm --->

2. <cfquery name="retrieve" datasource="#session.datasource#">

3. SELECT Id FROM Voters

4. </cfquery>

5. <p><h2><font color="Blue">List of ID for updating</font></h2></p>

6. <CFOUTPUT QUERY="retrieve">

7. <TABLE>

8. <tr>

9. <td><a href="retrieve2.cfm?Id=#Id#">#Id#</a></td>

10. </tr>

11. </table>

12. </cfoutput>

This template, called from the menu, is extremely simple. The SQL has a SELECT statement, which retrieves only the ID from each row, and tabulates a list of ID 's ( Figure 5 ). The table has one small finesse , the ID 's are linked in order to retrieve wanted rows of Voters by a click if wanted. The reference used is " retrieve2.cfm?Id=#Id# " which refer to a second retrieval template, retrieve2.cfm , and the attribute Id=#Id#. Remember that #Id# is the value of the ID variable for the particular row considered. By attaching the name-value after the template name by ? , the value #Id# is made available to template retrieve2.cfm when this template is processed. The output is illustrated in Figure 5.

We now want to design a template, which returns an individual form with the old values for the selected panel voter to the client for updating changes in data of the voter. The task is solved, see Lines 2-4, by a CFQUERY to the #session.datasource# combined with a SELECT of all variables values from Voters for the panel voter with Id=#Id# . A pre-filled updating form is created by means of the query object named retrieve2 in the CFQUERY tag:

1. <!--- retrieve2 --->

2. <cfquery name="retrieve2" datasource="#sessionh.datasource#">

3. SELECT Id,FamilyName,FirstName,Telephone,Age,Area, Vote FROM Voters WHERE Id='#Id#'

4. </cfquery>

5. <cfform action="update.cfm" >

6. <cfoutput query="retrieve2">

7. <h2><font color="Blue">Update form for Id:#Id#</font></h2>

8. <table>

9. <tr><td>Family name:</td><td><input type="text" name="FamilyName" value="#retrieve2.FamilyName#"> </td> </tr>

10. <tr><td>First name:</td><td><input type="text" name="FirstName" value="#retrieve2.FirstName#"></td></tr>

11. <tr><td>Telephone:</td><td><input type="text" name="Telephone" value="#retrieve2.Telephone#"></td></tr>

12. <tr><td>Age:</td><td><input type="text" name="Age" value="#retrieve2.Age#"></td></tr>

13. <tr><td>Area:</td><td><input type="text" name="Area" value="#retrieve2.Area#"></td></tr>

14. <tr><td>Vote:</td><td><input type="text" name="Vote" value="#retrieve2.Vote#"></td></tr>

15. </table>

16. <input type="hidden" name="ID" value="#Id#">

17. </cfoutput>

18. <p><input type="submit" value="Update"></p>

19. </cfform>

Since the Id is permanently assigned to each panel voter, it cannot be updated and should not be included in the form, but it has to be attached and the returned values to get the correct line updated. This is solved by sending it to the client as a hidden variable as can be seen in Line 16. The form will be displayed with the old values on the client screen, and all values (except the Id value )can be changed according to the information received at the interview and with the vote added ( Figure 6 ).

When the form has been corrected and the update submitted, it calls a small template update.cfm template:

1. <!--- update --->

2. <cfquery name="update" datasource="db" >

3. UPDATE voters SET

4. FamilyName='#FamilyName#',

5. FirstName='#FirstName#',

6. Telephone='#Telephone#',

7. Age='#Age#',

8. Area='#Area#',

9. Vote='#Vote#'

10. WHERE ID='#Id#'

11. </cfquery>

12. <CFLOCATION url="retrieve.cfm">

This template demonstrate the CFQUERY tags with the statement UPDATE . The UPDATE statement specifies the table, Voters , which should be updated and use the attribute SET with a list of name-value pairs of the variables to be updated. The update refers to the row WHERE ID=#ID# . Also in the UPDATE statement, the different components must have exact names and acceptable values with no comma delimiter before the WHERE . Finally, by means of the CFLOCATION , the control is transferred to the retrieve.cfm template to produce the list and for selection of an another voter for updating.

Statistical report

When all received updates have been performed, the database table is ready for providing data for statistics of the week. In this example, we aim at very simple statistical report about the number of panel voters having preference for each of the 5 alternatives. The computation can easily be extended to computations of the preference frequencies by age and by area. The presentation of statistics requires 4 steps to be performed:

1. Defining frequency variables
2. Retrieving data
3. Computing statistics
4. Presenting results

The template compute.cfm takes care of these tasks:

1. <!--- compute.cfm --->

2. <cfset A="0">

3. <cfset B="0">

4. <cfset C="0">

5. <cfset D="0">

6. <cfset E="0">

7. <cfquery name="statistics" datasource="#session.datasource#">

8. SELECT vote FROM voters

9. </cfquery>

10. <cfloop query="statistics">

11. <cfswitch expression="#vote#">

12. <cfcase value="a"><cfset A=#A#+1></cfcase>

13. <cfcase value="b"><cfset B=#B#+1></cfcase>

14. <cfcase value="c"><cfset C=#C#+1></cfcase>

15. <cfcase value="d"><cfset D=#D#+1></cfcase>

16. <cfcase value="e"><cfset E=#E#+1></cfcase>

17. </cfswitch>

18. </cfloop>

19. <cfoutput>

20. <p><h2><font color="Blue">The votes this week:</font></h2></p>

21. <table>

22. <tr><td>

23. <b>Alternative</b>:</td>

24. <td><b>Frequency</b>:</td>

25. </tr>

26. <tr>

27. <td>A</td><td>#A#</td>

28. </tr>

29. <tr>

30. <td>B</td><td>#B#</td>

31. </tr>

32. <tr>

33. <td>C</td><td>#C#</td>

34. </tr>

35. <tr>

36. <td>D</td><td>#D#</td>

37. </tr>

38. <tr>

39. <td>E</td><td>#E#</td>

40. </tr>

41. </table>

</cfoutput>

After using CFSET to define a set of 5 aggregate variable all with values "0", a CFQUERY with SELECT of all variables in all rows of Voters retrieves the data from the database. It is followed by a CFLOOP block with a CFSWITCH and connected CFCASE statements to loop through all rows of the retrieved data from Voters, and to count the number of cases with preference for A, B, C, D and E, respectively.

When the looping has finished, tabulation of the five frequency variables and their values is done by means of a simple set of TABLE tags. Figure 7 shows the distribution of 3 panel voters we have used for this example.

CFMX has also included some powerful Graphing and Charting possibilities, which could be used for creating favourable alternatives to the statistical reporting used above.

DELETE statements

The final part of the system is to to make the system rotating , i.e. create a templates which add a new at the end of the list and delete the first voter of the list. The template called for this purpose from the menu is form2.cfm . It creates a form for filling in data for a new voter ( Figure 8 ):

1. <!--- form2.cfm --->

2. <h2><font color="Blue">Form to be used for deleting and adding voters to the panel</font></h2>

3. <h3>New voter data:</h3>

4. <form action="delete.cfm">

5. <p>Family name:<input type="text" name="FamilyName"></p>

6. <p>First name:<input type="text" name="FirstName"></p>

7. <p>Telephone no:<input type="text" name="Telephone"></p>

8. <p>Age(18-100):<input type="text" name="Age"></p>

9. <p>Area (1-10):<input type="text" name="Area"></p>

10. <p><input type="submit" name="NewMember" value="Submit new panel voter"></p>

11. </form>

When the form is completed and submitted, it calls upon template delete.cfm It starts by retrieving all ID values from Voters by means of a simple SELECT statement. The Id of the first row can e found in different ways. We use a CFLOOP statement with the attribute setting STARTROW="1" and ENDROW="12" which only extract the first Id which is defined as voter_out=#ID# . This variable is used in a CFQUERY with the SQL statement DELETE from Voters and for the row which has Id=#voter_out# which completes deleting the the first voter of the panel.

1. <--- delete.cfm --->

2. <cfquery name="retrieve" datasource="#session.datasource#">

3. SELECT Id FROM Voters

4. </cfquery>

5. <cfloop query="retrieve" startrow="1" endrow="1" >

6. <cfset voter_out='#retrieve.id#'>

7. </cfloop>

8. <cfquery name="delete" datasource="#session.datasource#">

9. DELETE FROM Voters WHERE id='#voter_out#'

10. </cfquery>

11. <CFSET application.id=#application.id#+1>

12. <cfquery name="add" datasource="#session.datasource#">

13. INSERT INTO Voters(Id,FamilyName, FirstName,Telephone,Age,Area,Vote)

14. VALUES('#application.id#','#FamilyName#','#FirstName#','#Telephone#',#Age#,'#Area#','-')

15. </cfquery>

16. <CFLOCATION url="form2.cfm">

The second part of this template adds the submitted data for the new panel voter at the end of the table by means of a CFQUERY and an INSERT statement. Note that the new value of ID is computed by the tag in Line 11.

In this section we have discussed some of the simplest and most basic SQL statements available in CF . In later sections we shall demonstrate some further features of the language available within ColdFusion MX .

Exercises

a. In this session, we have interacted with a database. You can develop complex applications interrogating local as well as remote databases, which frequently are the main cores of information systems. Read the Chapters 4 and 5 of RBB carefully and compare the example of this session with the text. See if you can point at possible improvements.

b. Try to copy this session's templates, implement them on your own server and run them.

c. In Session 3 you learned about the CFFILE tag. It is possible to by-pass the use of a database in the application discussed in this session using the CFFILE tag instead. Consider how you could re-write the templates in the opinion polls application using the CFFILE tag, and consider if it would be efficient if you had a panel of 10 000 voters.

d. Look through the the Chapters 1-10 and see if they can provide you with new ideas for preparing the project proposal you must submit before you can advance to the the next session.

e: Read Chapter 17 in RBB , and try to design a weekly graphical report on the votes.

Link to the session application example.

Link to the session test.

You are reminded to turn to the Assignment section, complete and submit your project proposal. You are required to both submit your proposal and pass the Test to obtain access to Session 5.

Link to the session assignment.

 

 

 

Other information:

Articles - Macromedia