Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Tuesday, January 09, 2007

Oracle SQL Developer 1.1 Supports MySQL

Being quite tied up in my daily job, I totally missed the that Oracle has been developing a database application development tool, SQL Developer. In fact, this tool was previously known as "project Raptor" and has now matured to version 1.1. The -to me- interesting news is that:

  • It's free of charge.

  • It runs on Linux, Mac OS/X and Windows

  • Besides the Oracle database, it also supports other databases, including MySQL


Ok - I know all free software adepts will probably have left by now for three reasons, being that the title of this blog entry contains "Oracle", the tool is "free as in beer" and of course because the tool runs on Microsoft Windows. But let's not be distracted. Let's just take a few moments and see how to set up this tool to work with MySQL to figure out how this tool can be useful to us. I read that some people are actually using it to work with MySQL databases, so why not give it a try.

I had a quick go and installed the tool on the family Windows XP desktop. For a few sad reasons, I have been using that as my work PC, because the laptop I especially purchased to work on is being repaired at this moment. I might revisit this topic and install this tool on Linux too. It should not be too much of a problem.

Anyway - what do we have to do to get this tool up and running? Here goes:

Download and License


First of all, if you're going to download anything at all from Oracle, you need to sign in using your otn account. You'll find out soon enough because you'll be prompted to sign in. I set up my account years ago, and I can't really remember the details. I'm pretty sure you need at least an e-mail address. I have never received unsollicited emails from Oracle. It's probably due to me not checking or unchecking checkboxes in the account form, but I just want to put you at ease and let you know that the account does not mean that you will be receiving spam.

You can download the tool free of charge from the following location: http://www.oracle.com/technology/software/products/sql/index.html. You will have to accept the license agreement though - this can be done by selecting the radiobutton the very top of the page.

The license is particularly interesting for those that plan to use this tool in a production environment. To a large extent, the content is what I'd expect for a typical "free as in beer" product. I have no principal objections to using software without being able to modify it, and I certainly have no intention of removing any of the logos or whatever. From a practical point of view, it becomes harder that you can't freely distribute the software to third parties, but still does not have to pose a problem if you plan to use this software only for you own business. Or does it?

The programs may be installed on one computer only, and used by one person in the operating environment identified by us.
...
[You may not]...assign this agreement or give or transfer the programs or an interest in them to another individual or entity
...
[You may not]...disclose results of any program benchmark tests without our prior consent

I think that a literal interpretation of these stanzas from the license agreement sums up the major impediments. If my interpretation is correct, it means that the license agreement is between Oracle and an individual, meaning that each developer in your team must download it's own copy. Each individual has to sign the agreement individually too. You cannot dowload one copy and put it on your central file server - not even if the distribution is confined to only your own business. I hope that me blogging about the tool is not interpreted as benchmarking, because that would mean I'm violating the agreement.

Assuming you're willing to accept the license agreement, you should choose the download that's most appropriate for you. Dowloads are available in the following formats:

  • .zip file including JDK 1.5, 65.2Mb. This is listed as the "Windows" distribution on the oracle site.

  • .zip file without JDK, 38.4Mb if you have a JDK 1.5 (Update 6) installed already. This one is listed twice on the Oracle download site: once as a "Windows" distribution, once as a "Multiple Platforms" distribution. This probably means that this distribution will run on any Windows or *nix system that has some sort of JDK 1.5 installed.

  • .tar.gz file for Mac OS/X, 38.3Mb. This also requires you to separately install J2SE 5.0 (Release 3)

  • .rpm file for Linux, 38.4Mb. This also requires you to separately install JDK 1.5 (Update 6)


Installation and Configuration


I chose to download the second option - the "Multiple Platforms" distribution. I already had a JDK 1.5 installed so this seemed to be most appropriate for me. This yields a single zip file, sqldeveloper-2364-no-jre.zip which you have to unzip after downloading it. It unzips a sqldeveloper directory wich contains all the software.

Inside the sqldeveloper directory, you'll find a sqldeveloper.exe file and a sqldeveloper.sh file. Windows users double-click the .exe, and although I haven't tried if it works, my bet is that *nix users need to run the .sh bash script. There mey be some hidden magic to discover the java environment on your machine. However, I got a dialog which required me to browse for one:

