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:
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>”
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”
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
10- After that i’ll press next as i don’t need any read-only view objects
12- After that the diagrammer screen comes, so go through that without doing anything and press next.
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”
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