Home > Uncategorized > Pagination across DB’s whilst using ColdFusion services in Flash Builder!

Pagination across DB’s whilst using ColdFusion services in Flash Builder!

Hey All, hope you guys have got a hang on the latest Flash Builder and its data centric features!

Know what, Flash Builder now also provides “Pagination” and client-side “Data Management” capabilites for the services.

FYI, “What is Pagination?”

If you have a form which allows the user to browse through the rows in a database table, what do you do if that table has hundreds or even thousands of rows? It would not be a good idea to show all those rows in a single form, instead you should split the database output into more manageable chunks or ‘pages’.

Let me consider a simple example considering a Cold Fusion service(CFC with utilities for create,update,delete etc). Here is how you write the paging functions for MySQL,MSSQL and Apache Derby.

Please follow the following instructions to create a CF service in Flash Builder :

Installers required: Flash Builder and ColdFusion8 server.

Flex project creation with CF :

a) Launch FB, select File->New->Flex Project.

b) Enter Pagination, select “Application Server Type” as ‘ColdFusion’,select “Use remote object access” and ‘Coldfusion Flash Remoting’,NEXT

c) In the “Configure ColdFusion Server” wizard, enter “C:ColdFusion8″(assuming this is the coldfusion installation folder) as ‘ColdFusion root folder’, enter “C:\ColdFusion8\wwwroot” in the ‘Web root’ and type “http://localhost:8500” as ‘Root URL’

d) Validate the configuration and click ‘FINISH’.

ColdFusion Service:

a) Go to Data/Services tab and click on  ‘Connect to Data/Services’ ,select ColdFusion and click NEXT.

b) Enter “PaginationService” as service name, select “Generate Sample CFC”  and click FINISH.

(A dialog to enter the remote authentication credentials would be shown, enter appropriate credentials(if server is configured for RDS) or select ‘No Password’ and OK)

The service file would open up in the default editor. Replace the code for getItems_Paged with :

a) Apache Derby DB

<cffunction name=”getBOOKS_paged” output=”false” access=”remote” returntype=”any” >
<cfargument name=”startIndex” type=”numeric” required=”true” />
<cfargument name=”numItems” type=”numeric” required=”true” />
<!— TODO Auto-generated method
Return a page of numRows number of records as an array or query from the database for this startRow.
Add authorization or any logical checks for secure access to your data —>
<!— Sample Code —>
<!— The LIMIT keyword is valid for mysql database only, you can modify it for your database —>
<cfset var qRead=””>
<cfquery name=”qRead” datasource=”cfbookclub”>
SELECT * FROM (Select ROW_NUMBER () OVER() AS rownum,BOOKS.* FROM BOOKS) AS tmp WHERE rownum > startIndex AND rownum <= numItems
</cfquery>
<cfreturn qRead>
</cffunction>

<cffunction name=”getBOOKS_paged” output=”false” access=”remote” returntype=”any” >

<cfargument name=”startIndex” type=”numeric” required=”true” />

<cfargument name=”numItems” type=”numeric” required=”true” />

<!— TODO Auto-generated method Return a page of numRows number of records as an array or query from the database for this startRow.

<cfset var qRead=””>

<cfquery name=”qRead” datasource=”<cf_data_source>”>

SELECT * FROM (Select ROW_NUMBER () OVER() AS rownum,<table>.* FROM <table>) AS tmp WHERE rownum > startIndex AND rownum <= numItems

</cfquery>

<cfreturn qRead>

</cffunction>

b) MSSQL DB

<cffunction name=”getbug77244_paged” output=”false” access=”remote” returntype=”any” >

<cfargument name=”startIndex” type=”numeric” required=”true” />

<cfargument name=”numItems” type=”numeric” required=”true” />

<!— TODO Auto-generated method

Return a page of numRows number of records as an array or query from the database for this startRow.

<!— Sample Code —>

<cfset var qRead=””>

<cfquery name=”qRead” datasource=”<cf_data_source>”>

WITH paged_t AS (Select * ,ROW_NUMBER() over(order by <primary_key>) as limit from <table>)

select top(numItems)* from paged_t where limit >= startIndex

</cfquery>

<cfreturn qRead>

</cffunction>

c) MYSSQLDB

<cffunction name=”getemployees_paged” output=”false” access=”remote” returntype=”any” >

<cfargument name=”startIndex” type=”numeric” required=”true” />

<cfargument name=”numItems” type=”numeric” required=”true” />

<!— TODO Auto-generated method
Return a page of numRows number of records as an array or query from the database for this startRow.
<!— Sample Code —>

<cfset var qRead=””>

<cfquery name=”qRead” datasource=”<cf_data_source>”>

SELECT * FROM <table>LIMIT #startIndex#, #numItems#

</cfquery>

<cfreturn qRead>

</cffunction>

From FB drag-drop a Data Grid from ‘Components’ panel and drag-drop the code mentioned above and launch the app to see your data ‘PAGED’.
Enjoy 🙂

Advertisements
Categories: Uncategorized
  1. July 16, 2009 at 1:57 pm

    Great Dude.. will keep watching this blog… nice work

    • balajisridhar
      July 17, 2009 at 2:39 pm

      Thanks a lot Kaushik 🙂

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: