Wednesday, February 1, 2012

MDB listening to Oracle AQ using Sun Adapter



Sometime back I had to work on MDB implementation for JBoss 5.1 to connect to oracle AQ. I implemented the solution using Sun AQ resource Adapter.
The purpose of this article is not to justify this approach versus JBoss recommended approaches like JBoss’s default JMS messaging implementation, ESB and so on but is to make it easy for anyone that decides to go with this approach. As this implementation could easily run into many issues, this article should help you going.
Yes this is time consuming and frustrating due to the facts that,
1.  The official support for this adapter has been stopped.
2.  No supporting documents for this implementation.
3.  There are threads in few forums that talk about this implementation but it is kind of hard to put it all together.
First Thing First
Let’s get started, deploy and run the example to see it working.
1.       Download and execute the scripts from myAQ.sql in your oracle schema
a.       Make sure you have proper privileges as stated in the script file, ask your DBA if you are not one.
b.      Create queue table and queue and start the queue
c.       Important create queue table with pay load type SYS.AQ$_JMS_MESSAGE.
d.      Create a procedure from the above script file to enqueue and dequeue the messages.
e.      Run the enqueue procedure.
f.        Run the dequeue procedure, you will see the output
JMS Message is: Testing my first AQ JMS message
2.       Download the sun AQ adapter oracleAQ.rar from the resources section below and put it under your {JBOSS_INSTALL}\server\default\deploy directory.
3.       Download the myAQ-ds.xml and put it under {JBOSS_INSTALL}\server\default\deploy directory.
a.       Modify the highlighted configuration to suite your environment

<mbean code="org.jboss.resource.deployment.AdminObject" name="jboss.oracleaq:service=Queue,name=DEMO_QUEUE">
   <attribute name="JNDIName">myaq/queue/demo</attribute>
   <depends optional-attribute-name="RARName">jboss.jca:service=RARDeployment,name='oracleaq.rar'</depends>
   <attribute name="Type">javax.jms.Queue</attribute>
   <attribute name="Properties">DestinationProperties=owner\=demouser, name\=DEMO_QUEUE</attribute>
</mbean>


<config-property name="ConnectionFactoryProperties" type="java.lang.String">jdbc_connect_string=jdbc:oracle:thin:demouser/demopassword@<host>:<port>:<sid>,host=<host>,user=demouser,password=demopassword,port=<port>,sid=<sid>,driver=thin
</config-property>

    <config-property name="username" type="java.lang.String">demouser</config-property>

    <config-property name="password" type="java.lang.String">demopassword</config-property>

 
4.       Now download all the dependency jars that are needed. Most of these libraries are part of oracle. You could handle these dependencies in 2 ways
a.       Download the following jar files from your oracle installed directory and copy them directly to {JBOSS_INSTALL}\server\default\lib directory.
aqapi.jar
bcel.jar
connector.jar
javax77.jar
jmxri.jar
oc4jclient.jar
ojdbc14.jar

b.      You could rebuild the downloaded oracleaq.rar file to include all the dependency jars above.
5.       Download the source jar file myAQ.jar and put it under {JBOSS_INSTALL}\server\default\deploy directory. Before copying you need to change some configuration in MDB, so to do that
a.       Import the jar project as EJB jar in to eclipse or any IDE of your choice.
b.      Open the java source file MyAqMessageBean.java and update the highlighted configuration through annotations.
@MessageDriven(

activationConfig = {

@ActivationConfigProperty(propertyName = "destinationType", propertyValue = "javax.jms.Queue"),

@ActivationConfigProperty(propertyName = "connectionFactoryProperties", propertyValue = "jdbc_connect_string=jdbc:oracle:thin:demouser/demopassword@{host:port:sid},host=<host>,user=demouser,password=demopassword,port=<port>,sid=<sid>,driver=thin"),

@ActivationConfigProperty(propertyName="destinationProperties", propertyValue="owner=demouser,name=demo_queue"),

    @ActivationConfigProperty(propertyName="userName", propertyValue="demouser"),

    @ActivationConfigProperty(propertyName="password", propertyValue="demopassword"),

    @ActivationConfigProperty(propertyName="ConnectionFactoryClassName", propertyValue="oracle.jms.AQjmsConnectionFactory"),

    @ActivationConfigProperty(propertyName="QueueConnectionFactoryClassName", propertyValue="oracle.jms.AQjmsQueueConnectionFactory")

})

@ResourceAdapter("oracleaq.rar")

c.       Now export the EJB jar directly in to the JBoss deploy directory.
6.       Now it’s time to start the JBoss server.
7.       Enqueue the message by running the procedure
8.       See the messages were picked by MDB on the JBoss console.

Issues
Here are the few issues that you could avoid though,
1.       Do not use 10g Lite since lite doesn’t come with DBMS_AQADM packages installed and so not able to enqueue or dequeue the message type SYS.AQ$_JMS_MESSAGE. There was an alternate solution suggested in some forum i.e. to run some DBMS-XXX package and I couldn’t get it to run so I decided to go for standard 10g edition as that was the only other option to get it work.
2.       Do not create the queue table with payload message type other than $AQ_JMS_MESSAGE as JBoss would talk to the AQ only if the payload is of this type.
3.       The adapter doesn't support XA connections in MDB.
4.       Now the bean listens to the first message only and any subsequent messages were simply lost. Solution to this is to update couple of files in oracleAQ.rar file. Well you should not run into this issue as I have supplied the updated rar file here. The details of those changes are in reference section.
5.       There is one minor glitch that you have to live with is when you try to stop the JBoss you will see error stack due to the JBoss thread confliction while calling releaseEndpoint method in InboundJmsResource.
6.       I tried this solution in JBoss 7.0.1 and at that time there were some issues related to registering the resource adapter with JBoss. I know recently some of those jira issues around this reported to be closed but I haven’t got back on it.
Conclusion
            This solution works perfectly considering application environment scope and limitations with one exception on shutdown. The community would surely appreciate if you share any resolutions to this issue or even better solution.
