Solved

Web Site & MS SQL

Posted on 2011-09-30
29
304 Views
Last Modified: 2012-06-21
I need to have my website which is hosted by a third party access our corporate Microsoft SQL database using PHP so our customers can access some data.  What exactly needs to be done?  I'm using WAMP version 5.1

Thanks in advance
0
Comment
Question by:chasmx1
  • 14
  • 11
  • 2
  • +2
29 Comments
 
LVL 21
ID: 36894673
I would create a web service that returns the data.
0
 

Author Comment

by:chasmx1
ID: 36894727
Can you be more specific?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36894737
What kind of hosting is your third party provider?  Microsoft's current PHP SQL driver only runs on Windows, not Linux.
0
 

Author Comment

by:chasmx1
ID: 36894757
My Web hosting company uses unix and our company is Windows Server 2008 based.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 36894809
A RESTful interface might do the job nicely.  Here is a teaching example showing all the necessary moving parts.  Please read it over and then post back with any specific question.
<?php // RAY_REST_get_last_name.php
error_reporting(E_ALL);



// DEMONSTRATE HOW A RESTFUL WEB SERVICE WORKS
// INPUT FIRST NAME, OUTPUT FAMILY NAME
// CALLING EXAMPLE:
// file_get_contents('http://laprbass.com/RAY_REST_get_last_name.php?key=ABC&resp=XML&name=Ray');



// OUR DATA MODEL CONTAINS ALL THE ANSWERS - THIS COULD BE A DATA BASE - AS SIMPLE OR COMPLEX AS NEEDED
$dataModel
= array
( 'Brian'   => 'Portlock'
, 'Ray'     => 'Paseur'
, 'Richard' => 'Quadling'
, 'Dave'    => 'Baldwin'
)
;


// RESPONSE CAN BE PLAIN TEXT OR XML FORMAT
$alpha = NULL;
$omega = NULL;
if ( (isset($_GET["resp"])) && ($_GET["resp"] == 'XML') )
{
    // PREPARE THE XML WRAPPER
    $alpha = '<response>';
    $omega = '</response>';
}



// TEST THE 'API KEY' - THIS COULD BE A DATA BASE VALIDATION LOOKUP - AS SIMPLE OR COMPLEX AS NEEDED
$key = (!empty($_GET["key"])) ? $_GET["key"] : FALSE;
if ($key !== 'ABC')
{
    echo $alpha . 'BOGUS API KEY' . $omega;
    die();
}



// LOOK UP THE FAMILY NAME
$name = (!empty($_GET["name"])) ? $_GET["name"] : 'UNKNOWN';

// IF THE NAME FROM THE URL IS FOUND IN THE DATA MODEL
if (array_key_exists($name, $dataModel))
{
    // RETURNS THE APPROPRIATE FAMILY NAME FROM THE DATA MODEL
    echo $alpha . $dataModel[$name] . $omega;
    die();
}

// RETURNS THE UNKNOWN NAME INDICATOR
else
{
    echo $alpha . 'UNKNOWN' . $omega;
    die();
}

Open in new window

0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36894944
There are a couple of ODBC drivers for Linux that will connect to SQL Server 2008 but they're not free.  'php_mssql' and 'FreeTDS' won't work with SQL Server 2008.  While you can develop software on your WAMP platform that will work on Windows and connect to SQL Server 2008, you will not be able to transfer it to your Unix hosting and make it work.  The drivers are not available for Linux.  This has come up several times this year and no one has been able to make it work (for free).
0
 

Author Comment

by:chasmx1
ID: 36895158
Is there a way to connect from Network Solutions's unix server that is hosting my site back to my Sql Server to retrieve my data to display data for our customers?  The only other thing i can think of is to export the needed data into a MySQL database on the web server.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36895222
Does your SQL Server 2008 have a public IP address?  Are you running IIS on it?  How often does the data get updated?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 36895243
A RESTful interface is the way that web services are provided between servers.  TheHiTechCoach gave you good advice and I provided an example of how it is done.

Best of luck with it, ~Ray
0
 

Author Comment

by:chasmx1
ID: 36895265
I can forward sql traffic to it via the router's port 1433 forwarding and yes IIS is running on it.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36895473
First of all If you wish to give access of some objects to that user then create a SQL user for that particular database and give read write or some permission.

You can also give SQL object level permission using Grant and Revoke functions.
0
 

Author Comment

by:chasmx1
ID: 36897073
I'm not familar with the RESTful interface.  What has to been done on the web site server and the corporate server?

Thanks
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36897113
You have to create two pages or programs, one on each server.  The one on the server where the SQL Server is accepts requests from outside, does a query on the database, and passes the results back as a response to the original request.  As shown in Ray's code,  you can use something like

