IBM Integration Bus - An unsupported value type ''NVARCHAR2'' was returned for column ''XXXX''.

If we have the the issue 'An unsupported value type ''NVARCHAR2'' was returned for column ''XXXX''.', we can workaround with "Java Compute" Node.

public class HTTPInputMessageFlow_JavaCompute extends MbJavaComputeNode {

public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbOutputTerminal alt = getOutputTerminal("alternate");

MbMessage inMessage = inAssembly.getMessage();
MbMessageAssembly outAssembly = null;
try {
// create new message as a copy of the input
MbMessage outMessage = new MbMessage(inMessage);
outAssembly = new MbMessageAssembly(inAssembly, outMessage);
// ----------------------------------------------------------
// Add user code below
                        

// Obtain a java.sql.Connection using a JDBC Type4 datasource - in this example for a 
        // JDBC broker configurable service called "MyDB2"  

        Connection conn = getJDBCType4Connection("MyDB2",
                     JDBC_TransactionType.MB_TRANSACTION_AUTO);

        // Example of using the Connection to create a java.sql.Statement  
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_READ_ONLY);
        ResultSet srs0 = stmt.executeQuery("SELECT NAME, CITY FROM MySchema.MyTable");    

        stmt.executeUpdate("UPDATE MySchema.MyTable SET CITY = \"Springfield\" WHERE Name = \"Bart\"");
       
        ..............
      } catch (SQLException sqx ){
        sqx.printStackTrace();
      }
// End of user code
// ----------------------------------------------------------
} catch (MbException e) {
// Re-throw to allow Broker handling of MbException
throw e;
} catch (RuntimeException e) {
// Re-throw to allow Broker handling of RuntimeException
throw e;
} catch (Exception e) {
// Consider replacing Exception with type(s) thrown by user code
// Example handling ensures all exceptions are re-thrown to be handled in the flow
throw new MbUserException(this, "evaluate()", "", "", e.toString(),
null);
}
// The following should only be changed
// if not propagating message to the 'out' terminal
out.propagate(outAssembly);

}

}

Reference: https://www.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ac30494_.htm

IBM Integration Bus - Assign node with SELECT command in ESQL

For Ex, We have the input below.
<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
<food>
    <name>Belgian Waffles</name>
    <price>$5.95</price>
    <description>
   Two of our famous Belgian Waffles with plenty of real maple syrup
   </description>
    <calories>650</calories>
</food>
<food>
    <name>Strawberry Belgian Waffles</name>
    <price>$7.95</price>
    <description>
    Light Belgian waffles covered with strawberries and whipped cream
    </description>
    <calories>900</calories>
</food>
<food>
    <name>Berry-Berry Belgian Waffles</name>
    <price>$8.95</price>
    <description>
    Belgian waffles covered with assorted fresh berries and whipped cream
    </description>
    <calories>900</calories>
</food>
<food>
    <name>French Toast</name>
    <price>$4.50</price>
    <description>
    Thick slices made from our homemade sourdough bread
    </description>
    <calories>600</calories>
</food>
<food>
    <name>Homestyle Breakfast</name>
    <price>$6.95</price>
    <description>
    Two eggs, bacon or sausage, toast, and our ever-popular hash browns
    </description>
    <calories>950</calories>
</food>
</breakfast_menu>

We just want create the output with the price of food that have name is "Homestyle Breakfast", We could use the SELECT command  in ESQL like below.

SET OutputRoot.XMLNSC.food.price= THE(SELECT FIELDVALUE(c.price) AS price FROM InputRoot.XMLNSC.breakfast_menu.food[] AS c WHERE c.name ='Homestyle Breakfast');

The SELECT command for querying the food with name is 'Homestyle Breakfast' and return a LIST of matched ROW. The "THE" command with the first element in the LIST and return for assigning to OutputRoot.XMLNSC.food.price

We will have the output like:

<food>
       <price>$6.95</price>
</food>

Without the "THE" command, we have use [] like below

SET OutputRoot.XMLNSC.food[]= (SELECT FIELDVALUE(c.price) AS price FROM InputRoot.XMLNSC.breakfast_menu.food[] AS c WHERE c.name ='Homestyle Breakfast');

The same output:

<food>

       <price>$6.95</price>

</food>

Oracle OSB 12C - OSB Always Adds a "Charset=Iso-8859-1" to the Content-Type Header

If you have the issue  Content-Type Header in OSB 12c  because OSB Always Adds a "Charset=Iso-8859-1" to the Content-Type Header, here are the solution.

For 12c you'll need to apply below patches:

• Patch 25434715: Forward Porting Bug 18729796 to 12.2.1.x.x release, which  addresses the HTTP inbound case.

