Sunday, 21 July 2013

Setup SqlServer DB Connection in Jdeveloper 11.1.1.6

To configure Microsoft SQL Server Driver in JDeveloper for DB adapter , follow the below mentioned steps

1.Download Microsoft SQL Server JDBC Driver 1033\sqljdbc_3.0.1301.101_enu.exe from the link


2. Unzip 1033\sqljdbc_3.0.1301.101_enu.tar.gz


3. Open JDeveloper 11g.

4. On the menu, navigate to Tools -> Preferences

5. Expand the Database.

6. Click on JDBC driver options.


7. Click New.

8. Click Browse


9. Click New.

10. Enter Library name as sqljdbc4.jar. Make sure that Library name doesn't have spaces.


11. Click on Add Entry.

12. Locate the file sqljdbc4.jar from system. It should be at
Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar


13. Click on Select -> Ok -> Ok.




14. Enter Driver Class of "com.microsoft.sqlserver.jdbc.SQLServerDriver"


15. Go to New Database Connection and create a Database Connection by providing all the required details. Remember to select SQLServer as Connection Type.


Another JDBC driver:
Create another new jdbc driver and named it weblogic.jdbc.sqlserver.SQLServerDriver


browse to select jar :weblogic.jar, wlclient.jar and wlsqlserver.jar from Oracle\Middleware\wlserver_10.3\server\lib


Connection using Generic JDBC


jdbc:weblogic:sqlserver://<hostname>:<portname>;DatabaseName=<DBName>

Data Source Creation:
Follow the below steps for creating a Data source for Microsoft SQL server,
1. Log into weblogic server. e.g. http://localhost:7001/console
2. Navigate to Services -> Data Sources.
3. On the configuration page, click on New ->GenericDataSource

4. Enter Data Source Name, JNDI name, Select Database type MS SQL Server.
5. Click Next.
6. Select Database Driver "*Oracle's MS SQL Server Driver (Type4) Versions:7.0 and later".


7. Click Next.
8. check "Supports Global Transactions".
9. Click Next.
10. Provide MS SQL server details and click Next.

11. Test Data Source by clicking on TestConfiguration at left top.
12. Once test is successful click next to select the target.
13. Click Finish.






Friday, 19 July 2013

Auto Deployment In OSB 11.1.1.6 Version

In this tutorial I will explain how OSB interfaces can deploy in unix environment using shell and python script.It will be helpful during production deployment.

We need to create one folder structure for OSB deployment.
Developers develop applications in Eclipse and create importable sbconfig.jar packages.
These sbconfig.jar packages are used to import integration projects into different runtime environments (dev/tst/qat/prd).
CustomizationFile.xml files are used to modify the imported OSB project to suit each environment.Also I have created deployment script for creation of project specific data source,JMS Queue,Connection factory.


1st we need to create below mentioned project structure through out all integration.


All integrations are using JMS resources (Queue/Topic) for persisting messages. Before deploying integration we need to create python script for JMS resource.Python script also located inside same configuration folder.
1.       OSBAppDeploymentProperties: secure and keyfile.secure are WLS server domain credentials for accessing domain admin console.
config.properties contains properties for connection.These files need not to be stored in SVN.
There will different set of these files for every environment (dev/test/prod).Files will be stored on physical disk location on the respective server.

1.1.    Creation of configfile.secure and  keyfile.secure (One time task)

Open the weblogic scripting tool and run the following command to create .secure files
connect('weblogic','Welcome1','localhost:7001')
1st edit all ‘’ to text pad


