Re: ODBC & Multithreading ...

K. Raghuraman wrote in message <362EEE8B.A975DA0D_at_india.hp.com>...

>I have Oracle 7.3 DB with its ODBC driver (supplied by Oracle Corp.
>version 2.00.0301). My application is written using VC++ 4.2
<snipped>

>As long as I have only one thread, everything is fine. But when I try
>to have more than one thread, the application behaves erratically and it
>shuts down sometimes.

What is the actual error message?

>Can anyone tell me about multithreading with
>ODBC to connect to Oracle 7.3 in general and what could be the reason
>for my multithreading to fail ?

A couple of reasons why multithreading can fail. Memory for example. If you open a new connection in a new thread it requires memory in the ODBC driver/dll, which in turns make a new connection via SQL*Net which also requires memory for that connection. Syncronization is another problem. You can not have multiple threads accessing the same device context objects at the same time - you need to sync access to make sure the call goes thru one at a time. It all depends on what you're trying to do in the thread I guess.

I have had a very similar problem with Oracle ODBC. Had a multithreaded application that opened a connection per thread. It's behaviour was so unpredictable and erratic I had to resorted to using native connectivity instead.

>Is it a problem with the ODBC driver
>manager as the Oracle driver documentation says that it supports
>multithreading ?

The ODBC driver manager only handles front-end access to an ODBC driver - listing the ODBC aliases, enabling the user to add, delete and update aliases. The manager is not involved when you're talking to a database via an ODBC driver AFAIK.

Another thing to remember is whether or not the driver makes actual multithreaded connections to the database.

From a Delphi client/server perspective: Many database interface implementations have something like a Session handle, Database handle and SQL/Table handle. If you create a SQL handle and run a SQL statement in a thread, that often blocks both the underlying Database and Session handles as both the Database and Session are synchronous implementations. So even if you multithread, the actual SQL statements are queued one after the other for execution. The solution is to create a separate unique Session and Database handle per thread.

Sorry that I can not be of much help. Think the bottomline is that you need to look and see exactly what the thread is trying to do and whether or not that is "valid" within the context of running multiple copies of that thread.

regards,
Billy Received on Thu Oct 22 1998 - 07:00:36 CDT