3.6 The RecordSet Class
| Now that we've seen the ease and power of UI components , let's see how interactivity is enhanced using one of the most impressive aspects of Flash Remoting technology: the RecordSet class. Remote method calls commonly return recordsets, which are converted to ActionScript RecordSet objects, to the Flash movie. The RecordSet class has methods that make it easy to work with data returned from a remote method and placed in a RecordSet object. For example, to find the number of records in a RecordSet object, use the getLength( ) method: my_rs.getLength( ); To sort the items in a RecordSet object from within the Flash movie ”without making another round trip to the server ”you can use the sortItemsBy( ) method: my_rs.sortItemsBy( columnName , order ); where order is asc ( ascending ) or desc (descending). In truth, specifying anything other than desc as the order parameter performs an ascending sort, but using asc explicitly is considered a best practice. Other methods of the RecordSet class are just as easy to use. Here are several possibilities:
A RecordSet object works seamlessly with a ColdFusion MX query object: the query object in ColdFusion is also used when addressing directories, POP email servers, and FTP servers, in addition to standard database calls. For example, to return a directory listing from the remote server to the Flash movie, you could create a ColdFusion Component with one method and one tag: <cfcomponent displayName="searchDirectory"> <cffunction name="getDirectory" access="remote" returnType="query"> <cfdirectory directory="c:\documents" name="myDirectory" sort="name ASC, size DESC"> <cfreturn myDirectory> </cffunction> </cfcomponent> Inside the Flash movie, you can call this method using Flash Remoting and attach the query results to a Tree component to display a list of documents in this directory: myTree_tree.setDataProvider(myResult_rs); Because ColdFusion allows you to work with certain structures as if they were recordsets, Flash allows you to work with them as RecordSet objects as well. ColdFusion is covered at length in Chapter 5.
3.6.1 Structure of a RecordSet ObjectA recordset is essentially a multidimensional array, and managing such arrays can be somewhat tricky. A RecordSet object works just like a recordset at the application level: it contains rows and columns. Each row represents a record of data returned from the query. The columns represent the field names and values of data returned from the server. Consider a recordset that contains the field names ProductID, ProductName, Supplier, and Category, which could be represented by the following SQL statement: SELECT ProductID, ProductName, Supplier, Category FROM Products A typical output from the database might look like that shown in Table 3-5 (available as RecordSetDemo.fla at the online Code Depot). Table 3-5. The results of an imaginary database query
The RecordSet . getItemAt( ) method allows you to access the data by row number to get an entire row as an object: var myRow = my_rs.getItemAt(0); // Returns entire first row You can access the data by row number and field name to get a specific item: var temp = my_rs.getItemAt(3).ProductName; // Returns "Windows 2000 Professional" The index number that is used in record retrieval, which is maintained internally by the Flash movie, is a zero-based index. It reflects the physical positioning of the items in the RecordSet object. The records also have an internal ID number that remains attached to each record, accessible using the _ _ID_ _ property (note the two underscores on either side of the name, ID ): var myID = my_rs.getItemAt(0)._ _ID_ _; // Returns 0 The _ _ID_ _ property reflects the index of the record within the RecordSet object as it was first created. If you perform a sort on the recordset, the index numbers change, but the internal ID numbers continue to be attached to the records to which they were originally bound. For example, if you sort the RecordSet object in Table 3-5 by ProductName, like this: my_rs.sortItemsBy("ProductName"); you receive the results shown in Table 3-6. Note that the index numbers are sequential, but the ID numbers are not. The earlier statement would return a different result: var myID = my_rs.getItemAt(0)._ _ID_ _; // Returns 1 Table 3-6. RecordSet results
Let's say you want to get the fourth item of the newly sorted recordset. If you attempt to get it using the getItemAt( ) method, as follows : trace(my_rs.getItemAt(3).ProductName); // Returns "Grandma's Extensions" you get "Grandma's Extensions" as the result, because index 3 is now associated with the original sixth record in the recordset. You can also access the fields and the items in the recordset as properties directly (although it isn't recommended) by accessing the items property using the following syntax: my_rs.items[3].ProductName; // Returns "Grandma's Extensions" Using the items array will also access the record that is physically in that position at that time (by index number, not by ID number). Using the getItemID( ) method instead of getItemAt( ) , you can retrieve an item's ID number (i.e., its original position in the recordset): trace(my_rs.getItemID(3)); // Returns 5 The preceding code returns the number 5, because "Grandma's Extensions" was originally the sixth item in the recordset (with a zero-based item number of 5). Using getItemID( ) is preferable to accessing the _ _ID_ _ property directly. There is no built-in method to retrieve an item by its original ID number (i.e., to retrieve its current record number based on its original position in the recordset). If you need to do so, you can write a custom function to resort the records by their _ _ID_ _ property, or you can write a loop to check the _ _ID_ _ property of each record and extract the desired match as shown under RecordSet.getItemID( ) in Chapter 15. 3.6.2 Using the RecordSet ClassThe RecordSet.as file is included automatically when you include the NetServices.as file in your Flash movie. However, you can use the RecordSet class by itself, even in Flash movies that don't utilize Flash Remoting, by including the RecordSet.as class directly: #include "RecordSet.as" This approach can be used when you need to create a custom structure that can benefit from the many built-in methods of the RecordSet class. For example, the RecordSet.sort( ) method, which allows you to sort on any column, is better suited to sorting multidimensional data than the basic Array.sort( ) method. There are many methods available for working with client-side RecordSet objects, which are examined in Chapter 4 and documented fully in Chapter 15. 3.6.3 Flash Remoting Using a RecordSetAlthough client-side recordsets are handy, the real power lies in returning server-side recordsets to your Flash movie and putting them into an ActionScript RecordSet object. The following example demonstrates how to pull a recordset resulting from a remote method call into Flash. The example is built using the sample Northwind database that is included with MS Access and MS SQL Server. The database is shipped with the ASP.NET version of Flash Remoting as well. If you don't have either of these database programs, you can download the database file from the following location:
The only thing you'll need to work with the sample database is a connection from your application server.
The Flash interface is very simple and can be downloaded from the online Code Depot ( SearchProducts.fla ). It consists of a search field, several text fields, and a Submit button. The interface allows a search of the Northwind Products table using the following SQL statement: SELECT ProductName, UnitPrice, QuantityPerUnit FROM Products WHERE ProductName LIKE '% searchfield %' This time, I'll show you the server-side code first, beginning with ColdFusion MX. 3.6.3.1 ColdFusionHere, we use a CFC to build the remote service. The CFC in Example 3-3 should be named SearchProducts.cfc and placed in the webroot \com\oreilly\frdg directory. You'll need a data source named " Northwind " set up in the ColdFusion administrator. Example 3-3. ColdFusion implementation of SearchProducts.cfc<cfcomponent displayName="SearchProducts"> <cffunction name="getSearchResult" access="remote" returnType="query"> <cfargument name="search" type="string" default="%"> <cfquery name="rsGetProducts" datasource="Northwind"> SELECT ProductName, UnitPrice, QuantityPerUnit FROM Products WHERE ProductName LIKE '%#search#%' </cfquery> <cfreturn rsGetProducts> </cffunction> </cfcomponent> Again, this component isn't much different from the HelloWorld example, except for the addition of a <cfquery> within the function. Note the returnType of " query " in the <cffunction> tag. The <cfreturn> tag returns the entire query to the caller. This query object becomes an instance of the RecordSet class in Flash. 3.6.3.2 Server-Side ActionScriptThis example of Server-Side ActionScript (SSAS) demonstrates a way to utilize the CF object of SSAS and its query( ) method. The code is shown in Example 3-4. Example 3-4. Server-Side ActionScript implementation of SearchProducts.asr function getSearchResult (search) { var theSql = "SELECT ProductName, UnitPrice, QuantityPerUnit"; theSql += " FROM Products"; if (search) { theSql += " WHERE ProductName LIKE "; theSql += "'%" + search + "%'"; } return CF.query({datasource:"Northwind", sql:theSql}); } The remote method name, getSearchResult( ) , must match the function name in the .asr file. This file should be named SearchProducts.asr and saved in the webroot \com\oreilly\frdg directory. Once again, if you've already created the SearchProducts.cfc file, you'll have to rename it to SomethingElse.cfc so that it doesn't respond to the service call before this SSAS service is able to. The search parameter is empty if there is no argument passed in, so I use a conditional WHERE clause in the SQL statement. The query returns all results if no search parameter is given. The variable theSql is used to hold the SQL statement, because spanning multiple lines with SQL statements causes errors in SSAS. For that reason, you should create your SQL statement as a string, as shown in Example 3-4, before calling the CF.query( ) method. The CF.query( ) method takes several parameters. This example uses only the datasource and sql parameters. More information on the CF.query( ) method and SSAS can be found in Chapter 6. 3.6.3.3 JRun and J2EEThe JRun version is implemented as a JavaBean named SearchProducts.java , also available at the online Code Depot. The JavaBean should be set up with a JDBC driver available for the sample Northwind database. The listing in Example 3-5 uses the Sun JDBC:ODBC bridge driver. The JavaBean should be compiled as before. You will also need sun.jdbc.rowset.CachedRowSet class for the resulting data, available from:
Chapter 7 explains the techniques for returning data from a server. The java.sql.ResultSet class is not recommended, as it is not a disconnected resultset like the CachedRowSet . Example 3-5. Java implementation of SearchProducts.java package com.oreilly.frdg; import java.sql.*; import java.io.Serializable; import sun.jdbc.rowset.*; public class SearchProducts implements Serializable{ public SearchProducts ( ) {} private String myDriverString = "sun.jdbc.odbc.JdbcOdbcDriver"; private String myConnectionString = "jdbc:odbc:northwind"; private String myUsername = "myUsername"; private String myPassword = "myPassword"; private Connection conn = null; public ResultSet getSearchResult(String search) throws Exception { String errors = ""; CachedRowSet rowset = new CachedRowSet( ); try { Class.forName(myDriverString); conn = DriverManager.getConnection(myConnectionString, myUsername, myPassword); } catch (ClassNotFoundException e) { errors = "Incorrect JDBC Driver\n"; } if (errors == "") { try { Statement s = conn.createStatement( ); String sql = "SELECT ProductName,UnitPrice,QuantityPerUnit FROM Products"; if (search != "") { sql += " WHERE ProductName LIKE '%" + search + "%'"; } ResultSet rs = s.executeQuery(sql); rowset.populate(rs); rs.close( ); s.close( ); } catch (SQLException e) { //catch any SQL errors errors += e.toString( ) ; } finally { if (conn != null) { conn.close( ); } } } if (errors!="") { throw new Exception (errors) ; }; return rowset; } } The Java implementation has some minor error handling, but it is intended mostly for demonstration purposes. Your own Java classes will be more robust. The Java errors are in fact passed to the Flash client even if they aren't handled in the class; the errors are returned in the onStatus event. You will have to handle them in ActionScript, as we've done here by throwing a new Exception with a custom error message. All JavaBeans should implement the java.io.Serializable interface. This interface allows the object instance (an instance of the SearchProducts class, in this case) to be serialized and deserialized. Flash Remoting automatically maintains the state of the object if the JavaBean uses the Serializable interface. If not, Flash Remoting does not store an instance of the class in the session. 3.6.3.4 ASP.NETThe ASP.NET version is implemented as an .aspx page using C# as the language. The code in Example 3-6 calls a SQL Server database and delivers a DataSet to the Flash movie. Using a DataSet is just one way to deliver a dynamic SQL resultset to Flash Remoting. Example 3-6. The ASP.NET implementation of getSearchResult.aspx<%@ Page Language="C#"%> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient"%> <%@ Register TagPrefix="FRDG" Namespace="FlashGateway" Assembly="flashgateway" %> <script runat="server" > void Page_Load ( ) { SqlConnection myConnection; SqlCommand myCommand; SqlDataAdapter myDataAdapter; DataSet myDataSet; String sql = "SELECT ProductName,UnitPrice,QuantityPerUnit FROM dbo.Products"; String conn = " Server=192.168.0.4;uid=myUsername;pwd=mypwd;database=Northwind"; if (Flash.Params.Count > 0) { sql += " WHERE ProductName Like '%" + Flash.Params[0].ToString( ) + "%'"; } myConnection = new SqlConnection(conn); myConnection.Open( ); myCommand = new SqlCommand(sql, myConnection); myDataAdapter = new SqlDataAdapter(myCommand); myDataSet = new DataSet( ); myDataAdapter.Fill(myDataSet,"Products"); Flash.DataSource = myDataSet.Tables["Products"]; Flash.DataBind( ); myConnection.Close( ); } </script> <FRDG:Flash ID="Flash" Runat="Server" /> The page is saved as getSearchResult.aspx in the com\oreilly\frdg\SearchProducts directory. Just as with the HelloUser example page, the FlashGateway assembly is registered as a tag (FRDG) and utilized in the page. 3.6.3.5 PHPPHP has a rich set of database extensions that allow you to use it with all sorts of databases (MySQL, Oracle, PostgreSQL, MS SQL, and mSQL, just to name a few). Example 3-7 uses MySQL, as it is fast, reliable, and the database most commonly used with PHP. With PHP, the functions to access the database change for each database, but for MySQL we use the mysql_pconnect( ) function to connect to the database. Example 3-7. PHP example for SearchProducts.php <?php class SearchProducts { function SearchProducts ( ) { /* constructor */ $this->methodTable = array( 'getSearchResult' => array( 'description' => 'Searches the database and returns a result.', 'access' => 'remote', 'arguments' => array('search') ) ); } function getSearchResult ($search) { if (!$link = mysql_pconnect('localhost', 'myUsername', 'myPassword')) return mysql_error( ); if (!mysql_select_db('Northwind', $link)) return mysql_error( ); $query = 'SELECT ProductName, UnitPrice, QuantityPerUnit FROM Products'; $query .= (!empty($search)) ? " WHERE ProductName LIKE '%$search%'" : ''; if (!($result = mysql_query($query, $link))) return mysql_error( ); return $result; } } ?> The page is saved as SearchProducts.php in the services\com\oreilly\frdg\ directory. One handy thing about PHP services is that you can paste the URL of the service into a browser to test the service for errors. If you see a blank page, the service does not have syntax errors. 3.6.3.6 The client-side ActionScript codeWith the server-side code in place, it's time to build the Flash movie. Add a layer named actions to the movie timeline, and add the script shown in Example 3-8 to the first frame of the timeline. Example 3-8. Client-side ActionScript file SearchProducts.fla #include "NetServices.as" // Connect to the gateway and create a service object 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); var Products_rs = null; } // Set up event handlers for buttons submit_pb.setClickHandler("onSubmit"); // Event handlers for buttons function onSubmit ( ) { myService.getSearchResult(search_txt.text); } // Responder function for onResult event function getSearchResult_Result (result_rs) { Products_rs = result_rs; var temp = ""; temp += "There were " + Products_rs.getLength( ); temp += " records returned."; results_txt.text = temp; } // Responder function for onStatus event function getSearchResult_Status (error) { results_txt.text = "There was an error: " + error.description; } The code includes NetServices.as , which includes the reference to RecordSet.as , so you don't have to include the latter explicitly. Next , it creates a connection to the Flash Remoting gateway and defines the service object. The remote service uses the same naming convention as the previous examples ( com.oreilly.frdg . serviceName ): // Connect to the gateway and create a service object 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); var Products_rs = null; } Next, it assigns and defines the Submit button click handler function. The Submit button calls the getSearchResult( ) method. The contents of the text field named search_txt are sent to the method: // Set up event handlers for buttons submit_pb.setClickHandler("onSubmit"); // Event handlers for buttons function onSubmit ( ) { myService.getSearchResult(search_txt.text); } Next, the code handles the results from the database search. A remote method invocation always returns one of two events: onResult or onStatus . The NetServices class can handle these events in several different ways. To capture responses from the HelloWorld and HelloUser services (Examples Example 1-1 and Example 2-1), we used a responder object that had two methods: onResult( ) and onStatus( ) . Alternatively, the events can be handled with functions that follow a specific naming convention.
In this case, the remote method is getSearchResult( ) , so the result and status functions are named getSearchResult_Result( ) and getSearchResult_Status( ) . The remote service returns an entire recordset, which I've called result_rs , to getSearchResult_Result( ) . Because the result is a recordset, using a variable name ending in _rs lets you take advantage of ActionScript's code hints and code completion features: // Responder function for onResult event function getSearchResult_Result (result_rs) { Products_rs = result_rs; The RecordSet . getLength( ) method returns the number of records in the recordset. The code displays the count in the results_txt text field along with some descriptive text. The text is first concatenated in a temporary variable, which is much quicker than setting the text property incrementally: var temp = ""; temp += "There were " + Products_rs.getLength( ); temp += " records returned."; results_txt.text = temp; } If the remote call is unsuccessful , the _Status function is called instead of the _Result function. The _Status function receives an error object with properties, including description , which identifies the error. The getSearchResult_Status( ) function simply displays the descriptive text of any error message that is returned: // Responder function for onStatus event function getSearchResult_Status (error) { results_txt.text = "There was an error: " + error.description; } If you run the movie ”either by publishing it and browsing to it, or by testing it in the authoring environment ”you should be able to enter something into the search field and get a count of the results as a message in the interface. If you get an error message, you'll probably wonder where the error occurred. This seemingly simple example uses several different technologies and demonstrates just how complex a Flash Remoting application can be. This example uses the following technologies:
Obviously, when using this many different technologies, errors can happen at any step along the way. Chapter 13 covers debugging and troubleshooting in depth. Once you have the code that counts the records returned to Flash working, you can add code to display the data and page through the recordset. The RecordSet class does not have client-side paging built in; it doesn't support the notion of a "current" record. Let's enhance the RecordSet class to include two custom methods: move( ) and getCurrentRecord( ) . ActionScript allows you to augment a class by assigning custom methods and properties directly to its prototype property. This is not the only way to add functionality to a class, but in this case it fits the bill nicely . First, add a currentRecord property to the RecordSet prototype along with a "getter" method, getCurrentRecordNum( ) : RecordSet.prototype.currentRecord = 0; RecordSet.prototype.getCurrentRecordNum = function ( ) { return this.currentRecord; }; Next, add the custom move( ) method, which accepts a parameter specifying the direction (" first ", " previous ", " next ", or " last "): RecordSet.prototype.move = function (direction) { direction = direction.toLowerCase( ); switch (direction) { case "first": this.currentRecord = 1; break; case "previous": if (--this.currentRecord < 1) { this.currentRecord = 1; } break; case "next": if (++this.currentRecord > this.getLength( )) { this.currentRecord = this.getLength( ); } break; case "last": this.currentRecord = this.getLength( ); break; } }; The custom move( ) method sets the currentRecord property of the RecordSet object, depending on which of the four buttons ”First, Previous, Next, or Last ” the user clicked. You have seen this typical recordset navigation scheme a thousand times before. Finally, create the getCurrentRecord( ) method, which retrieves the current record. Keep in mind that by the time these methods are called, the entire recordset is in memory. There are no more round trips to the server. Recordset.prototype.getCurrentRecord = function ( ) { return this.getItemAt(this.currentRecord-1); }; When you call RecordSet.getCurrentRecord( ) , an entire record is returned, but you can retrieve individual fields from the record using the field name, like this: myRecordsetName .getCurrentRecord( ). myFieldName ; Now that the RecordSet class has been enhanced to support a current record and a navigation method, you can attach onClick event handlers to the First, Previous, Next, and Last buttons. The function is written as a callback function named moveToRec( ) and assigned to the buttons (named moveFirst , movePrevious , moveNext , and moveLast ): function moveToRec (button) { Products_rs.move(button.label); getRecord( ); } moveFirst.setClickHandler("moveToRec"); movePrevious.setClickHandler("moveToRec"); moveNext.setClickHandler("moveToRec"); moveLast.setClickHandler("moveToRec"); Since the button label supplies the necessary argument to the RecordSet.move( ) method, one generic function is sufficient for all four buttons. Now we need to display fields extracted from the recordset data in some text fields. This task can be accomplished in several ways, but I'll use a function named getRecord( ) that is called from the moveToRec( ) click handler: function getRecord ( ) { if (Products_rs.getLength( ) == 0) { ProductName_txt.text = UnitPrice_txt.text = QuantityPerUnit_txt.text = ""; navStatus_txt.text = "No Records"; } else { var currentRecord = Products_rs.getCurrentRecord( ); ProductName_txt.text = currentRecord.ProductName; UnitPrice_txt.text = currentRecord.UnitPrice; QuantityPerUnit_txt.text = currentRecord.QuantityPerUnit; var temp = "Rec. No. " + (Products_rs.getCurrentRecordNum( )); temp += " of " + Products_rs.getLength( ); navStatus_txt.text = temp; } } The preceding code simply sets the text elements in the Flash movie to the current record's field values, or sets them to blank if there are no records. It retrieves the current record with the getCurrentRecord( ) method created earlier and then extracts each field individually. You can test this movie from the Flash environment, or you can publish it to your site and browse to the resulting HTML page. When you browse to the page, the browser doesn't need to reload the page, even if you search the database repeatedly. The communication with the server is done by Flash behind the scenes. Figure 3-3 shows the interface in use. The completed example, SearchProducts.fla , is available at the online Code Depot. Chapter 4 through Chapter 9 show more examples that utilize the RecordSet class. Figure 3-3. The Flash interface for SearchProducts.fla This section described a simple search interface in Flash that relied on ActionScript to manually set the text elements in the movie to the incoming recordset field values. The next section describes a much simpler approach that can be utilized with some types of UI components using another Flash Remoting class: DataGlue . |
EAN: 2147483647
Pages: 239