function xcopy($mssql, $mysql, $db, $table, $sql)
{
$start = microtime(true);
mysqli_select_db($mysql, $db);
mssql_select_db($db, $mssql);
$result = mssql_query($sql, $mssql, 20000);
if ($result === false) {
die("Error creating sync data\n");
}
$s = 0;
$r = mssql_num_rows($result);
$name_count = mssql_num_fields($result);
$name_list = "";
$update_list = "";
$value_list = "";
$sql = "";
$radix = 0;
for ($i = 0; $i < $name_count; $i++) {
$x = strtolower(mssql_field_name($result, $i));
$name_list .= "{$x},";
if ($x != "dex_row_id") {
$update_list .= "{$x} = values({$x}),";
}
}
$name_list = rtrim($name_list, ",");
$update_list = rtrim($update_list, ",");
do {
while ($row = mssql_fetch_row($result)) {
for ($i = 0; $i < $name_count; $i++) {
$value_list .= "'" . str_replace("'", "''", trim($row[$i])) . "',";
}
$value_list = rtrim($value_list, ",");
$radix++;
$sql .= "\n({$value_list}),";
$value_list = "";
if ($radix > 2000) {
$sql = trim($sql, ",");
$sql = "insert into {$table} ({$name_list}) values {$sql} on duplicate key update {$update_list};";
$rset = mysqli_query($mysql, $sql);
if ($rset === false) {
die("Error inserting mysql data. \n" . mysqli_error($mysql) . "\n\n{$sql}\n\n");
}
$radix = 0;
$sql = "";
}
$s++;
}
} while (mssql_fetch_batch($result));
if ($sql != "") {
$sql = trim($sql, ",");
$sql = "insert into {$table} ({$name_list}) values {$sql} on duplicate key update {$update_list};";
$rset = mysqli_query($mysql, $sql);
if ($rset === false) {
die("Error inserting mysql data. \n" . mysqli_error($mysql) . "\n\n{$sql}\n\n");
}
}
$end = microtime(true);
$total = $end - $start;
echo "imported {$db}.{$table} [ {$s} ] records in {$total} sec.\n";
}
public function read($sql)
{
$data = [];
$result = mssql_query($sql, $this->gp, 10000);
if ($result === false) {
throw new \Exception("Error creating sync data");
}
$name_count = mssql_num_fields($result);
do {
$x = [];
while ($row = mssql_fetch_assoc($result)) {
foreach ($row as $key => $value) {
$x[strtolower($key)] = trim($value);
}
$data[] = $x;
}
} while (mssql_fetch_batch($result));
return $data;
}
/**
* Gets the next row from the result and assigns it to the current row
*
* @return void
*/
private function advanceCurrentRow()
{
$type = $this->database->getType();
switch ($this->extension) {
case 'ibm_db2':
$row = db2_fetch_assoc($this->result);
break;
case 'mssql':
// For some reason the mssql extension will return an empty row even
// when now rows were returned, so we have to explicitly check for this
if ($this->pointer == 0 && !mssql_num_rows($this->result)) {
$row = FALSE;
} else {
$row = mssql_fetch_assoc($this->result);
if (empty($row)) {
mssql_fetch_batch($this->result);
$row = mssql_fetch_assoc($this->result);
}
if (!empty($row)) {
$row = $this->fixDblibMSSQLDriver($row);
}
}
break;
case 'mysql':
$row = mysql_fetch_assoc($this->result);
break;
case 'mysqli':
if (!$this->result instanceof stdClass) {
$row = mysqli_fetch_assoc($this->result);
} else {
$meta = $this->result->statement->result_metadata();
$row_references = array();
while ($field = $meta->fetch_field()) {
$row_references[] =& $fetched_row[$field->name];
}
call_user_func_array(array($this->result->statement, 'bind_result'), $row_references);
$this->result->statement->fetch();
$row = array();
foreach ($fetched_row as $key => $val) {
$row[$key] = $val;
}
unset($row_references);
$meta->free_result();
}
break;
case 'oci8':
$row = oci_fetch_assoc($this->result);
break;
case 'pgsql':
$row = pg_fetch_assoc($this->result);
break;
case 'sqlite':
$row = sqlite_fetch_array($this->result, SQLITE_ASSOC);
break;
case 'sqlsrv':
$resource = $this->result instanceof stdClass ? $this->result->statement : $this->result;
$row = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC);
break;
case 'pdo':
$row = $this->result->fetch(PDO::FETCH_ASSOC);
if (!empty($row) && $type == 'mssql') {
$row = $this->fixDblibMSSQLDriver($row);
}
break;
}
// Fix uppercase column names to lowercase
if ($row && ($type == 'oracle' || $type == 'db2' && $this->extension != 'ibm_db2')) {
$row = array_change_key_case($row);
}
// This is an unfortunate fix that required for databases that don't support limit
// clauses with an offset. It prevents unrequested columns from being returned.
if (isset($row['flourish__row__num'])) {
unset($row['flourish__row__num']);
}
// This decodes the data coming out of MSSQL into UTF-8
if ($row && $type == 'mssql') {
if ($this->character_set) {
foreach ($row as $key => $value) {
if (!is_string($value) || strpos($key, '__flourish_mssqln_') === 0 || isset($row['fmssqln__' . $key]) || preg_match('#[\\x0-\\x8\\xB\\xC\\xE-\\x1F]#', $value)) {
continue;
}
$row[$key] = self::iconv($this->character_set, 'UTF-8', $value);
}
}
$row = $this->decodeMSSQLNationalColumns($row);
// This resets the array pointer so that
// current() will work as expected
reset($row);
}
if ($this->unescape_map) {
foreach ($this->unescape_map as $column => $type) {
if (!isset($row[$column])) {
continue;
}
$row[$column] = $this->database->unescape($type, $row[$column]);
}
}
$this->current_row = $row;
}
function copy_table_data($db, $table, $keys, $filter)
{
global $gp;
global $con;
/*
if($db == "dynamics")
mssql_select_db("dynamics");
else
mssql_select_db("cpj");
*/
mssql_select_db($db);
echo "Pulling data for table {$db}_{$table}...\n";
$table = strtolower($table);
$filter = strtolower($filter);
$field_names = "";
$field_values = "";
#$key_id = 0;
$xkeys = !empty($keys["primary"]) ? explode(", ", strtolower($keys["primary"])) : [];
$primary = [];
foreach ($xkeys as $value) {
$primary[$value] = -1;
}
if (!table_exists($db, $table)) {
echo "Table {$db}_{$table} does not exist.\nCopying table structure.\n";
copy_table_structure($db, $table, $keys);
}
$result = mysqli_query($con, "select coalesce(max(modified), '01/10/1970') modified from sync_log where db = '{$db}' and tbl = '{$table}';");
if ($result === false) {
die("Error can not get last modified date. " . mysqli_error($con));
}
$row = mysqli_fetch_assoc($result);
$mysql_modified = $row["modified"];
mysqli_free_result($result);
$result = mssql_query("select convert(varchar(23), modify_date, 121) modify_date from {$db}.sys.tables where name = '{$table}';", $gp);
if ($result === false) {
die("Error can not get last modified date. " . mssql_get_last_message());
}
$row = mssql_fetch_assoc($result);
$mssql_modified = $row["modify_date"];
mssql_free_result($result);
echo "\n\n\n######################\n\n{$mssql_modified} == {$mysql_modified}\n\n#######################\n\n\n";
if ($mssql_modified == $mysql_modified) {
return;
}
$where = "";
switch ($filter) {
case "dex_row_id":
$result = mysqli_query($con, "select coalesce(max(dex_row_id), 0) modified from {$db}_{$table};");
if ($result === false) {
die("Error can not get last modified date. " . mysqli_error($con));
}
$row = mysqli_fetch_assoc($result);
$where = " where dex_row_id > '" . $row["modified"] . "'";
mysqli_free_result($result);
break;
case "dex_row_ts":
$result = mysqli_query($con, "select coalesce(max(dex_row_ts), '01/10/1970') modified from {$db}_{$table};");
if ($result === false) {
die("Error can not get last modified date. " . mysqli_error($con));
}
$row = mysqli_fetch_assoc($result);
$where = " where dex_row_ts > '" . $row["modified"] . "'";
mysqli_free_result($result);
break;
case "active":
$where = " where active = 1";
mysqli_query($con, utf8_encode("truncate table {$db}_{$table};"));
break;
default:
mysqli_query($con, utf8_encode("truncate table {$db}_{$table};"));
break;
}
$sql = "select * from {$table} {$where}";
$rset = mssql_query($sql, $gp, 5000);
if (!$rset) {
die('MSSQL error: ' . mssql_get_last_message() . "\n\n {$sql} \n\n\n");
}
$cnt = mssql_num_fields($rset);
for ($inc = 0; $inc < $cnt; $inc++) {
if ($field_names == "") {
$field_names = mssql_field_name($rset, $inc);
} else {
$field_names .= ", " . mssql_field_name($rset, $inc);
}
$ykey = trim(strtolower(mssql_field_name($rset, $inc)));
if (isset($primary[$ykey])) {
$primary[$ykey] = $inc;
}
}
$zkey = "";
foreach ($primary as $key => $value) {
if ($zkey != "") {
$zkey .= ":{$key}";
} else {
$zkey .= "{$key}";
}
}
$p_sql = "";
$radix = 0;
$counter = 0;
$xdata = "";
echo "\n\n";
do {
while ($line = mssql_fetch_row($rset)) {
$field_values = "";
$vkey = "";
foreach ($primary as $key => $value) {
if ($vkey != "") {
$vkey .= ":" . $line[$value];
} else {
$vkey .= $line[$value];
}
}
echo "Importing ({$zkey} " . $vkey . ")\r";
for ($inc = 0; $inc < $cnt; $inc++) {
if (mssql_field_type($rset, $inc) == "datetime") {
$xdata = "'" . date(DATE_ATOM, strtotime($line[$inc])) . "'";
} else {
$xdata = "'" . sanitize($line[$inc]) . "'";
}
if ($field_values == "") {
$field_values = "{$xdata}";
} else {
$field_values .= ", {$xdata}";
}
}
if ($p_sql != "") {
$p_sql .= ", ({$field_values})";
} else {
$p_sql .= "({$field_values})";
}
if ($radix == 500) {
mysqli_query($con, utf8_encode("insert into {$db}_{$table} ({$field_names}) values {$p_sql};"));
$radix = 0;
$p_sql = "";
}
$radix++;
$counter++;
#echo "$p_sql\n";
#mysqli_query($con, utf8_encode($p_sql));
}
} while (mssql_fetch_batch($rset));
if ($p_sql != "") {
mysqli_query($con, utf8_encode("insert into {$db}_{$table} ({$field_names}) values {$p_sql};"));
$radix = 0;
$p_sql = "";
}
$p_sql = "insert into sync_log (db, tbl, modified, synced, rows) values ('{$db}', '{$table}', '{$mssql_modified}', now(), '{$counter}');";
mysqli_query($con, utf8_encode($p_sql));
echo "\n\nTable {$db}_{$table} completed.\n\n";
}
/**
* Gets the next row from the result and assigns it to the current row
*
* @return void
*/
private function advanceCurrentRow()
{
switch ($this->database->getExtension()) {
case 'mssql':
// For some reason the mssql extension will return an empty row even
// when now rows were returned, so we have to explicitly check for this
if ($this->pointer == 0 && !mssql_num_rows($this->result)) {
$row = FALSE;
} else {
$row = mssql_fetch_assoc($this->result);
if (empty($row)) {
mssql_fetch_batch($this->result);
$row = mssql_fetch_assoc($this->result);
}
if (!empty($row)) {
$row = $this->fixDblibMSSQLDriver($row);
}
}
break;
case 'mysql':
$row = mysql_fetch_assoc($this->result);
break;
case 'mysqli':
$row = mysqli_fetch_assoc($this->result);
break;
case 'oci8':
$row = oci_fetch_assoc($this->result);
break;
case 'odbc':
$row = odbc_fetch_array($this->result);
break;
case 'pgsql':
$row = pg_fetch_assoc($this->result);
break;
case 'sqlite':
$row = sqlite_fetch_array($this->result, SQLITE_ASSOC);
break;
case 'sqlsrv':
$row = sqlsrv_fetch_array($this->result, SQLSRV_FETCH_ASSOC);
break;
case 'pdo':
$row = $this->result->fetch(PDO::FETCH_ASSOC);
break;
}
// Fix uppercase column names to lowercase
if ($row && $this->database->getType() == 'oracle') {
$new_row = array();
foreach ($row as $column => $value) {
$new_row[strtolower($column)] = $value;
}
$row = $new_row;
}
// This is an unfortunate fix that required for databases that don't support limit
// clauses with an offset. It prevents unrequested columns from being returned.
if ($row && ($this->database->getType() == 'mssql' || $this->database->getType() == 'oracle')) {
if ($this->untranslated_sql !== NULL && isset($row['flourish__row__num'])) {
unset($row['flourish__row__num']);
}
}
// This decodes the data coming out of MSSQL into UTF-8
if ($row && $this->database->getType() == 'mssql') {
if ($this->character_set) {
foreach ($row as $key => $value) {
if (!is_string($value) || strpos($key, '__flourish_mssqln_') === 0 || isset($row['fmssqln__' . $key]) || preg_match('#[\\x0-\\x8\\xB\\xC\\xE-\\x1F]#', $value)) {
continue;
}
$row[$key] = iconv($this->character_set, 'UTF-8', $value);
}
}
$row = $this->decodeMSSQLNationalColumns($row);
}
if ($this->unescape_map) {
foreach ($this->unescape_map as $column => $type) {
if (!isset($row[$column])) {
continue;
}
$row[$column] = $this->database->unescape($type, $row[$column]);
}
}
$this->current_row = $row;
}
public function fetchAll($fetch_style)
{
switch ($fetch_style) {
case self::FETCH_ARRAY:
$results = array();
while ($result = mssql_fetch_array($this->resource)) {
$results[] = $result;
}
break;
case self::FETCH_ASSOC:
$results = array();
while ($result = mssql_fetch_assoc($this->resource)) {
$results[] = $result;
}
break;
case self::FETCH_BATCH:
$results = array();
while ($result = mssql_fetch_batch($this->resource)) {
$results[] = $result;
}
break;
case self::FETCH_FIELD:
$results = array();
while ($result = mssql_fetch_field($this->resource)) {
$results[] = $result;
}
break;
case self::FETCH_OBJ:
$results = array();
while ($result = mssql_fetch_object($this->resource)) {
$results[] = $result;
}
break;
case self::FETCH_ROW:
$results = array();
while ($result = mssql_fetch_row($this->resource)) {
$results[] = $result;
}
break;
}
return $results;
}