file_get_contents('http://laprbass.com/RAY_REST_get_last_name.php?key=ABC&resp=XML&name=Ray');

on the outside web server to make a request to the corporate server.  The query string at the end has the data that you want from the database.
0
 

Author Comment

by:chasmx1
ID: 36898184
Does the SQL Server have to be running PHP and IIS?  DO you know where I can get some examples of a complete SQL data sample?

Thanks
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36898245
You said that SQL Server 2008 was running on Windows Server 2008 with IIS.  You can use any language you want on that server to return data.  You can install PHP if that makes it easier for you.  Just make sure that the version you install is compatible with the server.  If you install PHP, then you must install the Microsoft PHP SQL driver and the SQL Native client so that PHP can talk to the SQL Server.
0
 

Author Comment

by:chasmx1
ID: 36898291
Is their anything I should know about getting SQL data from the company's SQL server into the PHP pages at our hosting company?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36898390
We've told you basically what you need to do.  What is it that you don't understand?  Maybe if it is too complicated, you should hire a developer to do this project.
0
 

Author Comment

by:chasmx1
ID: 36898529
Sorry to bother you. I'll figure it out myself.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36898599
It's not a bother.  Ray and I and TheHiTechCoach and others spend a lot of time trying to help people here.  So far, I can't tell if anything we have said makes any sense to you.  You have to give us something to work with so we'll be able to figure out what comes next.

If you have never programmed or done PHP pages, then there is a lot that you will need to learn.  And programming is lots and lots of details that have to be correct for programs to work.  I can't tell you in a single post here what you have to do.
0
 

Author Comment

by:chasmx1
ID: 36905889
I have 30+ years as a programmer and have coded in over 40 languages but I'm new to PHP.  I just wanted a leg up to save some time.









0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36906519
Ray made a post a couple of days ago about PHP saying it's a lot like a 'real' programming language (somewhat 'C' like)... but made to be 'easy' with loose typing and the odd kinds of things you find in javascript.  I find myself mutiplying variables by 1 sometimes to make sure PHP thinks its a number.  The biggest advantage of PHP is their web site which has all their docs online.  http://www.php.net/  I consider it the best reference site of it's type on the web.

The problem you will confront is that Microsoft doesn't care if you are able to connect to MS SQL Server from Linux.  UnixODBC and FreeTDS worked up to SQL 2000 but nothing has been added to them after that.  But Microsoft has changed their interfaces with SQL 2005 and 2008 so the only drivers I know about are some ODBC drivers for Linux that you can buy or use the Microsoft drivers on Windows.

If you have used a language to create web pages, the RESTful interface is slightly different.  Instead of returning a full page, you just return the data to the page that is asking for it.  Hope that makes some sense.
0
 

Author Comment

by:chasmx1
ID: 36906563
How about importing the necessary tables into mySQL on the hosting server from MS SQL on the company server?  There are only a few thousand rows and we only add about 50 new rows per day.  Then I could do all my querries against the mySQL database on the web site.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36906978
If you can do that once a day at a slow time, that would probably be better.  Just less trafffic overall.  I've never exported for SQL Server so I'd have to look that up.  Network Solutions has phpmyAdmin available to manage your MySQL databases so that would be easy for the initial export/import.

You would still need a pair of programs, one on each server, to do the daily updates.  It be easier to schedule the company server and have it contact a secure page on Network Solutions to do it.  That would eliminate any need to make changes to the firewall or anything else.
0
 

Author Comment

by:chasmx1
ID: 36907336
Sounds like a winner.
0
 

Author Comment

by:chasmx1
ID: 36922547
Is there a way to have my website do updates to it's MySQL database on some kind of timer using PHP or a SQL script?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36922691
Network Solutions does provide for Scheduled Tasks / Cron Jobs.  The problem with doing it on their server is that you won't have access to your SQL Server unless it has a public IP address where it can be accessed directly from the internet.  Your web site does allow that kind of access so you can write program on the SQL Server that can contact it.

I would create a program on the SQL server that can be launched by Windows Scheduled Tasks every night.  It would query your database for the updates and collect them.  Then it would do a page request to a PHP page on the web hosting and send the updates as a POST so that the PHP program could do the INSERTs into the MySQL database on the hosting.  I make sure there is a ridiculously long access code when I do something like that so it would be difficult to type it in.  Something like "access=1acode2thebase4data3could5for9zee73quirt8jj".  Or longer.
0
 

Author Comment

by:chasmx1
ID: 36936330
I'm not sure how to access a web page programmatically from within a Transact SQL script.

Thanks
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36936398
I meant that you could use VB or C# to create a program that will get the data from the database and then make a POST to the web page.
0
 

Author Comment

by:chasmx1
ID: 36959716
Okay I'll try it.  Thanks
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now