BizTalk Services Mapper - WCF SQL Select Query - Cumulative Concatenate

Say you want to create a map in order to produce the SELECT statement for a WCF-SQL query and you start of with a list of values to include in the where clause.

How to achieve this with the BizTalk Services mapper?

The Source XML:

<ns0:GetProducts xmlns:ns0="http://GetProductsBizTalkService.Schemas">
  <Column>
    <Name>Name_0</Name>
    <Value>Value_0</Value>
  </Column>
  <Column>
    <Name>Name_1</Name>
    <Value>Value_1</Value>
  </Column>
  <Column>
    <Name>Name_2</Name>
    <Value>Value_2</Value>
  </Column>
</ns0:GetProducts>

The Destination XML:

<ns3:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns3:Columns>*</ns3:Columns>
  <ns3:Query>where Name_0='Value_0' and Name_1='Value_1' and Name_2='Value_2'</ns3:Query>
</ns3:Select>

First, how could we do this within XSLT?

This can be done with the following XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:s0="http://GetProductsBizTalkService.Schemas"
                xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products"
                exclude-result-prefixes="msxsl s0">
  <xsl:output method="xml"
              indent="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates select="/s0:GetProducts" />
  </xsl:template>
  <xsl:template match="/s0:GetProducts">
    <ns0:Select>
      <ns0:Columns>*</ns0:Columns>
      <xsl:if test="Column">
        <ns0:Query>
          <xsl:for-each select="Column">
            <xsl:if test='position() = 1'>where </xsl:if>
            <xsl:value-of select="Name"/>='<xsl:value-of select="Value"/>'
            <xsl:if test='position() != last()'> and </xsl:if>
          </xsl:for-each>
        </ns0:Query>
      </xsl:if>
    </ns0:Select>
  </xsl:template>
</xsl:stylesheet>

The result of the above will be:

<?xml version="1.0" encoding="utf-8"?>
<ns0:Select xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns0:Columns>*</ns0:Columns>
  <ns0:Query>
    where Name_0='Value_0'
    and Name_1='Value_1'
    and Name_2='Value_2'
  </ns0:Query>
</ns0:Select>

Finally, how to achieve the same thing with the mapper:



What did we do?

First of all we need a list and the list will contain the list of expressions based on the column name and values:

Name_0='Value_0'
Name_1='Value_1'
Name_2='Value_2'

For this we use the new "Create List" functoid:

include the "ForEach Loop" functoid - in order to loop the "Column" records -:

use a "String Concatenate" in combination with the "Add Item to List" functoid to produce the above list:


Now that we have the list, how to produce the query statement? "Cumulative Concatenate" comes to the rescue:


Since the "Query" element in the destination schema is optional, I also included a "Conditional Assignment" functoid to only map the query in case we have columns on the input.
I also used the "Cumulative Count" functoid to count the number of items in the list and used the "Logical Expression" functoid to check if the value is greater than 0:


Based on that result we will create the "Query" element or not, but first we need to prefix the query with the "where" word using the "String Concatenate" functoid:


Simple!

The thing to remember here is to include a "ForEach" in the "Create List"...

Koen

Installing Terracotta as a windows service

Since the release of webMethods 9.0, Software AG uses TerraCotta as distributed cache for their cluster solution on Integration Server. The software can be easily installed using the SoftwareAG installer, but has a big drawback on Microsoft Windows systems: there is no procedure to install Terracotta as a Windows Service. Therefore, the server needs to be started on command line with all its consequences.

Fortunately, there are several solutions available:

Tanuki java wrapper

Can be configured to work with Terracotta, but the community edition only supports 32-bit JVM. Due to its memory limitation, this is not a very good solution. You can find the software at http://wrapper.tanukisoftware.com/

In case you want to buy the software for 64-bit support, configuration can be done as follows:

• ·        Download the wrapper and unzip in folder D:\SoftwareAG\Terracotta\wrapper 
•       Make a wrapper file TerraCotta.conf with the following configuration and place it under D:\SoftwareAG\Terracotta. Suppose the Terracotta config file tc-config.xml can be found under D:\SoftwareAG\Terracotta\bin

