Configuring MSSQL with SOA Suite 11g

MSSQL DB Setup

 

This examples uses SQL Server Express 2000 and Oracle SOA Suite 11.1.1.1. SOA Suite is also deployed on Linux so in some situations you will need to look for the windows versions of the file referenced in this document.

 

DB Setup

 

  1. Start SQL Server Configuration Manager
  2. Double click the TCP/IP configuration

    image 
  3. Set dynamic ports off by setting dynamic ports to blank and specify a port for TCP Port. I did this for all IP address to make sure.

    image 
  4. Bounce the database.

 

Design Time Configuration

 

  1. Source the MSSQL jdbc driver and copy it to the your JDeveloper library,
    sqljdbc.jar
    JDEV_HOME/lib

    image 
  2. Start JDev and create a new DB connection
  3. Choose Connection Type = Generic JDBC (Future releases SQL Server is supplied) 
  4. Provide username and password
  5. Create a new Driver class using the below screen shot, you need to browse for the driver on the OS.

    image 
  6. Provide the JDBC URL, in the following format

    jdbc:sqlserver://hostname:port;databasename=DB

    e.g.

    jdbc:sqlserver://xpone:1433;databasename=SALES_DEV

  7. Test and make sure it is successful.

  8. Develop your composite using this connection.

 

Run Time Configuration

 

  1. Copy the same sqljdbc.jar used for JDev above to the following locations

    WLS_HOME/server/lib

    user_projects/domains/soainfra/lib


  2. Edit the WEBLOGIC_CLASSPATH to point to the above locations. This is done by editing the file:
     
    $WLS_HOME/common/bin/commEnv.sh (Linux)

    %WLS_HOME%/common/bin/commEnv.bat (Windows)

    e.g.

    WEBLOGIC_CLASSPATH="${JAVA_HOME}/lib/tools.jar${CLASSPATHSEP}${BEA_HOME}/utils/config/10.3.1.0/config-launch.jar${CLASSPATHSEP}${WL_HOME}/server/lib/weblogic_sp.jar${CLASSPATHSEP}${WL_HOME}/server/lib/weblogic.jar${CLASSPATHSEP}${FEATURES_DIR}/weblogic.server.modules_10.3.1.0.jar${CLASSPATHSEP}${WL_HOME}/server/lib/webservices.jar${CLASSPATHSEP}${ANT_HOME}/lib/ant-all.jar${CLASSPATHSEP}${ANT_CONTRIB}/lib/ant-contrib.jar${CLASSPATHSEP}${WL_HOME}/server/lib/sqljdbc.jar${CLASSPATHSEP}${MW_HOME}/user_projects/domains/soainfra/lib/sqljdbc.jar"

 

  1. Start WLS console and navigate

    soa_domain -> services -> JDBC -> Data Sources

    image
  2. Create a new data source using screen shot below.

    JNDI Name = jdbc/MSSQL

    Don't worry about the Database driver we will change that manually in next steps. 

    image  
    click Next
  3. Click Next on Transaction Options page

  4. Enter DB connection details

    image 
    click Next
  5. Modify the URL and the driver as follows and test connection

    Driver = com.microsoft.sqlserver.jdbc.SQLServerDriver

    URL = jdbc:sqlserver://xpone:1433;databasename=SALES_DEV

  6. My Connection Pool should look something like this:

    image 
  7. In the connection Pool page click on the Test Connections On Reserve, under the Advanced options.

    image 
  8. In WLS Console navigate to the following

    soa_domain -> deployments -> DbAdapter -> Configuration -> Outbound Connection Pools

  9. Add a new connection pool accept the javax.resource.cci.ConnectionFactory option

  10. Define a JNDI Name, this should match you design time JNDI Name.

    JNDI Name = eis/DB/MSSQL

    image 
    click Finish

  11. Edit the new connection as below, you need to change the platformClassName and the xADataSourceName. When you make the change you have to press enter for it to register.

    platformClassName = oracle.toplink.platform.database.SQLServerPlatform

    xADataSourceName = jdbc/MSSQL

    image 
    click Save

  12. Deploy the Db adapter again to recognise the changes. Click the update button, on the next page accept the defaults and click finish.

    DeployDB.JPG

  13. Bounce WLS and SOA Suite.

 

TEST your SOA Process.

Comments:

Thanks
I follow the steps on SOA part and configure the data source from Web logic console, but I am getting the error below when Click on Test Configuration, can you please let me know what is the issues?

Connection test failed.
[FMWGEN][SQLServer JDBC Driver]Error establishing socket to host and port: 10.130.5.22:1433. Reason: Connection refused<br/>weblogic.jdbc.sqlserverbase.dda4.b(Unknown Source)<br/>weblogic.jdbc.sqlserverbase.dda4.a(Unknown Source)<br/>weblogic.jdbc.sqlserverbase.dda3.b(Unknown Source)<br/>weblogic.jdbc.sqlserverbase.dda3.a(Unknown Source)<br/>weblogic.jdbc.sqlserver.tds.ddc.<init>(Unknown Source)<br/>weblogic.jdbc.sqlserver.SQLServerImplConnection.f(Unknown Source)<br/>weblogic.jdbc.sqlserverbase.BaseConnection.a(Unknown Source)<br/>weblogic.jdbc.sqlserverbase.BaseConnection.j(Unknown Source)<br/>weblogic.jdbc.sqlserverbase.BaseConnection.a(Unknown Source)<br/>weblogic.jdbc.sqlserverbase.BaseConnection.a(Unknown Source)<br/>weblogic.jdbcx.sqlserverbase.ddt.a(Unknown Source)<br/>weblogic.jdbcx.sqlserverbase.ddt.getXAConnection(Unknown Source)<br/>com.bea.console.utils.jdbc.JDBCUtils.testConnection(JDBCUtils.java:745)<br/>com.bea.console.actions.jdbc.datasources.createjdbcdatasource.CreateJDBCDataSource.testConnectionConfiguration(CreateJDBCDataSource.java:458)<br/>sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)<br/>sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)<br/>sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)<br/>java.lang.reflect.Method.invoke(Method.java:597)<br/>org.apache.beehive.netui.pageflow.FlowController.invokeActionMethod(FlowController.java:870)<br/>org.apache.beehive.netui.pageflow.FlowController.getActionMethodForward(FlowController.java:809)<br/>...

Appreciate! Kelly

Posted by Kelly on March 20, 2013 at 08:22 AM GMT+13:00 #

Can you tell me if the Design Time connection ins JDeveloper works, e.g. step 7. This will tell me there is no issues with the sqljdbc.jar file or firewalls.

If this does work then you need to check the classpath of weblogic to make sure it includes the sqljdbc.jar. Once you have done this make sure you restart.

cheers
James

Posted by guest on March 21, 2013 at 10:41 AM GMT+13:00 #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Discussions and Examples using Oracle Fusion Middleware. Some image links are broken when using Firefox, Safari, and Chrome. If you want to see the full image please use IE.

Twitter:@james8001

tumblr hit counter vistors, thanks for your support

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today