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

INTRODUCTION

In the Simple Database Extension tutorial we've seen how to connect to a database and the basics of the Database Manager object . Now we're ready to create some more advanced server side interaction.

In this new example we'll complete our contact list application by adding the possibility to add new records and edit and delete existing ones.

As you can see from the simple interface of the application, you will be able to select any record from the Datagrid component and edit/delete it in the form on the right side or you can add new records using the form on the left side.

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

Extension actions

For this application we will need four different command names, one for each possible action:

We have already seen how to get all the database records and show them in the Datagrid component, so we will analyze how the other three actions are perfomed on the server side.

Adding records

When the "Add New" button is clicked the request is sent to the server:

function addRecord()
{
        // prepare the request object
        var req:Object = {}
        
        if (add_name_txt.text != "")
        {
                req.name 		= add_name_txt.text
                req.location 	= add_location_txt.text
                req.email 		= add_email_txt.text
                
                // Clear textfields
                add_name_txt.text = add_location_txt.text = add_email_txt.text = ""
                
                smartfox.sendXtMessage(extensionName, "addData", req, "xml")
        }
}
            

First we create an empty object and we add the name, location and email properties to it by reading the values in the input text fields. Then we send the message to the extension passing the "addData" as the command name that we want to execute.

Let's see how this is handled in our extension:

//--- Handle and "add record" request ----------------------------------------------------------
else if (cmd == "addData")
{
        var id = getTimer()
        
        // Create the SQL statement to update the record
        var sql = "INSERT INTO contacts (id, name, location, email) VALUES ("
        sql += "'" + id + "', "
        sql += "'" + _server.escapeQuotes(params.name) + "', "
        sql += "'" + _server.escapeQuotes(params.location) + "', "
        sql += "'" + _server.escapeQuotes(params.email) + "')"
        
        var success = dbase.executeCommand(sql)
        
        // If it was successfully updated send a response
        if (success)
        {
                var response 		= {}
                response._cmd		= "addData"
                response.id 		= id
                response.name 		= params.name
                response.location 	= params.location
                response.email 		= params.email
                
                _server.sendResponse(response, -1, null, [user])
        }
}

As you can see the code is almost straightforward. We create the INSERT sql statement dynamically and then call the executeCommand() method on the Database Manager object, which we encounter for the first time.

To summarize, the DatabaseManager object exposes two methods:

It is also important to note that we used the _server.escapeQuotes() method to process each parameter coming from the client. The method fixes possible problems with quotes (') and double quotes (") that may cause SQL syntax errors.
In the sql statement all values to be insterted in the database are enclosed in single quotes('). If one of those values contains the same symbol the statement will be malformed and the command execution will fail. Always remember to use the escapeQuotes() method to avoid these problems.

At the end of the code, if the insertion was successful, a message is sent back to the client with the data inserted and this is the code that handles the response:

else if (resObj._cmd == "addData")
{
        // Remove the _cmd property
        delete resObj._cmd
        
        grid_dg.addItem(resObj)
}

The object received is directly added to the Datagrid, removing the _cmd property that is not needed by the component.

Updating records

Modifying an existing record is very similar to what we've done in the previous example:

function updateRecord()
{
        var obj = grid_dg.selectedItem
        
        if (obj != undefined)
        {
                // prepare the request object
                var req:Object = {}
                
                req.id 			= obj["id"]
                req.name 		= mod_name_txt.text
                req.location 	= mod_location_txt.text
                req.email 		= mod_email_txt.text
                
                smartfox.sendXtMessage(extensionName, "updData", req, "xml")
        }
}

First we check if an element is selected in the Datagrid, the we proceed by sending the id, name, location and email fields to the server by wrapping them into an object as we did before. This time the command to be executed in the extension is called "updData".

The server code is also very similar to what we have done so far:

//--- Handle and "update record" request -------------------------------------------------------
else if (cmd == "updData")
{
        // Create the SQL statement to update the record
        var sql = "UPDATE contacts SET "
        sql += " name='" + _server.escapeQuotes(params.name) + "',"
        sql += " location='" + _server.escapeQuotes(params.location) + "',"
        sql += " email='" + _server.escapeQuotes(params.email) + "'"
        sql += " WHERE id='" + params.id + "'"
        
        var success = dbase.executeCommand(sql)
        
        // If it was successfully updated send a response
        if (success)
        {
                var response 		= {}
                response._cmd		= "updData"
                response.id 		= params.id
                response.name 		= params.name
                response.location 	= params.location
                response.email 		= params.email
                
                _server.sendResponse(response, -1, null, [user])
        }
}

The UPDATE sql command is built using the client side parameters and a response is sent back to the user if the database was updated successfully.

The code in the source .FLA that handles the response looks like this:

else if (resObj._cmd == "updData")
{
        // Remove the _cmd property
        delete resObj._cmd
        
        // Look for the correct row ID
        // If found, replace the row with the new, updated one
        for (var i = 0; i < grid_dg.length; i++)
        {
                var row = grid_dg.getItemAt(i)
                if (row["id"] == resObj.id)
                {
                        grid_dg.replaceItemAt(i, resObj)
                        break
                }
        }
}

We cycle through all the elements in the Datagrid looking for the one with the ID passed by the server: when we find it we replace the object in that position with the new one sent by the extension. Also note that we have deleted the _cmd property as it is not needed in the visual component.

Deleting records and conclusions

Deleting records is also done using the same coding scheme. We check on the client which record was selected, we send the ID of that record to the extension invoking the "delData" command, and we finally send a message to the client (if the operation was successful) so that we can update the local view.

In conclusion we have seen how to interact with databases using this example contact list application. We've kept everything as simple as possible in order to concentrate on the way requests are exchanged between your client and your custom extensions.

Now that you have learned all the basics, you should easily see how these concepts could be applied to real world mutiplayer applications: for example a registration form for an online game, the management of a server side high score table, database driven user profiles, game statuses and much more...