8.3 Tutorials: Simple Database Extension
The source FLA of this example is found under the Examples/AS2/pro_dbExtension folder. |
» Introduction
This tutorial will show you how to connect to an existing database, 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 dbExtension.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 contacts]]></TestSQL>
<MaxActive>10</MaxActive> <MaxIdle>10</MaxIdle>
<OnExhaustedPool>fail</OnExhaustedPool> <BlockTime>5000</BlockTime>
</DatabaseManager>
Driver | The name of the driver that should be loaded by SmartFoxServer in order
to talk to the database. In the examples provided we will use the JDBC-ODBC driver provided by Sun. It's fully qualified name is: sun.jdbc.odbc.JdbcOdbcDriver In the above example there's also an example (between comment signs) on how to use the MySql driver. (not provided by Sun JRE, must be downloaded from www.mysql.com) |
ConnectionString | The database connections string. Using the JDBC-ODBC you only have to change the last name, which is the name of the database you want to use. |
UserName | A username for the database (optional) |
Password | A passord for the database (optional) |
TestSQL | A simple SQL statement that will be executed by SFS to test the connection. |
For a more in-depth view of the Database Manager please check also chapter
6.3 of the 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 (it is located in the example folder)
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) }
var extensionName:String = "dbTest" butt_load.addEventListener("click", getData)
function getData() { smartfox.sendXtMessage(extensionName, "getData", {}, "xml") }
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) }
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]) } } } }
doc index |