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.


Steps:

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. 



3 comments:

  1. hey sridhar,hey sridhar can you tell whether its a XA or NON XA Datasource. if its non xa data source then i am doing the above liek
    DB Adapter's Conection pool properties like below :


    a.datasourcename : Empty
    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.
    h. XADAtasourceName=name of DS

    And but getting an error saying that .





    env:Server
    Exception occured when binding was invoked.
    Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'IDMService' failed due to: Stored procedure invocation error.
    Error while trying to prepare and execute the ACS.dbo.usp_INT_UpdateVoucherUsage API.
    An error occurred while preparing and executing the ACS.dbo.usp_INT_UpdateVoucherUsage API. Cause: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
    Check to ensure that the API is defined in the database and that the parameters match the signature of the API. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value "-8525" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers.
    ".
    The invoked JCA adapter raised a resource exception.
    Please examine the above error message carefully to determine a resolution.


    [FMWGEN][SQLServer JDBC Driver][SQLServer]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.





    ReplyDelete
  2. Are you gandhi?

    ReplyDelete
  3. Your article so informative and i have cleared all of my doubts.your way of explanation is awesome thank you for sharing useful information.For more information please visit our website.

    Oracle Fusion Financial Training

    ReplyDelete