Very Computer

Hi,

our DB client application currently uses several threads
during startup to load data which is displayed in
different windows. Currently each thread uses its
own connection which adds up to a substantial load for the
server, especially when the number of clients is large.

Therefore I would like to use a single connection which is
shared by the threads (which do selects only, no updates
or inserts). My first tries on this failed since all
threads seem to lock up when I'm initializing my
recordset (the client is MFC based). The statement where
they hang is
AFX_SQL_SYNC(::SQLAllocStmt(m_pDatabase->m_hdbc,
&m_hstmt));
which is somewhere in the MFC source code (constructor of
CRecordset).

The documentation for the ODBC driver I'm using (Oracle
8.1.7.6) says that it is thread safe.

Can this work at all? How are the concurrent queries
executed? Do I have to expect a performance hit for
some internal thread synchronisation?

Thanks and best regards
Uwe

Uwe,

Theoritically speaking, this should work as far as ODBC is concerned. You
should be able to use as many hdbc's and hstmt's as you want in as many
threads as you want but you need to keep in mind the following basic rules:
1. Use no more than one hstmt per hdbc. 2. Keep each hdbc/hstmt pair in the
same thread.

I think there was a known issue with Oracle's Trace Collection Services
that may have been causing similar problems; Oracle should have a patch for
this bug.  So, I recommend contacting Oracle and provide them with your
Oracle client version and the behavior you're running into. They may have a
patch that solves the problem for you.

Hope this helps...

Ammar Abuthuraya
Microsoft Developer Support

This posting is provided as is without any warranttee and confers no rights

On Fri, 14 Jun 2002 05:51:22 -0700, "Uwe Nassal"

I believe the MFC ODBC database classes aren't thread-safe. But you
can share an ODBC connection by simply using the same connection
handle, AFAIK.

Maybe you can change a setting in Oracle to manage more concurrent
connections? Or is it a licensing thing?

Bob Hairgrove

But surely this is a MFC limitation, not an ODBC limitation?

What you are advising him to do is to continue doing what he was doing
... creating one thread per connection. He wants to share the
connection among different statements, and according to the ODBC
documentation, this is perfectly OK.

Quote:

>I think there was a known issue with Oracle's Trace Collection Services
>that may have been causing similar problems; Oracle should have a patch for
>this bug.  So, I recommend contacting Oracle and provide them with your
>Oracle client version and the behavior you're running into. They may have a
>patch that solves the problem for you.

>Hope this helps...

>Ammar Abuthuraya
>Microsoft Developer Support

>This posting is provided as is without any warranttee and confers no rights

Bob Hairgrove