sql-dev-jdk

You must take care to specify the java executable from a java development kit (JDK) - a java runtime environment (JRE) is not sufficient. I tried it, and got this error message:

sql-developer-jre

The download page is pretty clear about it, but because the .zip file name contains no-jre I figured I'd give it a try anyway. So to avoid that problem, make sure you download and install JDK 1.5 (or a compatible implementation) and specify the java executable located in {JDK 1.5 home}/bin.

sql-developer-splash

After correctly specifying the java program in the JDK, sqldeveloper will start. It will pop up the splashscreen, and ask you if you want to migrate your settings froma previous installation. You won't have to specify the location of java hereafter. That is, unless you upgrade your JDK - you might want to respecify it.

Unfortunately, I haven't found out yet where sqldeveloper stores the information. I noticed a jdk.conf file in the sqldeveloper\jdev\bin directory, but that appears to be there for that other Oracle IDE, JDeveloper. At least, I don't see anything being written there so it's probably not what you'd be looking for.

Post-installation tasks


In order to connect a MySQL database, you will need the MySQL jdbc driver, Connector/J. You can download it here.

Download the archive and unpack it at a location you feel is convenient. We then have to tell the SQL Developer tool about it too. To do that, navigate to the "Preferences..." item in the "Tools" menu.

sql-developer-preferences

This will open the Preferences dialog. In the treeview on the left side of the dialog, expand the "Databases" node, and activate the "Third Party JDBC Drivers" item beneath it.

sql-developer-jdbc

Use the "Add Entry" button to open a file browser. From there, locate the MySQL Connector/J .jar file:

sql-developer-jdbc-2

This is a one time action too . You won't have to repeat it, unless you upgrade Connector/J. On Linux, I just make a symbolic link to the most recent .jar file, and use instead of the actual .jar file. That way, you never have to worry about upgrades, because you simply re-wire the symbolic link to the newer .jar file. Alas, windows users are not so lucky and have to put up with manually reconfiguring their programs to use the newer driver.

Connecting to MySQL


Now we're ready to actually make a new connection. To do that, make sure you have the "Connections" toolpane activated. By default, it's on the left and active but you can drag and drop the different toolpanes almost everywhere, or you might accidentally have closed it. If you're in doubt, explicitly activate it using the "View" menu:

sql-developer-view

This should pop up and activate the "Connections" toolpane. Use the mouse to right-click the "Connections" node in the treeview on the "Connections" toolpane to create a new connection. A menu pops up. Although you might be tempted to think that you need the "New" item, you really need the "New Connection" item:

sql-developer-new-connection

This pops up a dialog where you can specify the connection details. For MySQL, be sure to select the "MySQL" tab. You will see pretty much all the fields that are required to set up a connection:

sql-developer-new-connection-mysql

The "Connection Name" field is just a local name for the connection for use within SQL Developer. The "Username" and "Password" correspond to the credentials corresponding to the database account. In the "MySQL" Tab, you can specify the hostname and port - all the usual data.

On the bottom of the dialog, there is a "Test" button, which is supposedly there in order to test the connection. It does pop up a small dialog, but immediately, it disappears, so I don't really know what it is telling me. I suspect this is by design, because you can see whether the tes was successfull: after hitting the test button, a status message will appear immediately above the Help button. The message will be shown in red if the test does not succeed:

sql-developer-test-connection

If you want to, you can select a particular database, but you are not required to do so. If you want to, simply hit the "Choose Database" button, and use the combobox to select one of the available databases:

sql-developer-new-connection-choose-database

I find it a bit dissapointing that there seems to be no way to specify custom properties for the connection. There is a whole bunch of extra configuration properties implemented by the MySQL Connector/J, but there is no interface to specify them. Maybe this will be solved in a newer version of SQL Developer.

Using SQL Developer with MySQL


I played a bit with SQL developer, and so far, I think that the MySQL Support is rather limited. The tool lets you browse the database and execute SQL statements, but that's about it.

