How to update MySQL DB directly from an MSSQL DB?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
heet
Forum Newbie
Posts: 2
Joined: Fri Mar 14, 2008 2:54 pm
Location: Alabama

How to update MySQL DB directly from an MSSQL DB?

Post by heet »

I have been unsuccessful in updating data in a MySQL DB via an MSSQL DB.

I can connect to each DB, view the data and display the data. But, I cannot figure out how to INSERT or UPDATE the data from the MSSQL to the MySQL. So, either INSERT a non existing record or UPDATE an existing record.

I am only dealing with one table that has about twelve fields.

I have the following concept:
<?php

//connect to the MSSQL server and select database
//connect to the MySQL server, select database
//query to get required fields from MSSQL DB
//run the query on the database
//loop to get each row from the MSSQL DB
//connect to MySQL server, select db and UPDATE or INSERT row

?>
Also, this is the code that I am trying to utilize:

Code: Select all

<?php
while (!$rs->EOF)  //carry on looping through while there are records
{
        $counter++;//For testing
        $gen_name = $rs["storename"];
        $gen_add = $rs["address"];
        $gen_city = $rs["city"];
        $gen_state = $rs["state"];
        $gen_zip = $rs["zip"];
        
    //connect to mysql server, select db and insert row
    mysql_connect($myhostname,$myusername,$mypassword)  or die ("MySQL Database connection failed during loop.");
    mysql_select_db($mydbname)  or die ("MySQL DB unavailable during loop.");
    //test display
        echo $counter,"MySQL looping1 - name=$gen_name<br />";
    //test display  
        $sql_in = ("INSERT INTO locations (storename,address,city,state,zip) 
    VALUES ('$gen_name','$gen_add','$gen_city','$gen_state','$gen_zip')");
    mysql_query($sql_in);
            
    //create an instance of the  ADO connection object
    $conn = new COM ("ADODB.Connection")
     or die("Cannot start ADO");
            
    //connect to MSSQL server
    $connStr = "PROVIDER=SQLOLEDB;SERVER=".$msServer.";UID=".$msUser.";PWD=".$msPass.";DATABASE=".$msDB;
    $conn->open($connStr); //Open the connection to the database
                    
    $rs->MoveNext(); //move on to the next record
}
?>
With the test displays in the code it will display the data for each loop of the $sql_in variable
Thanks for any help or advice!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to update MySQL DB directly from an MSSQL DB?

Post by Christopher »

Instead of creating a connection every loop, but not querying -- try something more like this:

Code: Select all

<?php
//connect to mysql server, select db and insert row
mysql_connect($myhostname,$myusername,$mypassword)  or die ("MySQL Database connection failed during loop.");
mysql_select_db($mydbname)  or die ("MySQL DB unavailable during loop.");
 
//create an instance of the  ADO connection object
$conn = new COM ("ADODB.Connection") or die("Cannot start ADO");
            
//connect to MSSQL server
$connStr = "PROVIDER=SQLOLEDB;SERVER=".$msServer.";UID=".$msUser.";PWD=".$msPass.";DATABASE=".$msDB;
$conn->open($connStr); //Open the connection to the database
                    
// A SELECT STATEMENT HERE TO CREATE $rs WOULD BE SUPER!!!!!! 
 
while (!$rs->EOF)  //carry on looping through while there are records
{
        $counter++;//For testing
        $gen_name = $rs["storename"];
        $gen_add = $rs["address"];
        $gen_city = $rs["city"];
        $gen_state = $rs["state"];
        $gen_zip = $rs["zip"];
        
    //test display
        echo $counter,"MySQL looping1 - name=$gen_name<br />";
    //test display  
        $sql_in = ("INSERT INTO locations (storename,address,city,state,zip) 
    VALUES ('$gen_name','$gen_add','$gen_city','$gen_state','$gen_zip')");
    mysql_query($sql_in);
            
    $rs->MoveNext(); //move on to the next record
}
?>
(#10850)
User avatar
heet
Forum Newbie
Posts: 2
Joined: Fri Mar 14, 2008 2:54 pm
Location: Alabama

Re: How to update MySQL DB directly from an MSSQL DB?

Post by heet »

I have found my issue and that issue was, I did not have all the fields of my MySQL DB being INSERTED or populated. This caused a 'row count' error. I also had a datetime error, which was from MSSQL's date format.

I finally awoke and used a phpMyAdmin SQL editor window to test my queries which flagged the above errors.

Also, by removing the DB connection strings in the loop cut the query time in half. So, thanks a ton arborint for your help on that one!!
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to update MySQL DB directly from an MSSQL DB?

Post by Christopher »

Excellent! And good to see that you are moving your data in the right direction. ;)
(#10850)
Post Reply