storeUserConfig(C:/OSB Deployment/OSBAppDeploymentProperties/configfile.secure','C:/OSB Deployment/OSBAppDeploymentProperties/keyfile.secure')
connect(userConfigFile='C:/OSB Deployment/OSBAppDeploymentProperties/configfile.secure',userKeyFile='C:/OSB Deployment/OSBAppDeploymentProperties/keyfile.secure')


   /OSBAppDeploymentProperties/config.properties
   /OSBAppDeploymentProperties/userkey
   /OSBAppDeploymentProperties/userconfig

Domain specific common properties can be used in WLST scripts either by hardcoding the reference to the script file or by passing a reference in the weblogic.WLST invocation with argument -loadProperties. 



2.       Deployment Script
           2.1 deploy.sh: It is the startup script for integration deployment.It will 1st load property file and sbimport python script which will deploy jar as well as customization file to DEV/TEST/PROD environment.

#!/bin/sh
#--------------------------------------------------------
# shell script for importing an OSB configuration jar file and executing an OSB customization file using a #wlst script. This script also gives a Soaker domain specific property file as an argument to the wlst script
# This script takes one argument
# $1 = OSB customization (e.g. dev, tst, qat, prd)
#
# Version History:
# 2013-07-05 Souvik Pal   First version
# 2013-07-08 Souvik Pal   'mode' argument and JMS resources creation added
# 2013-07-10 Souvik Pal   project specific changes, JMS script execution, datasource and dbadapter
#creation
# --------------------------------------------------------
FMW_HOME=/opt/oracle/FMW11gR5/osb
OSB_DEPLOYMENT=/opt/app/cluster/OSBAppDeploymentProperties
echo Setting Weblogic server Environment...

. ${FMW_HOME}/wlserver_10.3/server/bin/setWLSEnv.sh

CLASSPATH=${CLASSPATH}:${FMW_HOME}/Oracle_OSB1/modules/com.bea.common.configfwk_1.6.0.0.jar:${FMW_HOME}/Oracle_OSB1/lib/alsb.jar
export CLASSPATH

echo Creating JMSResources...
java weblogic.WLST -loadProperties ${OSB_DEPLOYMENT}/config.properties ./configuration/WebLogicJMS_config.py

echo Creating Data sources...

java weblogic.WLST -loadProperties ${OSB_DEPLOYMENT}/config.properties configuration/${1}/createDS.py
java weblogic.WLST -loadProperties ${OSB_DEPLOYMENT}/config.properties configuration/${1}/UpdateDBPlan.py


echo Importing OSB Configuration and executing ${1} Customization file...
# parameters for sbimport.py are: [1] OSB configuration jar, [2] OSB customization file, [3] OSB Domain property file, [4] mode[add/update]
#
java weblogic.WLST ${OSB_DEPLOYMENT}/sbimport.py ./build/sbconfig.jar ./configuration/${1}/ALSBCustomizationFile.xml ${OSB_DEPLOYMENT}/config.properties update

        2.2 import script:

sbimport.py: Import sbconfig.jar and execute ALSBCustomizationFile.xml file
------------------------------------------------------------------------------------------------------------------------------------------
#================= sbimport.py ========================================================
# This wlst script can be used to import OSB configuration and to execute a customization file.
# Version History:
# 2012-09-13   Souvik Pal    Copied from Oracle samples and modified.
# 2012-09-17   Souvik Pal    'mode' parameter added.
#====================================================================================
import wlstModule
from java.util import HashMap
from java.util import HashSet
from java.util import ArrayList
from java.io import FileInputStream

from com.bea.wli.sb.util import Refs
from com.bea.wli.config.customization import Customization
from com.bea.wli.sb.management.importexport import ALSBImportOperation
import sys
#==============================================================================
# Entry function to deploy project configuration and resources
#        into a ALSB domain
#==============================================================================
def importToALSBDomain(importJar, customizationFile, domainPropertyFile, mode):
    try:
        print 'Starting the script ...'
        SessionMBean = None
        configProps = loadProps(domainPropertyFile)
        adminUrl = configProps.get('adminUrl')

# Connect to admin server
        connect(userConfigFile=configProps.get('configFile'), userKeyFile=configProps.get('keyFile'), url=adminUrl)
        domainRuntime()

# Import jar
        print 'Attempting to import :', importJar, "on ALSB Admin Server listening on :", adminUrl

        theBytes = readBinaryFile(importJar)
        print 'Read file', importJar
        sessionName = createSessionName()
        print 'Created session', sessionName
        SessionMBean = getSessionManagementMBean(sessionName)
        print 'SessionMBean started session'
        ALSBConfigurationMBean = findService(String("ALSBConfiguration.").concat(sessionName), "com.bea.wli.sb.management.configuration.ALSBConfigurationMBean")
        print "ALSBConfiguration MBean found", ALSBConfigurationMBean
        ALSBConfigurationMBean.uploadJarFile(theBytes)
        print 'Jar Uploaded'

        if mode == 'add':
            print 'Additive deployment performed'
            alsbJarInfo = ALSBConfigurationMBean.getImportJarInfo()
            alsbImportPlan = alsbJarInfo.getDefaultImportPlan()
#            alsbImportPlan.setPassphrase(passphrase)
            alsbImportPlan.setPreserveExistingEnvValues(true)
            importResult = ALSBConfigurationMBean.importUploaded(alsbImportPlan)
            SessionMBean.activateSession(sessionName, "Complete test import with customization using wlst")
        else:
            print 'ALSB project will get overlaid'
            alsbJarInfo = ALSBConfigurationMBean.getImportJarInfo()
            alsbImportPlan = alsbJarInfo.getDefaultImportPlan()
#            alsbImportPlan.setPassphrase(passphrase)
            operationMap=HashMap()
            operationMap = alsbImportPlan.getOperations()
            print
            print 'Default importPlan'
            printOpMap(operationMap)
            set = operationMap.entrySet()

            alsbImportPlan.setPreserveExistingEnvValues(true)

            #boolean
            abort = false
            #list of created ref
            createdRef = ArrayList()

            for entry in set:
                ref = entry.getKey()
                op = entry.getValue()
                #set different logic based on the resource type
                type = ref.getTypeId
                if type == Refs.SERVICE_ACCOUNT_TYPE or type == Refs.SERVICE_PROVIDER_TYPE:
                    if op.getOperation() == ALSBImportOperation.Operation.Create:
                        print 'Unable to import a service account or a service provider on a target system', ref
                        abort = true
                elif op.getOperation() == ALSBImportOperation.Operation.Create:
                    #keep the list of created resources
                    createdRef.add(ref)

            if abort == true :
                print 'This jar must be imported manually to resolve the service account and service provider dependencies'
                SessionMBean.discardSession(sessionName)
                raise

            print
            print 'Modified importPlan'
            printOpMap(operationMap)
            importResult = ALSBConfigurationMBean.importUploaded(alsbImportPlan)

            printDiagMap(importResult.getImportDiagnostics())

            if importResult.getFailed().isEmpty() == false:
                print 'One or more resources could not be imported properly'
                raise

            #customize if a customization file is specified
            #affects only the created resources
            if customizationFile != None :
                print 'Loading customization File', customizationFile
                print 'Customization applied to the created resources only', createdRef
                iStream = FileInputStream(customizationFile)
                customizationList = Customization.fromXML(iStream)
                filteredCustomizationList = ArrayList()
                setRef = HashSet(createdRef)

                # apply a filter to all the customizations to narrow the target to the created resources
                for customization in customizationList:
                    print customization
                    newcustomization = customization.clone(setRef)
                    filteredCustomizationList.add(newcustomization)

                ALSBConfigurationMBean.customize(filteredCustomizationList)

            SessionMBean.activateSession(sessionName, "Complete import with customization using wlst")

        print "Deployment of : " + importJar + " successful"
    except:
        print "Unexpected error:", sys.exc_info()[0]
        if SessionMBean != None:
            SessionMBean.discardSession(sessionName)
        raise

#==============================================================================
# Utility function to print the list of operations
#==============================================================================
def printOpMap(map):
    set = map.entrySet()
    for entry in set:
        op = entry.getValue()
        print op.getOperation(),
        ref = entry.getKey()
        print ref
    print

#==============================================================================
# Utility function to print the diagnostics
#==============================================================================
def printDiagMap(map):
    set = map.entrySet()
    for entry in set:
        diag = entry.getValue().toString()
        print diag
    print

#==============================================================================
# Utility function to load properties from a config file
#==============================================================================

def loadProps(configPropFile):
    propInputStream = FileInputStream(configPropFile)
    configProps = Properties()
    configProps.load(propInputStream)
    return configProps

#==============================================================================
# Utility function to read a binary file
#==============================================================================
def readBinaryFile(fileName):
    file = open(fileName, 'rb')
    bytes = file.read()
    return bytes

#==============================================================================
# Utility function to create an arbitrary session name
#==============================================================================
def createSessionName():
    sessionName = String("SessionScript"+Long(System.currentTimeMillis()).toString())
    return sessionName

#==============================================================================
# Utility function to load a session MBeans
#==============================================================================
def getSessionManagementMBean(sessionName):
    SessionMBean = findService("SessionManagement", "com.bea.wli.sb.management.configuration.SessionManagementMBean")
    SessionMBean.createSession(sessionName)
    return SessionMBean


# IMPORT script init
try:
    # import the service bus configuration and execute customization file
    # argv[1] is the sbconfig filepath
    # argv[2] is the customization file filepath
                # argv[3] is the domain specific property file
                # argv[4] is the mode [add, update]
    importToALSBDomain(sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4])