A few things I would expect to be implemented:

Database selection


In the connection dialog, we had an option to select a particular database. Surprisingly, all databases pop up anyway in the treeview. Of coure, if you don't specify a particular database, all databases pop up too, which I would expect. In that case however, I would expect that selecting a particular database would also USE that database. This is not the case: SQL statements will fail unless all identifiers are properly qualified. On the following screenshot, you might notice that the test database was selected, but under the hood, this does not USE the database.

sql-developer-default-database

Maybe all this can all be explained because of the different semantics between Oracle and MySQL. As you can see, all MySQL Databases or schemata (a synonymous term in MySQL) appear under the connection. But the icon used to adorn them, is that of a little puppet - a user. This is probably because in Oracle, a schema is not really a separate kind of thing - rather, it is the collection of all objects owned by a particular user, and a database is a container for these schemata.

Schema object editor


One of the first things I tried, was to create a new table. It seemed most reasonable to expand the node corresponding to my "test" database, to right-click the "Tables" node. As expected, a menu pops up, but surprisingly, there is no option to create a new table.

sql-developer-new-table

I also tried the marked toolbar buttons as these suggested to be meant to create a new object, but again no luck. I'm pretty sure the functionality is supported for Oracle databases, because the integrated help files do mention dialogs to edit schema objects.

When you try to edit an existing table, a set of tabpages appear that show the structure of the table.

sql-developer-edit-table

However, in that case too, there is no functionality to modify or alter the table design. The status bar does suggest a real edit mode exists, but I can't seem to unlock or activate it. Assuming that it does work for Oracle databases, it's introspection only for MySQL. Creating and editing tables can be done only through DDL.

Working with Data


It seems that working with table data is largely confined to the same limitations as working with schema objects: existing data is displayed, and you can sort it, but that's as far as it goes.

There is no interface to insert new rows in an existing table; no interface to delete rows. You can move your cursor to a field, and click. A blinking cursor appears in the field, and suggests you can type a new value: you can't. Surprisingly, double-clicking on a field in an existing row does pop up an editor dialog - however, it does not let you modify the value. As you can see, the Ok button is disabled.

sql-developer-edit-data

This puzzles me enormously. I mean, I can imagine why the developers waited a little before implementing full blown schema editors: I think it's safe to say that DDL is the part of SQL that has most differences between the various dialects. It's quite understandable too, because creating tables involves a specification of datatypes (which tend to differ between SQL dialects) and all kinds of physical properties, such as storage options.

However, once the schema objects are created, the SQL for working with the data is fairly similar for all SQL dialects alike. Ok, there are of course differences, mainly for the built-in functions, but once you have jdbc access, it should not be hard to provide a generic table data editor that works regardless of the underlying SQL dialect. However, it's not in SQL developer at present - at least, not for MySQL.

No resultset for CALL, EXECUTE and SHOW


SQL Developer does not display the resultset(s) returned by SHOW, EXECUTE and CALL statements. This is particularly annoying for CALL, because it makes it pretty hard to debug stored procedures.

An 'orphan' SELECT statement in a MySQL stored procedure will return a resultset to the calling client. Although some procedures return a resultset as part of their intended operation, I find this feature particularly useful for debugging purposes. With SQL Developer, there is no way to see the data returned by those statements, which means that debugging can only be done by setting up a separate table to log the debugging messages.

SET syntax


I mentioned that it is currently not possible to set all kinds of jdbc driver options when configuring a MySQL connection. Luckily, a lot of the options you'd want to set for the driver can normally be controlled also through the MySQL SET syntax. Alas - not with SQL Developer.

sql-developer-set

The SET keyword happens to identify a command that is implemented by Oracle's SQL*Plus command line query tool. Apparently, there is some logic between the SQL command editor in SQL Developer and the jdbc driver which senses that the SET command is not to be sent to the server, and hence it is skipped.

I can see the usefulness of not letting SQL*Plus commands through to an Oracle server: it makes it possible to use SQL Developer to run legacy SQL*Plus scripts. However, the current implementation blocks a part of the commands in MySQL's SQL dialect. I know how all kinds of people will tell me that you should never use SET commands anyway because they are non-standard and proprietary and all that - fact is that sometimes, you simply need these commands to get some work done. Whatever work that is, you won't get it done with SQL Developer, because it decides to skip these commands.

