Saturday, August 4, 2012

Connection to SQl Server from JDeveloper to work with DB Adapters in SOA 11g

This post explains 

a) Setup the SqlServer's connection in Jdeveloper 11g.
b) Creating the DB Adapter in Weblogic console to access the SQl Server database.


1. Add Oracle’s SQL JDBC driver (Present in Weblogic home/Server/lib) to JDeveloper. Files to copy would be weblogic.jar, wlclient.jar and wlsqlserver.jar. Create a folder called OracleJDBC_MSSQL_Driver under <your path>Oracle/Middleware folder and copy these files in there. Also add these files to the JDeveloper classpath.

2. Create the DB connection with the following :ConnectionType : Generic JDBC.DriverClass : weblogic.jdbc.sqlserver.SQLServerDriver.Library: OracleJDBC_MSSQL_Driver  ( browse the above path in which you have the 3 jars)
3. Jdbc url woud be like : jdbc:weblogic:sqlserver://<hostname>:<portname>;DatabaseName=<yourDBName>Note : its a semi colon after port and not a :

4.Create a new JDBC data source with JNDI value you like  and database type as MS SQL Server. Selected Database driver as Oracle’s MS SQL Server Driver (Type 4) Version 7.0.

5. Make sure you have the DB Adapter's Conection pool properties like below :

a.datasourcename : Your Sql Server datasource name created in above step.
b.platformClassName -  oracle.toplink.platform.database.SQLServerPlatform.
(The default one i.e org.eclipse.persistence.platform.database.Oracle10Platform is for connecting to Oracle Databases.)
c. defaultNchar should be 'false'.
d. sequencePreallocationSize to 50.
e. batchwriting - true.
f. nativesequencing - true.
g. skipLocking - true.