8.4 Tutorials: Advanced Database Extension
The source FLA of this example is found under the Examples/AS2/pro_dbExtensionAdvanced folder. |
» Introduction
Now that we've seen how to connect to a database and the basics of the Database
Manager object 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 dbExtensionAdv.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:
getData | get all the records from the db | |
addData | add a new record | |
updData | update a record | |
delData | delete a record |
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") } }
//--- 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]) } }
executeQuery | Executes any SQL "SELECT" statement. It returns a "RecordSet". | |
executeCommand | Execute any other SQL command (INSERT, UPDATE, DELETE ... etc ...). Returns a boolean telling if the operation was successful |
else if (resObj._cmd == "addData") { // Remove the _cmd property delete resObj._cmd grid_dg.addItem(resObj) }
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") } }
//--- 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]) } }
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 } } }
doc index |