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?"

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.

Re:Port to Perl

[Thu Anon Coward]

but aren't you still stuck with the database files in proprietary format? have you tried looking at Pervasive.SQL 2000 for Linux? PSQL is the old Btrieve renamed. the Btrieve data format is a flat binary file that can be ported anywhere with no changes. want to change operating systems? fine, install the new o/s, install the relevant version of PSQL for the o/s, then just copy the datafile over! slick!!
as for presenting data to the web, you can use the Tango product for Linux, it does ODBC and has the PSQL data engine buit in to it, your choice of either CGI or Apache plug-in.

Re:Port to Perl

[bueller]

Maybe you should have a yarn to some of the folks at Telstra looking after their customer datawarehouse - they are using perl to access their Oracle database.

I've had a brief look at one of their scripts - it was the first time I've seen the parallel option used in a query and also the first time I'd seen a subquery as part of the from statement.

Sure, they probably could have used Pro*C, but I doubt the execution time would be much different - the slowness is in the retrieval from the database and transmission across the network. The power of string manipulation in perl makes the coding so much quicker and easier.

I've used perl accessing Sybase to populate tables from log files and my bottleneck was the fact I was doing row at a time inserts. It would have run just as slowly if I'd used E*SQL.

If I was choosing a database interface based on platform independence I'd look to java and then perl or PHP. Java has the added bonus of being able to run within the database for some databases (no more PL/SQL?).

For a web based app the startup time of your script is likely to be the slow point. Plus if you are using a web hosting service you might not be able to upload a compiled C program (you are probably also forced to use mySQL, and would therefore use PHP.

At the end of the day its all horses for courses.

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.

OpenLink

[matthewg]

My company [gardencitygroup.com] uses OpenLink [openlinksw.com] multi-tier ODBC drivers and PHP [php.net] 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.

unixODBC

[numo]

We are using unixODBC [genix.net] at the company. We are using ODBC mainly to be able to have single source under Win (native ODBC, several possible databases) and Linux (PostgreSQL only at the moment), so I cannot speak on cooperating with other databases, but the Web page mentions some solutions.

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,