Problems copying Oracle result set to MySQL

Question

Hi, What I'm trying to do is copy the result of a query in oracle into table in a MySQL database. The reasoning behind this is not important to the question, and I can't really take a different approach.

What I am doing is running the query though php, then copying the result into a newly created table in oracle. I know this is not very efficient, but my table sizes are pretty small, even though the queries run very long.

Everything works other than I'm having trouble bringing the date over from oracle; when I run it as is, my date fields are set to 0. What I'm doing is checking the result set from oracle to see if the type of a column is "DATE(7)" and if it is then I create a column in MySQL with type "DATE". But for some reason this doesn't work.

I'm using the following code, sorry it's quite long, but it seemed best to provide it all.

function hmis_query_transfer ($query_name, $parameters = NULL) {
    //Create Connections to both servers
    $dbConn_Oracle = hmis_oci_connect();
    hmis_mysql_connect("hmis_temp");

    //Retrieve the text for the query and run it on the Oracle server
    $query_Oracle = hmis_query_by_name($query_name, $parameters);
    $stmt_Oracle = hmis_oci_query($dbConn_Oracle , $query_Oracle);

    $ncols = oci_num_fields($stmt_Oracle);

    //Test if table is already created in MySQL
    $mysql_check = "DESC ".$query_name;
    @hmis_mysql_query($mysql_check);
    if (mysql_errno()==1146){
        $mysql_create = "CREATE TABLE ".$query_name." ( ";

        //Transform and append column names to query string
        for ($j = 1; $j <= $ncols; $j++) {
        $name = oci_field_name($stmt_Oracle, $j);
        $type = oci_field_type($stmt_Oracle, $j);
        if ($type == "NUMBER") {
            $type = "INT";
        } else if ($type == "VARCHAR2"){
            $type = "VARCHAR";
        }
        $type .= "(".oci_field_size($stmt_Oracle, $j).")";
        if ($type == "DATE(7)") {
            $type = "DATE";
        }

        $mysql_create .= $name." ".$type.",";
        }
        $mysql_create = substr_replace( $mysql_create, "", -1 );
        $mysql_create .= ')';
        //Create Table
        $result = hmis_mysql_query($mysql_create);
        if ( !$result ){
        die('<strong>Failed Create:</strong>'.mysql_error());
        }
    }
    elseif (!mysql_errno()) {
        //If the table already exists, empty it
        $mysql_truncate = "TRUNCATE TABLE ".$query_name;
        $result = hmis_mysql_query($mysql_truncate);
        if ( !$result ){
        die('<strong>Failed Truncate:</strong>'.mysql_error());
        }
    }

    //Copy over row by row the data from the result set to MySQL
    while ($results_row = oci_fetch_array($stmt_Oracle, OCI_ASSOC )) {
        $mysql_insert = "INSERT INTO ".$query_name." VALUES (";

        for ($i = 1; $i <= $ncols; $i++) {
        $mysql_insert .= "'".$results_row[oci_field_name($stmt_Oracle, $i)]."',";
        }
        $mysql_insert = substr_replace( $mysql_insert, "", -1 );
        $mysql_insert .= ")";
        $result = hmis_mysql_query($mysql_insert);
        if ( !$result ){
        die('<strong>Failed Insert:</strong>'.mysql_error());
        }
    }
}

Can anyone see any flaws in my code? I'm open to suggestions on how I could do this a different way, though I would prefer to be able to keep my code. Thanks for any help.

---

The reason I'm copying data from oracle to MySQL is because most of the queries take very long to run (15-20 min), and deal wit huge datasets (200 million), but my result sets are very small(a few thousand at most). The way my application was built it has to run repeat queries on the same dataset to accomplish it's task. I would have liked to create views to accomplish this in oracle, but I don't have the authority to do so. Therefore I save an mid layer result set and perform my analysis on it, which happens to be on a much faster machine.

Answer 1

I do not know the format of the date from Oracle, but I would use the strtotime() function to convert it to a unix timestamp and then use the date() function to put it into MySQL (YYYY-MM-DD) format. This should work as long as the date field in oracle is not suppose to be a birthdate or out of the range of a UNIX Timestamp.