kBLOG

All things about Life, Oracle Middleware and Technology

How to configure Microsoft SQL Server 2005 to work with ADF

| 5 Comments

I’ve been asked if it’s possible to integrate MS SQL Server 2005 with JDeveloper, and I just answered without hesitating “Yes sure”, but as i was knowing Microsoft SQL for long, I know there are issues in it.

Especially it’s modes of authentication, so I’ll only help who has Mixed mode authentication and can access using username and password, for windows authentication that would be another article

At first you have to download Microsoft SQL Server 2005 JDBC driver to work with Microsoft SQL Server 2005 in JDeveloper – you can use ODBC Bridge but i don’t recommend it-.

After downloading the eula, and accepting it the installation will start and an exe file will be installed, double clicking on the exe file will extract its content /Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0\enu you will find two .jar files, basiclly we are talking about sqljdbc.jar so just move it to something like C:\MSQQL and for short i’ll call it <mssql_jdbc_location>.

Now open the JDeveloper and follow the steps to create the connection from this steps:

1- Select Tools>Default Project Properties. In the Default Project Properties dialog select the Libraries node and click the Add Library button
add-library

2- In the Add Library dialog, select the User node, and click on the New button

3- In the Create Library dialog specify a Library Name, such as “SQLServer2005″ Select the Class Path node and click the Add Entry button and navigate till you find sqljdbc.jar in <mssql_jdbc_location> and click OK. Click OK several more times until you’ve exited the Libraries dialog .

4- Next, configure a JDBC connection with the SQL Server database . In the Connections Navigator -If you dont see Connections Navigator Select view from the toolbar > connection navigator- and then right-click on the Database node and select New Database Connection to start the Create Database Connection wizard.

5- Call the connection name : “MSConnection” In the Type dialog specify a Connection Name and select ‘Third Party JDBC Driver’ in the Connection Type field. Click on the Next button.

6- In the Authentication dialog specify “sa” as the Username and the Password as the password used to install the SQL Server database -you have, windows authentication wont be working-

7- In the Connection dialog, locate the Driver Class field and specify the driver class, “com.microsoft.sqlserver.jdbc.SQLServerDriver”. Click on New to add a driver class. In the Register JDBC Driver dialog, specify driver class in Driver Class field. In the Library field select the SQLServer2005 library that you configured in the last section. Click OK to add it.

8- In the URL field specify the connection URL as shown in like this “jdbc:sqlserver://<host_server>:<host_port>;databaseName=<database_name>”

connection_url

9- Now you can test the connection using the Test Connection button.If you’ve done everything correctly, a JDBC connection is established with the database. Click on the Finish button.

Now that we created the connection, lets start creating our ADF Application

1- Now click on applications navigator -that is besides connection navigator- and right click on applications->New Application

2- Let’s call the application name: MSProject and choose the directory Name that you wish to save the project in and, choose application template : Web Applications [JSF,  ADF BC] and click “ok”

3- Now there should appear two projects under the application “Model” and “ViewController“, go to “Model” project->right click and select “New”

4- Expand “Business Tier” node, and select “ADF Business Components” node
5- Choose “Business Components from Tables” item from the right side and click “Ok”

choose-business-components

6- After clikcing “ok” a new screen appear “Initialize Business Component Project” for you to select a connection, so select a connection “MSConnection” from the list and click “Ok”
chooseconnection

7- It may ask you for the password, just press “ok” and go throw the welcome screen of  “Create busines components from tables” so just press “Next”

8- Now choose the entities that you want to select from the database tables and view -If table doesnt appear in teh left bottom, just check the button “Auto-Query” and rename whatever you want, and rename the package name as you want and click next to go to view Objects for me I made the package ends with entity
entity

9- After that select all and shatter that to the right list and change the package name for whatever you want, for me i made the package ends with .views
viewobject

10- After that i’ll press next as i don’t need any read-only view objects

11- After that check on application module and change the name, for me I called it ActivityModule and changesd the package to end with .model without any further things
appmodule

12- After that the diagrammer screen comes, so go through that without doing anything and press next.

13- And finally the summary for all what you have done, it should look similar to something like the following, just click finish, to finish creating
summary

14- Optionally make sure everything is working by testing your application module, right click on your application module in my case its “ActivityModule” and click Test to test that the functionality is working well.

15- important! Now for microsoft JDBC to work effectiantly and make the JDBC running well on the JSP you have to add the JDBC library to the OC4J Libraries by clicking tools>Embedded OC4J Server Preferences and select the Libraries node under the Global node and click add in the top right and navigate to  <mssql_jdbc_location> and select that .jar file and click “Ok”

16- important! Now right click on the application module->”Configurations…”

17- important! Select “<application_module_name>Local” from the left node and click “edit”

18- important! In the application module tab, in the “connection type” section change the connection type from JDBC URL to JDBC Datasource and in the Database name write down “jdbc/MSConnectionDS” or basiclly “jdbc/<your_connection_Name>DS” and click “Ok”
jdbcdatasource

19- Now we are finished with the Model project, we will go for the view controller to create our JSPs

20- Right click on “ViewController” project and click on “New”, under the “Web Tier” node, select “JSF” and in the right node select “JSF JSP”

21- Go through the screens with next and whatever you want, my page was named “index.jspx” and it has no managed-bean within it

22- important! From the “Data Control Palette” under the application module, select whatever view object you like and drag it as a Tables->ADF read-only table

23- Right click and run, and enjoy your Microsoft SQL Server database connection with the power of  ADF Framework :)

Share on... Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Flattr the author
  • http://www.KevinsStory.net KeHoeff

    hey this is a very interesting article!

  • James Smith

    Nice Article was very helpful,

    JDev 11G latest Release 11.1.1.2.0 has Connection Type SQL Server ,

    Remember this might be of some help / reference

    HostName : localhost
    Port : 2187
    Instance Name : soa
    Database Name : mydb

    Driver Class : com.microsoft.sqlserver.jdbc.SQLServerDriver
    Library : sqljdbc4.jar

    Thanks

    James

  • http://blog.amr-gawish.com agawish

    11g is providing a better connectivity choices, and the Project based connections is actually helping a lot.

  • Will

    hi, I have problem with PK autoincrement sql server2005, Jdeveloper 11G Release 11.1.1.2.0 don’t insert PK on my AMOUNT_OF_WORK entity object, any ideas? thanks

  • Apoorv Jain

    I have few columns as nvarchar, now in view criteria I am not able to see all the operands (like contains,startwith etc) how to overcome this ?

    2nd Ques: Is it possible to use ms sql lobs with adf, i want to save image to DB.

    Thanks in advance for help.