The User Interface Graphics Repository (UIGR) Web Application tool, developed in the IBM Toronto Software Lab Media Design Studio, is used as an example to show the basic setup of a Coldfusion-DB2 application. The UIGR application allows designers, developers, information developers, and human factor professionals to conduct advanced searches on more than 10,000 visuals used in IBM software.
Figure 1. User Interface Graphics Repository (UIGR) Web application
After reading this article, you should be able to set up an environment using DB2 UDB as your back-end database, and Coldfusion as your application server, connect Coldfusion to DB2, become familiar with the tools in DB2 and Coldfusion that are often used, and understand how to access and manipulate your data through the Coldfusion language.
DB2 UDB Version 8.2 (JDK IBM 1.4.1)
DB2 Universal Database is the database management system that delivers a flexible and cost-effective database platform to build robust on demand business applications. DB2 UDB further leverages your resources with broad support for open standards and popular development platforms like J2EE™ and Microsoft® .NET. Get more information.
Macromedia ColdfusionMX Standard:
With ColdFusion MX, you can build and deploy powerful Web applications and Web services with far less training time and fewer lines of code than ASP, PHP, and JSP. Get more information about Macromedia.
Apache 2.0.48 Web server:
Apache has been the most popular Web server on the Internet since April of 1996. The October 2004 Netcraft Web Server Survey found that more than 67% of the Web sites on the Internet are using Apache, thus making it more widely used than all other Web servers combined. Get more information about Apache.
- Operating System used: Microsoft Windows® Server 2003 Standard Edition . Also available for Linux.
Apache is installed on your computer as the Web server. Coldfusion is installed on top of the Web server as the Application Server. DB2 is installed as the relational database. Once installed and configured, the Web server passes all Coldfusion file formats (*.cfm, *.cfml, *.cfc,) over to the ColdFusion Application Server for handling. Coldfusion interacts with the database if necessary and returns the parsed page back to the Web server and the Web server back to display within the browser.
Figure 2. Application setup and workflow
The recommended order of installation is to install the Web server first (in our example, Apache), followed by the database (DB2), and lastly the application server (Coldfusion). Although not a necessity, installing in the specified order will make the configuration easier, as Coldfusion will recognize Apache Web server and modify the configuration file to instruct any Coldfusion file formats to be passed to the Coldfusion application server for parsing. It will also recognize DB2 and, depending on the version of Coldfusion and DB2 you have, add the appropriate data driver to make connecting to the database as easy as selecting the connection type from a drop-down menu.
Apache:Download and install the Apache Web server according to the instructions given on the Apache Web site. The modifications to the configuration file are covered in the Coldfusion setup, below.
DB2 UDB: If you do not have DB2 UDB installed, a trial version is available from the data site. There are no special install details required to use Coldfusion. The UIGR application uses DB2 UDB V8.2. Once installed, you need to create at least one database and one table in order to create a connection and test to see if you can access data in the created table. There are a number of ways to register your database so that Coldfusion can see it. Two common methods are:
1. Use the Configuration Assistant tool in DB2
Available from the Windows start menu and from the DB2 Control Center, the Configuration Assistant tool will enable you to "Add Database Using Wizard" or modify connection properties for an existing database. Go through the wizard and then test your selected database. Here are the steps involved:
Figure 3. Registering an ODBC connection using DB2 Configuration Assistant
Choose your protocol based on your environment. In the UIGR application, DB2 was installed local to the server, so chose the Local setting.
Figure 3.1. Select your communications protocol
Select the instance where you created your database, and then select the database you want to register.
Figure 3.2. Select the instance and database to register
Supply a database name and alias. You will need to identify this alias name in the Coldfusion data source settings. In the next step, set up the connection to this database from Coldfusion.
Figure 3.3. Supply an alias name for your database
Register the database as a data soure to make it is available to ODBC applications.
Figure 3.4. Register your database as a data source
2. Register the database through your operating system
The second method to register your database so that Coldfusion can see it is to register your database through your operating system. In our case it is Windows, using the ODBC Data Source Administrator.
Select Add to add a new datasource, and select the driver and a database alias to register (Available from the control panel / Administrative Tools / Data Source).
Figure 3.5. Add your database alias
Figure 3.6. Select the driver for which you want to set up a data source
Figure 3.7. Select your alias for your DB2 database
Now that DB2 is set up and configured, the next step is to install and configure Macromedia Coldfusion.
Coldfusion MX: If you do not have Coldfusion MX installed, a trial version is available from the Macromedia Web site. The UIGR application uses Coldfusion MX server, Standard edition.
If you installed Coldfusion after installing Apache Web server, you will be prompted to locate your Apache httpd.conf configuration file. Ensure the following has been added to the base configuration for you. If not, you may want to uninstall and re-install in the proper order to ensure your Web server knows how to handle acceptable Coldfusion files. You can modify your Apache configuration file manually, but you may need to modify the code snippets below based on your environment (for example, the location where you installed Coldfusion may not be the same as shown in Listing 2.1).
Open your httpd.conf file located in the "conf" folder, where you installed Apache.
Locate these comments and lines of code:
Listing 1. Apache httpd.conf configuration file modifications
# # DirectoryIndex: sets the file that Apache will serve if a directory # is requested. # # The index.html.var file (a type-map) is used to deliver content- # negotiated documents. The MultiViews Option can be used for the # same purpose, but it is much slower. # DirectoryIndex index.html index.html.var index.cfm |
If your last line reads:
Listing 1.1
DirectoryIndex index.html index.html.var |
You will need to add "index.cfm" to this line, like shown in Listing 1.
Next, locate these comments and lines of code:
Listing 2.
#
# Bring in additional module-specific configurations
#
<IfModule mod_ssl.c>
Include conf/ssl.conf
</IfModule>
|
Coldfusion will add the following to this code to ensure your Web server passes the appropriate Coldfusion file formats to the Coldfusion application to handle. Remember, if you are modifying your configuration file manually, you will need to reflect the location settings according to where you installed Coldfusion. These areas are shown in italics.
Listing 2.1
#
# Bring in additional module-specific configurations
#
<IfModule mod_ssl.c>
Include conf/ssl.conf
</IfModule>
# JRun Settings
LoadModule jrun_module "D:/CFusionMX/runtime/lib/wsconfig/1/mod_jrun20.so"
<IfModule mod_jrun20.c>
JRunConfig Verbose false
JRunConfig Apialloc false
JRunConfig Ssl false
JRunConfig Ignoresuffixmap false
JRunConfig Serverstore "D:/CFusionMX/runtime/lib/wsconfig/1/jrunserver.store"
JRunConfig Bootstrap 127.0.0.1:XXXXX
#JRunConfig Errorurl <optionally redirect to this URL on errors>
AddHandler jrun-handler .jsp .jws .cfm .cfml .cfc
</IfModule>
|
Now that your Web server configuration is aware of Coldfusion, launch the Coldfusion Administrator and choose the Data Sources link from the left-hand navigation.
Provide Coldfusion with the data source name and an appropriate driver from the select box. Depending on the version of Coldfusion you are using, a DB2 driver (JDBC/ODBC) may be provided for you. The data source name provided here is a Coldfusion-specific alias to the registered ODBC data source you completed using the DB2 Configuration Assistant or the data source administration tool.
Figure 4. Coldfusion Administrator: Data Sources
Selecting the new data soure hyperlink in the "Connected Data Sources" table displays the properties of the new data source. Select the registered ODBC DSN (data source name) from the select box. You also need to provide authentication to the database. For our UIGR application, to authenticate to DB2 you need to enter the administrative profile username and password where you installed DB2. Verify that Coldfusion connects to the database by selecting the Verify All Connections button. The Status column of the table will show if you're successful. Review the other options available in the properties dialog, as they will determine the scope of database functionality you can achieve.
Figure 4.1. Coldfusion Administrator: Data Sources: ODBC Socket
Connecting to your DB2 database and manipulating data using Coldfusion
This section takes a look at a number of ColdFusion Markup Language (CFML) tags. If you are familiar with Hyper Text Markup Language (HTML), then you are already well on your way. The complete CFML language syntax can be found in the Coldfusion documentation on the Macromedia Web site.
Here are some basic code examples to help you understand how to interact with the database using CFML.
Application.cfm file: Every Coldfusion application needs to contain a file called the application.cfm file. The application.cfm file is executed each time a new page is loaded. It defines application-level settings and functions. Any variables written here are available to any page (Global). Here is an example from the UIGR application:
Listing 3. Application.cfm page
cfapplication name="DB2_UIGR" sessionmanagement="yes">
<!--- global variable for the database source name which was defined in the
coldfusion administrator --->
<cfset DSN = "CF_TEST2">
<!--- set login max to 20 minutes --->
<cfset timeOut = 20*60>
<cfset schemaName = "Administrator">
|
<cfapplication name="DB2_UIGR" sessionmanagement="yes">. To define each page of your application as part of one logical application, define a cfapplication name in the application.cfm file. Set the variable scope to limit variables to live only within a "session". You can see above that if there is no activity for 20 minutes, then a session will time out. If the user closes his browser window, the session also ends. This is important when your application requires users to log in to certain pages or specific features. Since the appliation.cfm file is executed on every page, the username and password variables would also be tested on every page, stopping unwanted users from typing in specific URL's to gain access without proper authentication.
<cfset DSN = "CF_TEST2">. It is always a good idea in the application.cfm file to set a variable for the Data Source Name, because you will need to reference it every time you make a query to your database. Defining a variable here makes it easy to switch to another data source.
<cfset schemaName = "Administrator">. Adding a variable for your database schema name is also a good idea if you anticipate ever changing the schema for a particular database. Again, setting it in the application.cfm makes it a global variable and accessible from any page in your application. If you change your database or schema name in DB2, update this variable once, in the application.cfm page, and then you are done.
Figure 5. DB2 Control Center: Table Details View
How to query the database and display the results back in the browser
Here is an example from the UIGR application. A Coldfusion page contains the same framework as your typical HTML page, but with the addition of Coldfusion (CF) tags that only the Coldfusion application server parses.
Listing 4. Query the database through CFML
<cfquery name="getBrand" datasource="#DSN#"> SELECT brand_id,brandname FROM UIGR_Brand ORDER BY brandname </cfquery> <cfquery name="getUIgraphictype" datasource="#DSN#> SELECT uigt_id,uigtname FROM UIGR_uigraphictype ORDER BY uigtname </cfquery> <BODY> <form action="results1.cfm" method="get"> <table> <tr> <td>Brand<br> <select name="Brand" size="7" > <cfoutput query="getBrand"> <option value="#brandname#">#brandname#</option> </cfoutput> </select> </td> </tr> </table> </form> </BODY> |
<cfquery name="getBrand" datasource="#DSN#">. This is the tag used in Coldfusion to pass queries or SQL
statements to the data source. The name attribute is given as an ID, so you can use the results of this query within the <cfoutput> tags. The datasource attribute identitifies the database where the query gets
the data. In the application.cfm file, this variable is declared to the data source in DB2 ,where our data is stored <cfset DSN = "CF_TEST2">. To reference Coldfusion variables in a Coldfusion file, you need to add #variablename# around the variable. Inside a <cfquery> tag, you pass SQL (Structured Query Language) statements to your database. DB2 executes the SQL and sends back the results. In the first example above, SELECT the "brand_id" and "brandname" columns FROM the "UIGR_Brand" table in the CF_TEST2 database. ORDER the returned results according to the "brandname" column in that table. By default, the "brandname" column will be in alphabetical order.
Now that <cfquery> has been written, output this data so you can see it within the browser. In Coldfusion, the <cfoutput> tag is used to display the results of a <cfquery>.
Listing 5. Output data results using CFML
<cfoutput query="getBrand"> <option value="#brandname#">#brandname#</option> </cfoutput> |
The query attribute tells Coldfusion which query record sets you want to output. In this case it makes available the records returned from the "getBrand" query you defined earlier.
<option value="#brandname#">#brandname#</option>. Whatever appears between the <cfoutput></cfoutput> tags will apply to each record that is returned. In this case, each brand name will populate inside a select box form field as shown in the highlighted area within the UIGR application screen capture below. There were three records returned from the "getBrand" query and each one has been assigned as an option value for the select form field shown below.
Figure 6. UIGR Dynamic Pre-filter select box
You can insert, update, delete, and filter your records the same way using the <cfquery> tags to send SQL statements to your database and then using the <cfoutput> tags to display the results of the query.
Following are some specific examples from the UIGR application.
Updating existing data in your database example:
Listing 6. Updating existing data
<cfif isDefined("Form.submit")>
<cfquery datasource="#DSN#">
UPDATE uigr_visuals
SET
name = '#Form.name#',
version = '#Form.version#',
description = '#Form.description#',
uigraphictype = '#Form.uigraphictype#',
filename = '#Form.filename#',
location = '#Form.location#',
brandname = '#Form.brand#',
toolname = '#Form.tool#',
productname = '#Form.product#'
WHERE id = #Form.productid#;
</cfquery>
<cflocation url="search2.cfm?sMessage=Successful:
Selected record has been updated in the database">
</cfif>
|
<cfif isDefined("Form.submit")>. When the user clicks the Submit button, the <cfquery> executes.
UPDATE uigr_visuals. The uigr_visuals table is the target database to update.
SET
name = '#Form.name#',. Set the value of the column "name" in the uigr_visuals table of our CF_TEST2 database, to the value the user entered in the text Form Field "Form.name".
WHERE id = #Form.productid#;. Make the updates to the columns listed where the id column value of the uigr_visuals table equals the value of the text Form Field "Form.productid".
Delete data in your database example
Listing 7. Delete existing data from the database
<cfquery datasource="#DSN#"> DELETE FROM uigr_visuals WHERE id = #Form.productid#; </cfquery> |
WHERE id = #Form.productid#;. Delete record where the value of the ID column in our uigr_visuals table equals the value of the text Form Field "Form.productid".
Inserting new data into your database
Listing 8. Insert new data into the database
<cfif isDefined("Form.submit")>
<cfquery datasource="#DSN#">
INSERT INTO uigr_users (employeenum,lastname,firstname,usergroup,username,password,
useremail,phonenumber,officelocation)
VALUES
('#Form.employeenum#',
'#Form.lastname#',
'#Form.firstname#',
'#Form.usergroup#',
'#Form.username#',
'#Form.password#',
'#Form.useremail#',
'#Form.phonenumber#',
'#Form.officelocation#');
</cfquery>
</cfif>
|
<cfif isDefined("Form.submit")>. When the user clicks the Submit button, the <cfquery> executes.
INSERT INTO uigr_users (employeenum,lastname,firstname,usergroup,username,password, useremail,phonenumber,officelocation).
Insert new records into the uigr_users table, in these columns, in this order.
VALUES ('#Form.employeenum#'). The values for each of the ordered columns above. In this case, each column value was entered by the user into a text form field. When the user clicks the Submit button, these values are entered into the database.
The capabilities of an application built using Coldfusion and DB2 UDB are more extensive than outlined in this introductory article. With the lack of published information regarding the use of Coldfusion with DB2, the intention of this article was to bring awareness to the subject and illustrate how easy it is to make powerful Web applications quickly with limited database and programming skills.
The DB2 UDB database is the workhorse behind any Web application or service built with Coldfusion. The relational data model and use of the SQL language allows for enterprise level performance and scalability. Data Administration tasks such as "Backup and Restore," "Indexing," and "Health Monitoring" allow you to keep your application running efficiently.
Coldfusion continues to expand its capabilities as a development technology. Because of its compatibility to run within a J2EE environment and .NET framework solutions, Coldfusion has the flexibility and tools to build powerful applications quickly. New features such as Flash forms generation, structured reports, dynamic generation of printable documents, SMS application gateway, and a host of others makes it a worthwhile consideration as an application development technology used on top of DB2 Universal Database.
| Description | Name | Size | Download method |
|---|---|---|---|
| Coldfusion CFM files used in the article | CFM_files.zip | 4KB |
FTP
 |
HTTP
|
Information about download methods
- Visit the IBM Information Management Web site to learn more about DB2.
- Visit the Coldfusion Web site to learn more about Macromedia Coldfusion and to read about new features, view tutorials, and participate in the Coldfusion community.
- Visit the developerWorks DB2
zone to learn more about DB2. You'll find technical documentation, how-to articles,
education, downloads, product information, and more.
- Visit the developerWorks
blogs to get involved in the developerWorks community.
David Budreau has worked for the Media Design Studio at the IBM Toronto Software Laboratory since 1999. His core responsibilities include user interface design and visual design for IBM DB2 Universal Database. Those familiar with DB2 UDB will recognize David's work within the Install and First Steps LaunchPads, DB2 Health Center, Client Configuration Assistant, User assistance tools such as wizards, Informational content such as the DB2 Try & Buy CDROM, product demonstrations and recently the DB2 Discovery CD-ROM. He has extensive experience in user interface design, Web and multimedia design, and authoring. He is the owner and creator of the User Interface Graphics Repository Web application tool.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