except:
    print "Unexpected error: ", sys.exc_info()[0]
    dumpStack()
    raise

3.       Configurations- It contain customization file which need to be modified(hostname and port) while deploying different environment.(DEV,Test,Prod). within a typical customization file you will have environment specific settings for your endpoint addresses for business services, operational settings for retry logic, alert destinations etc. This file will need to be modified for each environment. In most cases the endpoint addresses will have to be modified to match the specific environment.

Also it will contain python script for Queue/topic,Data source creation.
All these .py script can be created by start/stop recording during manual creation.



WebLogicJMS_config_dev.py -import jms queue
WebLogicJMS_JMSServer.py- import jms server,Module,Connection factory, persistent stores
WebLogicJMS_DeleteQueue.py-delete jms Queues
createDS.py- Data source creation
UpdateDBPlan.py- DB Adapter update and redeployment



4.       Jar-It contain the sbconfig.jar file which contains project artifects. You can export jar file from export resource option.


Click create Customization file option and name it ALSBCustomizationFile.xml


Save these file under below mentioned SVN location (depends on integration).
1.       src : src folder contain project workspace or source code.
2.       Run the deployment script
Command:

Ø  deploy.sh <dev>    where <dev> = [prd,qat,tst,dev]
this will create
a) datasource -> integrationDS
b) dbadapter pool eis/db/integrationdb
c) import the OSB project that uses the created dbadapter.
Ø  Log in to SBConsole, Verify that project is deployed to the domain.
Ø  Log in to WLS Console. Verify that the datasource is created
Ø  Open an edit session. Browse to Deployments --> DbAdapter --> Configuration --> Outbound connection pools --> eis/db/integrationdb





