Manage the access of data through paging



For various performance and network bottleneck issues, you typically do not retrieve all the records in a database with a single call. Paging allows you to retrieve the records incrementally in sets, and only on an as needed basis.

To use paging to retrieve records, implement a data service that includes two operations with the following signatures:

  • getItems_paged($startIndex, $numItems)

  • count()

    Paging (ColdFusion)

    The application you create in this tutorial accesses the fb_tutorial_db database. For information on installing this database, see Installing the tutorial database

    This tutorial creates an application that uses paging to populate a DataGrid.

    Create the remote service and import it into a Flex project

    1. In your favorite ColdFusion editor, create a ColdFusion service that implements the required paging functions.

      <cfcomponent output="false"> 
    <cffunction name="getItems_paged" output="false" access="remote" returntype="any" > 
        <cfargument name="startIndex" type="numeric" required="true" /> 
<cfargument name="numItems" type="numeric" required="true" /> 
 
        <cfset var qRead=""> 
        <cfquery name="qRead" datasource="fb_tutorial_db"> 
            SELECT * FROM employees LIMIT #startIndex#, #numItems# 
        </cfquery> 
 
        <cfreturn qRead> 
         
    </cffunction> 
 
    <cffunction name="count" output="false" access="remote" returntype="numeric" > 
 
        <cfquery name="qread" datasource="fb_tutorial_db"> 
            SELECT COUNT(emp_no) AS empCount FROM employees 
        </cfquery> 
 
        <cfreturn qread.empCount> 
         
    </cffunction> 
</cfcomponent>

    2. Save the CFC as PagingService.cfc in your web root. Place it in a folder named PagingCF.

    3. In Flash Builder, create a Flex project. Name the project PagingCF and specify ColdFusion for the server technology as listed below. Click Next.

      • Application Server Type: ColdFusion

      • Enable Use Remote Object Access Service

      • Select ColdFusion Flash Remoting

    4. Validate your ColdFusion settings and specify the PagingCF directory for the Output Folder. Click Finish.

    5. From the Flash Builder Data menu, select Connect to Data Service. Select ColdFusion and click Next.

    6. Click Browse and navigate to the PagingService.cfc file you created in step 1. Click Finish.

    7. In the Flash Builder Data/Services view, from the context menu for the getItems_paged() operation, select Configure Return Type.

    8. In the Configure Return Type dialog, Auto-Detect the Return Type is selected by default. Click Next.

    9. Specify values and types for the parameters to the getItems_paged() operation as described below. Click Next:

      Argument

      Argument Type

      Value

      startIndex

      Number

      0

      numItems

      Number

      10

    10. Specify Employee in the Array Of field toto define a custom type for returned data.

      Flex uses custom data types to access and update complex data types returned from a server.

      The Configure Return Type dialog displays the properties of the data type returned by the service.

    11. Click Finish to configure the Employee return type.

    12. In the Data/Services view, from the context menu for the getItems_paged() operation, select Enable Paging.

    13. In the Select Unique Identifier dialog, select emp_no and click Next.

    14. In the Confirm Paging dialog, specify the count() operation from the drop-down list. Click Finish.

      You do not have to specify the number of records to retrieve for each page operation.

    Create an application and bind getItems_paged() to a DataGrid

    1. Select Design to open the MXML editor in Design mode.

    2. From the Components view, drag a DataGrid component onto the Design Area and place it near the top.

      The DataGrid component is available under Data Controls.

    3. Make sure the DataGrid component is selected, then in the Properties view specify false for the Editable attribute.

    4. With the DataGrid component selected, from the Data menu select Bind to Data.

    5. In the Bind to Data operation, select New Service Call. For the PagingService, select the getItems_paged() operation. Click OK.

    6. Save and run the file.

      In the application, click in the DataGrid scroll bar or move the thumb of the scroll bar to observe the paging of data.

    Paging tutorial (PHP)

    The application you create in this tutorial accesses the employee database that you previously downloaded and installed.

    This tutorial creates an application that uses paging to populate a DataGrid.

    Create the remote service and import it into a Flex project

    1. In your favorite PHP editor, create a PHP service class that implements the required paging functions.

      <?php 
 
class PagingService { 
    /* connection variables */ 
    var $username = "root"; 
    var $password = ""; 
    var $server = "localhost"; 
    var $port = "3306"; 
    var $databasename = "fb_tutorial_db"; 
    var $tablename = "employees"; 
    var $connection; 
 
    public function __construct() { 
        $this->connection = mysqli_connect( 
            $this->server, 
            $this->username, 
            $this->password, 
            $this->databasename, 
            $this->port); 
 
    $this->throwExceptionOnError($this->connection); 
    } 
 
