Thursday, 25 November 2010

Linking SmartFoxServer and MySQL

How to get a zone set up to use a specific database is actually rather well covered in the documentation. Here's my set up after doing that:
The important bits to change are the connection string, which will need the right port for the MySQL instance that you want, and the database name (test_db in the example above). The username and password are for the database. I have used the oh-so-insecure root with no password, but normally you would want to create a specific user with a strong password who only has access to that database.

The test SQL is used by the server when it sets up the zone to check that the server connection is good. If your test SQL is broken it will refuse to connect, but it doesn't really matter what the result of your test is. Short and sweet might be good! It will need to be specific for each database.

The code side of talking to the database is more convoluted and we will probably end up wrapping some of our common actions. It is dealt with in the server-side code. It relies on us having access to the Zone object,  which has a DBManager property that can access the database using the connection settings above.

This is an example of the code that could be used in a request handler class that extends the BaseClientRequestHandler:

    SFSExtension parentExtension = this.getParentExtension();
    Zone parentZone = parentExtension.getParentZone();
    IDBManager dbManager = parentZone.getDBManager();
    try {
        dbManager.executeUpdate("INSERT  INTO log VALUES ('"+sender.getName()+"',     
        'input two numbers to add')");
    } catch (SQLException e) {
        // TODO Auto-generated catch block
That will successfully write data to the table in the database linked to in the zone manager when that request handler is fired. 

No comments:

Post a Comment