# for logging

wrapper.java.command.loglevel=INFO

wrapper.java.command=D:\SoftwareAG\jvm\jvm170_32\bin\java

# Method 1 main class

wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperStartStopApp

# classpath

wrapper.java.classpath.1=D:\Temp\wrapper\lib/wrapper.jar

wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar

wrapper.java.library.path.1=D:\Temp\wrapper/lib

# Java Additional Parameters

wrapper.java.additional.1=-server

wrapper.java.additional.2=-XX:+HeapDumpOnOutOfMemoryError

wrapper.java.additional.3=-Dcom.sun.management.jmxremote

wrapper.java.additional.4=-Dsun.rmi.dgc.server.gcInterval=31536000000

wrapper.java.additional.5=-Dnet.sf.ehcache.enableShutdownHook=false

wrapper.java.additional.6=-Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBFactory

wrapper.java.additional.7=-Dtc.install-root=D:/SoftwareAG/Terracotta

# Initial Java Heap Size (in MB)

wrapper.java.initmemory=512

# Maximum Java Heap Size (in MB)

wrapper.java.maxmemory=512

# Application parameters. This is where you specify TC server main class

wrapper.app.parameter.1=com.tc.server.TCServerMain

wrapper.app.parameter.2=2

wrapper.app.parameter.3=-f

wrapper.app.parameter.4=D:\SoftwareAG\Terracotta\bin\tc-config.xml

wrapper.app.parameter.5=com.tc.admin.TCStop

wrapper.app.parameter.6=FALSE

wrapper.app.parameter.7=5

wrapper.app.parameter.8=-f

wrapper.app.parameter.9=D:\SoftwareAG\Terracotta\bin\tc-config.xml

wrapper.app.parameter.10=-n

wrapper.app.parameter.11=Server2

wrapper.app.parameter.12=9520

wrapper.logfile=D:\SoftwareAG\Terracotta/wrapper.log

# Format of output for the log file.  (See docs for formats)

wrapper.logfile.format=LPTM

# Log Level for log file output.  (See docs for log levels)

wrapper.logfile.loglevel=INFO

# On exit behaviour

wrapper.on_exit.0=SHUTDOWN

wrapper.on_exit.default=RESTART

• ·        You can install the service by running the following command: sc create Terracotta binPath= D:\SoftwareAG\Terracotta\wrapper\wrapper.exe -s D:\SoftwareAG\Terracotta\TerraCotta.conf start= auto

Yet Another Java Service Wrapper (YAJSW)

YAJSW is a pure java implementation of the Tanuki Java Wrapper. Its big advantage is that it’s free and supports both 32 and 64-bit JDK 5, 6 and 7. You can find the software at http://yajsw.sourceforge.net/

Configuration can be done as follows:

• ·        Download and unzip the wrapper under D:\SoftwareAG\Terracotta\wrapper 
•       Make a wrapper.conf file with the following configuration and place it under D:\SoftwareAG\Terracotta\wrapper. Important to mention is to use / instead of \ !!

wrapper.java.command=D:/SoftwareAG/jvm/jvm170_64/bin/java

wrapper.stop.conf = D:/SoftwareAG/Terracotta/wrapper/wrapper.stop.conf

wrapper.java.classpath.1= D:/SoftwareAG/Terracotta/wrapper/wrapper.jar

wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar

wrapper.java.app.mainclass = com.tc.server.TCServerMain

wrapper.console.title = TerraCotta

wrapper.ntservice.name = TerraCotta

wrapper.ntservice.displayname = TerraCotta

wrapper.ntservice.description = TerraCotta

wrapper.app.parameter.1= -f

wrapper.app.parameter.2= D:/SoftwareAG/Terracotta/bin/tc-config.xml

wrapper.logfile=D:/logs/wrapper.log

wrapper.logfile.format=LPTM

wrapper.logfile.rollmode=DATE

wrapper.logfile.maxdays=10

wrapper.java.additional.1=-server

wrapper.java.additional.2=-Xms1024M

wrapper.java.additional.3=-Xmx1024M

