Link to home
Start Free TrialLog in
Avatar of wcotech
wcotech

asked on

Retrieve data from MSSQL remotely from PHP/Apache on Linux box

Our company has a db on a Windows box running IIS/MSSQL.  There's a table full of site names & addresses that gets updated regularly.  We have another system on a LAMP settup that's on a different domain but still inside of the corporate firewall.  I'd like to run a query against the data in the MSSQL table on the LAMP system.  Here's what I can't do though:

-Can't install mssql_connect because I don't have access to the linux box
-Can't use file() or fopen() for some reason.  Whenever I try either function, the script runs for a minute or two and then times out.
-Can't schedule dumps/imports because again, I don't have access to the linux box
-Can't manually dump the MSSQL table because data gets updated regularly.

I was able to come up with a solution where I had a php page that submitted a POST form to a page on the windows box that in turn ran a query and returned the results in a comma-separated POST value back to the originating form.  I then take those values and explode them into an array.  While this worked as I was testing, I'm not sure how to implement this into an actual working page.

I suspect that there's either firewall or IIS settings that are preventing me from using file() or fopen() because I can use those with http://google.com.  I have no clue if that's the case though.  Any help would be greatly appreciated!
Avatar of wcotech
wcotech

ASKER

raising points for grins :)
Avatar of Steve Bink
You can use a variation of the strategy I use for AJAX.  Write a page on the Windows box that returns all the records you need.  Format them into a flat-file text response.  On the Linux box, create a CURL session, call the page on the Windows box, and parse the response to get the records.  For example, say your db has this:

ID  Name  Dept
1   John    Finance
2   Bill       Support
3   Mae     Operations

The Windows page will return this:

1|John|Finance
2|Bill|Support
3|Mae|Operations

When you receive the response back, the Linux PHP page should parse the response:

<?
$buffer = curl_exec($curl_object);
// this makes $records an array of each record
$records = explode("\n",$buffer);
foreach ($records as $row) {
  $onerecord = explode("|",$row);
  // $onerecord is now an array of the fields in a single record.  
}
?>

You can use to build the INSERT, or any other query you may need.
Avatar of wcotech

ASKER

:(.....curl isn't compiled.  Any other options?
I'd say your best option is to annoy your IT department until they reconfigure the box in such a way that you can actually do your work.  If they expect you to work on a box without having any access to it, you should ask them if they will reimburse you for the magical fairy dust you'll need to make it happen.

Point of fact: data resides on box A and needs to get box B.  Solution, box B needs to be able to retrieve the data (pull) or have it supplied (push).

Pull options:
1) direct query to the database (no mssql_connect)
2) curl request (no curl)
3) file request (no fopen)
4) fairy dust

Push options:
1) replication (no access)
2) imports (updated too often)
3) fairy dust

Avatar of wcotech

ASKER

Thanks for the input routinet.  Just to clarify, I do have access to IIS on the Windows box.  Do you know of any settings that I could check to enable fopen() or curl()?  If not, I just might have to search ebay for some of that fairy dust.
See here for fopen():

http://www.php.net/manual/en/function.fopen.php

There's three basic settings: safe_mode, open_basedir, allow_url_fopen.  Each has their own effects on the ability of fopen() to find its target.  If you don't have any access to configure the Linux box, though, that information will not be of much help.

What reasons do they have for not allowing mssql_connect or odbc?  I can understand locking down fopen() with a basedir or even safe_mode, but not allowing the database extensions...?  
ASKER CERTIFIED SOLUTION
Avatar of wcotech
wcotech

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@PengiunMod:

<bow>  THANK YOU!  THANK YOU!  I'll be here all week!  youvebeengreatgnight!