Setting Database Connections in JBoss from ATG

ATG communicates to a database via JBoss via a dynamo server setup or in your home/localconfig directory if you’re not using specific dynamo servers. In this setup you specify the JNDI connection name which will then refer to an XML file which makes up part of your JBoss server. So to summarise ATG connects via its own JNDI config file which maps to an XML file on the JBoss server. JBoss then handles the connection out to the data source via a JDBC call – this can be any type of database or repository potentially.

Here’s how to set the database connections for something like Oracle. For MySQL etc… the procedure is pretty much the same and you can find example XML configs for these in your JBoss install the only real difference is the driver used for the connection.

First start with JBoss and setup the XML. Go to your servers deploy directory: \jboss\server\MY_SERVER_NAME\deploy. If you haven’t setup a server then this will be called default – if you’ve installed ATG then there will be a server called ATG. In your deploy directory there will be XML files normally with the mention of ‘ds’ in the file name, to let us know its for a data Source. If you go to:  \jboss\docs\examples\jca you should find example XML connection files here.

Alternatively using the below code we can create one for Oracle called something like ATG-Oracle-DS.xml.

In this XML file we basically set the JNDI reference name, the JDBC connection URL which is the database server IP/name along with the port and the SID (Service Identifier). We then set the schema that we want to connect to and it’s password. It’s the same for any database connection, although you’ll also see that we set the driver to use for the connection (more on this after the below XML)…

<?xml version="1.0" encoding="UTF-8"?>

<datasources>

<local-tx-datasource>

<!-- The jndi name of the DataSource, it is prefixed with java:/ -->

<!-- Datasources are not available outside the virtual machine -->

<jndi-name>ATGOracleDS</jndi-name>

<!-- The jdbc url -->

<connection-url>jdbc:oracle:thin:@MY_ORACLE_DB_URL:1521:MY_SID</connection-url>

<!-- The driver class -->

<driver-class>oracle.jdbc.OracleDriver</driver-class>

<!-- The login and password -->

<user-name>SCHEMA_NAME</user-name>

<password>PASSWORD</password>

<!-- The minimum connections in a pool/sub-pool.

Pools are lazily constructed on first use -->

<min-pool-size>10</min-pool-size>

<!-- The maximum connections in a pool/sub-pool -->

<max-pool-size>10</max-pool-size>

</local-tx-datasource>

</datasources>

OK so the XML is pretty simple – you can have as many connections in one XML file as you need but sometimes it’s easier to keep them in separate files to identify connections. You can also MIX connections, so I can have a schema on Oracle, another on MsSQL and a third on MySQL and ATG can read and use data from all of these as part of its data anywhere architecture.

Here’s the gotcha – you MUST make sure the driver/ libary jar file is installed for your server to connect to your database. So go to your server’s lib directory e.g. \jboss\server\MY_SERVER_NAME\lib and for Oracle you will need a jar called ojdbc14.jar which contains the necessary classes for JBoss to connect to your database – generally JBoss does come with most database library jars but you may need to hunt this one down on Oracles website.

So We’ve set an XML file that specifies a connection for JBoss to use, we’ve added the necessary libary files to our JBoss server so it can make the connection. Finally we need ATG to be configured to use this connection, this is the really easy part!

In ATG the sources are referenced via the Dynamo servers localconfig, so for example in: \ATG\ATG2007.1\home\servers\MY_SERVER_NAME\localconfig\atg\dynamo\service\jdbc or if you’re not using Dynamo servers then just look in home\localconfig\atg\dynamo\service\jdbc.

If you’re building an external EAR file for your deployment then include this  in your Dynamo servers that you export with your EAR file. Anyway in this config directory you need to make a .properties file to set the JNDI connection to use. Just add the below 2 lines – the JNDIName variable should reference the JNDI name in your XML file.

$class=atg.nucleus.JNDIReference

JNDIName=java:/ATGOracleDS

And thats it! You should be able to map any database for use with ATG replacing the Solid database that comes with ATG.

Now a word of warning – it’s fine using additional databases for ATG but if you want to completely replace the Solid database, and in a production environment this is a must, you will need to load in the tables to your database for ATG to use, but I’ll write this up in a separate post – It’s pretty easy as ATG ships with various SQL setup scripts for you to use to achieve this.