[esb-issues] [JBoss JIRA] (JBESB-3738) Upgrade helloworld_action/Oracle AQ quickstart

Upgrade helloworld_action/Oracle AQ quickstart ---------------------------------------------- Key: JBESB-3738 URL: https://issues.jboss.org/browse/JBESB-3738 Project: JBoss ESB Issue Type: Bug Security Level: Public(Everyone can see) Components: Documentation Affects Versions: 4.11 Reporter: Tom Cunningham Assignee: Tom Cunningham Fix For: 4.11 Customer-provided tips for working with Oracle AQ: 1. Schema different to User If the configuration of the adapter has been selected as that using the URL, rather than the individual configuration elements, then the 'SCHEMA' element is not discussed in the documentation. This could cause a problem if the connect as user/password is different to the actual owner of the queue. To get round this, we have configured our adapter as follows: <jms-provider connection-factory="ConnectionFactory" name="JBossAQ"> <property name="java.naming.factory.initial" value="org.jboss.soa.esb.oracle.aq.AQInitialContextFactory"/> <property name="java.naming.oracle.aq.user" value="${eessi.oracle.aq.user}"/> <property name="java.naming.oracle.aq.password" value="${eessi.oracle.aq.password}"/> <property name="java.naming.oracle.aq.url" value="${eessi.oracle.aq.url}"/> <property name="java.naming.oracle.aq.schema" value="${eessi.oracle.aq.queue.owner}"/> Also, we have had to change the adapter class AQInitialContextFactory as follows: public void init(Hashtable<?, ?> env) { dbuser = (String) env.get(AQInitialContextFactory.DB_USER); dbpass = (String) env.get(AQInitialContextFactory.DB_PASSWD); aqConnectionUrl = (String) env.get(AQInitialContextFactory.DB_CONNECTION_URL); // Added by DH to allow for schema to be set if provided alongside the URL if (env.get(AQInitialContextFactory.DB_SCHEMA)!=null) { aqSchemaName = (String) env.get(AQInitialContextFactory.DB_SCHEMA); } else { aqSchemaName = (String) env.get(AQInitialContextFactory.DB_USER); } if (aqConnectionUrl == null ) { aqServerName = (String) env.get(AQInitialContextFactory.DB_SERVER); aqServerDBInst = (String) env.get(AQInitialContextFactory.DB_INSTANCE); aqDBDriver = (String) env.get(AQInitialContextFactory.DB_DRIVER); aqSchemaName = (String) env.get(AQInitialContextFactory.DB_SCHEMA); aqServerPort = (String) env.get(AQInitialContextFactory.DB_PORT); if (aqServerPort != null) { try { aqServerPortNumber = Integer.parseInt(aqServerPort); } catch (NumberFormatException e) { log.error("caught exception converting port", e); } } } } This allows the configuration to be a little more flexible. 2. Same named queue/queue table across multiple schemas. If, for example, on a test instance, multiple schemas own the same named queue, then the query in the AQUtil class is not correct. By not joining queue table owner to queue owner, a semi cartesian product ensues and binding to the correct queue is not always possible (success will depend on the ordering of the returned records in the resultset). to fix this, we have changed the AQUtil class as follows: private static final String GET_QUEUES_PSQL = "SELECT allq.OWNER as owner, allq.NAME as name, allq.QUEUE_TYPE as queue_type, allqt.RECIPIENTS as recipients" + " from SYS.ALL_QUEUES allq, SYS.ALL_QUEUE_TABLES allqt " + " where allq.QUEUE_TABLE = allqt.QUEUE_TABLE and upper(allq.OWNER) in (?) and allqt.owner = allq.owner order by name asc"; You will need to change the GET_QUEUES_SQL statement to reflect this fix also.