php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #33681 PHP5+OLEDB(COM)+IIS pools DB connections, PHP5+OLEDB(COM)+Apache2 does not!
Submitted: 2005-07-13 17:06 UTC Modified: 2005-07-14 09:19 UTC
From: fjortiz at comunet dot es Assigned: wez (profile)
Status: Not a bug Package: COM related
PHP Version: 5CVS-2005-07-13 OS: Win2K Server
Private report: No CVE-ID: None
 [2005-07-13 17:06 UTC] 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.




Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-07-13 17:54 UTC] 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 18:23 UTC] fjortiz at comunet dot es
tried with latest version (5.1.0-dev) on Win32 but still the same. Sorry.
 [2005-07-13 19:04 UTC] sniper@php.net
Assigned to the maintainer.

 [2005-07-13 19:21 UTC] 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-14 09:19 UTC] fjortiz at comunet dot es
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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Oct 05 06:01:28 2024 UTC