I think this could easily have been avoided too. The SQL Developer tool can detect which driver it is talking too. That should enable it to block the SET commands from being sent to Oracle servers, and simply allow them to pass through to non-Oracle servers. I know MS SQL Server implements the SET command too - MS SQL Server users will encounter similar problems.

Stored Procedure Parameters


I really don't know how long I can keep this up - my guess is, too long. Therefore, one more to wrap it up.

SQL Developer does not provide a stored procedure editor, wich is rather unsurprising for a tool that does not provide a table editor. However, we can just resign to using DDL statements - a stored procedure editor would not add that much value anyway.

sql-developer-parameters

It would be nice to be able to at least retrieve the stored procedure code using SQL Developer, right? No such luck. When you use the treeview to browse for you procedure, you can retrieve the code. Alas, the parameters are missing from the code. They're just not there:

sql-developer-parameters2

I suspect this is because SQL Developer tries to use the information_schema.PARAMETERS view, which is not implemented in MySQL. Of course, I hope MySQL will eventually implement that, but even then - there is a perfectly good solution to this problem: the SHOW CREATE PROCEDURE command. Of course, this is MySQL proprietary syntax etc, but the point is, if this tool is to support older MySQL releases, it will need to use that method in order to be able to offer the user the possibility to edit stored procedures.

Now what?


Good question. I think it's very encouraging to see Oracle adding connectivity for other database products to their tool. I suspect I would like to use this tool too, provided that it would actually support MySQL Databases at least half as good as it supposedly does so for Oracle Databases.

Frankly, at this point, I wouldn't exactly say SQL Developer supports MySQL. It allows you to connect to it and to issue some queries against it. I can imagine that some people like the ability to use a graphical user interface to browse the database too. But I can't help but conclude that in it's present form, SQL Developer cannot be used for any serious MySQL Application development. The graphical user interface does just not provide enough functionality to perform even the most basic tasks, and even as a Query tool it's lacking too much functionality.

I really hope that what we're seeing now is just the result of a premature release of this functionality. Also, to the best of my knowledge, it's quite uncommon for Oracle to develop tools that even try to support other database products than their own, so I guess we should have a little patience before dismissing this tool alltogether. I will keep an eye on this tool, but my guess it will take at least a year before we'll see it mature enough to be seriously usable.

Is there nothing good to say about SQL Developer in it's present form? Well, so far, it seems to be pretty stable. I found it to be reasonable fast too once it's up and running. As far as it goes, the GUI is pretty intuitive too. But except for the stability, these are all minor pro's compared to the major con's in my opinion. Let's hope it gets better.

41 comments:

Sue said...

Hi Roland,

This is a great entry and well laid out. It's good to read a clear blog. Well done.

I have a few bits of info for you and your readers. The first is that license issue. We are working on an update to that, so that redistribution will not be a problem. We'll no doubt update the OTN Forum with a message as soon as the license agreement is updated. Until then, the issue is as you have stated.

Secondly the MySQL support. SQL Developer is an extensible product. But that I mean that users can build extensions to add functionality important to them. We have customers and teams in Oracle doing this. One of the extensions we are planning is the Oracle Migration Workbench. This tool will help those who want to consolidate databases by moving data and objects from databases, such as MySQL, to Oracle. The connectivity you see in 1.1 allows those companies that have a mix of databases to browse their objects and data in one location.

I hope that helps.

I have passed on your very useful pointers to the team building the 3rd party connectivity.

Regards
Sue Harper
SQL Developer PM - Oracle

Roland Bouman said...

Hi Sue!

Thanks for your encouraging comment, it is much appreciated.

Great to hear the license issues are being worked on - in the end, all will benefit if the limitations on distribution and use are lifted.

