The related source file for this article is found in the "Examples/mx2004/pro_dbExtension" folder.

INTRODUCTION

This tutorial will show you how to connect to an existing database in SmartFoxServer PRO, retrieve data from it and finally show it on the client side using a DataGrid component. In order to keep things simple we have included a .mdb Access database file, that you will find in the example folder: the database is a simple contact list with the name, location and email address of friends and co-workers.

The example connects to the Zone called "dbZone" and it will call methods on the extension called dbExample.as, that you can find in the main sfsExtensions/ folder.

Setting up a database connection

SmartFoxServer PRO can talk to almost all database engines out there. Connections can be done through native JDBC database drivers (available from all major DB producers) or using the JDBC-ODBC bridge driver provided by Sun in the Java Runtime.

Each Zone can specify a different database configuration. Here's an example:

<DatabaseManager active="true">

	<Driver>sun.jdbc.odbc.JdbcOdbcDriver</Driver>
	<ConnectionString>jdbc:odbc:sfsTest</ConnectionString>

	<!-- If you use MySQL, the connections string becomes...
	<Driver>org.gjt.mm.mysql.Driver</Driver>
	<ConnectionString>jdbc:mysql://192.168.0.15:3306/sfsTest</ConnectionString>
	-->

	<UserName>username</UserName>
	<Password>password</Password>


	<TestSQL><![CDATA[SELECT COUNT(*) FROM users]]></TestSQL>


	<MaxActive>10</MaxActive>
	<MaxIdle>10</MaxIdle>


	<OnExhaustedPool>fail</OnExhaustedPool>
	<BlockTime>5000</BlockTime> 

</DatabaseManager>

Some of the last parameters (maxActive, maxIdle, OnExhaustedPool and BlockTime) are pretty advanced settings and we can skip them for now. Let's concentrate on the configuration basics:

For a more in-depth view of the Database Manager please check also the SmartFoxServer documentation.

Setting up the example database (Windows NT-2000-XP only)

In order to make the database file available through ODBC you must create a new ODBC data source name (DSN) under Windows.

» Open the Control Panel, click on Administrative Tools > Data sources (ODBC)
» In the User DSN tab, click Add
» Choose Microsoft Access driver (*.mdb)
» In the data source name field write sfsTest
» Under the database label, click on Choose... navigate to the SFS folder and select the provided .mdb file

The database should be now accessibile through ODBC.

THE CLIENT SIDE

You can now open the source .FLA file, skip directly to the "chat" label in the main timeline, and open your code view.

function setupDataGrid()
{
        var col_nick:DataGridColumn 	 = new DataGridColumn("name")
        var col_location:DataGridColumn  = new DataGridColumn("location")
        var col_email:DataGridColumn 	 = new DataGridColumn("email")
        
        
        col_nick.width = 200
        col_location.width = 100
        col_email.width = 100
        
        
        grid_dg.addColumn(col_nick)
        grid_dg.addColumn(col_location)
        grid_dg.addColumn(col_email)
}

The first function we encounter sets up the datagrid component that will show the contact list. The code is very simple: we just create the three columns we need (name, location and email) and add them to the component, setting also each column width.

A few lines below in the source code, we have the main button listener which delegates the click handling to the getData() function. Also we set a variable called extension name to "dbTest". This is the name of the extension that we will use in this example (check the config.xml for the "dbTest" Zone).

var extensionName:String = "dbTest"
butt_load.addEventListener("click", getData)

The function is made of one line only:

function getData()
{
        smartfox.sendXtMessage(extensionName, "getData", {}, "xml")
}

The code calls the "getData" action of our server side extension, passing an empty object as third parameter (no arguments passed to the extension).

The server side

You can now check what happens in the extension (sfsExtensions/dbExtension.as):

var dbase

function init()
{
        trace("Initing dbExtension")
        
        // get a reference to the database manager object
        // This will let you interact the database configure for this zone
        dbase = _server.getDatabaseManager()
}


function destroy()
{
        // Release the reference to the dbase manager
        delete dbase
}


function handleRequest(cmd, params, user, fromRoom)
{
        if (cmd == "getData")
        {
				// create a SQL statement
                var sql = "SELECT * FROM contacts ORDER BY name"
                
                // execute query on DB
                // queryRes is a ResultSet object
                var queryRes = dbase.executeQuery(sql)

				// prepare the response object
                var response = {}
                
                response._cmd = "getData"
                
                // Here we create an array for storing the database data
                response.db = []
                
                if (queryRes != null)
                {
                        // Cycle through all records in the ResultSet
                        for (var i = 0; i < queryRes.size(); i++)
                        {
                                // Get a record
                                var tempRow = queryRes.get(i)
                                
                                // This object will hold the record data that we'll send to the client
                                var item = {}
                                
                                // From the record object we can get each field value
                                item.name 		= tempRow.getItem("name")
                                item.location 	= tempRow.getItem("location")
                                item.email 		= tempRow.getItem("email")
                                
                                response.db.push( item )
                        }
                }
                else
                	trace("DB Query failed")
                
                _server.sendResponse(response, -1, null, [user])
                
                
        }
}


function handleInternalEvent(evt)
{
        // Simply print the name of the event that was received
        trace("Event received: " + evt.name)
}

In the first line we declare a global variable called "dbase" which is immediately initialized as a reference to the Database Manager, in the init() method. In the the destroy() method the same variable is deleted to release the reference to the Database Manager object (you can read more about this object in the Server-Side Actionscript API documentation).

Now we can inspect the handleRequest() function: as you can see from the code it handles our "getData" command we've sent from the client.

The executeQuery() method of the Database Manager object runs the passed SQL statement and returns a collection of records (RecordSet). If the returned value is null some error occurred while querying the database and you should check your server console or logs to check what happened.

The returned RecordSet has a size() method that tells us how many records are there in the collection, so that we can cycle through it.

In order to send the records back to the client we create the response object and we setup an array property called db, that will contain objects. Each object will represent one of the records and it will have three properties corresponding to the database fields: name, location, email.

Inside the for loop we use the get() method of the RecordSet to get a Record object and store it temporarily in a local variable called tempRow. Finally we can get the values of each field by calling the getItem() method of the Record object and send the data back to client.

Back to client

Handling the server response is really very simple:

smartfox.onExtensionResponse = function(resObj:Object, type:String)
{
        // We expect the response to be xml formatted
        if (type == "xml")
        {
                // Let's check if it's a "getData" message
                if (resObj._cmd == "getData")
                {
						// Cycle through the "db" object sent by
                        // the server and add it to the datagrid for visualization
                        for (var i = 0; i < resObj.db.length; i++)
                        {
                                grid_dg.addItem(resObj.db[i])
                        }
                }
        }
}

Once we've checked the response format (xml) and command name ("getData") we can finally loop through the array of object and add each item to the DataGrid component.