3.7 DataGlue

Typically, when working with recordsets in server-side applications and HTML pages, the most tedious part is formatting the recordset on a page or in a user-interface element, such as a list box. Fortunately, the RecordSet class, in combination with the DataGlue class, simplifies this immensely. The DataGrid and the Dynamic Chart components are perhaps the most impressive, but any data-aware component can be dynamically populated with a RecordSet object with one line of code:

myComponent.setDataProvider(myRecordset_rs);

This code effectively binds the recordset to the component and creates the visual output in the Flash movie.

3.7.1 Using the DataGlue Class

So-called data-aware components can interact with DataProviderClass objects to easily attach a data source to the component. Components that support DataProviderClass objects include:

• ComboBox

• ListBox

• Tree

• BarChart

• LineChart

• PieChart

• DataGrid

These items support DataProviderClass methods, such as addItem( ), addItemAt( ), getLength( ), removeAll( ), removeItemAt( ), replaceItemAt( ), setDataProvider( ), and sortItemsBy( ). These methods are handy when you're working with static data, but when you're working with a RecordSet object there is one added bonus: changes to the RecordSet object are reflected automatically in the UI component's display. The DataGlue.bindFormatStrings( ) class-level method effectively glues the recordset to the UI component; so, when you use one of the RecordSet methods, the component that is tied to the RecordSet object is also affected. For example, deleting an item from the client-side RecordSet object with the following code:

myRecordset_rs.removeItemAt(myRecordNumber);

automatically removes the item from any components that are tied to the RecordSet, such as a ListBox or ComboBox.

Take a ComboBox as an example. A ComboBox can be thought of as two arrays: one containing the label property for each item in the list, and one containing the data property. The label is displayed in a drop-down list, but the data can be almost anything, including a recordset row. Let's look at a simple example, which allows a server-side service to populate a ComboBox. The examples use the same server-side services as the last example: com.oreilly.frdg.SearchProducts.

The UI has a ComboBox to hold the resulting recordset, a ListBox to hold items chosen by the user, and two buttons to add and remove items from the ListBox. The code is listed in Example 3-9 and is also available at the online Code Depot as DataGlueDemo.fla.

Example 3-9. DataGlueDemo.fla

#include "NetServices.as"
#include "DataGlue.as"

if (connected == null) {
  connected = true;
  NetServices.setDefaultGatewayUrl("http://localhost/flashservices/gateway");
  var my_conn = NetServices.createGatewayConnection( );
  var myService = my_conn.getService("com.oreilly.frdg.SearchProducts", this);
}
// Call method inline when movie loads
myService.getSearchResult('');

// Set up event handlers for buttons
add_pb.setClickHandler("onAdd");
remove_pb.setClickHandler("onRemove");

// Event handlers for buttons
function onAdd ( ) {
  products_lb.addItem(allproducts_cb.getSelectedItem( ).label);
}

function onRemove ( ) {
  products_lb.removeItemAt(products_lb.getSelectedIndex( ));
}

function getSearchResult_Result (result_rs) {
  DataGlue.bindFormatStrings(allProducts_cb,result_rs,
                            "#productname#", "#unitprice#");
}

The first thing you should notice is the new include file, DataGlue.as:

#include "DataGlue.as"

The connection and service creation are the same as seen earlier. The getSearchResult( ) remote method is called inline rather than triggered by an event; the recordset is loaded from the remote server when the movie loads. Two buttons add items to the ListBox from the ComboBox and remove items from the ListBox. They are set up with click handler functions:

// Set up event handlers for buttons
add_pb.setClickHandler("onAdd");
remove_pb.setClickHandler("onRemove");

// Event handlers for buttons
function onAdd ( ) {
  products_lb.addItem(allproducts_cb.getSelectedItem( ).label);
}

function onRemove ( ) {
  products_lb.removeItemAt(products_lb.getSelectedIndex( ));
}

The only other code in this example is the _Result function, which handles the result from the remote method. This is where DataGlue.bindFormatStrings( ) is used to tie the recordset to the UI component:

function getSearchResult_Result (result_rs) {
  DataGlue.bindFormatStrings (allProducts_cb,result_rs,"#productname#");
}

The bindFormatStrings( ) method takes four parameters:

Data consumer

The ComboBox in this case

Data provider

The RecordSet object in this case

Label data

Using the current ProductName field from the recordset

Value data

Not used here

The DataGlue.bindFormatStrings( ) method essentially glues your recordset to the ComboBox. When the recordset is loaded from the remote server, it is automatically placed in the ComboBox.

