ODBC to MySQL

Source: Internet
Author: User
Tags filter foreach array odbc mysql split sprintf table name
Mysql|odbc <?php
/**
* ODBC to MySQL
* Xu Zuning (nagging)
* czjsz_ah@stats.gov.cn
*
* First contact with php+mysql friends, there are many are the master of ASP. As soon as possible to get started
* Close-up of this program, used to convert the original database through the ODBC data source to MySQL
* In fact, there are many such procedures and there are many in the elite areas.
* This procedure is characterized by the need to know the data source name and MySQL library name can be. Program will
* Work automatically according to the tables in the data source.
*
*/
Set_time_limit (0);

$DSN = "Tjtz"; Data Source Name
$dsn _name = ""; Data source user Name
$dsn _pass = ""; Data source Password

$sql = "Tjtz"; MySQL library name, usually available data source name
$sql _host = ""; MySQL server name
$sql _name = ""; MySQL User name
$sql _pass = ""; MySQL Password

$ODBC = Odbc_connect ($dsn, $dsn _name, $dsn _pass);
$result = Odbc_tables ($ODBC);

$fields = Odbc_num_fields ($result);

if (! function_exists ("Odbc_fetch_array")) {
Check the existence of function Odbc_fetch_array, if not defined
The manual says there's this function, but there's definitely no 4.1.2.
Note that an array using the Odbc_fetch_into back string can only be accessed using the subscript
Function Odbc_fetch_array (& $result) {
$ar = Array ();
Odbc_fetch_into ($result, $ar);
return $ar;
}
}
To get a table in a library
while ($ar [] = Odbc_fetch_array ($result));
Defining filter functions
function Filter ($var) {
Return ($var [3] = = "TABLE");
}
Filter out user tables
$ar = Array_filter ($ar, "filter");
foreach ($ar as $tab) {
$tables [] = $tab [2];
}
Generate a Build table document, if present, do not execute
if (! file_exists ("$sql. sql")) {
Extract field information from each table
$fp = fopen ("$sql. sql", "w");
foreach ($tables as $table) {
$result = Odbc_do ($ODBC, "select * from $table");
$str = sprintf ("CREATE table%s", $table);
for ($i =0; $i <odbc_num_fields ($result); $i + +) {
if (eregi ("DOUBLE", Odbc_field_type ($result, $i + 1)) {
$s = sprintf ("%s%s%s"
, ($i >0? ",": ")
, Odbc_field_name ($result, $i + 1)
, Odbc_field_type ($result, $i + 1)
);
}else if (eregi ("COUNTER", Odbc_field_type ($result, $i + 1)) {
$s = sprintf ("%s%s TINYINT (%s) auto_increment PRIMARY KEY"
, ($i >0? ",": ")
, Odbc_field_name ($result, $i + 1)
, Odbc_field_len ($result, $i + 1)
);
}else {
$s = sprintf ("%s%s%s (%s)"
, ($i >0? ",": ")
, Odbc_field_name ($result, $i + 1)
, Odbc_field_type ($result, $i + 1)
, Odbc_field_len ($result, $i + 1)
);
}
$str. = $s;
}
$str. = "); \ n";
Fputs ($fp, $STR);
}
Fclose ($FP);
echo "has produced a table-table file $sql.sql. <br> ";
echo Please check that the command is correct. If you do not need to rebuild the table, change the following if (1) to if (0) <br> ";
echo "Do not change the table name, to increase the field, please put it in the back!" ";
echo "Confirm and rerun this program!" ";
Odbc_close ($ODBC);
Exit
}

if (1) {//If you do not need to rebuild the table, change if (1) to if (0)
echo "began to build table ...<br>";
$mysql = mysql_connect ();
mysql_drop_db ($sql);
mysql_create_db ($sql);
mysql_select_db ($sql);
$fp = fopen ("$sql. sql", "R");
$buffer = Fread ($fp, FileSize ("$sql. sql"));
Fclose ($FP);
$ar = Split ("\ n", $buffer);
foreach ($ar as $query) {
if (Trim ($query)!= "") {
echo "$query <br>";
mysql_query ($query, $mysql);
}
}
}

Extracting table information from a table-building file
$info = Array ();
$s = File ("$sql. sql");
foreach ($s as $value) {
if (eregi ("Create +table + ([a-z0-9_-]+) *[\ (] (. +) [\)]", $value, $regs)) {
$table = $regs [1];
$info [$table] = array ();
$ar = Split (",", $regs [2]);
foreach ($ar as $v) {
SSCANF ($v, "%s%s", $p, $p);
if (eregi ("Double|count|int", $p)) {//These types do not need to be added ', and those?
$info [$table] = "";
}else {
$info [$table] = "'";
}
}
}
}

$mysql = mysql_connect ($sql _host, $sql _name, $sql _pass);
mysql_select_db ($sql);

Echo <br> is transferring data from DSN to MySQL ... ";
foreach ($info as $key => $ar) {
$query = "SELECT * from $key"; Generate an ODBC query
$result = Odbc_do ($odbc, $query);
$mode = true;
while ($rs = Odbc_fetch_array ($result)) {
$sql _query = "INSERT INTO $key values (";
for ($i =0; $i <count ($rs); $i + +) {
if ($i > 0) $sql _query. = ",";
$sql _query. = "{$ar [$i]}{$rs [$i]}{$ar [$i]}";
}
$sql _query. = ")";
mysql_query ($sql _query, $mysql);
}
}

Odbc_close ($ODBC);
echo "<br> operation over";
?>


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.