macOS OS X and database connectivity

[AlmostThere]

<To mods : sorry for the earlier foul up, thanks for swift action>

What I wanted to ask is about the current state of database connectivity under OS X. In essence, I want a desktop app that will let me connect to and extract largish data sets from assorted (networked) databases. MySQL and DB2 are essential, others currently desirable. Note, that this is specifically not a general SQL program but one for analytical processing based on the data stored in pre-existing databases.

The obvious (only?) choice is to use JDBC but other options include ODBC-JDBC wrappers and wrappers around those (which also have licensing costs starting at about $99)

In addition, I would love to be able to use some of Apple's modelling tools (e.g. as in Core Data) and built-in storage functionality.

However, it seems to me that Core Data is not really a data centric tool but desktop app centric (i.e. your model is subservient to the app design, rather than building your app around your existing data model) and is not really designed around 'proper' databases.

I have downloaded Cayenne, and am looking at that BUT am hesitant to start anything on OS X that is centred around Java, especially from a desktop perspective, given the indicators of deprecating Cocoa-binding support and now WebObjects tools.

Do I have any other options? And what would people suggest is the best route to follow?

[Edit : currently I am prototyping my algorithms around some hand-written SQL in python with MySQL but I would like to move towards some better modelling tools]
[Edit : previously I was be working on Linux - which may still be the better choice - where DB2 support in particular was less of an issue. I am wondering if OS X is a practical development platform for what I want to do]

 

[jeremy.king]

You need not worry about applications written in Java (Swing, AWT, or SWT) - you only need to worry about Cocoa applications written in Java.

That said, you have many options but a couple came to mind.

http://www.minq.se/products/dbvis/ (ROCKS, in my opinion)
http://squirrel-sql.sourceforge.net/

 

[AlmostThere]

Ah, yes, good point about the AWT etc.

I have always quite liked Apple's tools but am not clear which, if any, integrate with these Java toolkits.

My java is a little rusty but it seems to be the way to go for DB connectivity on the Mac.

I had a quick look at the apps mentioned, but they look more like Database managers and SQL editors, unless I am mistaken ...? The programming side of stuff for me is more akin to the functionality offered by SAS, SPSS and maybe some data-mining stuff.

My main desire is to do as little database interface coding as possible, so I can concentrate on the algorithms.

 

[jeremy.king]

My main desire is to do as little database interface coding as possible, so I can concentrate on the algorithms.

I guess I'm wasn't clear on your needs - I think I understand now...I think. For clarity, you are writing a desktop application and you wish for it to talk to databases without having to write JDBC code? If so,

Hibernate
iBatis
OJB
Castor
Cayenne

all come to mind.

Now I understand what you meant about avoiding Java-Cocoa binding support. Have you considered writing the entire app in Java?

 

[AlmostThere]

It's kind of looking like Java would be a reasonable choice - but really right tool for the right job and (re-)learning a language has pretty low overhead.

I have a fairly large body of analytical functions in a different languages already - converting these - rewriting or possibly binding - again is not that much of an issue as most of the development time is really in the design, maybe 10:1 split (the code is often pretty short, several hundred lines or so). IIRC, Java has some pretty good scientific libraries already.

I have had a number of brushes with GUI programming over the years (WIN32, MFC, GTK, a little Cocoa) and while distribution is really a goal, I know it makes a big difference when it comes to being able to test different processing pipelines and produce some visual output, especially if sharing between 3 or 4 colleagues. A quick alternative I have often used is stringing half a dozen shell scripts together and dumping some output although this often ends up a maintenance nightmare. Plus the boss likes a simple shiny GUI tool for demos and stuff

But the overhead (i.e. the bit I know the least about) right now is in interfacing and connecting with new datasets, this time stored in assorted databases, designed by a DBA and building objects around these. While certainly invaluable, database I/O is not something I really want to spend much time mucking about with. From a general interest in development, Apple appear to have some nice ORM type GUI tools e.g. Core Data stuff and EOModeller, which really seem to lift much of this overhead but are either Web or application centric, rather than database centric neither of which really seem to be quite general enough. Not to mention, of course, that they appear fairly straightforward to express relationships and objects.

Any, databases are pretty ubiquitous nowadays and I can't imagine what I want to do hasn't been done quite a few times before so I was wondering, and specifically from an OS X perspective, what people's experience was.

I have Cayenne downloaded and will have a look at that over the next week or so - it does look like one of the slicker tools out there, certainly in terms of interface.

 

[jeremy.king]

I have a fairly large body of analytical functions in a different languages already - converting these - rewriting or possibly binding - again is not that much of an issue as most of the development time is really in the design, maybe 10:1 split (the code is often pretty short, several hundred lines or so). IIRC, Java has some pretty good scientific libraries already.

I hope Tor Norbye isn't reading this, but you could always use JNI instead of rewriting the existing functions.

With respect to your database stuff, I would highly recommend you use Stored procedures especially if there are large complex queries and other transformations involved. The close you can stay to the data layer with the heavy processing, the better the performance.

Good Luck to you!

 

[ChrisA]

[Not quite what you asked for but I just did a DBMS centric application. I wanted a GUI for the user and a DBMS back end. I decided to make it a web based applaction. Most of the code was done in Perl was CGI scripts. Some was compiled C but called from the Perl scripts. The major limitation is HTML forms. Ugly and limiting as HTML forms are users like web based apps and of course it runs on Windows, Solaris, Linux and Mac. Windows and Solaris where hard requirements for me.

Even complex graphics can be done on a web server. Have the CGI scrip build the graphic as a JPG then ship it over to the user as an image. You can do a pretty complex app with a few lines of Perl this way.

Perl has an easy to use database independent interface. So you are not tied to ((say) Oracle or PostgreSQL

 

[AlmostThere]

[Not quite what you asked for but I just did a DBMS centric application. I wanted a GUI for the user and a DBMS back end.

Has also occurred to me - with the obvious benefits in enabling widespread use (well, maybe one day!) ... as has writing procedures in Java / C and calling them directly within the database through a UDF ... or even wondering how much could actually be rewritten directly with PL/SQL, but these do tie me to a single DB again (I think some of the existing routines against which I need to compare results have been done along these lines, many years ago now) ... just too many options out there, sometimes I think life would be just that bit easier with a little less free rein.

Anyway, cheers for the advice, hopefully can get something basic put together in the next 3 or 4 weeks and see if the people upstairs want to carry on from there ...