The next section describes the DataGlue class (or simply DataGlue) as it applies to the DataGrid component.

3.7.2 Gluing the DataGrid

The previous example showed a simple recordset feeding a ComboBox using the DataGlue class. However, DataGlue works just as easily with more sophisticated components. The DataGrid component comes with its own method for handling the DataProviderClass, called setDataProvider( ):

myGrid.setDataProvider(myRecordset_rs);

Calling DataGrid.setDataProvider( ) causes a DataGrid component to display an entire recordset in a sortable grid, as shown in Figure 3-4. Because the DataGrid contains its own DataGlue functionality, you don't have to specifically include DataGlue.as in your Flash file.

Figure 3-4. The DataGrid component in use

You'll notice that the row colors are alternating in Figure 3-4. This was done by adding one line of code:

myGrid_dg.alternateRowColors(0xCCCCCC,0xFFFFFF);

The entire code listing for the DataGrid demo (DataGridDemo.fla), which you'll find at the online Code Depot, is shown in Example 3-10.

Example 3-10. DataGridDemo.fla

#include "NetServices.as"

if (initialized == null) {
  initialized = true;
  NetServices.setDefaultGatewayUrl("http://localhost/flashservices/gateway");
  var my_conn = NetServices.createGatewayConnection( );
  var myService = my_conn.getService("com.oreilly.frdg.SearchProducts", this);
}

myService.getSearchResult('');

function getSearchResult_Result(result_rs) {
  myGrid_dg.alternateRowColors(0xCCCCCC,0xFFFFFF);
  myGrid_dg.setDataProvider(result_rs);
}

The online example files utilizing the DataGrid do not contain the grid component, due to licensing issues, as it is a commercial component. You need to add a DataGrid component to the .fla and give it an instance name of myGrid_dg in order to make the examples work.

3.7.3 Gluing the Dynamic Chart Components

Flash's Dynamic Chart Components are perhaps the most sophisticated components available from Macromedia. Attaching a RecordSet object to one of these charts is just as simple as it was using a ComboBox or DataGrid component. Just like DataGrid components, the Chart Components have their own DataProviderClass built in and don't need DataGlue.as.

To demonstrate, I'll go back to the Northwind database and create a service that queries the Category Sales for 1997 view. The SQL statement used for this service is simply:

SELECT * FROM [Category Sales for 1997]

The MySQL version of the database does not have built-in views or queries like MS SQL Server or MS Access. If you are using the MySQL database, you can substitute the following SQL statement for the previous query:

SELECT Categories.CategoryName,
Sum((order_details.UnitPrice * Quantity * (1-Discount)/100)*100) 
AS CategorySales
FROM Categories
INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
INNER JOIN Orders ON Orders.OrderID = order_details.OrderID
INNER JOIN order_details
ON Products.ProductID = order_details.ProductID
WHERE Orders.ShippedDate Between '19970101' And '19971231'
GROUP BY Categories.CategoryName

The server-side code is identical to the SearchProducts service that was created earlier, with the exception of the previous SQL statement and the method name: getCategorySales( ). The code for the server-side service is not shown here but is available as a CFC for ColdFusion, a Java class for J2EE, an .aspx page for ASP.NET, and a .php page for PHP at the online Code Depot.

The Flash source file, ChartDemo.fla, can also be downloaded from the online Code Depot. It contains one item: a PieChart object named myChart. Example 3-11 shows the commented ActionScript code.

Example 3-11. ChartDemo.fla

#include "NetServices.as"

if (connected == null) {
  connected = true;
  NetServices.setDefaultGatewayUrl("http://localhost/flashservices/gateway");
  var my_conn = NetServices.createGatewayConnection( );
  var myService = my_conn.getService("com.oreilly.frdg.getStats", this);
}
// Set up the chart title, the label field, and the value field
myChart.setChartTitle("Category Sales for 1997");
myChart.setLabelSource("CategoryName");
myChart.setValueSource("CategorySales");

// Get the remote service
myService.getCategorySales( );

// Handle the result by setting the DataProvider of the chart
function getCategorySales_Result(result_rs) {
  myChart.setDataProvider(result_rs);
}

This code provides a simple pie chart based on the data returned from the server. If you roll your mouse over the pie elements, you can see the data from the recordset (shown in Figure 3-5).

Figure 3-5. The dynamic PieChart component in use

To change this PieChart to a BarChart, simply remove the PieChart object from the Flash movie and replace it with a BarChart object. Name the BarChart myChart, using the PI. If you test the movie at this point, you should see the data displayed in bar chart format.