6.3 Connecting to databases

By exploiting the JDBC and ODBC technologies, SmartFoxServer PRO is capable of connecting to almost any existing database engine. To name a few: MySQL, Microsoft Access, Microsoft SQL Server, Oracle, Sybase, IBM DB2 and a lot more.

Accessing external data sources will enable developers to create complex multiplayer applications and games, adding data persistence and allowing to access existing databases.

» The Database Manger

The SmartFoxServer extension API exposes an object called DatabaseManager, which is responsible for connecting to the external data source and interact with it. For each Zone in your SmartFoxServer configuration you can activate a separate DatabaseManager, allowing each application running in the server to connect to a different data source.

» JDBC, ODBC and drivers.

In order to connect to your database engine you have two possibilities:

1) Use the Java Runtime built-in ODBC connector
2) Download and install the specific JDBC driver for your database engine.

In the first case you don't need to do anything and you could skip to the next section in this article. The ODBC connector is already provided in your Java Runtime and you don't need to configure anything else.

Using a JDBC driver is also very common where the database engine does not support ODBC natively (MySQL for example) or when the JDBC driver delivers better performance than the ODBC one.
In this case we reccomend to download the latest version of the driver and install it, by copying it to your JRE lib/ext/ directory, so that it will be available to the Java runtime without having to mess with the Java classpath.

» Configuring database connections.

Here follows the XML used in the config.xml file to set up a connection with a MS Access database:

<DatabaseManager active="true">

	<Driver>sun.jdbc.odbc.JdbcOdbcDriver</Driver>
	<ConnectionString>jdbc:odbc:sfsTest</ConnectionString>
	<UserName>myUserName</UserName>
	<Password>myPassword</Password>

	<TestSQL><![CDATA[SELECT COUNT(*) FROM contacts]]></TestSQL>
	
	<MaxActive>10</MaxActive>
	<MaxIdle>10</MaxIdle>
	  
	<OnExhaustedPool>fail</OnExhaustedPool>
	<BlockTime>5000</BlockTime>
	
</DatabaseManager>

Even if it may look like a lot of paramaters, those that we need to specify are only a few.
The first thing to notice is the active attribute in the <DatabaseManager> tag which will turn on or off the connection to the data source. If you don't really need the Database Manager in your Zone you can completely omit it.

The next four parameters are essential for a successfull connection:

Driver   This is the name of the driver that SmartFoxServer PRO will use to connect to your database. In this case we use the default ODBC driver provided with the Java Runtime
ConnectionString   This is the database connection string. The first part of it "jdbc:odbc:" should not be modified while the last "sfsTest" is the name of the Access database that we want to use
UserName   This is the user name for the database
Password   This is the password for the database

The last parameter we should specify is a test SQL statement that SmartFoxServer will run after the connection is established to verify that the database is accessible. The <TestSQL></TestSQL> tag can contain any valid SQL statement. If the test string fails you will receive an error and you should check what's wrong with your settings.

If we were going to connect to a MySQL database the configuration would have looked like this:

<DatabaseManager active="true">

	<Driver>org.gjt.mm.mysql.Driver</Driver>
	<ConnectionString>jdbc:mysql://192.168.0.15:3306/sfsTest</ConnectionString>
	<UserName>myUserName</UserName>
	<Password>myPassword</Password>

	<TestSQL><![CDATA[SELECT COUNT(*) FROM contacts]]></TestSQL>
	
	<MaxActive>10</MaxActive>
	<MaxIdle>10</MaxIdle>
	  
	<OnExhaustedPool>fail</OnExhaustedPool>
	<BlockTime>5000</BlockTime>

As you can see the only thinsg that change are the driver name and the connection string. This time the "jdbc:mysql" tells the server to use a MySQL driver for the connection, and the remaining part of the string indicates the db server address, the db server port and the name of the database we want to use.

» Advanced settings: connection pool

Each Database Manager is backed by a connection pool which allows better performance and reuse of the server resources.
We usually reccomend not to change the last four parameters in the configuration block, however there may be cases in which you would need to adjust these values based on the requirements of your application.

( If you don't know what a Database connection-pool is, we reccomend to use the default settings. )

The following table illustrates the meaning of the those tags:

MaxActive   The max. number of pooled connections
MaxIdle   The max. number of idle pooled connections (-1 = no limits)
OnExhaustedPool   Specifies what action should be taken when the pool of connections is exhausted:

fail >> returns an error (RECCOMENDED)
grow >> grow the pool (Attention! You may need extra heap memory in your JVM or you could crash it)
block >> block for some time before returning an error
BlockTime   used only if you choose to block when the pool is exhausted. Expressed in ms.




doc index