Connection Leak with WFC ADO COM Components in MTS

ID: Q247757

The information in this article applies to:

Microsoft Data Access Components versions 2.1 SP2, 2.5
Microsoft Visual J++, version 6.0

SYMPTOMS

When using Windows Foundation Classes for Java (WFC) and ADO, and opening a recordset using a parameterized command object, connections that are properly closed are not properly pooled and recycled, resulting in leaked connections.

CAUSE

Microsoft has confirmed this to be a problem in Microsoft Data Access Objects 2.1 SP2.

WORKAROUND

To work around this problem, call System.gc() after closing the ADO connection in your Java COM object. In normal situations, you do not need to call System.gc() after closing an ADO connection in order to free the connection.

MORE INFORMATION

This connection/session pooling issue occurs when all of the following conditions are present:

Microsoft Data Access Objects 2.1 SP2 is installed.

A parameterized ADO Command object is used.

The Java COM object is hosted in MTS or COM+.

The Java COM object creates a disconnected recordset.

Steps to Reproduce the Behavior

Create a Java COM object project named ConnLoss with Visual J++ 6.0 using the following code:


import com.ms.wfc.data.*;

public class ConnLoss
{
  // Modify this connection string to point to a running SQL Server.
  private static String m_connect = 
    "Provider=SQLOLEDB;Server=(Local);Database=Pubs;UID=sa;PWD=;";
  public com.ms.wfc.data.adodb._Recordset 
    FindAuthorsLastName( String au_id, boolean fCallGC )
  {
    Connection conn = null;
    Command cmd   = null;
    Recordset rs  = null;
    try
    {     
      // Open connection to SQL Server.
      conn = new Connection();
      conn.setCursorLocation( AdoEnums.CursorLocation.CLIENT );
      conn.open( m_connect );
      
      // Prepare command object.
      cmd = new Command();
      cmd.setActiveConnection( conn );
      
      cmd.setCommandText( "select au_lname from authors where au_id=?" );
    
      cmd.getParameters().append( 
        cmd.createParameter( "au_id", 
                   AdoEnums.DataType.VARCHAR,
                   AdoEnums.ParameterDirection.INPUT,
                   20, au_id ) );
      
      // Execute command.
      rs = cmd.execute();
      
      // Disconnect recordset and close connection.
      rs.setActiveConnection( (Connection) null );
      conn.close();
      
      // Call gc if requested.
      if (fCallGC) System.gc();
      
      // Return disconnected recordset.
      return (com.ms.wfc.data.adodb._Recordset) rs.getDataSource();
      
    }
    catch( AdoException adoEX )
    {
      // Log errors here.
    }
    return null;
  }
}

Add the Java COM object to MTS or the COM+ package.

Call the Java COM object with the following Microsoft Visual Basic for Applications (VBA) code:


Sub TestConnLoss()
Dim objCL As Object
Dim i As Long
Dim rs As ADODB.recordset
  set objCL = CreateObject("ConnLoss.ConnLoss")
  For i = 1 To 100
    Set rs = objCL.FindAuthorsLastName("756-30-7391", False)
    Debug.Print rs.Fields("au_lname").Value
    rs.Close
    Set rs = Nothing    
  Next i    
End Sub

Run Windows NT Performance Monitor on the machine where SQL Server 7.0 is located and monitor User Connections under the SQL Server:General Statistics performance counter.

Run the VBA client code.

Change the second parameter of FindAuthorsLastName to True in order to activate the System.gc() code.

Stop and restart the MTS or COM+ package.

Run the VBA client code a second time.

At this point only a few user connections are generated by the code, indicating that the OLE DB session pooling is working properly for the SQL OLE DB provider used by the Java COM object when System.gc() is called.

NOTE: Calling System.gc() greatly impacts the performance of the Java COM object, so calling System.gc() in general should be avoided when performance is a consideration. For example, the business object could be coded to only call System.gc() every 10 or 100 method calls to reduce the per method performance impact of calling System.gc(). Also, the use of a parameterized command object could be avoided by hard-coding parameter values into a SQL string and not using parameter tokens as in the above example; this circumvents the issue as well.

Additional query words:

Keywords : kbMTS kbGrpJava kbDSupport kbADO210sp2bug kbMDAC210SP2 kbMDAC250
Version : WINDOWS:2.1 SP2,2.5,6.0
Platform : WINDOWS
Issue type : kbprb