Database – Modeling – Oracle SQL Developer Data Modeler (v 3.3)

Introduction

In the thick of converting from a legacy database to a new system and thank goodness the legacy system has well defined relationship mappings (Primary Key and Foreign Keys).

And, this will help my familiarization.

It always helps to look at a diagram and make sure that my relationships are maintained in the new DB.

Potential Problem

The in-built tool that comes with SQL Server Management Studio is just not cutting it for me.   There some important pieces missing.  And, so looked on the .Net for free Data Modeling tools.

Oracle SQL Developer Data Modeler

Kent Graziano talked a good game about “Oracle SQL Developer Data Modeler” in his web post (http://kentgraziano.com/2012/02/20/the-best-free-data-modeling-tool-ever/)  and so I think I will give it a good look).

Download – Oracle SQL Developer Data Modeler

Downloaded the v3.3 tool from http://www.oracle.com/technetwork/developer-tools/datamodeler/data-modeler-releasenotes33-1869517.html

Download – Microsoft JDBC Driver

Downloaded the latest Microsoft JDBC Driver (v 4.0 ) from http://www.microsoft.com/en-us/download/confirmation.aspx?id=11774.

Extract – Oracle SQL Developer Data Modeler

Extract the compressed file.

folderHier

Extract – Extract Microsoft JDBC Driver

Extract Microsoft JDBC Driver.  The files we really want are the jar files named sqljdbc.jar and sqljdbc4.jar and they are in the sqljdbc_4.0\enu folder.

Copy Extracted Microsoft JDBC Jar files to Tools (jdbc\lib) folder

Copy the extracted jar files from \sqljdbc_4.0\enu to the tool’s jdbc-lib path (jdbc\lib)

Aware Oracle SQL Developer of location of MS SQL Server JDBC Jar files

  • In SQL Developer, access the menu Item Tools/Preferences/
  • Access Data Modeler/Third Party JDBC Drivers
  • Depending on the version of Java JRE bin, indicate SQL Server Jar file.  If you chose to the download SQL Developer with the bundled JRE, then please choose sqljdbc4.jar

Reverse Engineer

To reverse engineer an existing database, please do the following:

  1. On the main menu, select File\Import\Data Dictionary
  2. On the “Connect to Database” Tab, click the “Add” button
  3. Enter Database specific connection detail
    1. Connection Name: Any name you  like
    2. Username: Database user name
    3. Password: Database User Password
    4. For anything other than Oracle, please select the JDBC Tab
    5. JDBC URL :- jdbc:sqlserver://<hostname>:<portNumber>;database=database
    6. Driver Class :- com.microsoft.sqlserver.jdbc.SQLServerDriver
    7. From the Database Type drop-down, select the Database Type

MSSQLServerConnectionInfo

  1. Verify your connection data, by pressing on the Test button
  2. Click Save once verified
  3. Ensure that the right Connection Item is selected and click on the Next button
  4. On the “Select Schema/Database”, select the Database NameSelectSchemaAndDatabase
  5. On the “Select Objects to Import” screen, please select the objects you want

SelectObjectsToImport

  1. On the “Generate Design” screen, please review the summary per how many objects will be imported
    GenerateDesign
  2. Click the Finish button to generate your model

Sample

Here is sample model from the ASP.Net Membership Database.

Sample - ASP.Net Membership Model

Error

A quick round-up of possible errors:

Error – Status : Failure -Test failed: Java Runtime Environment (JRE) version 1.6 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.

Get Java JRE Version#

Using GUI

The relative path to the bundled JRE is datamodeler\jdk\jre\bin and the file’s name is java.exe.

Get the file’s property by right clicking on the file in Windows Explorer.

Java Version (GUI)

Using OS Cmd Line

java -version

Result


java version "1.6.0_35"
Java(TM) SE Runtime Environment (build 1.6.0_35-b10)
Java HotSpot(TM) Client VM (build 20.10-b01, mixed mode)

Adjust Third Party JDBC Drivers

  • Access Menu Item Tools/Preferences/
  • Access Data Modeler/Third Party JDBC Drivers

Incorrect

DataModeler-ThirdPartyJDBCDrivers (incorrect)

Correct:

DataModeler-ThirdPartyJDBCDrivers (correct)

Error – Status : Failure -Test failed: Driver class not found.  Verify the Driver Location

DriverClassNotFound

Please ensure that you have entered the Driver Class exactly as “com.microsoft.sqlserver.jdbc.SQLServerDriver”.  I had an extra space before the Driver Class (on the screen dump above).

 

 

5 thoughts on “Database – Modeling – Oracle SQL Developer Data Modeler (v 3.3)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s