• Patch 26628960: Cont of Bug 26591735 (Remove charset=utf-8 in Content-Type for Outbound...), which addresses the HTTP Outbound case.

• And add Startup parameter: -Dcom.bea.wli.sb.kernel.charsetRequired=false( in setDomainEnv.sh)
• Remove encoding in business if you have

IBM Integration Bus- Database Definition

Securing database connections

mqsisetdbparms broker_name -n jdbc::security_identity -u userID -p password

Download JDBC driver for type 4 connections

Example for Oracle: https://www.oracle.com/database/technologies/jdbc-drivers-12c-downloads.html 

Setting up a JDBC provider for type 4 connections

mqsicreateconfigurableservice LOCALBROKER -c JDBCProviders -o JDBCOracleDBConnector -n connectionUrlFormat,connectionUrlFormatAttr1,connectionUrlFormatAttr2,connectionUrlFormatAttr3,connectionUrlFormatAttr4,connectionUrlFormatAttr5,databaseName,databaseType,databaseVersion,description,environmentParms,jarsURL,jdbcProviderXASupport,maxConnectionPoolSize,portNumber,securityIdentity,serverName,type4DatasourceClassName,type4DriverClassName -v jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1],MYDB,,,,,MYDB,Oracle,11.2,"Oracle DEV",default_none,"C:\temp\jars",false,0,5001,security_identity,dev72.hdd.com,oracle.jdbc.xa.client.OracleXADataSource,oracle.jdbc.OracleDriver

Please note: Please correct  the below information 

- securityIdentity( ex: security_identity is same with step Securing database connections)

- jarsURL(ex: "C:\temp\jars" is folder contains the jdbc driver jar files).

- connectionUrlFormat( ex: "jdbc:oracle:thin:[user]/[password]@[serverName]:[portNumber]:[connectionUrlFormatAttr1]"  is jdbc url  to connect to  the BD).

-  databaseName( Ex: "MYDB").

- serverName( Ex: "dev72.hdd.com")

- type4DatasourceClassName( Ex: com,oracle.jdbc.xa.client.OracleXADataSource)

- type4DriverClassName( Ex: oracle.jdbc.OracleDriver).

- Provider name( Ex: JDBCOracleDBConnector).

Create Database Definition

Create database definition( note that  provider name should be same with jdbc provider name as ablove, ex: JDBCOracleDBConnector )

IBM Integration Bus - How to debug in IBM integration tookit

For debugging, We have to set the debug port with comment below

mqsichangeproperties integrationNodeName -e default  -o ComIbmJVMManager -n jvmDebugPort -v 3920

The port(ex: 3920) is used for debug

We have to stop and start node for effect.

Now, choose the "Launch Debuger", add breakpoint and enjoy.

IBM Integration Bus - Webphere MQ

For using the Webphere MQ in  IIB, We have installed the MQ Client at least. Download MQ Client in https://developer.ibm.com/messaging/mq-downloads/

Create the MQ Security Identity:

Create the MQ Security Identity&nbsp; with below command line:

mqsisetdbparms integrationNodeName -n mq::queueid-u username -p password

The command above, create the MQ Security Identity name is queueid

Config the MQ node

After finish, We create 1 flow, and drag and drop a MQ node( Like: MQInput,...)

We have choose the Properties of Node. In Basic Tab, input the Queue Name

In "MQ Connection Tab":

• Select "MQ client connection Properties" in Connection
• Input Queue Manager, Host name, port, Channel Name and Security Identity( ex: queueid that is create before)

ActiveMQ Setup in Weblogic for OSB

ActiveMQ Setup in Weblogic for OSB

I had a requirement where i needed a ActiveMQ configured in the same box as my weblogic server.I need to publish messages and subscribe as well.
I am not publishing the Business service details > below are the steps needed to set up the Active MQ and Weblogic connectivity.


Add activemq-all-5.9.1.jar to the WebLogic $Domain(in our case OSBDomain)/lib folder

Add

set PRE_CLASSPATH=%DOMAIN_HOME%\lib\activemq-all-5.9.1.jar;%PRE_CLASSPATH%   in the setDomainEnv.cmd.


Create a JMS Module : ABCModule

Create Foreign Server Name: xxxxx

General tab

JNDI Initial Context Factory: org.apache.activemq.jndi.ActiveMQInitialContextFactory
JNDI Connection URL: tcp://localhost:61616

Destinations Tab