Regarding extensibility: From reading (parts of) the integrated documentation, I noticed the focus on extensibility, and I think that in iteself, providing that is great. However, one of the reasons for my somewhat unfavourable final judgement is that I read in http://www.techworld.com/applications/news/index.cfm?newsID=7721&pagtype=samechan (mentioned in the introduction of my article) that people are actually using the MySQL connectivity to actually do their work. That's what triggered me to try for myself, and I just had to conclude that, out of the box, in its present form, there is no way you can use the tool for MySQL application development.

I do understand and respect that it's not Oracle's primary focus to provide a platform for MySQL development, but in the past I have always been able to happily use, say, JDeveloper to develop non-Oracle applications, and I figured I'd give SQL Developer a shot too.

I have a question about the extensibility. I understand one can write custom plugins to add to functionality provided by SQL Developer. Could this mechanism be used to lift the limitations that are now seen with respect to the handling of MySQL resultsets returned by SHOW and CALL statements? Also, would it be possible for the end user to allow the MySQL SET syntax to be passed on to the MySQL server? If possible, would such a plugin be very hard to write, or would it essentially mean writing the SQL Editor component from scratch?

Thanks in advance and kind regards,

Roland Bouman.

PaulR said...

I've found that (with v1.1 / 2364) you can specify the jdk location in sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

This file contains a line
SetJavaHome C:\tools\apps\jdk-1.5.0_06

In the absence of this line you will be prompted for the java location.

Otherwise it will not prompt you.

Thanks for the great entry.

Roland Bouman said...

Hey PaulR,

thanks for the information!

Yeah, new JDKs aren't released that often, but it's good to know where to look for when you want to upgrade, or try an implementation from another vendor. Great, thanks!

Roland Bouman

GornDog said...

I posted in the SQL Developer Forum on Oracle Technical Network regarding my problem using v1.1 against tables that use the InnoDB engine. It appears that query results are not current and instead are a snapshot, as-of the commit you issued before doing the query.

Any suggestions / other feedback appeciated.

GornDog said...

Sorry, that was a PEBKAC (Problem Exists Between User And Chair)

All I needed to do was turn on "auto commit" in SQL Developer.
Tools -> Preferences
Click + next to Database,
Select Worksheet Parameters
Click checkbox to mark "Autocommit in SQL Worksheet"
Close SQL Developer and re-launch.

Problem solved.

HombreMusgo said...

Hi Roland,

Fantastic aid here for me, an oracle "used" for configuring a friendly enviroment for working with a "new" (if ive not seen it for me is new!) tech with a known tool.
You have saved me some hours of configuration ;)
Thanks!
Regards
Pedro

Curious said...

Great entry, I got SQLDeveloper set up in no time flat! Any tool that does one-stop shopping for all my supported databases is good by definition for me. If you don't like this tool for MySQL though, what do you like? Aquadata Studio? other tools?

Roland Bouman said...

Hi Curious,

Thanks for your comment, glad you like the entry.

For development, I use Eclipse with the Data tools project plugin. The advantages are that it supports arbitrary jdbc compliant rdbms-es and that I have the other code and language support that is part of the project nearby.

Colin said...

In the screen capture illustration in your description of 'Database Selection', in the right hand side of the bar above the "Enter SQL statement" there is a dropdown which is only partly visible, obscured by the "Snippets" tab.
In there you must select the database (or schema, if the database has several schemas) to run your SQL.
I only use Oracle, and had the same problem initially - that drop-down is not at all obvious.
You should then be able to create tables - I use it all the time with Oracle.
We are not the only ones not seeing the drop-down: see http://htmldb.oracle.com/pls/otn/f?p=42626:54:5569220507753012::::P54_ID:261

Roland Bouman said...

Colin,

thanks for this tip!

Jason said...

thanks, very good instructions

BJ said...

Roland -

This is great information! Thanks.

I'm having a problem migrating a MySQL database to an Oracle database. I can make the connection the MySQL database and query the data, but I can't do the migration.

When I right-click on the MySQL schema, "Capture Schema" is greyed out. Same problem with clicking on an individual table. Also, the menu item "Migration --> Migrate" is greyed out.

Can you help?

Thanks -

BJ

Roland Bouman said...

Hi BJ,