References
AQ data source configuration myAQ-ds.xml
Source Code myAQ.jar
Oracle AQ resource adapter oracleaq.rar
Oracle10g AQ SQL myAQ_10g.sql
Oracle AQ resource Adapter source oracleaq-source.rar

16 comments:

  1. Classes in oracleaq.rar log something ? Is it possible to see that log, setting some packages (which?) in log4j ?

    The problem we have is that only the first messages are read (at application deploy) , all the others are not read and remains in Oracle Queue. (we are using the updated oracleaq.rar)

    Thanks

    ReplyDelete
  2. Yes, try turn on log for the package "com.sun.generica".
    If you got the updated rar file then it should pick up all available messages. What version of jBoss are you on?

    ReplyDelete
  3. It seems that :

    1) if there are messages inside the queue BEFORE application redeploy, these message are read (all), but all the following messages are not read

    2) if there are no messages inside the queue BEFORE application redeploy, all works ok (all the messages are read)

    ReplyDelete
  4. If I understood your comment right, the problem is with deploying (restarting) the application when there are messages in the queue. The application will pick all existing messages but then it would pick up only first enqueued message and stops for subsequent messages.
    I implemented this solution on JBoss 5.1 and no such issue in fact it is responsive.
    Try if you can dig it to the cause, see log trace?

    ReplyDelete
  5. Hi,
    Have you any experience of this working with any of the 7.x.x stream releases of JBoss AS?
    Best regards,
    Bob Walker

    ReplyDelete
  6. As I mentioned in the issues list I tried this on version 7.0.0 either Lightning or Zap and couldn't get to work. There were some issues with JBoss registering the resource adapter at that time. Thats where I left this off and went with JMS implementation. But I know these issues are closed and theoretically should work.
    Best of luck.

    ReplyDelete
    Replies
    1. Hi

      I tried to use with JBoss AS 7.1.1 and works fine, but there's one problem without transaction and message acknoledgement set to AUTO you are not able to drop the message back to AQ in case of a failure.
      So as I see there's no redelivery only in case of SupportsXA=true but this integration only works without XA. Is there's any solution for this?
      Are there any source codes available for oracleqa.rar, I only found sources of genericra.rar?

      Delete
    2. Good to know it works on 7.1.1 version, thanks for taking time to update us here. It would be helpful to lot out if you could post the summary of steps/changes you did to get it to work on JBoss 7.1.1.
      Anyways I will see if I can get you the oracleaq.rar source code.
      Regards

      Delete
    3. The steps to deploy on JBoss 7.1.1:

      1. Implement Referenceable interface in com.sun.genericra.outbound.QueueProxy, com.sun.genericra.outbound.TopicProxy
      2. Copy aqapi.jar, ojdbc6.jar into the rar file
      3. Add the resource adapter to standalone.xml:





      oracleaq.rar

      NoTransaction




      4. Copy oracleaq.rar to jboss_home/standalone/deployments.
      5. Annotate your MDB with @ResourceAdapter("oracleaq.rar") annotation, because by default hornetq.rar is used.

      The problem comes that we dont have transactions here, because only works without transactions. The JMSSession is opened with AUTO_ACKNOLEDGE so in case of a processing failure / exceptions we will loose the message. That's why I'm asking for sources we should at least make the JmsSession acknoledgement configurable and then we can set to MANUAL_ACK then ack the message this message.acknloedge().
      I've found sources for genericra.rar https://svn.java.net/svn/genericjmsra~svn/branches/RELEASE_1_7_BRANCH I guess this was the starting point for oracleaq.rar version, but the generic adapter doesnt contains the AQ related modifications.

      Delete
  7. Iam able to find the source code for oracle AQ, I added the link under downloads section to download the oracle AQ source. Included ant build script would help you update and build the rar easily.
    Hope it helps, please keep us posted with your findings.
    Regards

    ReplyDelete
    Replies
    1. Thanks! Do you know that this source code is under the same license Apache License v2 http://www.apache.org/licenses/LICENSE-2.0 as the original sun generic implementation? Have you used in commercial product?

      Delete
  8. Yes it is under the same license even though it is not supported officially.
    Yes you can use in commercial product as per license and we have implemented in production.

    ReplyDelete
  9. Did someone solve the XA transaction issue?
    Regards Essey

    ReplyDelete
  10. I played around with the oracleaq.rar and it works well with Oracle Queues and Topics.
    But when I configure durable subscription I see following exception:
    SubscriptionDurability has been configured as Durable. But destination configured is not a topic

    Using following properties in the activation-config of the MDB
    supportsXA=false
    destinationType=javax.jms.Topic
    connectionFactoryProperties=jdbc_connect_string=jdbc:oracle:oci:@test
    destinationProperties=owner=user,name=TEST_TOPIC
    userName=user
    password=password
    ConnectionFactoryClassName=oracle.jms.AQjmsConnectionFactory
    TopicConnectionFactoryClassName=oracle.jms.AQjmsTopicConnectionFactory
    subscriptionName=TestSubscription
    subscriptionDurability=Durable
    clientID=cluster1

    Did I configure it wrong or does the resource adapter not support durable subscriptions?

    Regards,
    Yusuf

    ReplyDelete
  11. Hello,

    Has anyone tried to connect AQ with JBoss 7.1.3?

    Thanks in advance.

    ReplyDelete