Friday, 31 May 2013

Ant script deployment for BPEL


In this tutorial I will explain how BPEL interfaces can deploy in unix environment using shell script.
But before that you need to set all the path.



Create on setWLSEnv.cmd file where set all the path or individually set all these path in comment promt.

set ORACLE_HOME=C:\oracle\Middleware
set ANT_HOME=%ORACLE_HOME%\jdeveloper\ant
set PATH=%ANT_HOME%\bin;%PATH%
set JAVA_HOME=%ORACLE_HOME%\jdk160_24


after setting path echo the path.

Now back to the command prompt.
Check ant exists or not

But if you want to permanently set the path ,use environment variable to set it

A sample project has been to created to print an input message from a Asynchronous BPEL. The user gives input through the client.

Before deployment I can configure a deployment plan xml file.
Right Click on composite.xml file and Generate config plan.

Appending env name:dev
A file generates name <projectName_cfgPlan_env.xml>.

Env may be dev,test,prod according to your need.

This configuration can be attached at the time of deployment. You can change properties of the project with the configuration plan.

Add two files (build.properties, build.xml) in the same project of the JDeveloper (.Jca Project).
Sample files is given with the document.


build.properties
build .properties file contain all the environment related path and port details.
There should be one build .properties for each environment.Here  

# build file for HelloWorldComposite
proj.composite.name=HelloWorld
# revision of the composite
proj.composite.revision=1.0
# Simply specify Java Home
java.home =C:/Oracle/Middleware/jdk160_18/bin
#Project directory
proj.compositeDir=C:/JDeveloper/mywork/HelloApps/HelloWorld
# Set oracle.home to <JDEV_HOME>/jdeveloper, where <JDEV_HOME> is JDEV # installation directory
oracle.home=C:/Oracle/Middleware/jdeveloper
# soa-server side oracle home directory - needed for deployment plans
# and the weblogic sca library deployment
soa.server.oracle.home=C:/Oracle/Middleware/Oracle_SOA1
#################Deployment server connection information########################
# the admin server connection information
admin.server.host=localhost
admin.server.port=7001
# the domain where soa infra is installed
server.domain.name=soa_domain
# connection information for the managed server, used for soa-deployment
managed.server.host=localhost
managed.server.port=8001
# User and credentials for the servers
server.user=weblogic
server.password=Welcome1
# wls server where soa is targeted.
server.targets=soa_server1
deployment.plan.environment=dev

build.xml

