Thursday, September 29, 2011

Connect to Oracle Queue using RAC URL in TIBCO BW


Currently, Oracle AQ has not been fully certified with BW yet and TIBCO does not suggest its customers to use the JMS activities to interact with Oracle AQ.

However this can be implemented using JAVA Code activity in TIBCO BW. I am writing a sample code which can be used to connect to oracle queue. Please note that in below code I am using Oracle Thin drier to connect to database.

Before trying the below code following points must be noted:

1. The Code below is only meant for ‘JavaCode’ palette in TIBCO BW. In order to run the below code in other API, it will need a modification.

2. The java classes that need to be imported should be included along with the code by selection full class radio button present in the ‘code’ tab of palette.

3. The basic connection parameters like username, password etc is passed Global Variables.

4. Please note that If you are getting exception like “ClassNotFound” then you must place the jar file “apaqi.jar” in <>/bw/<>/lib folder.

5. The syntax of complete RAC URL(not appearing in screenshot) is:

RAC URL : jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testhost-vip)(PORT=1561))(ADDRESS=(PROTOCOL=TCP)(HOST=testbackuphost-vip)(PORT=1561)))(CONNECT_DATA=(SERVICE_NAME=oraclsid)))

6. Please refer to screenshot attached in this post in order to view the exact GV’s I have used in this.

Import java.util.*;

Import java.io.*;

Import javax.jms.*;

Import javax.jms.Queue;

Import oracle.jms.*;

Import oracle.jdbc.*;

Import oracle.jdbc.pool.OracleDataSource;

Import java.lang.Object.*;

Import java.lang.Class.*;

Import java.sql.Connection;

//Create Objects for the classes which are needed to create JMS connection

QueueConnection qconn = null;

QueueConnectionFactory qcfact = null;

Queue queue = null;

QueueSession qsess = null;

QueueSender sender = null;

TextMessage txtmsg;

//Creating Property to get User Name and Password.

Properties connection = new Properties();

connection.put("user",USER);

connection.put("password",PassWord);

//Get Queue Connection factory from Driver and Property initialized above.

qcfact = AQjmsFactory.getQueueConnectionFactory(Driver,connection);

qconn = qcfact.createQueueConnection(USER, PassWord);

//Creating Session and provide the acknowledgement mode

qsess = qconn.createQueueSession(true, Session.CLIENT_ACKNOWLEDGE);

qconn.start();

queue = ((AQjmsSession) qsess).getQueue(QueueUser, Destination_Queue);

// Publishing the message

sender = qsess.createSender(queue);

txtmsg = qsess.createTextMessage();

txtmsg.setJMSCorrelationID(CorrelationID);

txtmsg.setText(XML_DATA);

((AQjmsQueueSender)sender).send(queue,txtmsg);

qsess.commit();

output = "Succesfully placed the message on OracleAdvancedQueue";

// Cleaning up before exiting

qsess.close();

qconn.close();

Cheers,

Vivek