Find a good way to convert from SQL Server to Mysql database _mysql

At the beginning of the year has been doing a Web site MSSQL2000-> MySQL5 Migration work because of the use of different program systems, so the main problem in data migration. Because of the 2 system database structure is very large, not convenient to take the SQL Server to import MySQL ODBC data source features (also not recommended, field types, such as different points will kill the ~), so in Windows to write their own PHP script from SQL Server read data, Then in accordance with the needs of the new system after the insertion of MySQL inside, flexible and more convenient. The actual process mainly has the following questions:

1, the database connection, the main is to connect SQL SERVER. There are mainly 3 ways:
1.1 Using the Mssql_ series function in PHP, this is similar to using the Mysql_ series function, but to open the associated extension (Extension=php_mssql.dll) in php.ini.
1.2 Using ODBC connection, because of the abstraction of a specific database, so there is no way to use the Data table field name => array key name characteristics, for specific applications is not very convenient, code form:

$conn = Odbc_connect ("DataSource", "username", "password");
$sql = "SELECT * FROM News";
$cur = Odbc_exec ($conn, $sql);
while (Odbc_fetch_row ($cur)) {
$field 1 = Odbc_result ($cur, 1);
$field 2 = Odbc_result ($cur, 2);
Do something
}
1.3 Use PDO-PHP5 to add data Object Abstraction layer, as the official data access interface, there are many advantages, such as supporting parameter binding to prevent SQL injection, for different databases loaded with different drivers, the program code is consistent, easy to transplant and so on, I believe it should be the general trend. However, due to the use of PHP5 new object-oriented features, the need for PHP5 support, 5.1 can be used directly, 5 need to install PECL, in addition to modify php.ini, add: Extension=php_pdo_mysql.dll and Extension=php_ Pdo_mssql.dll, the actual code is as follows:

try {
$DBH =new PDO ("Mssql:dbname=xxx;host=localhost"),
"Root", "password");//connect to DB
catch (Pdoexception $e) {
Print "error!:". $e->getmessage ();//error message
Die ();
}
$stmt = $DBH->prepare ("SELECT * from News");//stmt Handle $stmt
if ($stmt->execute ()) {
while ($row = $stmt->fetch ()) {
Do something
}
}
$stmt 2 = $mssql->prepare ("INSERT into News"
(Title,author) VALUES (: Title,: Author) ");
$stmt 2->bindparam (': Title ', $title);
$stmt 2->bindparam (': Author ', $author);
$stmt 2->execute ();
$DBH = null;//Close connection here to remind you that there is no MySQL inside the limit this syntax.

2, the text field is truncated problem.
Above tried 3 kinds of connection database method, because the original after the select after the MSSQL data is always only 4 K length, thought is the connection way limit caused, so change a few are so, finally check the data just know, is php.ini inside these 2 sentence configuration problem:
; Valid Range 0-2147483647. Default = 4096.
Mssql.textlimit = 4096

; Valid Range 0-2147483647. Default = 4096.
Mssql.textsize = 4096
Change 4096 to 1 (for unrestricted), or you can use Mssql_query ("SET textsize 65536") to implement it.

3. Two types of database fields different problems have 2 places to talk about, one is the maximum length to support the field to avoid inserting the database is truncated, the other is a date format problem, I prefer to use the Unix timestamp. In even MSSQL time can use "select Unix_timestamp (created) from news" to implement the DateTime in the MySQL inside the time stamp conversion. However, when migrating all the fields in the table at one time, there is no simple "select * from news" approach like the above, and you need to list all the fields. The actual can be directly select out, get a string, such as in the MSSQL inside is 2006-01-01 12:01, take out the string is "20,061 months 01 12:01" (Some strange, do not know why it will produce Chinese). Use the following function to convert to a timestamp:

function ConvertTime ($timestring) {
if ($timestring = = null) {
return 0;
}
$time = Explode ("", $timestring);
$year = $time [0];
Switch ($time [1]) {
Case "January": $month = "1";
Case "February": $month = "2";
Case "March": $month = "3";
Case "April": $month = "4";
Case "May": $month = "5";
Case "June": $month = "6";
Case "July": $month = "7";
Case "August": $month = "8";
Case "September": $month = "9";
Case "October": $month = "ten";
Case "November": $month = "one";
Case "December": $month = "a";
Default:break;
}
$day = $time [2];
$h = 0;
$m = 0;
$s = 0;
if (!empty ($time [3])) {
$time 2 = Explode (":", $time [3]);
$h = $time 2[0];
$i = $time 2[1];
}
Return Date ("Y-m-d h:i:s", Mktime ($h, $i, $s, $month, $day, $year));
Return Mktime ($h, $i, $s, $month, $day, $year);
The final conversion script can be executed in the cmd window with Php.exe abc.php, which is not timed out and is suitable for migrating large quantities of data.
Basically this is the content, hope to be helpful to everybody.

Update (06/05/05): About time stamp conversion, add in php.ini
Mssql.datetimeconvert = Off
Then you can get a format similar to 2006-01-01 12:01 without Chinese.