SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Nov 2001
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to publish an MSSQL table to MySQL?

    Greetings

    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!

    Joe

  2. #2
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Member
    Join Date
    Dec 2001
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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();
    }


    mysql_select_db("able");
    //connected to able db


    $dsn="ableparts";
    $user="sa";
    $upasswd="";
    $conn = odbc_connect($dsn, $user,$upasswd );
    echo "conn: $conn";
    if ($conn <= 0) {
    echo "Error in connection<BR>";
    exit;
    }
    else {
    echo "<P>Connection successful\n<br>";
    };
    $Query=stripslashes($Query);
    echo("$Query");

    $queryexe = odbc_exec($conn, $Query);


    //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.

    Hope this points in the right direction

  4. #4
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by Marshall; Dec 14, 2001 at 20:33.

  5. #5
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Last edited by westmich; Dec 15, 2001 at 02:02.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  6. #6
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes - MS SQL has a good scheduler built in...
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •