Friday, January 06, 2006

Microsoft Visio communicates happily with Apache Derby databases via ODBC

For those who like to cut the bulldust, the key to achieving this can be found in this developerWorks article: ODBC programming using Apache Derby. For those who are interested in my inane asshat ramblings, read on...

I've been working on an application with a database backend of late and one thing I find quite frustrating is the lack of a decent ERD / database reverse engineering diagramming tool.

Of course if I had oodles of cash I could buy something like CA's AllFusion ERwin package and I'm sure I'd be quite happy, however, I want something free or at least at negligible cost. I don't like the Eclipse IDE so I won't use their plugins. Also, I'm not sure why but I've been having difficulty installing anything that uses the install4j installer ever since I upgraded my JVM, so Visual Paradigm's community offerings and similar are not currently an option for me to try.

Some time ago I tried dia, which has some associated SQL related tools but I thought it was a little too much work for too little result. What I really wanted was Microsoft Visio compatibility. Visio is quite happy to talk to the expensive commercial databases from Microsoft, IBM or Oracle so this would be very easy if I were using one of those. However, I'm currently using an open source Java database (at least for development). Some time ago I looked into Visio and Java database interaction and it looked like I needed to buy some expensive looking ODBC-JDBC bridge software for my Visio to JDBC connections so I thought all was lost but I now find that this is not necessarily so.

I generally develop web applications for an Oracle database but I like to use Java databases in development (I admire PostgreSQL and MySQL but feel they are just too much work for development purposes). I like the idea of a Java database as I'm generally happy with Java as a portable technology and have more idea what is happening when exceptions occur etc.. The embedded Java databases are particularly useful as they mean I can transport a database inside my WAR files. Even when I run a Java database in standalone mode via a console window they are easy enough to invoke from Ant scripts.

Previously I have mostly used HSQLDB (formerly Hypersonic), mainly because this is what came with the uPortal quickstart. I have recently been experimenting with another Java database, Apache Derby (which was formerly IBM's Cloudspace and has been donated to the Apache Foundation). The people behind HSQLDB have recently produced a new database called H2 which claims limited ODBC support. This might possibly provide a solution to allow connection with Visio and other Microsoft products. Although I decided against delving too far into H2 as it is so very new that I think I would be better off looking elsewhere for my ODBC compatibility.

After my initial impressions with Apache Derby it now seems a little odd to me that IBM opted to support MySQL, especially since they already have DB2 and Cloudscape/Derby (See also Cloudscape Versus MySQL). Also there is some talk of DB2 becoming a free product so almost everything in the IBM database line could soon be free software (well "free" in some form or other). To be fair Oracle already make almost everything they release free for development purposes.

I must admit I had previously viewed Java databases with some suspicion; they somehow seemed more like toy solutions than other more serious database offerings. My mind is changing in the light of recent experiences. Serious things do happen in the Java database arena, as examples, OpenOffice have chosen HSQLDB as it's core database and Sleepycat produce a Java version of their Berkeley DB.

Since Derby is a direct descendant of IBM's Cloudscape it was once a stable mate of DB2. It turns out that due to its IBM heritage, Apache Derby, like DB2, uses the distributed relation database architecture (DRDA) communication protocol. This apparently means you can use the DB2 Runtime Client software with Apache Derby as a means to provide ODBC compatibility and "hey presto", I can therefore configure my Derby database as an ODBC source (bar a few minor technical difficulties due to my inexperience of DB2).

After a little configuration, Visio will quite happily communicate with my new ODBC source and reverse engineer my database to give me a pretty ERD. Visio obviously has no idea that it is talking to an open source Java database else I'm sure it would rise up and rebel against me!

The other implication is that I expect other Microsoft and Windows products will be equally happy to talk to my Apache Derby Database. Therefore I could create Java/JSP web applications running on Tomcat that could be using the same datasource as Access or Excel. Okay, I can already do that using a commercial database (like Oracle or SQL Server) but the difference with this approach is that it could be done by almost any Java propeller head rather than just by the more DBA minded.


Mark McLaren said...

"After a little configuration, Visio will quite happily communicate with my new ODBC source and reverse engineer my database to give me a pretty ERD."

wow, that's great!
Note: Comment imported. Original by Phil Wilson website: at 2006-03-07 16:31

Mark McLaren said...

I am unable to configure an existing Apache Derby (currently at C:\isd-derby\trans1) file database as an ODBC source.

I would appreciate if you can provide any inputs.

Note: Comment imported. Original by Anonymous at 2006-11-28 20:39

Mark McLaren said...

This post was really useful, thanks!
Note: Comment imported. Original by Phil Sumner at 2007-12-13 16:21

Mark McLaren said...

Hi Mark,

I dont think it is possible but still strange things happens sometime. Is it possible to make Derby an ODBC source even if it is running in the embedded mode? I need such an arrangement in my Java based desktop application where some windows APIs are to be used and access to data repository is required.

If not derby, then can you suggest any other db which can be made accesible via JDBC and ODBC to my desktop application?


Note: Comment imported. Original by Nitin Gupta at 2008-06-30 08:09

Mark McLaren said...

Hi Nitin,

I'm not an expert on this but I'll try and give an answer. With Derby there are various kinds of "embedded" modes, one which might allow embedded alongside ODBC connections is the "Embedded Server" mode.

As for alternative databases, I have read that H2 works with PostgreSQL ODBC driver, however, this is still experimental.

Note: Comment imported. Original by markmc website: at 2008-06-30 21:38