Sharing ODBC Connection
2 messages in this thread
We urgently need some help!!!!
I am the European Vice-President for Research of Computer Sciences Corp. (as
you may know, the second largest systems integrator in the world).
We have been developing a system for one of our clients in France,
client-server based, Oracle DB on the server, WFW3.11 on the workstations. This
is a large development contract, worth several million dollars.
The system is written in Visual C++, is completely object oriented and uses the
Foundation Classes extensively.
We had recommended to the client that the access to the databases should be via
ODBC in order to increase flexibility.
The system is made up of several programs, each of which can be active at the
same time, and hits the same databases.
The problem we have (and believe me we have tried almost everything):
each of the programs sets up its own ODBC connection to the database, and after
a couple of workstations have started, Oracle runs out of authorized user
connections (the system is set up for 30 users).
We have explained the problem to Oracle, and they are willing to allow for more
connections at a discounted price, but our client does not want to pay the
increased number of connections.
Moreover, every time Oracle opens up an ODBC connection, it requires an extra
megabyte of memory on the server, and this also costs money and runs the risk
of hitting the max memory on the chosen server (Pentium with 128MB).
We need to find a way to share ONE ODBC connection amongst several programs,
but:
1 – The database class has some static variables, and the object cannot be in a
DLL, so the ODBC connection cannot be shared in this way
2 – We have tried to allocate the connection in a DLL and pass back to each of
the programs the handles to the connection, overriding the Database Open
method, but this does not seem to work either.
We are getting NO technical support from Microsoft France, except for a
suggestion to put the problem on Compuserve.
Can you help? If necessary we will fly to Redmond to meet with yourselves in
order to show the problem and the attempted solutions!!!
Paul G Mariotti
Vice-President for Advanced Technologies
Computer Sciences Corporation, Europe
100064,3331
Hi Paul,
<<We need to find a way to share ONE ODBC connection amongst several
programs, but:
1 – The database class has some static variables, and the object cannot be in
a DLL, so the ODBC connection cannot be shared in this way
2 – We have tried to allocate the connection in a DLL and pass back to each
of the programs the handles to the connection, overriding the Database Open
method, but this does not seem to work either.
>>
This is a known problem. Here is the text of an upcoming Microsoft
Knowledgebase which discusses the situation:
Program Crash When Connecting to a Data Source from a DLL
———————————————————————-
The information in this article applies to:
– Microsoft Foundation Classes for Windows, version 2.5
———————————————————————-
SYMPTOMS
========
A program may crash if it calls into a DLL which creates and opens a
CDatabase object. This happens when two applications share the
same _USRDLL DLL. It can also happen when two applications are
using the _AFXDLL version of the Microsoft Foundation Classes
database code; MFCD250.DLL or MFCD250D.DLL.
CAUSE
=====
The Microsoft Foundation Classes code uses a global static variable
called henvAllConnections to store the ODBC environment handle (HENV)
which is used by several of the ODBC API functions. The variable is
defined in DBCORE.CPP as:
HENV CDatabase::henvAllConnections = NULL;
This HENV variable is assigned when the first CDatabase object is
created. All subsequent CDatabase objects use this same variable.
This design doesn't work because, in the case of a DLL where a
CDatabase object is created, the handle stored in henvAllConnections
is used for all CDatabase objects created by all applications
which call into the DLL. ODBC allocates the memory associated with
an HENV to the task. Therefore, the memory associated with the
henvAllConnections handle belongs to the first application that called
into the DLL and created the first CDatabase. When the application
goes away so does the memory associated with the HENV. All other
applications that still have CDatabases open that were created in the
DLL will most likely crash.
RESOLUTION
==========
Make sure that only one application is using the DLL or only let one
application at a time call into the DLL and create a CDatabase object.
In the latter case, if an application calls into the DLL to create
a CDatabase it must delete the CDatabase before letting another
application call into the DLL to create another CDatabase object.
Another approach would be to modify the code in DBCORE.CPP such that
it stores and uses an HENV for each task. The Windows API function
GetCurrentTask() can be used to associate a HENV with a specific task
handle. You can modify the code such that everytime a CDatabase is
created it checks to see if a HENV has been allocated and associated
with the current task. If it hasn't it does so. Additional code would
need to be created which would replace all references to
henvAllConnections with a function call to retrieve the correct HENV
for the current task.
Another technique, if it is important to have all database code in
one central location, would be to create an application which will
act as a DDE or OLE server. The server can query the database and
return results to your application.
Status
======
Currently there isn't a fix for this bug. Microsoft will post new
information here as it becomes available.
Steve Roeder
Microsoft Developers Support