SFS2X Docs / DevelopmentBasics / database-recipes

» Database recipes

In this section we are going to demonstrate several examples of integration between SFS2X and an external database. We used MySQL for all recipes but you can easily use any other database of your choice (read this HowTo tutorial). We used standard SQL in all code so it can be ported to any other RDBMS.

  1. Custom login with database
  2. Querying the database and sending resultsets

Recipe #1 — Custom login with database

This recipe discusses the common scenario in which you need to validate the client credentials against the user profiles stored in a database. We will also show how to execute more custom logic right after the login, for example setting User Variables and joining a Room from server-side.

Implementing a custom login on the server side is a simple process. SFS2X fires the follwoing two login events:

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

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

» The login handler

The LoginEventHandler class is where we check the credentials. In this example we access the DBManager connection and use a PreparedStatement object in order to build the query with parameters and sanitize possible bad characters in the external arguments.

// Grab a connection from the DBManager connection pool
connection = dbManager.getConnection();

// Build a prepared statement
PreparedStatement stmt = connection.prepareStatement("SELECT pword,id FROM muppets WHERE name=?");
stmt.setString(1, userName);

// Execute query
ResultSet res = stmt.executeQuery();

As an alternative to this approach you could use the DBManager.executeQuery(String sql, Object[] params) method, which works similarly without the need to work at the connection level. We didn't use it for this example because at the time of writing it wasn't available yet (requires SmartFoxServer 2X version RC1b or later).

When an errror is encountered (e.g. bad password), we raise an exception of type SFSLoginException and provide an additional error code:

if (!getApi().checkSecurePassword(session, dbPword, cryptedPass))
{
    SFSErrorData data = new SFSErrorData(SFSErrorCode.LOGIN_BAD_PASSWORD);
    data.addParameter(userName);
    
    throw new SFSLoginException("Login failed for user: "  + userName, data);
}

In the case of a bad username or bad password we also specify the name or password used so that this is reported back to the client.

» Handling exceptions

One important aspect to keep in mind when working with a database is proper handling of exceptions. Any call to the database might throw an error if there is a problem with the SQL syntax etc..., so it is critical to make sure that the connection is closed in any cas,e otherwise it will leak and eventually exahust the connection pool. The best way to handle exceptions is the following:

try
{
	connection = dbManager.getConnection();
	// Your database code goes here
}
catch(SQLException sqle)
{
	// Here we handle the SQL failure
}
finally
{
	try
    {
    	connection.close();
    }
    catch(SQLException sqle)
    {
    	// It shouldn't happen, but if it does it's best to leave a trace in the logs
    }
}

The finally block ensures that the connection gets closed in any case before leaving the method, and the additional try/catch block around the close() method handles the rare possibility that an error occurs when the connection is returned to the pool.

» Post login handler

When the USER_JOIN_ZONE event is notified we are ready to do more work with the user, which is now fully logged in the system. The ZoneJoinEventHandler class sets the "dbID" User Variable with the user id coming from the database and finally joins the user in the main lobby room.

public class ZoneJoinEventHandler extends BaseServerEventHandler
{
	@Override
	public void handleServerEvent(ISFSEvent event) throws SFSException
	{
		User theUser = (User) event.getParameter(SFSEventParam.USER);
		
		// dbid is a hidden UserVariable, available only server side
		UserVariable uv_dbId = new SFSUserVariable("dbid", theUser.getSession().getProperty(DBLogin.DATABASE_ID));
		uv_dbId.setHidden(true);
		
		// The avatar UserVariable is a regular UserVariable
		UserVariable uv_avatar = new SFSUserVariable("avatar", "avatar_" + theUser.getName() + ".jpg");
		
		// Set the variables
		List<UserVariable> vars = Arrays.asList(uv_dbId, uv_avatar);
		getApi().setUserVariables(theUser, vars);
		
		// Join the user
		Room lobby = getParentExtension().getParentZone().getRoomByName("The Lobby");
		
		if (lobby == null)
			throw new SFSException("The Lobby Room was not found! Make sure a Room called 'The Lobby' exists in the Zone to make this example work correctly.");
		
		getApi().joinRoom(theUser, lobby);
	}
}

>> DOWNLOAD the source files for this recipe <<

^top

Recipe #2 — 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. 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 sources come with a MySQL dump that can be used to generate the database table used by the Extension (the download link is provided at the end of this recipe). These are the steps to setup and test the example application.

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);
	        
	        // 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.

>> DOWNLOAD the source files for this recipe <<

^top