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.
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.
#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:
The ComboBox in this case
The RecordSet object in this case
Using the current ProductName field from the recordset
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.
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.
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.
Squashing a DataGrid BugThere is a bug in the DataGrid implementation that is documented in the DataGrid help files. It has to do with the way that conflicting classes are dealt with in Flash MX. If you add a DataGrid to your page and you find that it displays nothing but blank results, you are seeing the bug. To work around the DataGrid bug, follow these steps:
At this point, the bug should be squashed and you should be able to see the results in your DataGrid. |
#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); }
|
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.
#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).
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.