[mdb-dev] PHP and ODBC leaving lots of files open

[Mike Prudence]

Hi guys,

Bit of a stab in the dark, but I thought I'd ask the list if anyone has
seen anything like this - Google hasn't helped me thus far.

We've got a bunch of PHP scripts using mbdtools via odbc. I've just
noticed that if I use 'lsof -ap' on one of the httpd processes running
the scripts, I discover that none of my 'odbc_close()' calls appear to
have closed the .mdb file that I assume 'odbc_open()' has opened.

This has become a problem because with more people using the scripts,
the open files build up and up until the process breaks because it has
too many open files.

Surely if I use odbc_close() it should be closing the .mdb file it has
opened, no ? Or have I missed something blindingly obvious ?

Thanks, as always, for any hints/help...

[Chris Craig]

Post by Mike Prudence
the scripts, I discover that none of my 'odbc_close()' calls appear to
have closed the .mdb file that I assume 'odbc_open()' has opened.
Surely if I use odbc_close() it should be closing the .mdb file it has
opened, no ? Or have I missed something blindingly obvious ?

I would guess you still have transactions pending, so it's not closing.
See: http://php.net/manual/en/function.odbc-close.php

"This function will fail if there are open transactions on this
connection. The connection will remain open in this case."

You could try running odbc_commit before the close...

[Mike Prudence]

Hi Chris,

Thanks for the hint.....but to no avail, sadly.

Here's the code in case anyone can spot my stupidity:-

function getClientName($s)
{
$myDB = odbc_connect("Clients", "", "");
$query = "select Name from Clients where ID = '".$s."'";
$result = odbc_exec($myDB, $query);

if (odbc_fetch_row($result))
$retval = odbc_result($result, 'Name');
else
$retval = "Unknown";

odbc_commit($myDB);
odbc_close($myDB);
return $retval;
}

Running this leaves my clients.mdb file open by the apache process
according to lsof -ap.

Puzzling....

[Chris Craig]

I have limited experience with PHP, but maybe I can muddle through...

Post by Mike Prudence
if (odbc_fetch_row($result))
$retval = odbc_result($result, 'Name');
else
$retval = "Unknown";

What if there are more rows?

You should more likely do something like:

if (odbc_fetch_row($result, 0))
$retval = odbc_result($result, 'Name');
else
$retval = "Unknown";

while(odbc_fetch_row($result)) {
// do something with the extra results (or not)
}

Or maybe you could call odbc_free_result before odbc_commit and odbc_close.

[Mike Prudence]

Hi guys,

Chris - thanks again for the ideas, sadly didn't cut it....

.....because I discovered something else...

Having got to the point of tracing the UnixODBC calls, I could see that
SQLConnect was being called followed by SQLDisconnect.

What I then discovered is that in odbc.c, SQLConnect calls
mdb_sql_open() as you'd expect..... but the corresponding SQLDisconnect
didn't do anything - most especially, it didn't call mdb_sql_close()!!

So, anyway, I submit a change to odbc.c for your pleasure:-

SQLRETURN SQL_API SQLDisconnect(
SQLHDBC hdbc)
{
struct _hdbc *dbc = (struct _hdbc *) hdbc;
struct _henv *env = (struct _henv *) dbc->henv;

TRACE("SQLDisconnect");

mdb_sql_close(env->sql);

return SQL_SUCCESS;
}


Fixing the code and recompiling causes my issue to go away. Quite why
this hasn't been noticed before is a bit of a mystery (me included!) but
I hope this discovery helps someone out there.