Working with Recordsets in Flash

One of ColdFusion's greatest strengths has always been its thoughtful treatment of the concept of a query recordset. Whenever you use a <cfquery> tag to select information from a database, you get back a query recordset object that contains rows and columns of information. Because it's such a natural way to think about data, and because it corresponds so closely to how relational database systems store data internally, it's hard to imagine any ColdFusion application that doesn't deal with query recordsets in one way or another.

One of the Flash Remoting Components is a special ActionScript object called RecordSet, which is similar conceptually to a query object in CFML. Whenever you use the FLASH.result variable to send a query object back to the Flash Player, the data becomes available to ActionScript as an equivalent RecordSet object.

About RecordSet Objects

Take a look at the ColdFusion listing in Listing 26.4. It's very similar to Listing 26.3, which supplied information about a single film as a simple string. In contrast, this listing simply runs a <cfquery> named merchQuery and then returns the entire query object to Flash.

Listing 26.4. MerchRecordsetProvider.cfmReturning a Query Object to the Flash Player

 <!---   Filename: MerchRecordsetProvider.cfm  Author: Nate Weiss (NMW)  Purpose: Provides data to a Flash movie ---> <!--- Query the database for merchandise records ---> <cfquery name="merchQuery" datasource="#APPLICATION.dataSource#">  SELECT MerchID, MerchName  FROM Merchandise  ORDER BY MerchName </cfquery> <!--- This will be available as the "result" variable in the ---> <!--- MerchRecordsetProvider_Result handler within the Flash movie ---> <cfset FLASH.result = merchQuery> 

As you have learned, whatever value ColdFusion returns with the FLASH.result variable becomes available to Flash as a native ActionScript variable called result. If the value of FLASH.result is a string on the server, then result is a string in Flash. If FLASH.result is an array, then result is an array, and so on. It follows that if the value of FLASH.result is a query object, then result will be a RecordSet object.

Unlike strings, dates, numbers, structures, and arrays, which have obvious equivalents in Java-Script, there is no native JavaScript data type that corresponds in a helpful way to the ColdFusion concept of a query result set. The RecordSet object type, which is included automatically with NetServices.as, was designed to fill this need.

NOTE

If you are really into JavaScript or ActionScript, you can check out how the RecordSet object is implemented by opening the RecordSet.as file in the Configuration/Include folder within the Flash's program folder. It's pure ActionScript.

RecordSet Functions

Table 26.2 lists the methods supported by the RecordSet object. This is by no means an exhaustive list; I am just trying to show you some of the most interesting methods. For a complete listing, you need to consult the ActionScript Dictionary part of the Flash Remoting online documentation (choose Window > Welcome to Flash Remoting to view this documentation).

Table 26.2. Important RecordSet Methods

METHOD	DESCRIPTION
MyRS.getLength()	Returns the number of records (rows) in the recordset. Equivalent to the RecordCount property of queries in CFML.
MyRS.getItemAt(row)	Returns the data in the row specified by the row argument. The record numbers are zero-based, so getItemAt(0) returns the first row of data, getItemAt(1) returns the second row, and so on. The data is returned as an ActionScript object (similar to a CFML structure), with properties that correspond to the recordset's column names.
MyRS.filter()	Provides an easy way to filter the records in a RecordSet object. This is similar conceptually to ColdFusion's Query of Queries feature (see Chapter 30, "More On SQL and Queries."
MyRS.setField()	Provides a way to change the values in Flash's local copy of the recordset. Similar conceptually to CFML's QuerySetCell() function.
MyRS.sortItemsBy()	Sorts Flash's local copy of the recordset by whatever column you specify.

NOTE

The only methods actually used in this chapter's listings are getLength() and getItemAt(). I am listing the other functions in this table mainly to give you an idea about what kinds of things the RecordSet object is capable of.

For details about the methods listed in Table 26.2, see the ActionScript dictionary in the Flash Remoting documentation.

