Disclaimer : This blog space does not necessarily reflect my views/ideas on the technology and beyond doubt, never reflects the views of my employer.

Monday, November 3, 2008

Connecting SQL Server from JDeveloper

Here is a small example on how to create a SQL Server Connection on JDeveloper.

First of all you will need the sqljdbc.jar. You can download it from the Microsoft site. sqljdbc.jar is available across different versions SQL Server 2000, SQL Server 2005, etc. I'm connecting to SQL Server 2K here using JDeveloper 10133.

Once downloaded, we need to add it to the JDeveloper Libraries. I have listed here the steps to create a user library from JDeveloper Help:
  • From the main menu, choose Tools Manage Libraries.
  • In the Manage Libraries dialog, select the Libraries tab, then select the User node, and click New.
  • In the Create Library dialog, enter a library name, select the Source Path node, and click Add Entry .
  • In the Select Path Entry dialog, browse to the location of the drivers for the database you are connecting to. Select the driver files, and click Select.
  • In the Create Library dialog, click OK, and in the Manage Libraries dialog, click OK .

We can add this library while creation of databse also, but adding first using Manage Libraries is a good practice.

Now in JDeveloper, go to Connection Navigator, Right-Click on Database and select "New Database Connection" from the context menu. Follow the wizard as displayed below:



Select the last option "Thid Party JDBC Driver" from the drop-down.

On the next page, provide valid UserID and Password.



On step 3

For Driver Class, name is com.microsoft.sqlserver.jdbc.SQLServerDriver, and select the library sqljdbc.jar from Manage Libraries as we have added above.



Define the URL as below
jdbc:sqlserver://<db_instance>:<port>;databaseName=<db_name>;user=<userid>;password=<password>


Click Next and Test the connection.

Let me know if you are facing any issues.

Cheers

Nirav

Note : Before deploying BPEL process, we need to deploy sqljdbc.jar file to two different locations on the server: soa_home/bpel/lib and soa_home/j2ee/home/applib. For more info..., refer to http://forums.oracle.com/forums/thread.jspa?threadID=978304&tstart=0

2 comments:

Anonymous said...

Thank for the post. I creted DB connection as you said. I created a DB adpter using this connection. When I try to deploy to server I am getting following error. Do we need to do any configuration or copy sqljdbc.jar file to server. Thanks in advance


BUILD FAILED
C:\SOA\jdevstudio10133_V2\jdev\mywork\SreedharK\TestBPELProcess2\build.xml:79: A problem occured while connecting to server "wbf-upk" using port "7777": bpel_TestBPELProcess2_1.0.jar failed to deploy. Exception message is: ORABPEL-09903
Could not initialize activation agent.
An error occured while initializing an activation agent for process "TestBPELProcess2", revision "1.0".
Please ensure that the activation agents are configured correctly in the bpel deployment descriptor (bpel.xml).
oracle.tip.adapter.fw.agent.jca.JCAActivationAgent: java.lang.reflect.InvocationTargetException
at com.collaxa.cube.engine.core.BaseCubeProcess.startAllActivationAgents(BaseCubeProcess.java:354)

Nirav Chhaya said...

hii Can you verify if the conn pool is correctly defined on the server. Make sure you have used the same JNDI URI in you JDev project as it is defined on the server.