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

ID: 33681 User updated by: fjortiz at comunet dot es Reported By: fjortiz at comunet dot es Status: Bogus Bug Type: COM related Operating System: Win2K Server PHP Version: 5CVS-2005-07-13 Assigned To: wez New Comment: remember that PHP5+IIS works fine, and PHP5+Apache2 does not. So this IS definitely a PHP5+Apache2 issue. I'll try asking the Apache2 guys (but I know the answer in advance: "ask the PHP guys") Anyway, thanks for your efforts. Previous Comments: ------------------------------------------------------------------------ [2005-07-13 19:21:21] wez@php.net This isn't really a PHP bug. It's more of an Apache bug, but even then, it's not really Apaches fault either. Assuming that the OLE DB stuff is actually routing out over ODBC, then connection pooling much be explicitly enabled in the application by making a couple of ODBC API calls. Nothing in the PHP core nor in Apache does this for you, because it can't possibly know that it should. I'm going to suggest a workaround for you; if it doesn't work, then there is nothing more we can do to help. Possible Workaround - load the PDO ODBC driver. Using the latest PHP 5.1 snap (the one you have already will do): Edit your php.ini file and add this line extension=php_pdo_odbc.dll Restart apache and re-run your tests Why might this work? the php_pdo_odbc.dll makes the API calls that enable connection ODBC pooling. If it doesn't work, you can try migrating your script to use PDO and the odbc driver itself. ------------------------------------------------------------------------ [2005-07-13 19:04:32] sniper@php.net Assigned to the maintainer. ------------------------------------------------------------------------ [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