MS-SQL multiple connect problem

[alasdair.watson]

PHP 4.3/MS-SQL7/IIS5.

At the start of every script, I include db.php which contains this:
<?
$db = mssql_connect('devbox','foo','bar');
mssql_select_db('[devsitedb]', $db) or die ("Couldn't open database
connection");
?>

And then I pass that connection into the various classes and functions
on the site. Fine and dandy.

However, in the course of one fo the scripts for this site, I need to
hook into a second database temporarily, so I do this at the
appropriate point:

<?
$second_db = mssql_connect('devbox','jim','bob');
mssql_select_db('[anothersitedb]', $second_db) or die ("Couldn't open
second database connection");
?>

Then do what I need to with that connection and close it with
<?
mssql_close($second_db);
?>

Here's the problem, though: despite the fact that the two connections
are clearly different, the second one is overwriting the first, so that
when I come back to doing things with the first connection, it now
can't find any of the stored procedures it needs. This is true at any
point after $second_db is opened, and it make no difference whether or
not I close $second_db. The connection is just being over-written.

At the point of opening, print_r($db) returns "Resource id #2". And
that holds true for the second connection as well - print_r($second_db)
also returns "Resource id #2".

mssql.max_links is set to -1, so there should be no problem with the
number of connections that I'm making...

Obviously, I would like it if this didn't happen, and $second_db become
"Resource id #3", thus allowing me to juggle the two connections as I
need to.

Anyone out there had similar problems, or able to spot what I'm missing?

[Erwin Moller]

al*************@gmail.com wrote:

PHP 4.3/MS-SQL7/IIS5.

At the start of every script, I include db.php which contains this:
<?
$db = mssql_connect('devbox','foo','bar');
mssql_select_db('[devsitedb]', $db) or die ("Couldn't open database
connection");
?>

And then I pass that connection into the various classes and functions
on the site. Fine and dandy.

However, in the course of one fo the scripts for this site, I need to
hook into a second database temporarily, so I do this at the
appropriate point:

<?
$second_db = mssql_connect('devbox','jim','bob');
mssql_select_db('[anothersitedb]', $second_db) or die ("Couldn't open
second database connection");
?>

Then do what I need to with that connection and close it with
<?
mssql_close($second_db);
?>

Here's the problem, though: despite the fact that the two connections
are clearly different, the second one is overwriting the first, so that
when I come back to doing things with the first connection, it now
can't find any of the stored procedures it needs. This is true at any
point after $second_db is opened, and it make no difference whether or
not I close $second_db. The connection is just being over-written.

At the point of opening, print_r($db) returns "Resource id #2". And
that holds true for the second connection as well - print_r($second_db)
also returns "Resource id #2".

mssql.max_links is set to -1, so there should be no problem with the
number of connections that I'm making...

Obviously, I would like it if this didn't happen, and $second_db become
"Resource id #3", thus allowing me to juggle the two connections as I
need to.

Anyone out there had similar problems, or able to spot what I'm missing?

Hi,

Just reconnect to the first DB after closing you second DB.
PHP will recycle the last used db, so you just have to point to the first
again, and can continue.

Regards,
Erwin Moller

[Sean]

With queries you can get around the problem with:

mssql_query ( $query1, $first_db);

mssql_query ( $query2, $second_db);
But for executes of stored procedures all you have is mixed
mssql_execute ( resource stmt [, bool skip_results] so it uses the
last connection resource opened.

A simple solution would then be to reformat the script so it works
like:

//Open db1
//Do all the db1 stuff
//close db 1

//Open db2
//Do all the db2 stuff
//close db 2