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

http://www.microsoft.com/en-us/download/details.aspx?id=21599

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.

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')

1^st 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