Thoughts On Unix ODBC Implementations?

scenic asks: "I was wondering what people thought about the available ODBC implementations that are available for Unix. I've been looking at ways to simplify database access in my applications on Linux and Solaris without having to use API's which are specific to a particular DB (or platform, ideally). Does anyone have suggestions or comments on the ODBC software that is available for Linux or any Unix flavor? What about advantages and disadvantages of specific ODBC packages?"

Comments

How about using JDBC in Java?

[SuperKendall]

If you want a really great portable solution, you could use JDBC in Java - the great thing is that you can get JDBC drivers for almost any DB you'd care to name, and the drivers are almost all type 4 (pure Java) drivers. That means you don't have to have any other client software installed on the target machine for it to work! It's been really handy for Oracle work when you don't want to bother setting up an Oracle client and SQL*Net. And because the drivers are all Java code you don't have to worry about a particular DB supporting your chosen platform as a client.

You could even use this solution within a C or C++ application, you can use JNI to make calls into the Java code and get results back.

Port to Perl

[Matts]

I'm not trolling, this really is a good option for any database app. Write an application that carefully uses DBI, doesn't make too many assumptions about the file system, and use DBIx::AnyDBD, and you've got an application that's not only portable between Unix, Win32 and probably MacOS, but also portable across different databases with a few trivial changes (provided you use DBIx::AnyDBD).

Not only that, but the Perl DBI drivers are written mostly in C, and interface directly to the underlying driver, rather than go through another layer like ODBC (unless of course you use DBD::ODBC or DBD::ADO). This means that any database bound app (one that is limited in speed by the underlying database) is going to run at a very similar speed to your C application.

Its just a thought. Try it, you might like it.

Re:Port to Perl

[PigleT]

I beg to disagree. How much bloat do you think there is *in the perl interpreter* itself between
my $db = new dbd.odbc->("", "", "");
(or whatever the method is, I can never remember these things), and the relevant call to SQLConnect() in ODBC?
I suggest that the extra layer of ODBC API pales into insignificance compared to that bloat.

To take an example: write a program with a subroutine that is empty apart one for() loop counting 0->a big number. Do this in both C and perl. The C one wins hands down by a factor of about 10.

Perl really is not the language of choice for speed freaks. If you're controlling and optimizing, of course, then you're aware that most performance is required when you're pulling back a large resultset, rather than the stop/start of individual queries. And I'd still bet that the differences in performance in the middle of a 'select * from ABigTable;' will be best in C with native API, followed closely by ODBC, followed a mile behind by perl.
Of course, it's at that point you ask *why* you want to retrieve zillions of rows - most probably not for a web-DB application, at least.
~Tim
--
.|` Clouds cross the black moonlight,

OpenLink

[matthewg]

My company uses OpenLink multi-tier ODBC drivers and PHP to talk to Microsoft SQL Server from Apache/Linux. It seems to work well, although we haven't had a chance to use it on any production websites yet, and my experiences with OpenLink's support department have been good.