http://www.jforum.net/posts/list/5.page JForum - http://www.jforum.net during the migration process for 4.1 from 4.0 i have encountered several difficulties converting my UTF8 data in 4.0 (which does not support UTF-8 nativly) to 4.1 (which does support it)

i have written a small (and already tested on my production jforum installation 2.0.2) program to help anyone to rapidly transform any UTF8 containing mysql 4.0 DB schema to a working 4.1 DB schema. the program basically runs over any DB table schema and as an output, the program writes a sequence of sql statements that change all character based columns (char, varchar, text) to UTF8 based column as described in mysql documentation here:
[url]http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html[/url]

all that is left to do by you is execute 'mysql "your db name"<utf.sql'
[quote]

package test.utf;

import java.io.File;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;

/**
* @author Guy Katz
*/
public class TestAlter {

private static String dbName = "<your DB name>";
private static String driverName = "com.mysql.jdbc.Driver";
private static String username = "<your username>";
private static String password = "<your password>";
private static String url = "jdbc:mysql://localhost:3306/"+dbName+"?autoReconnect=true&useUnicode=true&characterEncoding=utf-8";

public static void main(String[]args){
//TODO get dbName, password, driverName,userName,URL from args
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
new TestAlter().migrateUTF();
}

public TestAlter(){}

public void migrateUTF(){
try{
File sqlCommandsOutputFile = new File("./utf.sql");
PrintWriter write = new PrintWriter(sqlCommandsOutputFile);
Connection con = DriverManager.getConnection(url, username, password);
DatabaseMetaData dbmd = con.getMetaData();

// Specify the type of object; in this case we want tables
String[] types = {"TABLE"};
ResultSet resultSet = dbmd.getTables(null, null, "%", types);

// Get the table names
while (resultSet.next()) {
// Get the table name
String tableName = resultSet.getString("TABLE_NAME");
// Get the table's catalog and schema names (if any)
System.out.println();
System.out.println("TABLE="+tableName);

ResultSet columnsResultSet = dbmd.getColumns(null,null,tableName,"%");
while(columnsResultSet.next()){
String colName = columnsResultSet.getString("COLUMN_NAME");
String colType = columnsResultSet.getString("DATA_TYPE");
String colSize = columnsResultSet.getString("COLUMN_SIZE");
int colIntType = Integer.valueOf(colType).intValue();
int colIntSize = Integer.valueOf(colSize).intValue();

if(colIntType==java.sql.Types.CHAR || colIntType==java.sql.Types.VARCHAR || colIntType==java.sql.Types.LONGVARCHAR){
System.out.println("COLUMN: name="+colName+" type="+colType+" size="+colSize);
changeToUtf(write,tableName,colName,colIntSize,colIntType);
}

}
System.out.println();
}

write.flush();
write.close();
con.close();
}catch (Exception e) {
e.printStackTrace();
}
}
private void changeToUtf(PrintWriter write, String tableName, String colName, int colSize, int colIntType) {
System.out.println("CHAGING TABLE="+tableName+" COLUMN="+colName);
try{
String alterSQL = "ALTER TABLE "+tableName+" MODIFY "+colName+" BINARY("+colSize+")";
write.println(alterSQL+';');
System.out.println("WRITING EXECUTE COMMAND="+alterSQL);
String utfSQL ="ALTER TABLE "+tableName+" MODIFY "+colName+" "+mapColType(colIntType,colSize)+" CHARACTER SET utf8";
write.println(utfSQL+';');
System.out.println("WRITING EXECUTE COMMAND="+utfSQL);
}catch (Exception e) {
e.printStackTrace();
}
}

private String mapColType(int colType, int colSize) {
String result = null;
if(colType==Types.CHAR){
result = "CHAR("+Integer.toString(colSize * 1)+")";
}else if(colType==Types.VARCHAR){
result = "VARCHAR("+Integer.toString(colSize * 1)+")";
}else if(colType==Types.LONGVARCHAR){
result = "TEXT";
}else{
System.out.println("ERROR!! COULD NOT MAP SQL TYPE WITH VALUE="+colType);
}
return result;
}
}

/*
private String getDoubleColSize(int colType, int colSize) {
String result = null;
if(colType==Types.CHAR){
result = Integer.toString(colSize*2);
}else if(colType==Types.VARCHAR){
result = Integer.toString(colSize*2);
}else if(colType==Types.LONGVARCHAR){
result = Integer.toString(colSize);
}else{
System.out.println("COULD NOT MAP SQL TYPE WITH VALUE="+colType);
}
return result;
}
*/


[/quote] ]]> http://www.jforum.net/posts/preList/1750/7302.page http://www.jforum.net/posts/preList/1750/7302.page GMT
http://www.jforum.net/confluence/display/upgrade/MySQL+4.0+to+4.1+UTF8+converter

Rafael]]> http://www.jforum.net/posts/preList/1750/7305.page http://www.jforum.net/posts/preList/1750/7305.page GMT i just wanted to add 2 things to maybe make things clearer:

1. the program is non intrusive meaning that it will not modify anything in your DB, dont hesitate to use it and just view the output file and see if it is what you expected.

2. the program is generic, i used it once on my jforum database and the second time on my portal database (2 seperate mysql databases) and it worked fine.
]]> http://www.jforum.net/posts/preList/1750/7307.page http://www.jforum.net/posts/preList/1750/7307.page GMT
Rafael]]> http://www.jforum.net/posts/preList/1750/7311.page http://www.jforum.net/posts/preList/1750/7311.page GMT