<?xml version="1.0" encoding="UTF-8" ?>
<project name="SoaWSProject">
  <!-- build.properties --> 
   <property file="build.properties"/>
  <property name="deploy.dir" value="${proj.compositeDir}/deploy"/>
  <echo>deploy.dir :${deploy.dir}</echo>
   <target name="build">
    <property name="sca-inf.classes.dir" value="../SCA-INF/classes"/>
       <mkdir dir="${sca-inf.classes.dir}"/>
      <ant antfile="${oracle.home}/bin/ant-sca-package.xml" target="package" inheritall="false">
        <property name="oracle.home" value="${oracle.home}"/>
        <property name="compositeDir" value="${proj.compositeDir}"/>
               <!-- needed for adf config -->
         <property name="scac.application.home" value="${basedir}/../.."/>
         <!-- name of the composite -->
        <property name="compositeName" value="${proj.composite.name}"/>
         <!-- revision of the composite -->
        <property name="revision" value="${composite.revision}"/>
         <!-- java.passed.home passed to scac, this property will be
             overwritten to the env.JAVA_HOME if such env variable is defined in OS level-->
         <property name="java.passed.home" value="${java.home}"/>
    </ant>
  </target>
  <target name="deploy">
     <echo>Deploying to http://${managed.server.host}:${managed.server.port}/soa-infra/deployer</echo>
     <echo>proj.compositeDir :$(proj.compositeDir)</echo>
            <echo>composite.name :${composite.name}</echo>
            <echo>java.home :${java.home}</echo>
            <echo>deploy.dir :${deploy.dir}</echo>
    <ant antfile="${oracle.home}/bin/ant-sca-deploy.xml" target="deploy" inheritall="false">
        <!-- reformat of prop names -->
         <property name="user" value="${server.user}"/>
        <property name="password" value="${server.password}"/>
        <!-- create the url for deployment -->
        <property name="serverURL" value="http://${managed.server.host}:${managed.server.port}/soa-infra/deployer"/>
         <!-- location of the sar -->
        <property name="sarLocation" value="${deploy.dir}/sca_${proj.composite.name}_rev${composite.revision}.jar"/>
         <!-- force overwrite if already there -->
        <property name="overwrite" value="true"/>
        <property name="forceDefault" value="true"/>
         <!-- configplan-->
        <property name="configplan" value="${proj.compositeDir}/${proj.composite.name}_cfgplan_${deployment.plan.environment}.xml"/>
    </ant>
<echo>sarLocation :${sarLocation}</echo>
 </target> 
</project>

Now run the build.xml file to build the project.I am using "ant build" commend.Here is build
After compilation you have to deploy the project




Saturday, 25 May 2013

Add/Rename/Remove Namespace



In this tutorial I have tried to capture some xquery operation namespace.We are having lot many issues with these namepace.Sometimes source xml does not have any namespace.So middleware need to add those namespace and send it to target system with expected format.

Here I am creating one OSB proxy service with will poll messages from jms queue using jms transport protocol.Messages are xml type with no namespace.


I am using xquery to addnamespace with that input xml.

xquery version "1.0" encoding "Cp1252";
(:: pragma parameter="$noNamespaceXML" type="xs:anyType" ::)
(:: pragma parameter="$namespaceURI" type="xs:string" ::)
(:: pragma type="xs:anyType" ::)
declare namespace xf = "http://tempuri.org/Resources/XQueries/addNamespace/";
declare function addNamespaceToXML($noNamespaceXML as element(*),$namespaceURI as xs:string)as element(*)
{
 element {fn:expanded-QName($namespaceURI,fn:local-name($noNamespaceXML))}
{
      $noNamespaceXML/@*,
      for $node in $noNamespaceXML/node()
      return
      if (exists($node/node())) then addNamespaceToXML($node,$namespaceURI)
      else if ($node instance of element()) then element {fn:expanded-QName($namespaceURI,fn:local-name($node))}{$node/@*}
      else $node }
};

declare variable $noNamespaceXML as element(*) external;
declare variable $namespaceURI as xs:string external ;
addNamespaceToXML($noNamespaceXML, $namespaceURI)

Proxy message flow I am using replace activity to replace the existing input body with this xquery and adding log after that to display the change body.


To Test this,I am putting input xml in jms queue.

<?xml version="1.0" encoding="UTF-8"?>
<empDetails>
<emp>
<empID>1111</empID>
<empName>Souvik</empName>
<empaddr>2020 GOVERNMENT AVE,NORTH DAKOTA</empaddr>
<sal>10000</sal>
</emp>
</empDetails>



Here is output with namespace alias xmlns:emp="http://www.example.org/EmpDetails".


After adding namespace to request message I am renaming the namespace from "http://www.example.org/EmpDetails" to "http://www.souviksoa.com/EmpDetails"
I am using rename activity here.I xpath using .//* to rename all child elements under body.


Here is the next output


Now I am removing namespace using xquery or xslt.


xslt transformation for remove namespace


xquery transformation for remove namespace

xquery version "1.0" encoding "Cp1252";
(:: pragma  parameter="$anyType1" type="xs:anyType" ::)
(:: pragma  type="xs:anyType" ::)

declare namespace xf = "http://tempuri.org/Cancel_Order/Remove_Namespace/";

declare function xf:strip-namespace($e as element())as element()
{element { xs:QName(local-name($e)) }
{ for $child in $e/(@*,node())
 return  
 if ($child instance of element())
    then xf:strip-namespace($child)
       else $child
       }
 };

declare variable $e as element() external;
xf:strip-namespace($e)


Final output after removing the namespace.