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>
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 |