Application Server Connectivity > Sample 3: Recordset

  

Sample 3: Recordset

Once you have easy access to a database, you add data storage and retrieval to your Flash Communication Server. This simple test retrieves all the recordsets from a database. The work is done by one SQL statement inside a CFM file.

 
About the sample

When the user clicks the Get Records button, all the data in the database appears in the list box.

 
Re-creating the sample

The doc_rset.fla file provides an interface in which the user simply selects the Get Records button and the record set is returned.

Before you start to re-create the sample, see Creating your working environment.

 
To create the user interface for this sample:

1

In the Flash MX authoring environment, select File > New to open a new file.

2

To add an element for listing records, open the Components panel (Windows > Components), drag the List Box component onto the Stage, and give it the instance name records_list.

3

To add the button for connecting to the server, drag a push button from the Components panel onto the Stage. In the Property inspector, give it the instance name GetRecs_btn, the label Get Records, and the click handler doGetRecords.

4

Save the file as doc_rset.fla.

5

Create a directory named doc_rset in your Flash Communication Server applications directory.

 
To write the client-side ActionScript for this sample:

1

Create a new network connection and connect to the Flash Communication Server.

 
// Create a network connection and connect
nc = new NetConnection();
nc.onStatus = function(info) {
	trace(info.level + ": " + info.code + " " + info.description);
}
nc.connect("rtmp:/doc_rset/room_01");

2

Get a shared object, and update the list box (records_list) with the data from the shared object. 

// Get the records from the records shared object
recset_so = SharedObject.getRemote("records", nc.uri, false);

recset_so.onSync = function(list) {

	// Clear the list box
	records_list.removeAll();

	// Fill in the records, with column headers first. Use
	// the getColumnString function, defined below, to get
	// all of the column information.
	records_list.addItem(getColumnString(this.data.__COLUMNS__), -1);

3

In the same method, assign a variable name, recstr, for the data coming from the call to getRecordString and populate the list box with the data.

 
	for (var i in this.data) {
		if (i == "__COLUMNS__")
			continue;
		var recstr = getRecordString(i,this.data[i], this.data.__COLUMNS__);
		records_list.addItem(recstr, i);
	}

	records_list.sortItemsBy("data", "ASC");
}

4

Connect to the shared object.

 
recset_so.connect(nc);

5

Create the getColumnString function to get all the column data. 

// Get a string out of all column headers
function getColumnString(cols) {
	var colstr = "";
	if (cols.length > 0)
		colstr += cols[0];
	for (var i = 1; i < cols.length; i++)
		colstr += ", " + cols[i];
	return colstr;
}

6

Create the getRecordString function.

 
// Get a string containing all records in column order
function getRecordString(inx, recs, cols) {
	var recstr = "";
	if (cols.length > 0)
		recstr += recs[cols[0]];
	for (var i = 1; i < cols.length; i++)
		recstr += ", " + recs[cols[i]];
	return recstr;
}

7

Create the event handler for the GetRecs_btn button. When the user clicks the button, the doGetRecords method is called; this function contains the getRecords call defined in the server-side ActionScript.

 
// Call the server to initiate a database query and place
// the recordset data in 'records' shared object
function doGetRecords() {
	nc.call("getRecords", null);
}

 
To write the server-side ActionScript for this sample:

1

Create a new file using your server-side ActionScript editor, and load the netservices.asc file to enable Flash Remoting through the Flash Communication Server. 

load("netservices.asc");

2

Get the records shared object.

 
gRecords = SharedObject.get("records", false);

//Set the default gateway URL
NetServices.setDefaultGatewayUrl("http://localhost:8500/flashservices/gateway");
//Connect to the gateway.
var gRemotingGateway=NetServices.createGatewayConnection();

3

Create a global object to hold the recordset.

 
// An object to hold your service and recordset
gFoo = {};

4

Get a reference to the foo service.

 
// Get a named service
gFoo.service = gRemotingGateway.getService("foo", gFoo);

5

Create the onResult callback, which the Flash Remoting service expects.

 
// Set up the onResult callback and call it bar_Result.
// The function must be named such that if you call
// xxx method on the foo service, it will result in
// xxx_Result method being called. You set the data in
// the shared object so the Flash client can display it.
gFoo.bar_Result = function(result) {	
	var cols = result.getColumnNames();
	trace("Columns: " + cols);
	gRecords.setProperty("__COLUMNS__", cols);

	var reclen = result.getLength();
	trace("number of records " + reclen);

	for (var i = 0; i < reclen; i++) {
		trace(i + "] " + result.getItemAt(i));
		gRecords.setProperty(i, result.getItemAt(i));
	}
}

6

In the onConnect function, accept the client's connection.

 
application.onConnect = function(client) {
	trace(application.name + " connect from " + client.ip);
	application.acceptConnection(client);
}

7

Provide a prototype function that the client can call to get the records.

 
Client.prototype.getRecords = function() {
	// Call the bar method on foo service
	var result = gFoo.service.bar();
	trace("gFoo.service.bar returned " + result);
}

8

Save the file as main.asc in the /applications/doc_rset directory.

 
To write the ColdFusion component for this sample:

1

Using your standard editor, create a new file and add code to query the database for all records.

 
<cfquery name="flash.result" datasource="ExampleApps">

    SELECT * FROM tblItems

</cfquery>

2

Save the file as bar.cfm in the foo directory under the directory where you publish all of your ColdFusion MX files.