Connecting to multiple databases with sqlsrv_connect

Hi there,

Can anyone help me or provide advice on this connection issue I am having. Basically at the moment I can connect to one database at a time. The thing is the data that I need to query is from multiple databases (all starting with V1_database…). For example my database includes V1_database_newyork, V1_database_denver and many others etc. The user can dynamically add the databases but all start with V1_database…

The context I am doing this is as follows… I need to display rail defects on many different runs over America. Each database has data on a single track inspection run. I have quite a few of these databases (i.e. multiple runs). On one of the pages on my site, I need to output in a table format some basic information about each database. e,g, division and where the inspection starts and ends. What is the best way of doing this?

I have created a query like this in Microsoft SQL Server Management Studio which picks up all the database beginning with V1_database:

Select name FROM sys.databases WHERE name NOT In ('master','tempdb','model','msdb') AND name LIKE 'V1_database%'

My current code using PHP5.3 to connect to one of my databases is as follows (this works fine):

<?php 

/*data base connection */ 

$serverName = ".\\SQLEXPRESS";

 $connectionOptions = array("Database"=>"V1_database_newyork", "UID"=>"username", "PWD" => "password"); 

 

/* Connect using Windows Authentication */    

 $conn = sqlsrv_connect($serverName, $connectionOptions); 

 

 /* Check whether connection is established */

if($conn === false) { die(print_r(sqlsrv_errors(), true));  }

 

/* Close the connection. */

sqlsrv_close( $conn);

?>

So in summary, can someone provide any example code or advice on connecting to multiple databases using the sqlsrv_connect function? Is it at all possible to pick up the databases dynamically? If anything is at all unclear, please let me know. Any help would be greatly appreciated!

Cheers,

Neil

i’ve never used sqlsrv, so I may be stating the obvious here, but…
open database1, retrieve data, close database1
open database2, retrieve data, close database2

?

Thank you for the reply StarLion. I was thinking that but I need a way of connecting to all the databases starting with V1_database…

Is this at all possible?

Kind regards,

Neil

sounds to me like all those databases should have been just one database

Can you not connect to each then save the handle in an array, unless you need additional behaviour?


<?php
$servers = array(
  'db_branch_ukwa1',
  'db_branch_uswa1',
  'db_branch_ukde1'
);

$connections = array();

/*
  Create a connection to each server, then save it
*/
foreach($servers as $server){
  $connections[$server] = mssql_connect($server, 'username', 'password');
}

/*
  Execute query on branch server db_branch_uswa1
*/
$result = mssql_query('SELECT foo FROM table', $connections['db_branch_uswa1']);

This is quite wasteful though, I’d look to only connect when needed.

Thank you for everyone replies! The reason why I have to query multiple database is because the client way the client currently supplies the information.

I am creating a web portal in Drupal 6 to display train track inspections from an automated vehicle. Each run from the vehicle is stored in a different database. I have attached these database to SQL Server and I need a page that can output just basic information about each run in the SQL Server (i.e. location, where the run starts and ends).

Can anybody advice on possible solutions to be able to connect to multiple databases for the situation above?

Any help would be greatly appreciated!

Cheers,

Neil

By taking a glance at the sqlsrv documentation, you can’t do it all in a single connection.
You have to connect to each database, retrieve the data, maybe store it in a array or object, and handle the data display.

Now the real question would be, why the hell is “Each run from the vehicle … stored in a different database”!