Copying a DB with PHP

[Peter Poulsen <peter@xxxxxxxxx>]

I have the following situation:

Machine A: Unix with a Progress DB
Machine B: Linux with a MySql DB
Machine C: Windows 2003 with PHP

Now, I would like to copy the entire content of the Progress DB to the 
MySql DB. I'm trying to do that using a PHP script (seen below). 
Unfortunately PHP is only able to connect to the Progress DB through 
ODBC, and I don't have a Progress ODBC driver for Linux, but I have one 
for Windows. So what I have done is creating two ODBC connections on 
Machine C, one for the Progress DB and one for the MySql DB, then I'm 
trying to run the script below on Machine C.

And it works fine for a couple of small tables I have (no more than 
700.000 rows), but then I also have a single large table with 2.500.000 
rows which causes problems. After having copied about 1.300.000 rows the 
script prints "Done" and terminates as if no problems have occured. Why?

Code:

<?php
function copy_data($to_conn, $table_name, $from_res) {
        odbc_fetch_row($from_res, 0);
        do {
                $insert_sql = "INSERT INTO $table_name (\n";
                for($i = 1; $i <= odbc_num_fields($from_res); ++$i) {
                        $field_name = odbc_field_name($from_res, $i);
                        $field_name = str_replace("-", "_", $field_name);

                        $insert_sql .= $field_name;
                        if($i < odbc_num_fields($from_res)) {
                               $insert_sql .= ",\n";
                        }
                }
                $insert_sql .= ") VALUES(\n";
                for($i = 1; $i <= odbc_num_fields($from_res); ++$i) {
                        $field_value = odbc_result($from_res, $i);
                        $field_value = str_replace("'", "\'", 
$field_value);

                        if(odbc_field_type($from_res, $i) == "varchar") {
                               $insert_sql .= "'$field_value'";
                        }
                        else if(odbc_field_type($from_res, $i) == "date") {
                               $insert_sql .= "'$field_value'";
                        }
                        else {
                               if($field_value == '')

                                       $insert_sql .= "0";
                               else
                                        $insert_sql .= "$field_value";
                        }

                        if($i < odbc_num_fields($from_res)) {
                               $insert_sql .= ",\n";
                        }
                }
                $insert_sql .= ")";
                $res = odbc_exec($to_conn, $insert_sql);
                if($res == false) {
                        echo "SQL: $insert_sql\n";
                        $file = fopen("c:\PHP\copy_data.log", "w");
                        if (!$file) {
                           echo "<p>Unable to open remote file for 
writing.\n";
                           exit;
                        }

                        fwrite ($file, $insert_sql . "\n");
                        fclose ($file);
               }
        }
        while(odbc_fetch_row($from_res));
}



function copy_all_data($from_conn, $to_conn, $table_name) {
        $sql = "DELETE FROM $table_name";
        odbc_exec($to_conn, $sql);

        $sql = "SELECT * FROM PUB.$table_name";
        $from_res = odbc_exec($from_conn, $sql);
        copy_data($to_conn, $table_name, $from_res);
}

// --- MAIN ---
        $conn_a = odbc_connect("DB_A","xxx","yyy");
        if($conn_a == 0)
                die("Failed to connect to DB_A");
        $conn_b = odbc_connect("DB_B","xxx","yyy");
        if($conn_b == 0)
                die("Failed to connect to DB_B");

        echo "Copying X data...\n";
        copy_all_data($conn_a, $conn_b, "x");
        echo "Copying Y data...\n";
        copy_all_data($conn_a, $conn_b, "y");
        echo "Done\n";

        odbc_close($conn_a);
        odbc_close($conn_b);
?>

Yours
Peter

--
A: Because it messes up the order in which people normally read text.
Q: Why is it such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php