Database Introduction

ComCap allows captured data to be saved in a database table.

Currently only databases supported by Microsoft Data Access Components (MDAC or ADO) may be used, with testing using Microsoft SQL Servers 2000, 2005 and 2008, Sun MySQL 5.1 and IBM DB2 v9.7.

In theory Access (Jet) could be used, but since SQL Server 2005 and 2008 Express are free (database limited to 4 gigs), there seems little point.

MDAC may be used with any database with an ODBC or OLE DB Provider driver, however not all drivers provide the same level of support and may not be usable with ComCap. Please check the help page for the specific databases for recommended drivers and data link set-up:

       Microsoft SQL Server

       Sun MySQL

       IBM DB2

For Windows 2000, MDAC 2.8 SP1 (free download from Microsoft MDAC Downloads) should be installed before attempting to configure database support in ComCap.  Windows XP SP2, Windows 2003, Vista, 2008 and 7 include MDAC as part of the core operating system.

ComCap also includes Sample Database SQL scripts to create empty tables and stored procedures, to illustrate database capture.

A database needs to be specified separately for each capture channel, on the Capture Settings, Database tab.  The columns to save and how to extract them from the captured data are defined on the Capture Settings, Data Format tab.   For problem solving, Common Settings, Log Files has an Database Diagnostics

tick box that specifies that extra database diagnostic messages should be written to the information log file.

ComCap can write to different databases for different channels.

Database Performance

ComCap has been successfully tested with one network channel capturing and writing 50 records per second to a database table using SQL Server 2005 (running on a separate computer), while 20 other channels were capturing more slowly and writing to different tables.  Record are buffered in memory before being written, so if capture rate is briefly higher no records should be lost.

Serious SQL database issue with Windows 7 SP1 and Windows 2008 R2 SP1

Microsoft released service pack 1 for Windows 7 32-bit and 64-bit and Windows 2008 R2 during February 2011, and has introduced a serious SQL database issue in the Microsoft Data Access Components with all versions of ComCap prior to Release 4.9. If captured data is being saved to a database, for each written line three internal handles and some memory are no longer correctly released by a Microsoft database component, which causes a memory leak in ComCap.  As more lines are written, more memory is leaked, causing a PC slow down and eventually ComCap, and perhaps the PC, will crash having run out of memory, probably once about 100,000 lines have been captured depending on PC resources.

Note this database problem does not happen on the original versions of Windows 7 or Windows 2008 R2, or any other earlier versions of Windows, but was probably introduced by Microsoft hotfix KB983246 which fixed a different deadlock issue that could cause ComCap to hang, and which is available for Windows XP and later.  So if this hotfix is installed, the same memory leak may be introduced.

The issue may be seen in SP1 with ComCap running by opening Task Manager, on the Processes tab Select Columns, Memory Working Set and Handles, and for

process comcap4.exe these will seen to be increasing each time a line is written to the database.  ComCap will normally be using a few hundred handles depending on configuration, anything more than 1,000 is unexpected. Also note that ComCap will be very slow to stop when handles are being leaked, during testing it became non-responsive for 10 minutes as 180,000 handles were released. This Microsoft database component has been widely used by other Windows applications for 10 years, which may also be effected after SP1 is installed if they use the same ExecuteComplete database event as ComCap.

This database issue was reported in March 2011 to Microsoft at http://social.msdn.microsoft.com/Forums/en-PH/sqldataaccess/thread/68e23681-f6b5-4ed5-b963-e63e34eeac2f  where it has been reproduced by Microsoft and other developers, the cause determined and a fix discussed in October 2011 at http://blogs.msdn.com/b/psssql/archive/2011/10/03/yes-we-made-a-mistake-and-are-finally-going-to-fix-it.aspx .   The bug has been fixed in the first beta of Windows 8, but may not be fixed for Windows 7 and 2008 R2 until service pack 2 in 2012 or 2013, there is no hot fix as yet.

ComCap Release 4.9 and later fix this problem by no longer using the faulty event and should also resolve possible longer term rare reports of ComCap locking up during database updates in Windows XP and 2003.  Any ComCap database users with 4.8 or earlier are strong recommended to upgrade to 4.9 or later.