Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

upgrading UTF data from mysql 4.0 to 4.1 script

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, if you register for an account in Confluence, I can give you rights to change stuff there!

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hello;
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:
http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html

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



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;
}
*/


:idea:
[originally posted on jforum.net by gkatz]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Great! I added it to Confluence:

http://www.jforum.net/confluence/display/upgrade/MySQL+4.0+to+4.1+UTF8+converter

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thanks;
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.

[originally posted on jforum.net by gkatz]
 
I was her plaything! And so was this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic