How to configure XA transactions with Microsoft SQL Server and TIBCO EMS Server in JBoss

This article describes the processes involved in enabling XA transactions within an SQL Server and TIBCO EMS environment.

Prerequisites
1. SQL Server JDBC Driver
2. JBoss 5.1.0
3. TIBCO EMS server
4. An SQL Server 2000/2005 instance

Enabling XA transactions in SQL Server:
1. Download the SQL Server JDBC Driver from here and unzip the file.

2. Prior to running the SQL script you must copy the extended stored procedure dll SQLJDBC_XA.dll to the target SQL Server's Binn folder (typically in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn).

3. Permissions to the distributed transaction support procedures for the Microsoft SQL Server JDBC Driver 2.0 are granted through the SQL Server role SqlJDBCXAUser. To maintain a secure default configuration, no user is granted access to this role by default.

4. Run the following SQL script in the target SQL Server:

use master
go

-- Drop any existing procedure definitions.
exec sp_dropextendedproc 'xp_sqljdbc_xa_init' 
exec sp_dropextendedproc 'xp_sqljdbc_xa_start'
exec sp_dropextendedproc 'xp_sqljdbc_xa_end'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare'
exec sp_dropextendedproc 'xp_sqljdbc_xa_commit'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget'
exec sp_dropextendedproc 'xp_sqljdbc_xa_recover'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_init_ex'
go

-- Install the procedures.
exec sp_addextendedproc 'xp_sqljdbc_xa_init', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_start', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_end', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_commit', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_recover', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_init_ex', 'SQLJDBC_XA.dll'
go

-- Create the [SqlJDBCXAUser] role in master database.
-- The SQL administrator can later add users to this role to allow users to participate 
-- in Microsoft SQL Server JDBC Driver 2.0 distributed transactions.
sp_addrole [SqlJDBCXAUser]
go

-- Grant privileges to [SqlJDBCXAUser] role to the extended stored procedures.
grant execute on xp_sqljdbc_xa_init to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_start to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_end to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_commit to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_recover to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_init_ex to [SqlJDBCXAUser]
go

-- Add users to the [SqlJDBCXAUser] role as needed.

-- Example for adding a SQL authentication user to the SqlJDBCXAUser role.
-- exec sp_addrolemember [SqlJDBCXAUser], 'MySQLUser'

-- Example for adding a windows domain user to the SqlJDBCXAUser role.
-- exec sp_addrolemember [SqlJDBCXAUser], 'MyDomain\MyWindowsUser'

print ''
print 'SQLJDBC XA DLL installation script complete.'
print 'Check for any error messages generated above.'

5. From Control Panel, open Administrative Tools, and then open Component Services. You can also click the Start button, click Run, type dcomcnfg in the Open box, and then press OK to open Component Services.

6. Expand Component Services, Computers and right-click My Computer, and then select Properties.

7. Click the MSDTC tab, and then click Security Configuration.

8. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

9. Click OK again to close the Properties dialog box, and then close Component Services.

10. Stop and then restart SQL Server to ensure that it syncs up with the MS DTC changes.

Configuring an XA Datasource
This is a sample XA Datasource to connect to SQL Server 2005 and SQL Server 2000. This -ds.xml file must be place under the deploy folder of your server.

serviceName-ds.xml

    
        nameOfDataSource
        
        com.microsoft.sqlserver.jdbc.SQLServerXADataSource
        server
        database
        cursor
        username
        password
        jdbc:sqlserver://server:1433;databasename=database
        false
        
        select 1
        
        select 1
        
        
            
            MS SQLSERVER2000
        

    

Configuring TIBCO EMS Provider
This is a sample JMS Provider to connect to TIBCO EMS Server. Please note that the Connection Factory uses tx-connection-factory element which tells the Transaction Manager that this connection will take part in JTA transactions. This -ds.xml file must be place under the deploy folder of your server.

ems-ds.xml

EmsQueueProvider
org.jboss.jms.jndi.JNDIProviderAdapter
XAQueueConnectionFactory
XAQueueConnectionFactory
XATopicConnectionFactory

java.naming.factory.initial=com.tibco.tibjms.naming.TibjmsInitialContextFactory
java.naming.provider.url=tcp://server:port
java.naming.security.principal=username
java.naming.security.credentials=password




EMSQueueFactory
jms-ra.rar
org.jboss.resource.adapter.jms.JmsConnectionFactory
javax.jms.Queue
java:/EmsQueueProvider
20
username
password

Also add the following factories to your EMS Server as follows:

create factory XAQueueConnectionFactory xaqueue url=tcp://server:port
create factory XATopicConnectionFactory xatopic url=tcp://server:port
create factory XAConnectionFactory xageneric url=tcp://server:port

Sending messages to destinations
The following code snippet creates an Connection, creates a transacted session and sends a message to a queue. The message will appear in the queue when the container commits the transaction.

Context ctx = new InitialContext();
        // get the connection factory fron jndi and create a connection
        ConnectionFactory connectionFactory = (ConnectionFactory)ctx.lookup("java:/EMSQueueFactory");
        Connection connection = connectionFactory.createConnection();

        // create a session
        QueueSession session = connection.createQueueSession(true,javax.jms.Session.AUTO_ACKNOWLEDGE);
        Queue queue = session.createQueue(queueName);
        QueueSender sender = session.createSender(queue);

        // create an send a message
        msg = session.createTextMessage();
        msg.setText("Hello");
        sender.send(queue, msg);

        // close the connection
        connection.close();

JBoss 5 + Apache Camel + Spring + EJB

The following is a step-by-step guide to how to make Apache Camel 2.6.0 and Spring work with JBoss 5.

Step 1. Install JBoss
Download JBoss Application Server 5.1.0.GA. Extract the contents of the zip file to a directory of your choice (i.e. c:\jboss-5.1.0.GA). We are going to use the default server for this example.

Step 2. Install Snowdrop
Download JBoss Snowdrop version 1.1.0.GA. Snowdrop is a utility package that contains JBoss-specific extensions to the Spring Framework. Extract the contents of the zip file (spring.deployer) to the deployers folder (C:\jboss-5.1.0.GA\server\default\deployers):

Step 3. Download camel-jboss package scan loader
Download camel-jboss from camel-extra. The lirary contains JBoss specific package scan classloader to be used when Camel is running inside JBoss Application Server. JBoss uses LPGL license which means that camel-jboss cannot be hosted at Apache. Alternatively you can get a copy of the library from here.

Step 4. Install camel-jboss in Maven repository
Install the camel-jboss library in your Maven repository as follows:

mvn install:install-file -DgroupId=org.apache.camel -DartifactId=camel-jboss -Dversion=2.3.0 -Dpackaging=jar -Dfile=/path/to/camel-jboss-2.3.0.jar

Step 5. Include Maven dependencies
Add the following dependencies to your ejb's pom:

            org.jboss.ejb3
            jboss-ejb3-ext-api
            1.0.0
            
                
                    org.jboss.javaee
                    jboss-ejb-api
                
                
                    org.jboss.metadata
                    jboss-metadata
                
            
        
        
            javax.ejb
            ejb-api
            3.0
            provided
        
        
            org.springframework
            spring-beans
            3.0.2.RELEASE
        
        
            org.springframework
            spring-core
            3.0.2.RELEASE
        
        
            org.jboss.snowdrop
            snowdrop-deployers
            1.1.0.GA
        

        
            org.apache.camel
            camel-core
            2.6.0
        

        
            org.apache.camel
            camel-spring
            2.6.0
        

        
            org.apache.xbean
            xbean-spring
            3.4
            
                
                    org.springframework
                    spring
                
            
        

        
            org.apache.camel
            camel-jboss
            2.3.0
        

Step 6. Add JBoss class resolver
You will have to set the class resolver on the CamelContext which can be done using Java DSL as follows:

PackageScanClassResolver jbossResolver = new JBossPackageScanClassResolver();

  CamelContext context = new DefaultCamelContext();
  context.setPackageScanClassResolver(jbossResolver);

or adding the following bean definition in the jboss-spring.xml file using Spring XML:

Step 7. Create your RouteBuilder and add it to jboss-spring.xml
By doing so you let JBoss Application Server container manage Camel’s lifecycle. First you define the route:

package org.example.camel;

import org.apache.camel.builder.RouteBuilder;

/**
 * @author Eduardo Sanchez-Ros
 */
public class ExampleRouteBuilder extends RouteBuilder {

    @Override
    public void configure() throws Exception {
        
        from("file:C:\\temp\\source")
                .convertBodyTo(String.class)
                .process(new ExampleRouteProcessor())
                .to("file:C:\\temp\\destination");

    }
}

and then you add it to the jboss-spring.xml file:

Step 8. Create your enterprise application
To demonstrate the previous I have created an example project that will create a route from file://C:\temp\source folder to file://C:\temp\destination and display the content of the file by calling a Stateless session bean.

Step 9. Build and deploy your ear
Copy your enterprise archive into the deploy folder.

You can download the source code from here.