We use ODBC to connect to Oracle Rdb (Rdb = former DEC product acquired by Oracle during DEC's death-throes) databases running on DEC/Compaq/HP Alpha servers running VMS. We typically have hundreds of concurrent database connections without appreciable problems. My experience has been that ODBC is no better or worse than other database call-level interfaces, and does supply some level of vendor independence. Given the nature of ODBC (it's a call-level interface where the vendor-supplied ODBC driver translates ODBC calls to "native" calls) I'd hazard a guess that any concurrency problems you might encounter are likely a problem with the underlying vendor-supplied software.

Re non-blocking calls - you can do that in ODBC too. There is a statement option called SQL_ASYNC_ENABLE that is normally set to SQL_ASYNC_ENABLE_OFF - if you set this option to SQL_ASYNC_ENABLE_ON you can now execute that statement asynchronously, but having done so you can't execute anything else against either that statement or the database associated with the asynchronous statement (with a few exceptions) until the original async call completes. (Note that you can call functions against other statements). I've never messed with this as I've never had the need to do so, but it's there to try if you feel the urge.

Bob Jarvis Compuware @ Timken

-----Original Message----- From: squeak-dev-bounces@lists.squeakfoundation.org [mailto:squeak-dev-bounces@lists.squeakfoundation.org]On Behalf Of Avi Bryant Sent: Monday, November 29, 2004 5:11 PM To: john@pierce.name Cc: The general-purpose Squeak developers list Subject: Re: Connecting to Oracle (was: ODBC connection)

On Mon, 29 Nov 2004 16:49:07 -0500, John Pierce john.raymond.pierce@gmail.com wrote:

...

Hi Avi,

We do a lot of ODBC FFI work in Squeak with great success,

but I must

...

confess that we have very limited concurrency requirements (e.g. 5 simulatenous users). In your opinion, where does the ODBC

FFI plugin

...

begin to *not* work well as we have not experienced any issues from the blocking behavior of FFI?

It's good to hear it's working well for you. I've never used ODBC in production, so I'm speaking with less experience and less authority than you are. Where I would *expect* it to be a problem is when you have single queries that take a significant amount of time, rather than a large number of fast ones. I'd think that this could lead, for example, to HTTP connections being refused if someone tries to access your application at the same time as someone else is in the middle of a lengthy search or complex report. But again, this isn't something I've actually experienced, so maybe it's less of a problem in practice than it seems in theory. That would be nice; it's so often the other way around...

...

Also, what do you think it would take to implement a native ODBC interface. Would native interfaces require implementing native drivers for each vendor's database that one was targeting?

I think I

...

know the answer to this question, but just checking.

Well, I thought the whole point of ODBC was that you don't have to deal with individual vendors' APIs. If the ODBC C library has a non-blocking interface (which it must, surely), then you could write a plugin that used that instead of the blocking FFI interface we currently have and the problem would go away.

...

For the record, we use Microsoft SQL Server and the ODBC

FFI interface

...

with essentially no foul side effects of the blocking

problem of using

...

FFI -- so I am a bit baffeled by folks hestitancy in using the ODBC FFI library.

Out of curiosity, what happens if you simulate higher loads (10 or 20 or 50 concurrent users) with your app? It would be good to get a real world sense of where the issue kicks in, so people can make better informed choices (like me, who until I read this message had more or less written ODBC off for use in web apps).

Avi

----------------------------------------- ********************************************************************** PLEASE NOTE: The above email address has recently changed from a previous naming standard -- if this does not match your records, please update them to use this new name in future email addressed to this individual. This message and any attachments are intended for the individual or entity named above. If you are not the intended recipient, please do not forward, copy, print, use or disclose this communication to others; also please notify the sender by replying to this message, and then delete it from your system. The Timken Company **********************************************************************