Set up the sample database and ColdFusion data source

Time to complete: 15 minutes.

Many of the tutorials in the Flex and ColdFusion mini learning center use the same sample database with employee sales information for the Fictitious Sales Planner. In this section you set up the HSQLDB database engine and sample database, and create the ColdFusion data source.

Note: The following setup instructions assume that you have a local, standalone installation of the Developer's Edition of ColdFusion 8 Professional

Setting up the HSQLDB database engine

The following step show you how to set up the open source, lightweight HyperSQL Database (HSQLDB) engine.

We have chosen to use HSQLDB because it is easy to set up for use with a local ColdFusion environment and does not require additional downloads beyond the student files that accompany this tutorial:

1. Download the f4cf_getting_started_db_hsqldb.zip sample file if you haven't done so already.
2. Unzip the file on your computer and review the hsqldb_license.txt file.
3. Stop the ColdFusion server if it is running.
4. Move the hsqldb.jar file to the \wwwroot\WEB-INF\lib directory.

The HSQLDB.jar file is the database engine and will automatically be loaded when the ColdFusion server is started.

Setting up the sample database

In this section you will set up the F4CF_FictitiousSalesPlanner database instance in the HSQLDB engine. This database contains the employee sales data for the Fictitious Sales Planner sample application, which is used in other tutorials of the Flex and ColdFusion mini learning center:

1. Download the f4cf_getting_started_db_fictitious.zip sample file if you haven't done so already.

2. Unzip the file to the \wwwroot\WEB-INF directory and review the contents:

   • The \wwwroot\WEB-INF folder now contains a folder named db. This folder helps you keep your HSQLDB database instance files organized.
   • The db folder contains a subfolder named F4CF_FictitiousSalesPlanner. This subfolder contains the files for this database instance and also helps you keep your files organized.
   • The F4CF_FictitiousSalesPlanner folder contains two files named F4CF_FictitiousSalesPlanner.script and F4CF_FictitiousSalesPlanner.properties. These two files are read by the HSQLDB engine when the ColdFusion server starts up. The .properties file contains the database configuration properties and the .script file contains the SQL script statements that generate the database tables and data. The database is stored in the ColdFusion server memory and changes to the data are periodically written to a log file.
3. Start the ColdFusion server.

The HSQLDB engine should be initialized and the new F4CF_FictitiousSalesPlanner database instance should be created in the memory of your ColdFusion server.

Configuring the ColdFusion data source

In this section, you will use the ColdFusion Administrator to create the data source for the HSQLDB database instance:

1. Log on to the ColdFusion Administrator and create a new data source named F4CF_FictitiousSalesPlanner with a Driver type of other (see Figure 1).

   

   Figure 1. Create a new data source using the ColdFusion Administrator.

2. Click the Add button.

3. In the JDBC URL box, enter jdbc:hsqldb:file:yourlocalpath where yourlocalpath refers to the full path to the F4CF_FictitiousPlanner.properties file on your system. Leave the .properties extension off the file name (see Figure 2).

   For a standalone installation of ColdFusion for the PC, for example, copy and paste the following value:

   jdbc:hsqldb:file:C:\ColdFusion8\wwwroot\WEB-INF\db\F4CF_FictitiousSalesPlanner\F4CF_FictitiousSalesPlanner

   For a standalone installation of ColdFusion for the Mac, use the following value:

   jdbc:hsqldb:file:/Applications/ColdFusion8/wwwroot/WEB-INF/db/F4CF_FictitiousSalesPlanner/F4CF_FictitiousSalesPlanner

   Figure 2 shows the settings for a default standalone installation of ColdFusion on Windows XP.

   

   Figure 2. FictitiousSalesPlanner data source properties for Windows XP.

   Figure 3 shows the settings for a default standalone installation of ColdFusion on Mac OS X.

   

   Figure 3. FictitiousSalesPlanner data source properties for Mac OS X.

4. Set the Driver Class to org.hsqldb.jdbcDriver.
5. Type JDBC Driver for the Driver Name.
6. Type sa for the User name.
7. Click Submit.

You should see the data source updated successfully message at the top of the screen and an OK status in the table as shown in Figure 4.

Figure 4. Your ColdFusion data sources are set up.

You will be testing the installation of the F4CF_FictitiousSalesPlanner data source later in this tutorial.

Debugging an HSQLDB data source

If the ColdFusion Administrator reports an error connecting to the database, you may need to restart the ColdFusion server to refresh the HSQLDB engine and databases in memory before you attempt to set up the data source again.

As you develop your applications, keep in mind that the HSQLDB engine and databases are stored in ColdFusion server memory. If you make changes to the .script or .properties files, you will likely need to restart the server.

Additionally, if you encounter an error (like the one shown in Figure 5 which states that a database table cannot be found), consider that the HSQLDB data source path may not be configured properly. If your data source path points to the wrong location and HSQLDB does not find the expected .properties file, it will create a .properties file with an empty database. The data source will validate, but the tables, columns, and data will not exist.

Figure 5. Some errors may be symptomatic of a bad HSQLDB data source path.

Note: If you are working through the Getting Started section of the Flex and ColdFusion mini learning center, continue with the following section. If you came to this section from another Flex for ColdFusion developers tutorial, return to that tutorial for your next tasks.

Page 2 of 5

Previous Page

Next Page