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

[Tom Cunningham (JIRA)]

Tom Cunningham created JBESB-3738:
-------------------------------------

             Summary: 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.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.jboss.org/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira