We help IT Professionals succeed at work.

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

wcotech
wcotech asked
on
1,070 Views
Last Modified: 2013-12-13
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!
Comment
Watch Question

Author

Commented:
raising points for grins :)
CERTIFIED EXPERT
Top Expert 2004

Commented:
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.

Author

Commented:
:(.....curl isn't compiled.  Any other options?
CERTIFIED EXPERT
Top Expert 2004

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2004

Commented:
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...?  
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2004

Commented:
@PengiunMod:

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.