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 Resource from the SOA component palette 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 DataSource 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.
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.
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 Resource from the SOA component palette 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 DataSource 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.
No comments:
Post a Comment