"I'm having a problem migrating a MySQL database to an Oracle database. I can make the connection the MySQL database and query the data, but I can't do the migration."

I am sorry - I don't know. Did you check the Oracle forums?

kind regards,

Roland

Venu said...

Roland,

Thanks a lot!

Great job done..keep doing!

Thanks,
~Venu.

Anonymous said...

Roland,

Thanks for the information saved me lots of time trying to find things online or posting to forums to only get answers like "did you search on google?"

Nate

I Wayan Saryada said...

Hi Roland,

Thanks for the post. I was just looking around where can I configure the JDBC driver in the tool. I was missed to look in the preferences menu till I found this post. Yes, this tool is great!

bbottlejo said...

Thanks, Roland.
I really appreciate you. I found that I'm looking for it.

Anonymous said...

Do you know if it possible to connect Mysql that is embedded in WAMP with SQLdeveloper????

Roland Bouman said...

Hi Anonymous,

I am pretty sure you can. You should lookup in thee WAMP documentation what your server properties are, or use their forums to find out.

I hope that helps,

kind regards,

WNBraulio said...

Tks!

Alao said...

I love SQLDevloper for oracle way better than Toad. I use it for work and it makes my job a lot easier. However I find it basically useless for MySql. I was surprised that many of the features that makes it so appealing for oracle database management are basically non-existent for MySql. It's better to stick with MyPhpAdmin even though i really don't like that program either. But the ability to edit records and add new ones which is the most important to any developer.

Roland Bouman said...

Hi Alao,

yeah, I agree it's a pretty useful tool for Oracle. I think it shouldn't be too hard to create a java tool that works largely db agnostic, but I don't think that's the point of Oracle SQL developer. At the time, the MySQL capabilities of the tool were mentioned in the press as a kind of novelty but Like Sue already mentioned in the first few comments, MySQL support is mostly there to allow for easier migration from MySQL to Oracle.

regards,

Roland Bouman

Anders Grusell said...

Thanks Roland, this post really saved me a lot of time!
I also like the sqldeveloper very much, it's really one of Oracle's better products in my opinion.

Felipe Carvalho said...

really helpful, thanks a whole lot!!

Anonymous said...

Great post! Only wish I read this before I installed sqldeveloper. not sure how it is good for mySql dev. guess it's back to mssql.....

Khadar said...

Thanks Roland. Really helped in setting up the MySQL with SQLDeveloper

Anonymous said...

I'v found here very interesting articles, please keep us posting

Anonymous said...

I spent couple of minutes at your website and i'm very impressed, very good quality content

Anonymous said...

Ha, this mirrored my experience with almost exactly. Type the path to java.exe, heh, and I got exactly the same error message when I pointed it at a JRE. You'd think one Oracle program could find another Oracle program when it's sitting right there in my $PATH. :-)

Derek said...

I may be a few years too late, but I came across this entry and found it extremely helpful. It had all the information I needed to connect Oracle SQL Developer to MySQL. Thank you for all the great detail!

Hanna said...

Thanks! Exactly what I needed to know

Anonymous said...

Thank you. This post is awesome!
People like you, who share knowledge, make others' lives much, much easier.
I really appreciate it.

Anonymous said...

thank you so much! this was very helpful :)

Anonymous said...

Hi Roland,

I have stumbled upon your blog while trying to enable "insert row" and "delete row" buttons for MySQL in SQL Developer ("plus" and "X" buttons on data editor). Now I see that these features are not included at all in SQL Developer when dealing with MySQL database, even in 2013., six years after your post. Sadly. Thank you for great article!

Satish Raja said...

Hi Roland,

Thank you so much for the blog. It was very helpful...

Anonymous said...

Hi Roland,

Thanks dear this is perfect document Migration data from Mysql to Oracle Database.

Anonymous said...

Roland, great job, very helpful blog. Well done

Anonymous said...

does it support any jdbc driver?
anyhow, thanks for the help

-srihari konakanchi

Roland Bouman said...

srihari konakanchi,

I dunno. This post is ancient. You better download the current version of this product and try for yourself.

Let us know if it worked!

Farheen Master said...

Thank you. This was very helpful.