For instance, the following snippet is an example of a event handler that loops through the recordset returned by the MerchRecordsetProvider.cfm page (Listing 26.4). You can usually use code like this to loop through any given RecordSet object. Think of this as the ActionScript equivalent of a <cfloop> block that loops over a ColdFusion query object with the query attribute.

 function MerchRecordsetProvider_Result(result) {   // For each record in the recordset...  for (var i = 0; i < result.getLength(); i++) {  // Use the record variable to refer to the current row of recordset  var record = result.getItemAt(i);  /*  Now the rest of this block can refer to the current row's data  as properties of the record variable, such as record.MerchName,  record.MerchDescription, or record.MerchPrice.  */  }; } 

The for statement at the top uses result.getLength() to find out the number of records in the result recordset. The code within the for block will execute once for each row in the recordset, incrementing the value of i for each pass through the loop. In other words, i is the current row number, starting with 0. Next, the getItemAt() method grabs the data from the current row of the recordset and places it into the record variable. Now the rest of the code in the loop can refer to the current row's data as properties of the record variable, such as record.MerchName, record.MerchDescription, or record.MerchPrice.

NOTE

Unlike JavaScript, ActionScript isn't case sensitive (except for statement keywords like for, var, and function), so you don't have to get the capitalization of column names or of the record variable itself exactly right. That said, it's generally easier to follow and maintain code that uses capitalization consistently.

A Complete Example

The next Flash example is a movie that displays a simple but effective Merchandise Browser for Orange Whip Studios. The idea is to provide an interesting way for users to look through the items for sale, without taking up too much space on the page and without reloading the page to show the details about each item. The example will also use a bit of animation to make the display seem livelier and to give the user a sense that the information about each product is looked up in real time. Actually, it's more than a sense; the information really will be looked up in real time.

The User Experience

The Flash Document used to create the Merchandise Browser example is included on the CD-ROM for this chapter and should be copied to the same location as the other files in this chapter. The filename is MerchBrowser.fla. Go ahead and open the file in Flash now, then view the movie by choosing Control > Test Movie or File > Publish Preview > Default. That should produce a (partially) working version of the example.

NOTE

You may need to adjust the URL used in the NetServices.setDefaultGatewayUrl() method before the example will operate properly (see Listing 26.5). The version of the file on the CD-ROM assumes you are using a ColdFusion on your local machine in stand-alone mode (that is, at port 8501). If not, just adjust the URL accordingly.

The movie shows a list of merchandise available for sale in a scrolling list box (Figure 26.7). When the user selects an item in the list, details about the item slide out from underneath the list (Figure 26.8) in an animated fashion. This will not work, however, until we build the service for this later on. The details include the product's name, description, and price.

Figure 26.7. The movie shows a list of products; the user can drill down by selecting items.

Figure 26.8. When the user selects an item, details slide out from under the list.

NOTE

The Add To Cart button shown in Figure 26.8 isn't operational in this movie. I'll create a second version of this movie later, one that includes working cart functionality. See the "Instantiated CFCs" section, later in this chapter.

This is only an example, and Orange Whip Studios isn't any more likely to win design awards for this interface than to win Academy Awards for its films. My main goal here is to get you thinking about how you can use Flash to reinvent certain kinds of Web experiences, like master-detail record navigation or shopping carts. Information can slide in or fade out, giving users visual feedback about what exactly is happening as they make choices or push buttons. Of course, the point isn't just to be showy, but to make the user experience more engaging, more fun, and more efficient.

Building the Interface

Compared to the last example, this one is a bit involved, and requires more individual steps than I can reasonably list here. I recommend that you open and explore the MerchBrowser.fla file, rather than taking the time to reproduce it from scratch. That said, I would like to call your attention to the important elements in the movie so you can understand how the code works.

Figure 26.10 shows how the movie looks in the Flash workspace. Note that there are four layers in the timeline (at the top of the window). Also, unlike the last example, this one has more than one frame in the timeline. The various frames represent different moments in the animation that takes place while the user interacts with the movie. Figure 26.9 shows Frame 10, which is when the detail view is fully showing (refer to Figure 26.8). The frames before Frame 10 are when the detail view is sliding out from under the list of products; the frames after Frame 10 are when the details are sliding back under the list.

Figure 26.10. Users can add items to their shopping carts with the Add To Cart button.

Figure 26.9. You can use Flash's concept of animation through time to make your pages more interactive.

Table 26.3 explains what is in each layer of the movie.

Table 26.3. Layers in MerchBrowser.fla

LAYER	DESCRIPTION
Actions layer	As is the custom, a separate layer called Actions holds actions (that is, blocks of ActionScript code) that should execute at different moments in the movie's animation sequence. The code in the first frame is similar conceptually to the code in the first frame of the first example (Listing 26.2). Since this layer isn't meant to hold any visual elements, it's locked so nothing gets placed on it accidentally.
ListUI layer	This layer contains a ListBox component called MerchListBox, which is what the user uses to browse the list of products. This layer appears above the others in the timeline, which is why the ListBox always stays on top of other items (like the detail view that slides from under it).
DetailUI layer	This layer contains the animated detail view. The animation is pretty modest by Flash standards; just a simple slide from left to right. The layer contains several text box elements to show the selected product's name, description, and price. The actual animation was created with just a few mouse clicks and Flash's motion-tweening feature. This is the only layer that changes from frame to frame. Note also that each of the layer's keyframes (turning points in the animation) is named with descriptive labels (StartSlideIn, StartSlideOut, and EndSlideOut).
Background layer	This layer contains a decorative background and some artwork, as shown in Figure 26.7.

Writing the ActionScript Code

Listing 26.5 shows the ActionScript code in the first frame of MerchBrowser.fla. The code here has many of the same elements as the first example (Listing 26.2). Considering that this movie appears to do more, it's remarkable that not much additional code is needed. I will explain each of the important points in the code shortly.

Listing 26.5. ActionScript Code in the First Frame of MerchBrowser.fla

 // Include support for Flash Remoting Components #include "NetServices.as" // uncomment this line when you want to use the NetConnect debugger // #include "NetDebug.as" // -------------------------------------------------- // Handlers for user interaction events // -------------------------------------------------- // -------------------------------------------------- // Application initialization // -------------------------------------------------- if (inited == null) {  // do this code only once  inited = true;  // set the default gateway URL (this is used only in authoring) NetServices.setDefaultGatewayUrl("http://localhost:8501/flashservices/gateway")  // connect to the gateway  gateway_conn = NetServices.createGatewayConnection();  // get a reference to a service  // In this case, the "service" is the /ows/26 directory in web server root  myService = gateway_conn.getService("ows.26", this);  // Call the service function that fills the ListBox with a list  // of products (from ColdFusion) for the user to browse through  myService.MerchRecordsetProvider(); } // This function executes when the user selects an item in the ListBox function MerchListBox_Changed() {  // If this is the first time an item has been selected,  // go straight to the frame that loads the detail information  if (_currentFrame == 1) {  gotoAndPlay("EndSlideOut");  // Otherwise, go to the frame that slides the display back in (hides it)  // When it finishes sliding, it will load the detail information  } else {  gotoAndPlay("StartSlideOut");  } } // This function retrieves the detail information about the selected product. // It's executed when the last frame of the movie is reached // (when the detail view has finished hiding itself under the product list) function getSelectedItemDetails() {  myService.MerchDetailProvider({MerchID:MerchListBox.getValue()}); } // -------------------------------------------------- // Handlers for data coming in from server // -------------------------------------------------- function MerchRecordsetProvider_Result(result) {   // First, remove any existing items from the list box  MerchListBox.removeAll();  //DataGlue.bindFormatStrings (MerchListBox, result, "#MerchName#", "#MerchID#");  // For each record in the recordset...  for (var i = 0; i < result.getLength(); i++) {  // Use the record variable to refer to the current row of recordset  var record = result.getItemAt(i);  // Add item to the MerchListBox widget, which is like a <SELECT> in HTML  MerchListBox.addItem(record.MerchName, record.MerchID);  }; } // This executes when a merchandise detail record has been received function MerchDetailProvider_Result(result) {  // The result variable is a recordset that contains just one row  // The detailRecord variable will represent the row of data  var detailRecord = result.getItemAt(0);  // Display detail information in text boxes  _root.TitleTextBox.text = detailRecord.MerchName;  _root.DescriptionTextBox.text = detailRecord.MerchDescription;  _root.PriceTextBox.text = "Price: " + detailRecord.MerchPrice;  // If the ImageNameSmall column contains an image filename, display it  if (detailRecord.ImageNameSmall.length > 0) {  // Load and display the product image  loadMovie("../images/" + detailRecord.ImageNameSmall, _root.ImageMovie);  // Hide the OWS logo  OWSLogo._visible = false;  // If there is no image file for this record, display the OWS logo instead  } else {  // Unload any product image that might already be showing  unloadMovie(_root.ImageMovie);  // Make the OWS logo visible  OWSLogo._visible = true;  }  // Now that the information about the merchandise has been placed,  // make the display slide back into view, revealing the information  gotoAndPlay("StartSlideIn"); } // Stop here, so animation doesn't occur until user selects a product stop(); 

In addition to the code in the first frame, frames 10 and 15 contain a few additional lines of ActionScript code, as shown in Listing 26.6 and Listing 26.7.

Listing 26.6. ActionScript Code in Frame 10 of MerchBrowser.fla

 // Stop the animation for now, so the detail view remains visible. // The animation will remain stopped until the user selects a different // item from the list of products. stop(); 

Listing 26.7. ActionScript Code in Frame 15 of MerchBrowser.fla

 // Retrieve detail information about the selected item in the product list getSelectedItemDetails(); // Stop the animation for now, so detail view remains hidden behind list // until the details have been retrieved. The event handler for the // MerchDetailProvider.cfm page will bring it back into view when ready. stop(); 

Understanding the Code

You have now seen all the ActionScript code needed to create the Merchandise Browser example. Some of it is familiar to you from the first Flash movie we created (Listing 26.2). Let's go through the sequence of events that occurs within the movie, from when it first appears to what happens when users click the various elements in the movie.

The normal sequence of events is as follows:

1.	When the movie first appears, the first thing the Flash Player does is to execute the ActionScript code in the first frame (Listing 26.5).
2.	The initialization block at the top of Listing 26.5 executes. Except for the last line, this block is the same as the one in Listing 26.2. The last line executes calls the Merch RecordsetProvider.cfm page from Listing 26.4 as a Flash Remoting service function.
3.	On the ColdFusion server, the <cfquery> in Listing 26.4 is run, and the query records are passed back to Flash with FLASH.result.
4.	When the Flash Player receives the data from ColdFusion, it executes the MerchRecordsetProvider_Result() event handler.
5.	Within MerchRecordsetProvider_Result(), the MerchListBox is populated with the data in the recordset returned by ColdFusion. First, any existing items are removed from the list with the removeAll() method. Then a simple for loop loops through the recordset, as discussed in the "RecordSet Functions" section earlier in this chapter. Within the loop, the list box's addItem() method adds an item to the list for each record in the recordset. These items are conceptually similar to the individual <option> elements in a normal HTML <select> list. Each item will display a product's name, and the value of each item is the corresponding MerchID number.
6.	The initial work of the movie is now complete. Because of the stop() action at the bottom of Listing 26.5, the timeline doesn't advance past the first frame. Nothing further will happen until the user selects a product from the list.
7.	When the user selects a product, Flash Player executes the MerchListBox_Changed() function. This is because MerchListBox_Changed is specified as the Change Handler for the MerchListBox list box (you specify the Change Handler in the Properties panel).
8.	Within MerchListBox_Changed(), the idea is to make sure the detail view returns to its hidden position, where it will remain hidden while the detail information for the selected movie is retrieved from the server. If this is the first time a product has been selected, then the gotoAndPlay() command sends the timeline directly to the EndSlideOut frame of the movie. Normally, though, gotoAndPlay()sends the timeline to the StartSlideOut frame, which starts the animation of the details sliding back under the product list. Either way, the EndSlideOut frame (the last frame of the movie) is reached eventually, which is what starts the process of contacting the server for the details of the newly selected product.
9.	The code in the EndSlideOut frame of the movie (Listing 26.7) runs, simply executing the getSelectedItemDetails() function and halting the animation (now that the detail view is hidden under the product list).
10.	Within getSelectedItemDetails(), the Flash Player requests detail information about the selected movie by calling the MerchDetailProvider.cfm ColdFusion page as a service function, passing the value of the currently selected item as a parameter called MerchID. You haven't seen this listing yet, but it takes the MerchID, runs a query to get the details about the item, and returns the query.
11.	When the detail data is received from ColdFusion, the MerchRecordsetProvider_Result() event handler is called.
12.	Within MerchRecordsetProvider_Result(), a variable named detailRecord gets the first row of data from the recordset. This recordset will only return one row, so there is no need for looping. The name, description, and price of the item are displayed in the TitleTextBox, DescriptionTextBox, and PriceTextBox text boxes, respectively.
13.	Still within MerchRecordsetProvider_Result(), an if statement determines if the ImageNameSmall column of the recordset contains a filename. If so, it displays the picture by calling the loadMovie() command. The picture will be displayed where the movie clip called ImageMovie is positioned (it's in the DetailUI layer). If an image isn't available, a different movie clip called OWSLogo is shown in its place, visually indicating that there is no picture available.
14.	Now that the details of the selected product are visible, the final step within MerchRecordsetProvider_Result() is to send the timeline to the StartSlideIn frame (that's Frame 2), which begins the animation of the details sliding out from under the product list.
15.	The animation stops at Frame 10 because of the stop() command at Frame 10 (Listing 26.6), and remains at Frame 10 until the user selects a different product from the list, at which point the execution goes back to step 7, above.

That's it. I'm sorry I can't explain all of the Flash concepts mentioned in this section, such as how to create animations with Flash's motion-tweening feature, what keyframes and movie clips are, and how to load images dynamically at run time. That said, this example should give you a solid understanding of how to incorporate Flash Remoting into your Flash movies. If you're new to Flash, I hope it gives you some idea about what is involved in creating new movies of your own.

Listing 26.8 shows the ColdFusion page that provides the detail information about each product to Flash. This is the page called by the getSelectedItemDetails() function when the movie reaches its last frame (that is, when the detail view is fully hidden).

Listing 26.8. MerchDetailProvider.cfmProviding Details About the Selected Product

 <!---  Filename: MerchDetailProvider.cfm  Author: Nate Weiss (NMW)  Purpose: Provides film detail to a Flash movie ---> <!--- We are expecting a MerchID parameter to be passed from Flash ---> <cfparam name="FLASH.merchID" type="numeric"> <!--- Query the database for merchandise records ---> <cfquery name="MerchQuery" datasource="#APPLICATION.dataSource#" maxrows="1">  SELECT MerchID, MerchName, MerchDescription, ImageNameSmall, MerchPrice  FROM Merchandise   WHERE MerchID = #FLASH.merchID# </cfquery> <!--- Format the MerchPrice column in the appropriate currency format ---> <!--- (It's easier to do this with ColdFusion than with ActionScript) ---> <cfset merchQuery.merchPrice = lsCurrencyFormat(merchQuery.MerchPrice)> <!--- This will be available as the "result" variable in the ---> <!--- MerchDetailProvider_Result handler within the Flash movie ---> <cfset FLASH.result = merchQuery> 

As you can see, this is a very simple template. The <cfparam> tag makes sure that Flash provides a parameter called MerchID. Then a simple query retrieves information from the corresponding record of the Merchandise table, and the query is passed back to Flash with FLASH.result.

The only thing of note here is the fact that the MerchPrice column of the query is changed to hold the currency-formatted version of the price. This is done because the lsCurrencyFormat() function is easy to use in ColdFusion, but has no direct equivalent in Flash. This underscores the fact that you can use any of the tools available to you as a ColdFusion developer within a page that serves Flash via Flash Remoting.