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:
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:
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.
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.
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.
Use the "Add Entry" button to open a file browser. From there, locate the MySQL Connector/J
.jar file:
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:
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:
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:
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:
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:
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.
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.
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.
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.
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.
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.
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:
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.
