free all handles when disconnecting

[a0hirz]

Hello!

I have a problem using dbexpoda.dll Version 4.20.0.8. The problem also remains with current trial version 4.70.0.22.

I' am using Borland C++ Builder Version 6 and connect (for the test) to an "Oracle9i Release 9.2.0.8.0 - Production" database.

The database driver is used by a report generation application. It can be used as system service or as desktop application. (each running on Windows XP Professional, 32bit)

When I run it as system service I experience the problem, when I run it as desktop I DON'T experience the problem.

The problem origination:
The application often reconnects. (adjustable, but typically in a 5 seconds interval)
Thererfore each time I close the connection and then open a new connection (Tests have shown, that whether I reuse the old TSQLConnection object or not, does not matter.).

This behaviour is desired since the application design supports only a single database connection.
(It does not make sense to reconnect to the same database. But typically there are 3 database users (connect to USER1, close USER1, connect to USER2, close USER2, connect to USER3, close USER3, wait 5 seconds, connect to USER1, and so on).)

The resulting problem:
Every time a new connection is opened, (Connected=true), 11 handles are reserved.
Every time a connection is closed (Close()), 9 handles are freed.
>>>> The is a leak of 2 handles every time I reconnect.
#include
#include
#include
//---------------------------------------------------------------------------
// DBExpress:
#include
#include
#include
// SQLClientDataset
#include
#include
#include
#include
#include
//---------------------------------------------------------------------------
//---------------------------------------------------------------------------
class TForm1 : public TForm
{
__published: // Von der IDE verwaltete Komponenten
void __fastcall FormActivate(TObject *Sender);
private: // Anwender-Deklarationen
TSQLConnection* myDatabase;
public: // Anwender-Deklarationen
__fastcall TForm1(TComponent* Owner);
void __fastcall TimerEvent( TObject* Sender );
};
//---------------------------------------------------------------------------
extern PACKAGE TForm1 *Form1;
//---------------------------------------------------------------------------
#endif

THE C PLUS PLUS FILE
-------------------------------------------------------------------------------------

//---------------------------------------------------------------------------

#include
#pragma hdrstop

