Our company uses MSSQL2000 for our manufacturing database (job status, stock levels etc.). We wish to give our customers web access to the stock levels of their products.
The problem is that our IT department does not want to give the customers access to the manufacturing database via the web, for security reasons. It has been suggested that we setup MySQL on a separate Server and allow the webserver to query that database.
I’m looking for some guidance as to how I can automatically publish/mirror/replicate the Stocks table of MSSQL database into our MySQl database.
Any ideas/help/links etc would be very much appreciated!
SOunds like a job for a Perl script You can use the Perl DBI (database interface) to talk to mySQL and SQL Server and put together a simple script to query the data you want from SQL Server and insert it into mySQL. Set the script (via cron or something) to run at a predetermined interval and you’re sorted.
I did the same sort of thing with my parts database. I used the odbc support in php. First I manually created the database table in mysql the I wrote this little script to update it from the mssql.
<?php
include(“odbc_fetch_array.inc”);
if(!isset($Query))
{
?>
<html>
<head></head>
<body>
<form method=post>
<input type=“text” size=“30” name=“Query”>
<input type=“Submit” name=“submit” value=“Submit Query”>
</form>
</body>
</html>
<?php
exit;
}
// connect to able db
$dbcnx = @mysql_connect(“servername”,“user”,“password”);
if (!$dbcnx) {
echo( "<P>Unable to connect to the " . “database server at this time.</P>” );
exit();
}
//output results to standard output
//odbc_result_all($queryexe, “BORDER=1”);
//odbc_num_rows($queryexe);
while($row=odbc_fetch_array($queryexe))
{
$testarray=“$row[Product_Number]”;
$testqty=“$row[On_Hand]”;
$testloc=“$row[Bin]”;
?>
<table border=“1”><tr><td><?php echo(“$row[Product_Number]”);?></td><td><?php echo(“$row[Bin]”);?></td><td><?php echo(“$row[On_Hand]”);?></td></tr></table>
<?php
}
//$num_array=“count($testarray)”;
odbc_close($conn); //Disconnect from ms SQl database
for ($idx=0;$idx<count($testarray);++$idx)
{
mysql_query(“UPDATE PARTS SET loc=‘$testloc[$idx]’,qty_on_hand=‘$testqty[$idx]’ where omd_num=‘$testarray[$idx]’”);
//echo(“$testloc[$idx] $testqty[$idx]<br>”);
}
?>
I don’t write real good php scripts but this one worked for me. The include file is an odbc_fetch_array script that someone else wrote along with a brief tutorial on how to use it. I found it by searching google with “odbc fetch array” search string.
If you’re worried about the performance hit to the msSQL database by allowing online users to query it directly through a Web script, you might, as you suggested, want to mirror the content to a new database server instead. This can be done by creating a cron job on a unix or linux machine that executes every set period of time and calls a perl/php/sh/ack/sed/pascal/etc script that does the actual work of connecting to both databases and updating mySQL with the new data from msSQL.
Alternately, if you have a few thousand spare US dollars, you can buy software from IBM or a similar company that will allow you to perform this task using a pretty point-and-click interface
Marshall
Oops… didn’t see Skunk’s post until after I submitted this. Sorry for the repetition.
The best option, IMHO, would be to use the Data Transformation Services built into MS SQL Server. You can move data from one datasource to another (niether needs to be MS) and save and schedule the job.
MS SQL Server also replication/publishing built into it.
I am not suggesting that scripting isn’t a viable solution, its just that SQL Server is ‘high-end’ database for these very reasons that it has the functionality built-in.
You can use the Perl DBI (database interface) to talk to mySQL and SQL Server and put together a simple script to query the data you want from SQL Server and insert it into mySQL. Set the script (via cron or something) to run at a predetermined interval and you’re sorted.