Archive for August 30th, 2013

Configuring Oracle SQL Developer for Microsoft SQL Server and MySQL

Configuring Oracle SQL Developer for Microsoft SQL Server and MySQL

Most of DBAs I know, use TOAD for doing daily tasks, but I prefer Oracle SQL Developer. In my opinion, it has 3 important advantages against TOAD:

  • It’s platform-independent: Although I use Windows on my laptop now, I’m a big fan of Linux and Solaris, and I don’t like being dependent on a specific OS. Thanks to Java, Oracle SQL Developer is platform-independent.
  • It supports multiple databases: You can use SQL Developer for Microsoft Access, Microsoft SQL Server, MySQL, TimesTen, DB2 and (of course) Oracle.
  • It’s extendible: Yes I know that there are not too much documents about it, but it’s extensible. For example, check FourthElephant’s extensions for SQL Developer: http://www.fourthelephant.com/sqldeveloper/download/

TOAD has a better interface because of using native Windows components but you can get used to SQL Developer if you spend time on it. Anyway, this blog has nothing to do with comparing SQL Developer and TOAD. I’ll just show how to configure SQL Developer to connect Microsoft SQL Server and MySQL.

You need to download and install the required (and supported) JDBC drivers to make Oracle SQL Developer connect MySQL and Microsoft SQL Server.

For MySQL download the J/connecter from http://www.mysql.com/products/connector/

For Microsoft SQL Server download the jTDS from http://sourceforge.net/projects/jtds/. The JDBC driver which is published by Microsoft doesn’t work with Oracle SQL Developer, at least I couldn’t make it work.

After you download the JDBC drivers, unzip them and then open prefences window in SQL Developer ( tools >> preferences ):

To add JDBC driver for Microsoft SQL Server, click “add entry…” button and find “jtds-1.2.5.jar” in the file open dialog.

To add JDBC driver for MySQL, click “add entry…” button and find “mysql-connector-java-5.1.17-bin.jar” in the file open dialog.

You need to restart SQL Developer to make these new JDBC drivers active. Then you can create a connections for Microsoft SQL Server, Sybase (because JDTS supports Sybase) and MySQL:

that’s it.

Use Oracle’s FREE SQL Developer as a Microsoft SQL Server GUI on OSX

Use Oracle’s FREE SQL Developer as a Microsoft SQL Server GUI on OSX

I’m on a Mac, my company uses a SQL Server, and since there is not native SQL Server Enterprise manager client I was forced to find a workaround. One common solution is to have aVirtual Machine running windows but to me that approach feels like using a sledgehammer to pound in a finishing nail.  Thanks to this post on stackoverflow I am able to use Oracle’s FREE SQL Developer application to connect to Microsoft SQL Server Instances on my MacBook Pro running Lion.  I’ve used SQL Developer for Oracle development for a number of years now so the learning curve was pretty small for me but anybody who is familiar with SQL Server Enterprise Manager should be able to find their way around pretty easily, however there are a few things to be aware of which I’ll discuss a bit later.

Installation

Before you can install SQL Developer you’ll need to make sure that you have the JDK installed on your machine.  I have the Java for Mac Developer Preview installed on my machine from: https://developer.apple.com/downloads/.

Once the JDK is installed (you can check by opening a terminal and typing  javac -version ) the next step is to download and install SQL Developer from Oracle’s website here.  Once SQL Developer is installed you’ll need to get the jDTS plugin files from: http://sourceforge.net/projects/jtds/files/, at the time of this writing the most current version is 1.2.5.  Once the zip is download and extracted move the entire folder someplace where your account will have execute permissions on the jdsts-1.2.3.jar file.  I have a lib directory in my User folder where I keep all my third party .jar files.

You now have everything you need to get SQL Developer executing SQL queries, you just need to do some simple setup in SQL Developer.  Open SQL Developer and as of version 3.0 go to Tools -> Preferences  and expand the “Database” node.  Next click on Third Party JDBC Drivers click on “Add Entry…”.  From this menu browse to the /jdts-1.2.5-dist folder you copied in the step above and select jdts-1.2.5.jar (or whichever version corresponds to the version you downloaded) and click “OK”.

Configuration

SQL Server Connection Tab

Once you have the plugin installed exit out to the main SQL Developer IDE and add a new connection by either: clicking on File -> New -> Database Connection or by clicking on the green + icon in the “Connections” tab.   You should now see a “SQLServer” tab where you can enter the connection information for your SQL Server.

Using SQL Developer to Query Microsoft SQL Server Databases

Select Default Database
If you have not used SQL Developer before there are a few things to be aware of.  Since it is primarily an Oracle IDE you have to think in a somewhat “Oracle” way when running queries on your database. Since Oracle is a schema-centric database server you’ll save yourself lots of typing if you set a default database for your current connection.   If you don’t you’ll have to type the fully qualified table name for each table you reference in a query (e.g. database.owner.tablename).  To do this right click on the database you’d like to use and click on “Select Default Database” in the context menu.  Once you do this you can do normal SELECT x FROM Table queries and leave out the fully qualified table name.  You’ll have to do this each time you connect to SQL Server.
SQL Developer will allow you to do many of the basic database tasks you can do in Enterprise Manager, however there are quite a few limitations. You can view your Table/View structure and data, you can also view most of the other objects in SQL Server like Stored Procedures and Functions.   SQL Developer will also allow you write DML (Data Manipulation Language) statements like Update, Delete, and Insert.   You can also write some DDL (Data Design Language) statements like ALTER TABLE.  However, the one huge shortcoming of SQL Developer is its inability to recognize the “BEGIN” keyword.  This means that any statement that uses BEGIN, like transactions, stored procedures, and functions will all generate a syntax error and not execute.One nice “benefit” of using SQL Developer is the “Format” context menu.  When I’m writing code I often get SQL statements in debugging code, however, its usually one long string, debugging this code in Enterprise Manager usually meant having to manually update format the SQL, however in SQL Developer I can paste the code into the SQL Worksheet, right click, and click on “Format” and have a nicely formatted SQL Statement.

Conclusion

While its not a complete replacement for Enterprise Manager I do find that SQL Developer will allow me to do many of the things I spend 90% of my time doing to my databases.  The best part is that this is a completely Free and somewhat mature solution.
%d bloggers like this: