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:
Paging tutorial (ColdFusion)The application you create in this tutorial accesses the fb_tutorial_db database.
For information on installing this database, see Installing the Flash Builder tutorial database
This tutorial creates an application that uses paging to populate
a DataGrid.
Create the remote service and import it into a Flex projectIn your web root create a folder named PagingCF.
In your favorite ColdFusion editor, create the following
CFC. Name the CFC PagingService.cfc and place it
in the PagingCF folder in your web root.
PagingService.cfc
contains the functions required by Flash Builder to implement paging.
<cfcomponent output="false">
<!---
This sample service contains functions that illustrate typical service operations.
This code is for prototyping only.
Authenticate the user prior to allowing them to call these methods. You can find more
information at http://www.adobe.com/go/cf9_usersecurity
--->
<cffunction name="getItems_paged" output="false" access="remote" returntype="any" >
<cfargument name="startIndex" type="numeric" required="true" />
<cfargument name="numItems" type="numeric" required="true" />
<!--- The LIMIT keyword is valid for MySQL database only, modify according to your database --->
<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>
Save PagingService.cfc in your web root.
Place it in a folder named PagingCF.
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
Validate your ColdFusion settings and specify the PagingCF
directory for the Output Folder. Click Finish.
From the Flash Builder Data menu, select Connect to Data
Service. Select ColdFusion and click Next.
Click Browse and navigate to the PagingService.cfc file you
created in step 2. Click Finish.
In the Flash Builder Data/Services view, from the context
menu for the getItems_paged() operation, select
Configure Return Type.
In the Configure Return Type dialog, Auto-Detect the Return
Type is selected by default. Click Next.
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
|
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.
Click Finish to configure the Employee return type.
In the Data/Services view, from the context menu for the getItems_paged() operation,
select Enable Paging.
In the Select Unique Identifier dialog, select emp_no and
click Next.
In the Confirm Paging dialog, leave the Page Size field blank.
Specify the count() operation from the drop-down
list. Click Finish.
By default, Flash Builder configures the
page size to be 20 records. This dialog allows you to specify a
custom page size for an operation.
Create an application and bind getItems_paged() to a DataGridIf the MXML editor is in Source mode, select Design
to open the editor in Design mode.
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.
Make sure the DataGrid component is selected, then in the
Properties view specify false for the Editable
attribute.
With the DataGrid component selected, from the Data menu
select Bind to Data.
In the Bind to Data operation, select New Service Call. For
the PagingService, select the getItems_paged() operation.
Click OK.
 Bind To Data 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 projectIn your web root create a folder named PagingPHP.
In the PagingPHP directory, create a directory
named services.
In your favorite PHP editor, create the following PHP file
that implements the required paging functions. Name the file PagingService.php and
save it in the services directory.
<?php
/**
* This sample service contains functions that illustrate typical service operations.
* This code is for prototyping only.
*
* Authenticate users before allowing them to call these methods.
*/
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);
}
}
}
?>
In PagingService.php, modify the connection
variables to provide your server, username, and password for access
to the fb_tutorial_db database.
In Flash Builder, create a Flex project. Name the project
PagingPHP and specify PHP for the server technology. Click Next.
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.
From the Flash Builder Data menu, select Connect to Data
Service. Select PHP and click Next.
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.
In the Flash Builder Data/Services view, from the context
menu for the getItems_paged() operation, select
Configure Return Type.
In the Configure Return Type dialog, Auto-Detect the Return
Type is selected by default. Click Next.
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
|
20
|
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.
Click Finish to configure the Employee return type.
In the Select Unique Identifier dialog, select emp_no and
click Next.
In the Confirm Paging dialog, leave the Page Size field blank.
Specify the count() operation from the drop-down
list. Click Finish.
By default, Flash Builder configures the
page size to be 20 records. This dialog allows you to specify a
custom page size for an operation.
Create an application and bind getItems_paged() to a DataGridIf the MXML editor is in Source mode, select Design
to open the MXML editor in Design mode.
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.
Make sure the DataGrid component is selected, then in the
Properties view specify false for the Editable
attribute.
With the DataGrid component selected, from the Data menu
select Bind to Data.
In the Bind to Data operation, select New Service Call, then
select the get Items Paged() operation from the
drop-down list,. Click OK.
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.
|