    public function count() { 
        $stmt = mysqli_prepare($this->connection, 
                "SELECT COUNT(*) AS COUNT FROM $this->tablename"); 
        $this->throwExceptionOnError(); 
 
        mysqli_stmt_execute($stmt); 
        $this->throwExceptionOnError(); 
 
        mysqli_stmt_bind_result($stmt, $rec_count); 
        $this->throwExceptionOnError(); 
 
        mysqli_stmt_fetch($stmt); 
        $this->throwExceptionOnError(); 
 
        mysqli_stmt_free_result($stmt); 
        mysqli_close($this->connection); 
 
        return $rec_count; 
    } 
 
    public function getItems_paged($startIndex, $numItems) { 
 
        $stmt = mysqli_prepare($this->connection, 
            "SELECT * FROM $this->tablename LIMIT ?, ?"); 
        $this->throwExceptionOnError(); 
 
        mysqli_bind_param($stmt, 'ii', $startIndex, $numItems); 
        mysqli_stmt_execute($stmt); 
        $this->throwExceptionOnError(); 
 
        $rows = array(); 
 
        mysqli_stmt_bind_result($stmt, $row->emp_no, $row->birth_date, 
                                    $row->first_name, $row->last_name, 
                                    $row->gender, $row->hire_date); 
 
        while (mysqli_stmt_fetch($stmt)) { 
            $rows[] = $row; 
            $row = new stdClass(); 
            mysqli_stmt_bind_result($stmt, $row->emp_no, $row->birth_date, 
                                        $row->first_name, $row->last_name, 
                                        $row->gender, $row->hire_date); 
        } 
 
        mysqli_stmt_free_result($stmt); 
        mysqli_close($this->connection); 
 
        return $rows; 
    } 
    /** 
     * Utitity function to throw an exception if an error occurs 
     * while running a mysql command. 
     */ 
    private function throwExceptionOnError($link = null) { 
        if($link == null) { 
            $link = $this->connection; 
        } 
        if(mysqli_error($link)) { 
            $msg = mysqli_errno($link) . ": " . mysqli_error($link); 
            throw new Exception('MySQL Error - '. $msg); 
        }     
    } 
 
 
} 
?>

    2. Modify the connection variables to provide your server, username, and password for access to the fb_tutorial_db database.

    3. Save the service in the web root of your PHP installation, as described below.

      • In your web root directory, create a directory named PagingPHP.

      • In the PagingPHP directory, create a directory named services.

      • Name the file PagingService.php and save it the services directory.

    4. In Flash Builder, create a Flex project. Name the project PagingPHP and specify PHP for the server technology. Click Next.

    5. Specify the Web Root and Root URL for your system. Validate your server settings and specify the PagingPHP directory for the Output Folder. Click Finish.

    6. From the Flash Builder Data menu, select Connect to Data Service. Select PHP and click Next.

    7. Click Browse and navigate to the PagingService.php file you created in step 1. Click Finish.

      If the Zend Framework, which includes Zend AMF, is not installed on your system, click OK to install the Zend Framework.

    8. In the Flash Builder Data/Services view, from the context menu for the getItems_paged() operation, select Configure Return Type.

    9. In the Configure Return Type dialog, Auto-Detect the Return Type is selected by default. Click Next.

    10. Specify values and types for the parameters to the getItems_paged() operation as described below. Click Next:

      Argument

      Argument Type

      Value

      startIndex

      int

      0

      numItems

      int

      10

    11. Specify Employee in the Array Of field toto define a custom type for returned data.

      Flex uses custom data types to access and update complex data types returned from a server.

      The Configure Return Type dialog displays the properties of the data type returned by the service.

    12. Click Finish to configure the Employee return type.

    13. From the context menu for the getItems_paged() operation, select Enable Paging.

    14. In the Select Unique Identifier dialog, select emp_no and click Next.

    15. In the Confirm Paging dialog, specify the count() operation from the drop-down list. Click Finish

      You do not have to specify the number of records to retrieve for each page operation.

    Create an application and bind getItems_paged() to a DataGrid

    1. Select Design to open the MXML editor in Design mode.

    2. From the Components view, drag a DataGrid component onto the Design Area and place it near the top.

      The DataGrid component is available under Data Controls.

    3. Make sure the DataGrid component is selected, then in the Properties view specify false for the Editable attribute.

    4. With the DataGrid component selected, from the Data menu select Bind to Data.

    5. In the Bind to Data operation, select New Service Call, then select the get Items Paged() operation from the drop-down list, and click OK.

    6. Save and run the file.

      In the application, click in the DataGrid scroll bar or move the thumb of the scroll bar to observe the paging of data.