#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::FormActivate(TObject *Sender)
{
// I know the timer and the lastly opened connection should be disposed when the application is closed, but it is not relevant for this example.
TTimer* timer = new TTimer( NULL );
this->myDatabase = NULL;
timer->OnTimer = TimerEvent;
timer->Interval = 5000;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::TimerEvent( TObject* Sender )
{
// (plus 11 handles minus 9 handles) is plus 2 handles for each timer event

// disconnect
if( this->myDatabase != NULL )
{
// following line: setting Connected to false does not help
//myDatabase->Connected = false;
// following line: closing frees 9 handles
myDatabase->Close();
// following two lines: disposing the connection does not help
//delete myDatabase;
//this->myDatabase = NULL;
}

// connect
if( myDatabase == NULL )
{
myDatabase = new TSQLConnection( NULL );
myDatabase->LoginPrompt = false;
}

try
{
String inAlias = "DATAOBJECTS_ORC1";
String inUserName = "USER1";
String inPassWord = "USER1";

String driverName, vendorLib, getDriverFunc, libraryName, dataBase;

driverName = "Oracle (Core Lab)";
vendorLib = "oci.dll";
libraryName = "dbexpoda.dll";
getDriverFunc = "getSQLDriverORA";
dataBase = inAlias;

myDatabase->DriverName = driverName;
myDatabase->VendorLib = vendorLib;

myDatabase->LibraryName = libraryName;
myDatabase->GetDriverFunc = getDriverFunc;

myDatabase->Params->Clear();
myDatabase->Params->Add( "Database=" + dataBase );
myDatabase->Params->Add( "User_Name=" + inUserName );
myDatabase->Params->Add( "Password=" + inPassWord );
// following line: connecting requires 11 handles
myDatabase->Connected = true;
}
catch(Exception &e)
{
int debug2 = 0;
debug2++;
}
int debug3 = 0;
debug3++;
}
//---------------------------------------------------------------------------


Any help or hint is appreciated. Am I doing anything wrong? (Possibly it is not an issue of the devart driver, since I have experienced the same when using the Borland (dbexpora.dll) driver.)

[a0hirz]

Hello!

Now I have found a workaround, but it is not sufficient.

If parallel to the reconnecting with dbExpress an ODBC connection is open, the handle leaks do not occur.

In some scenarios I always have an ODBC connection open parallel to the reconnecting, but on some computers there is no ODBC connection available.

(Typically the ODBC connection is used to manage access rights for specific users. The dbExpress connection delivers data for the reports. But on some computers there is no database user for managing user access rights (ODBC) available.)
(The ODBC connection points again to a database with version "Oracle9i Release 9.2.0.8.0 - Production".)
(The usage of an ODBC connection is the reason, why I never experienced the problem with the report generation desktop application. The report generation system service (has the handle leak problem) never uses an ODBC connection.)

With the following example I want to encourage you to find ideas, why the problem occurs. But establishing an ODBC connection is absolutely no solution for my problem.
I want to fix the problem, when I reconnect dbExpress connections (without any ODBC connection).
But without ODBC connection I experience handle leaks for each reconnect.

The example is the based on the example of the previous post.
The example has a ConnectODBCButton and a DisconnectODBCButton button.
Initially the demo application has no ODBC connection. Therefore the number of handles continuously increases.
When you press the ConnectODBCButton, an ODBC connection is established, and the increasing of the number of handles stops. (The connection must be successfully established. You must apply valid connection information in the source code and in the ODBC settings.)
When you press the DisconnectODBCButton, the ODBC conneciton is closed, and the increasing of the number of handles starts again.

THE HEADER FILE
-------------------------------------------------------------------------------------

//---------------------------------------------------------------------------

#ifndef Unit1H
#define Unit1H
//---------------------------------------------------------------------------
#include
#include
#include
#include
//---------------------------------------------------------------------------
// DBExpress:
#include
#include
#include
// SQLClientDataset
#include
#include
#include
#include
#include
//---------------------------------------------------------------------------
//---------------------------------------------------------------------------
class TForm1 : public TForm
{
__published: // Von der IDE verwaltete Komponenten
TButton *ConnectODBCButton;
TButton *DisconnectODBCButton;
void __fastcall FormActivate(TObject *Sender);
void __fastcall ConnectODBCButtonClick(TObject *Sender);
void __fastcall DisconnectODBCButtonClick(TObject *Sender);
private: // Anwender-Deklarationen
TSQLConnection* myDatabase;
public: // Anwender-Deklarationen
__fastcall TForm1(TComponent* Owner);
void __fastcall TimerEvent( TObject* Sender );
};
//---------------------------------------------------------------------------
extern PACKAGE TForm1 *Form1;
//---------------------------------------------------------------------------
#endif

THE C PLUS PLUS FILE
-------------------------------------------------------------------------------------

//---------------------------------------------------------------------------

#include
#pragma hdrstop

#include "Unit1.h"
#include "sql.h"

#define IDENTLEN 30

//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;

HENV henv=SQL_NULL_HENV;
HDBC hdbc=NULL;

//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::FormActivate(TObject *Sender)
{
TTimer* timer = new TTimer( NULL );
this->myDatabase = NULL;
timer->OnTimer = TimerEvent;
timer->Interval = 500;
}
//---------------------------------------------------------------------------
void __fastcall TForm1::TimerEvent( TObject* Sender )
{
// disconnect
if( this->myDatabase != NULL )
{
myDatabase->Close();
}

// connect
if( myDatabase == NULL )
{
myDatabase = new TSQLConnection( NULL );
myDatabase->LoginPrompt = false;
}

try
{
String inAlias = "DATAOBJECTS_ORC1";
String inUserName = "USER1";
String inPassWord = "USER1";

String driverName, vendorLib, getDriverFunc, libraryName, dataBase;

driverName = "Oracle (Core Lab)";
vendorLib = "oci.dll";
libraryName = "dbexpoda.dll";
getDriverFunc = "getSQLDriverORA";
dataBase = inAlias;

myDatabase->DriverName = driverName;
myDatabase->VendorLib = vendorLib;

myDatabase->LibraryName = libraryName;
myDatabase->GetDriverFunc = getDriverFunc;

myDatabase->Params->Clear();
myDatabase->Params->Add( "Database=" + dataBase );
myDatabase->Params->Add( "User_Name=" + inUserName );
myDatabase->Params->Add( "Password=" + inPassWord );
myDatabase->Connected = true;
}
catch(Exception &e)
{
int debug2 = 0;
debug2++;
}
int debug3 = 0;
debug3++;
}
//---------------------------------------------------------------------------


void __fastcall TForm1::ConnectODBCButtonClick(TObject *Sender)
{
char cUsername[IDENTLEN+1] = "USER2";
char cPassword[256] = "USER2";
char cDatasource[IDENTLEN+1] = "GLOBALMANAGERORA";

RETCODE rc;

rc=SQLAllocEnv(&henv);
SQLAllocConnect(henv, &hdbc);
rc=SQLConnect(hdbc, (UCHAR *)cDatasource, SQL_NTS, (UCHAR *)cUsername, SQL_NTS, (UCHAR *)cPassword, SQL_NTS);
}
//---------------------------------------------------------------------------

void __fastcall TForm1::DisconnectODBCButtonClick(TObject *Sender)
{
if( hdbc != NULL )
{
SQLDisconnect(hdbc);
SQLFreeConnect(hdbc);
hdbc = NULL;
}
SQLFreeEnv(henv);
henv = SQL_NULL_HENV;
}
//---------------------------------------------------------------------------

(Information related to the ODBC API: http://msdn.microsoft.com/en-us/library/ms714177.aspx)

Any help or hint is again appreciated.

Is this a very common problem?
Have you (or in general any devart customers) ever recognized such a handle leak problem?

[AlexP]

Hello,

I have created the application, added your sample code and launched it.
I have checked the number of application handles with ProcessExplorer and after each connection close the 11 handles were freed permanently. I set the sleep procedure before opening and closing connection (to make the process obvious).

[a0hirz]

Thanks a lot for your try to reproduce the handle leak.

Could you please post your (slightly different) source code in order to be able to verify your statement on my computer.

Could you also please send me all your dlls and libraries(, which were required, if you would install the application for the end user.). That are all loaded borland dlls/bpls/... (dbexpress, rtl, vcl, ... - I don't know exactly.) and oracle dlls/bpls/... (devart dll, but the oracle client dlls might not be relevant). Please use on demand depency walker (or another tool) to find out, which dlls/bpls/... are loaded. (The end user typically has no borland development environment and no devart drivers installed. An oracle client installation is typically required.)
My mail address is: a0hirz[AT]gmx[DOT]at [AT]=@ [DOT]=.

Which C++ Builder version exactly did you use? (Version 6?, with Update 1?, 2?, 3?, 4?)
As far as I know, I don't have any updates. (If you use a different C++ Builder installation your dlls/bpls/... will be very helpful.)

Which Oracle Client Version did you use?

[Absolutely not important: Have you ever noticed an AccessViolation in DbxCommonDriver150.bpl? (Windows 7 + current embarcadero C++ Builder)]

[AlexP]

Hello,

I have tested your example on C++ Builder 6 build 10.157 and Oracle 11g R2
To run the application you should have the following modules on the end user PC: midas.dll, dbexpoda.dll, and Oracle client installed.
You can enable the Build with runtime packages option to build your application with all needed packages.

I have added the following lines into your code:
....
sleep(100);
myDatabase->Close();
....
sleep(100);
myDatabase->Connected = true;

to make the process of creating and freeing handles obvious.

[a0hirz]

Hello!

The origination of the problem seems to be found.
"9203 2517189 Handle leak on repeated CONNECT/DISCONNECT in OCI_THREADED mode", see http://oracle-docs.dartmouth.edu/dba-do ... .2.0.4.htm

I have a computer with Oracle Client 9.2.0.1.0, on this the handle leaks occur.
On a computer with Oracle Client 9.2.0.8.0, the handle leaks do not occur.

Therefore the handle leak problem is an Oracle bug, which has been fixed in patch 9203 (or 9204).

Despite the patch I am interesent in a simple workaround, if available. Since my applications are single threaded, turning off OCIThreaded should help me.

In this thread (http://www.devart.com/forums/viewtopic. ... cithreaded) the devart team suggested setting OCIThreaded := False as workaround for the devart data access components.

Is such a configuration also possible with the dbexpress driver (Property Params or interface TCRSQLConnection)? Are there other ways to turn multithreading support off in order to work around the handle leak with Oracle Client 9.2.0.1.0?

[Not important, just for slight clarification: I will never know why the handle leak issue is related to the ODBC connection. I don't need to know it.]

[Also not important: The Windows 7 + current embarcadero C++ Builder problem was caused by upgrading from a C++ Builder Version 6 project. I have simply created a new project and have copied the source code.]

[AlexP]

Hello,

Now the OCIThreaded option is not available in the dbExpress driver for Oracle.
We will consider the possibility of adding this feature in one of the next builds/versions.