Querying the database and sending resultsets

This recipe shows how to read data from a database and send it to the client in a convenient format.

We use MySQL for this recipe, but you can easily use any other database of your choice (read this tutorial). We use standard SQL in all code so it can be ported to any other RDBMS.

The SFSDBManager.executeQuery method on the server-side provides an effective way to execute a SQL query and obtain an SFSArray-based data strcuture which can be sent to the client on the fly.
For more details on this we recommend to consult the SFSDBManager javadoc.

In this simple example we are going to obtain a list of records from a people database and send them over to the client in a few lines of code.

The source files (the download link is provided at the end of this recipe) come with a MySQL dump that can be used to generate the database table used by the Extension. These are the steps to setup and test the example application.

  • We need a database called sfs2x, if you don’t have one please create it.
  • Use the provided people-table.sql file to import the ‘people’ table into the database.
  • Deploy the extension by copying it in a subfolder of {sfs-install-dir}/SFS2X/extensions/. You can choose any name for the subfolder, for example peopleExt.
  • Launch the AdminTool, choose the Zone Configurator module and edit the BasicExamples zone. Set the Extension for the Zone as follows:
    • Name: peopleExt (or the name you have chosen, if different)
    • Type: JAVA
    • Main class: sfs2x.extension.test.people.PeopleExtension
  • In the General tab of the Zone Configurator make sure that the custom loginis turned off.
  • At this point you should be ready to restart SFS2X, unless you also need to setup the database connection. If you haven’t done this previously we suggest to follow this other recipe.

You can now startup the provided client and test the Extension.

» The request handler

In the source code you will find a GetPeopleHandler class which responds to the “getPeople” request coming from the client. Let’s take a look at what it does:

public class GetPeopleHandler extends BaseClientRequestHandler
{
    @Override
    public void handleClientRequest(User sender, ISFSObject params)
    {
        IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
        String sql = "SELECT * FROM people";
         
        try
        {
            // Obtain a resultset
            ISFSArray res = dbManager.executeQuery(sql, new Object[] {});
             
            // Populate the response parameters
            ISFSObject response = new SFSObject();
            response.putSFSArray("people", res);
             
            // Send back to requester
            send("getPeople", response, sender);
        }
        catch (SQLException e)
        {
            trace(ExtensionLogLevel.WARN, "SQL Failed: " + e.toString());
        }
    }
}

The returned SFSArray contains all the records represented as SFSObject(s), which makes it very easy to extract the field values. This is how it is done on the client side (ActionScript 3) when the EXTENSION_RESPONSE event is handled:

private function onExtensionResponse(evt:SFSEvent):void
{
    var params:ISFSObject = evt.params.params as ISFSObject
    var peopleArray:ISFSArray = params.getSFSArray("people")
     
    var dump:String = "PEOPLE LIST RECEIVED:\n\n"
     
    for (var i:int = 0; i < peopleArray.size(); i++)
    {
        var item:ISFSObject = peopleArray.getSFSObject(i)
        dump += "    > " + item.getUtfString("name") + ", " + item.getUtfString("location") + ", " + item.getUtfString("occupation") + "\n"
    }
     
    dTrace(dump)
    dTrace("Total records: " + peopleArray.size())
}

All we need to do is loop through each element in the received SFSArray. Each item is an SFSObject that represents one row of the resultset which allows us to access its fields by name. In our case the field names are: name, location, occupation.