MySQL 4.0 to 4.1 UTF8 Converter
Guy Katz
wrote a small utility program to convert UTF-8 data from MySQL 4.0 and previous to MySQL 4.1 and newer, based on http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html.
The original message can be found at upgrading UTF data from mysql 4.0 to 4.1 script.
Below you'll find the program itself. If you need help with it, please post any questions to the previous mentioned thread, in the forum.
001 import java.io.File;
002 import java.io.PrintWriter;
003 import java.sql.Connection;
004 import java.sql.DatabaseMetaData;
005 import java.sql.DriverManager;
006 import java.sql.ResultSet;
007 import java.sql.Types;
008
009 /**
010 * @author Guy Katz
011 */
012 public class MySQL40To41 {
013
014 private static String dbName = "<your DB name>";
015 private static String driverName = "com.mysql.jdbc.Driver";
016 private static String username = "<your username>";
017 private static String password = "<your password>";
018 private static String url = "jdbc:mysql://localhost:3306/" + dbName
019 + "?autoReconnect=true&useUnicode=true&characterEncoding=utf-8";
020
021 public static void main(String[] args) {
022 // TODO get dbName, password, driverName,userName,URL from args
023 try {
024 Class.forName(driverName);
025 }
026 catch (ClassNotFoundException e) {
027 e.printStackTrace();
028 }
029 new MySQL40To41().migrateUTF();
030 }
031
032 public MySQL40To41() {
033 }
034
035 public void migrateUTF() {
036 try {
037 File sqlCommandsOutputFile = new File("./utf.sql");
038 PrintWriter write = new PrintWriter(sqlCommandsOutputFile);
039 Connection con = DriverManager.getConnection(url, username, password);
040 DatabaseMetaData dbmd = con.getMetaData();
041
042 // Specify the type of object; in this case we want tables
043 String[] types = { "TABLE" };
044 ResultSet resultSet = dbmd.getTables(null, null, "%", types);
045
046 // Get the table names
047 while (resultSet.next()) {
048 // Get the table name
049 String tableName = resultSet.getString("TABLE_NAME");
050 // Get the table's catalog and schema names (if any)
051 System.out.println();
052 System.out.println("TABLE=" + tableName);
053
054 ResultSet columnsResultSet = dbmd.getColumns(null, null, tableName, "%");
055 while (columnsResultSet.next()) {
056 String colName = columnsResultSet.getString("COLUMN_NAME");
057 String colType = columnsResultSet.getString("DATA_TYPE");
058 String colSize = columnsResultSet.getString("COLUMN_SIZE");
059 int colIntType = Integer.valueOf(colType).intValue();
060 int colIntSize = Integer.valueOf(colSize).intValue();
061
062 if (colIntType == java.sql.Types.CHAR || colIntType == java.sql.Types.VARCHAR
063 || colIntType == java.sql.Types.LONGVARCHAR) {
064 System.out.println("COLUMN: name=" + colName + " type=" + colType + " size=" + colSize);
065 changeToUtf(write, tableName, colName, colIntSize, colIntType);
066 }
067
068 }
069 System.out.println();
070 }
071
072 write.flush();
073 write.close();
074 con.close();
075 }
076 catch (Exception e) {
077 e.printStackTrace();
078 }
079 }
080
081 private void changeToUtf(PrintWriter write, String tableName, String colName, int colSize, int colIntType) {
082 System.out.println("CHANGING TABLE=" + tableName + " COLUMN=" + colName);
083 try {
084 String alterSQL = "ALTER TABLE " + tableName + " MODIFY " + colName + " BINARY(" + colSize + ")";
085 write.println(alterSQL + ';');
086 System.out.println("WRITING EXECUTE COMMAND=" + alterSQL);
087 String utfSQL = "ALTER TABLE " + tableName + " MODIFY " + colName + " " + mapColType(colIntType, colSize)
088 + " CHARACTER SET utf8";
089 write.println(utfSQL + ';');
090 System.out.println("WRITING EXECUTE COMMAND=" + utfSQL);
091 }
092 catch (Exception e) {
093 e.printStackTrace();
094 }
095 }
096
097 private String mapColType(int colType, int colSize) {
098 String result = null;
099 if (colType == Types.CHAR) {
100 result = "CHAR(" + Integer.toString(colSize * 1) + ")";
101 }
102 else if (colType == Types.VARCHAR) {
103 result = "VARCHAR(" + Integer.toString(colSize * 1) + ")";
104 }
105 else if (colType == Types.LONGVARCHAR) {
106 result = "TEXT";
107 }
108 else {
109 System.out.println("ERROR!! COULD NOT MAP SQL TYPE WITH VALUE=" + colType);
110 }
111 return result;
112 }
113 }
114
115 /*
116 * private String getDoubleColSize(int colType, int colSize) { String result = null;
117 * if(colType==Types.CHAR){ result = Integer.toString(colSize*2); }else if(colType==Types.VARCHAR){
118 * result = Integer.toString(colSize*2); }else if(colType==Types.LONGVARCHAR){ result =
119 * Integer.toString(colSize); }else{ System.out.println("COULD NOT MAP SQL TYPE WITH
120 * VALUE="+colType); } return result; }
121 */
|