Name: TestQueue
Local JNDI Name: TestQueue
Remote JNDI Name: dynamicQueues/TestQueue (I tried with a normal queue, but it didn't work. So used dynamicQueues here. Will try with other queue also)

Connection Factories Tab
Name: AMQConnectionFactory
Local JNDI Name: AMQConnectionFactory
Remote JNDI Name: ConnectionFactory


OSB Business Service:

Use URI as jms://localhost:7001/AMQConnectionFactory/TestQueue

JNDI Support by ActiveMQ

ActiveMQ will work with any JNDI provider capable of storing Java objects. However it is common to require a JNDI initial context to be able to run many JMS example programs, like Sun’s JMS tutorial.

So we provide a simple JNDI InitialContextFactory which can be used to lookup JMS connection factory objects as well as Destination objects. For example if you place this jndi.properties file on your classpath, you can look inside the InitialContext and lookup ConnectionFactory objects and Destinations etc.

java.naming.factory.initial = org.apache.activemq.jndi.ActiveMQInitialContextFactory

# use the following property to configure the default connector
java.naming.provider.url = vm://localhost

# use the following property to specify the JNDI name the connection factory
# should appear as. 
#connectionFactoryNames = connectionFactory, queueConnectionFactory, topicConnectionFactry

# register some queues in JNDI using the form
# queue.[jndiName] = [physicalName]
queue.MyQueue = example.MyQueue


# register some topics in JNDI using the form
# topic.[jndiName] = [physicalName]
topic.MyTopic = example.MyTopic

You can edit the jndi.properties file to configure the ActiveMQConnectionFactory’s properties such as brokerURL and whether or not there should be an embedded broker etc. See how to embed a broker in a connection for more details.

ActiveMQ JNDI Tutorial

This is a quick one page tutorial on how to setup and use JNDI to create a connection to ActiveMQ. The first thing is ActiveMQ does not provide a full JNDI server. This means JMS clients need to use properties files to create a JNDI IntialContextFactory. If you need an example properties file, you can look the source distribution https://github.com/apache/activemq/blob/master/activemq-unit-tests/src/test/resources/jndi.properties. Before we proceed, here are the properties.

Name	Value
java.naming.factory.initial	org.apache.activemq.jndi.ActiveMQInitialContextFactory
java.naming.provider.url	tcp://hostname:61616
topic.MyTopic	example.MyTopic

Make sure to add activemq-_<version>_.jar and spring-1.x.jar to your classpath. If the libraries are not in the classpath, you will get a ClassNotFoundException at runtime. If you get ClassNotFoundException, try printing out the classpath and check it is present. You can also run ActiveMQ with -verbose option to verify the jar was loaded correctly.

Sample Code

// Create a new intial context, which loads from jndi.properties file: 
javax.naming.Context ctx = new javax.naming.InitialContext(); 
// Lookup the connection factory: 
javax.jms.TopicConnectionFactory factory = (javax.jms.TopicConnectionFactory)ctx.lookup("ConnectionFactory"); 
// Create a new TopicConnection for pub/sub messaging: 
javax.jms.TopicConnection conn = factory.getTopicConnection(); 
// Lookup an existing topic: 
javax.jms.Topic mytopic = (javax.jms.Topic)ctx.lookup("MyTopic"); 
// Create a new TopicSession for the client: 
javax.jms.TopicSession session = conn.createTopicSession(false,TopicSession.AUTO_ACKNOWLEDGE); 
// Create a new subscriber to receive messages: 
javax.jms.TopicSubscriber subscriber = session.createSubscriber(mytopic);

Notice the name of the topic in the sample is MyTopic. ActiveMQ will read the jndi.properties files and creates the topics and queues in a lazy fashion. The prefix topic and queue is stripped, so the JNDI name begins after the prefix.

Once you have the jndi.properties edited and ready, it needs to be accessible to your application. The easiest way is to add jndi.properties to a jar file. When new InitialContext() is called, it will scan the resources and find the file. If you get javax.naming.NamingException, it usually means the jndi.properties file is not accessible.

You can also try to create a new initial context using either an instance of properties file or a map. For example, the approach recommended by JMS specification will work just fine.

Example Recommended by Specification

javaProperties props = new Properties(); 
props.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.activemq.jndi.ActiveMQInitialContextFactory"); 
props.setProperty(Context.PROVIDER_URL, "tcp://hostname:61616"); 
javax.naming.Context ctx = new InitialContext(props);

If ActiveMQ is embedded within an EJB container, you will need to look at the containers documentation for the correct JNDI values.

Dynamically Creating Destinations

For the easiest possible configuration with JNDI based programs, there are two dynamic contexts, namely:

• dynamicQueues
• dynamicTopics

These allow you to lookup queues and topics using JNDI without any configuration.

For example, if you use the following name to lookup into JNDI:

dynamicQueues/FOO.BAR

you will get back an ActiveMQQueue of the name FOO.BAR. This can be very handy if you can easily reconfigure the JNDI name to use to lookup something in JNDI, but don’t want to have to double configure a jndi.properties to match.

Working With Embedded Brokers

It is often useful to use an embedded broker in the same JVM as the JMS client. For this see How do I embed a Broker inside a Connection.

If you want to use an embedded broker with your JNDI provider you can just use the VM Transport to connect to the broker in your URL. e.g. to create a purely in JVM broker use this URI

vm://locahost

If you want to customize the broker use something like this:

vm:broker:(tcp://localhost:61616)

More options are available in the VM Transport Reference

Example Java Code

Once you have configured JNDI on the classpath you can run any normal JMS application such as the following example. Notice that the Java code just uses pure JMS APIs and is not in any way ActiveMQ specific

/**
 * The SimpleQueueSender class consists only of a main method,
 * which sends several messages to a queue. 
 * 
 * Run this program in conjunction with SimpleQueueReceiver. 
 * Specify a queue name on the command line when you run the 
 * program. By default, the program sends one message. Specify 
 * a number after the queue name to send that number of messages. 
 */ 
 
package org.apache.activemq.demo; 

import javax.jms.Connection; 
import javax.jms.ConnectionFactory; 
import javax.jms.Destination; 
import javax.jms.JMSException; 
import javax.jms.MessageProducer; 
import javax.jms.Session; 
import javax.jms.TextMessage; 
import javax.naming.Context; 
import javax.naming.InitialContext; 
import javax.naming.NamingException; 
import org.slf4j.Logger; 
import org.slf4j.LoggerFactory; 
/**
 * A simple polymorphic JMS producer which can work with Queues or Topics which 
 * uses JNDI to lookup the JMS connection factory and destination.
 */ 
public final class SimpleProducer { 
   private static final Logger LOG = LoggerFactory.getLogger(SimpleProducer.class); 
   private SimpleProducer() {}  
  
   /**
    * @param args the destination name to send to and optionally, the number of 
    * messages to send 
    */ 
   public static void main(String[] args) { 
      Context jndiContext; 
      ConnectionFactory connectionFactory; 
      Connection connection; 
      Session session; 
      Destination destination; 
      MessageProducer producer; 
      String destinationName; 
      final int numMsgs;  
      
      if ((args.length < 1) || (args.length > 2)) { 
         LOG.info("Usage: java SimpleProducer <destination-name> [<number-of-messages>]"); System.exit(1); 
      }  
      
      destinationName = args[0]; 
      LOG.info("Destination name is " + destinationName); 
      
      if (args.length == 2) { 
         numMsgs = (new Integer(args[1])).intValue(); 
      } else { 
         numMsgs = 1; 
      } 
      
      /*
       * Create a JNDI API InitialContext object 
       */
      try { 
         jndiContext = new InitialContext(); 
      } catch (NamingException e) { 
         LOG.info("Could not create JNDI API context: " + e.toString()); 
         System.exit(1); 
      }
      
      /* 
       * Look up connection factory and destination. 
       */
      try { 
         connectionFactory = (ConnectionFactory)jndiContext.lookup("ConnectionFactory"); 
         destination = (Destination)jndiContext.lookup(destinationName); 
      } catch (NamingException e) { 
         LOG.info("JNDI API lookup failed: " + e); 
         System.exit(1); 
      }  
      
      /*
       * Create connection. Create session from connection; false means 
       * session is not transacted. Create sender and text message. Send 
       * messages, varying text slightly. Send end-of-messages message. 
       * Finally, close the connection. 
       */ 
      try { 
         connection = connectionFactory.createConnection(); 
         session = connection.createSession(false, Session.AUTO_ACKNOWLEDGE); 
         producer = session.createProducer(destination); 
         TextMessage message = session.createTextMessage();  
         for (int i = 0; i < numMsgs; i++) { 
            message.setText("This is message " + (i + 1)); 
            LOG.info("Sending message: " + message.getText()); producer.send(message); 
         }  
         
         /*
          * Send a non-text control message indicating end of messages. 
          */ 
         producer.send(session.createMessage()); 
      } catch (JMSException e) { 
         LOG.info("Exception occurred: " + e); 
      } finally { 
         if (connection != null) { 
            try { 
               connection.close(); 
            } catch (JMSException ignored) {
            } 
         } 
      } 
   } 
}

Reference:

• http://oraclefusionmiddlewaremyfewcents.blogspot.com/2015/01/activemq-setup-in-weblogic-for-osb.html
• https://activemq.apache.org/jndi-support.html