#33681 [Opn->Asn]: PHP5+OLEDB(COM)+IIS pools DB connections, PHP5+OLEDB(COM)+Apache2 does not!

ID: 33681 Updated by: sniper@php.net Reported By: fjortiz at comunet dot es -Status: Open +Status: Assigned Bug Type: COM related Operating System: Win2K Server -PHP Version: 5.0.4 +PHP Version: 5CVS-2005-07-13 -Assigned To: +Assigned To: wez New Comment: Assigned to the maintainer. Previous Comments: ------------------------------------------------------------------------ [2005-07-13 18:23:02] fjortiz at comunet dot es tried with latest version (5.1.0-dev) on Win32 but still the same. Sorry. ------------------------------------------------------------------------ [2005-07-13 17:54:23] tony2001@php.net Please try using this CVS snapshot: http://snaps.php.net/php5-latest.tar.gz For Windows: http://snaps.php.net/win32/php5-win32-latest.zip ------------------------------------------------------------------------ [2005-07-13 17:06:44] fjortiz at comunet dot es Description: ------------ Ok, this seems a tricky one, so sorry for the long post. It's related to the way Apache2 and IIS work with COM objects. So maybe some combined Apache2/IIS/Database/COM skills are needed. I want to connect to a DB (MSSQL in this case), but I need some features unavailable under php_mssql (asked to F. Kromman, thanks). So I'm forced to use COM/OLEDB via ADODB.Connection. COM works great now under PHP5 but... I have this problem: If I use this combination: PHP5+OLEDB(COM)+IIS: IIS keeps a pool of persistent connections so the next PHP scripts re-use this open connection and doesn't have to open a new one. PHP5+OLEDB(COM)+Apache2: Apache2 works fine with the queries but at the end of my PHP script, the connection is closed, and every subsecuent PHP script has to open a new connection. It's not a critical issue as long as your DB server is on the same machine as the Web Server: Open new connection on Apache2: 0.02 secs. Re-use existing connection on IIS: 0.002 secs. But it does matter if both are on different machines and/or while on very heavy traffic web-sites. My theory: not a COM guru, but maybe Apache2 lacks being compiled with some COM Apartment threading model which IIS already has. I'm really sad about being impelled to work with this COM/OLEDB shit but I'd really like to get this working on PHP5+Apache2 (will have to swap to PHP5+IIS if there is no way to solve this). Thanks in advance Reproduce code: --------------- <? // Win32, need MDAC and a SQL Server $HOST = "your-sql-server"; $USER = "user"; $PASS = "pwd"; $DB = "database"; $dsn = "Provider=SQLOLEDB;Data Source=$HOST;User Id=$USER;Password=$PASS;Initial Catalog=$DB"; $charPage = CP_UTF8; $db = new COM("ADODB.Connection", NULL, $charPage); $db->Open($dsn); // sample query to get all the SQL Server processes for your Database, along with the App that created them // (Apache, IIS, others...) $query="select distinct sd.name, sp.program_name from master..sysprocesses sp inner join master..sysdatabases sd on sd.dbid=sp.dbid where sd.name='$DB' order by 1,2"; $rs = $db->Execute($query); // some output print "<table border=1>"; while (!$rs->EOF) { print "<tr><td>".$rs->Fields["program_name"]->Value."</td><td>".$rs->Fields["name"]->Value."</td></tr>\n"; $rs->MoveNext(); } print "</table>"; $db->Close(); // doesn't really matter, IIS keeps connection open, Apache2 closes it even if this line is commented ?> Expected result: ---------------- We list all the SQL Server open processes, during and after PHP script execution: select distinct sd.name, sp.program_name from master..sysprocesses sp inner join master..sysdatabases sd on sd.dbid=sp.dbid where sd.name='nc208' order by 1,2 While executing PHP5+Apache2: database Apache HTTP Server database SQL Query Analyzer database SQL Query Analyzer - Object Browser When PHP script ends: database SQL Query Analyzer database SQL Query Analyzer - Object Browser PHP5+Apache2 CLOSES CONNECTION --> not there when script finishes. While executing the same on PHP5+IIS: database Internet Information Services database SQL Query Analyzer database SQL Query Analyzer - Object Browser When PHP script ends: database Internet Information Services database SQL Query Analyzer database SQL Query Analyzer - Object Browser PHP5+IIS KEEPS CONNECTION OPEN for re-use --> at least for 60 seconds, which is a default OLEDB driver setting I think. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=33681&edit=1