Creating a DBAdapter for MSSQL Server in OSB 11g

I recently came across a requirement to create a DB Adapter for doing some CRUD on MSSQL Server DB from OSB.  There were a few problems that I faced and would like to share the details of what I followed for the same.

To create a DB Adapter in OSB 11g we would have to create the Adapter WSDL and EIS xml files in JDeveloper.

Here is a sequence of steps that can be followed to create a DB Adapter for MSSQL in OSB.

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.

Add these files to the JDeveloper classpath. Start JDeveloper 11g and create a new SOA Application. (Remember to add the SOA composite plugin for JDeveloper before this)

Now create a project in the application with any desired name.

Select project type as “Empty Composite”.

Now add a Database Adapter Object from the SOA component pallete to the composite. Configure a new Database connection with any desired JNDI string. For me it would be “eis/DB/DB_Adapter_Connection” (Please get a better and a more intuitive name than the one i use)

Configure the Database Connection as under. I prefer to choose a Generic JDBC connection Type and enter specific driver class name than a typed connection. You are free to choose the default MSSQL driver type.

This being part one of creating a Database EIS for an MSSQL server. Now in the Adapter you can specify the above DB Connection and create Polling/Store Proc/Native SQL/CRUD type WSDL as per any database operation. The second part is creating a Datasource information in Weblogic Server and creating an outbound connection factory for the EIS. Here are the steps to be followed.

Create a new JDBC data source with JNDI value as “eis/DB/DB_DATASOURCE” and database type as MS SQL Server. Selected Database driver as Oracle’s MS SQL Server Driver (Type 4) Version 7.0.

Now the final step is to create an Outbound Connection Pool in the DBAdapter.ear in WLS and update the plan file.

In weblogic console, create a new outbound connection pool in DB Adapter deployments with JNDI value as “eis/DB/DB_Adapter_Connection” under the existing default connection factory (javax.resource.cci.ConnectionFactory).

Configure the data source property value as “eis/DB/DB_DATASOURCE” and saved the configuration as shown below.

Now save the changes and update the deployment plan. However doing so will throw runtime errors in OSB when we create any OSB resources with this EIS.

We have to make sure that we use the correct platformClassName when choosing a Database Driver type. The default one i.e org.eclipse.persistence.platform.database.Oracle10Platform is for connecting to Oracle Databases.

In cases we have to connecting to MSSQL Server 2008/10 the platformClassName will be oracle.toplink.platform.database.SQLServerPlatform.

Here is a table of platformClassName and Database Type.

Database PlatformClassName
Oracle9+ (including 10g) oracle.toplink.platform.database.Oracle9Platform
Oracle9+ (optional): oracle.toplink.platform.database.Oracle9Platform
Oracle8 oracle.toplink.platform.database.Oracle8Platform
Oracle7 oracle.toplink.platform.database.OraclePlatform
DB2 oracle.toplink.platform.database.DB2Platform
DB2 on AS400 oracle.tip.adapter.db.toplinkext.DB2AS400Platform
Informix oracle.toplink.platform.database.InformixPlatform
SQLServer oracle.toplink.platform.database.SQLServerPlatform
MySQL oracle.toplink.platform.database.MySQL4Platform
Any other database oracle.toplink.platform.database.DatabasePlatform

For more details see here

http://download.oracle.com/docs/cd/E12839_01/integration.1111/e10231/adptr_db.htm#BABEJAAJ

I have been able to get around working with MSSQL server DB Connections with the above configurations.

3 thoughts on “Creating a DBAdapter for MSSQL Server in OSB 11g

  1. Pingback: WebLogic by meijerdd - Pearltrees

If you have any comments, suggestions or feedback about the post, please feel free to type it here and I will do my best to address them asap

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s