wrapper.java.additional.4=-XX:+HeapDumpOnOutOfMemoryError

wrapper.java.additional.5=-Dcom.sun.management.jmxremote

wrapper.java.additional.6=-Dsun.rmi.dgc.server.gcInterval=31536000000

wrapper.java.additional.7=-Dnet.sf.ehcache.enableShutdownHook=true

wrapper.java.additional.8=-Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBFactory

wrapper.java.additional.9=-Dtc.install-root=D:/SoftwareAG/Terracotta

wrapper.java.additional.10=-Djava.net.preferIPv4Stack=true

wrapper.on_exit.0=SHUTDOWN

wrapper.on_exit.default=RESTART

• ·        Also make a wrapper.stop.conf file in the same folder with the following configuration:

wrapper.stopper = true

wrapper.java.command=D:/SoftwareAG/jvm/jvm170_64/bin/java

wrapper.java.classpath.1= D:/SoftwareAG/Terracotta/wrapper/wrapper.jar

wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar

wrapper.java.app.mainclass = com.tc.admin.TCStop

wrapper.app.parameter.1=-f

wrapper.app.parameter.2=D:/SoftwareAG/Terracotta/bin/tc-config.xml

wrapper.app.parameter.3=-n

wrapper.app.parameter.4=Server1

wrapper.app.parameter.5=9520

wrapper.logfile=D:/logs/wrapper_stop.log

wrapper.logfile.format=LPTM

wrapper.logfile.rollmode=DATE

wrapper.logfile.maxdays=10

wrapper.java.additional.1=-server

wrapper.java.additional.2=-Xms512M

wrapper.java.additional.3=-Xmx512M

wrapper.java.additional.4=-XX:+HeapDumpOnOutOfMemoryError

wrapper.java.additional.5=-Dcom.sun.management.jmxremote

wrapper.java.additional.6=-Dsun.rmi.dgc.server.gcInterval=31536000000

wrapper.java.additional.7=-Dnet.sf.ehcache.enableShutdownHook=true

wrapper.java.additional.8=Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBactory

wrapper.java.additional.9=-Dtc.install-root=D:/SoftwareAG/Terracotta

wrapper.java.additional.10=-Djava.net.preferIPv4Stack=true

·        You can test the configuration by running D:\SoftwareAG\Terracotta\wrapper\bat\runConsole.bat

In that case the wrapper will load the configuration file and start the process. This can be used to debug the wrapper configuration file. Afterwards the service can be installed using the D:\SoftwareAG\Terracotta\wrapper\bat\installService.bat command

For both wrappers you can define the wrapper on exit behavior. By setting wrapper.on_exit.0=SHUTDOWN the wrapper will do nothing when it gets a normal return code 0 from terracotta. For all other return codes the wrapper will automatically restart the terracotta service. We do this by setting wrapper.on_exit.default=RESTART.

We need this option in an active-mirror terracotta setup. If we shut down the passive node and restart it, we will get the following error in the terracotta server log:

ERROR com.tc.l2.ha.ClusterState -
********************************** ERROR ***********************************
* This server is running with persistence turned on and was stopped in
* PASSIVE-STANDBY state. Only the ACTIVE-COORDINATOR server is allowed  to
* be restarted without cleaning up the data directory with persistence
* turned on.
*
* Please clean up the data directory and make sure that the
* ACTIVE-COORDINATOR is up and running before starting this server. It is
* important that the ACTIVE-COORDINATOR is up and running before starting
* this server else you might end up losing data
****************************************************************************

[WrapperStartStopAppMain] ERROR tc.operator.event - NODE : Server2  Subsystem: CLUSTER_TOPOLOGY Message: Started with dirty database. Exiting!! Restart enabled
[WrapperStartStopAppMain] ERROR com.terracottatech.dso - Marking the object db as dirty ...
[WrapperStartStopAppMain] ERROR com.terracottatech.console - This standby Terracotta server instance had to restart to automatically wipe its database and rejoin the cluster.

Terracotta will stop with a return code different than 0. By using the on exit options, the wrapper automatically restarts the service.