Converting from Oracle to Mysql

Below is the code you can cut paste and use for converting Oracle structures and data to mysql.
You need additional libraries

Classes12.zip oracle jdbc driver from oracle freely downloadable,
jakarta-oro.jar from apache.org and
mysql jdbc driver from mysql.com

Enjoy!

/*-----------------------------------------------------------------------------------------------------------------------------------------------------
* Program: oracle2mysql.java
* Author: Sib
* Date : 11/17/2004
* Purpose: Program to move specified schema from Oracle to mysql
* This is a quick and dirty method its not the best way but it works and gives you an idea
* of the steps involved. If you make modifications please advise so I can post them too
* You are free to use this as you please.
----------------------------------------------------------------------------------------------------------------------------------------------------*/
import java.sql.*;
import org.apache.oro.text.perl.Perl5Util;


public class oracle2mysql {

public static void main(String args[])
{
// create oracle connection
Connection OracleConnection = null;
// create mysql connection
Connection MysqlConnection = null;
String stmt = "";
int columnNumber=1;
//String columnDescription = null;
String dataType = null;
String dataScale = null;
//String ColumnString;
boolean isCreateOk;
try {
// Load the JDBC driver
DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());
// Create a connection to the database
// replace local host with your database server name also change user scott and password tiger to reflect your
// This should be turned to some parameters or something

// You need classes.zip from oracle to establish the connection find it here otn.oracle.com
OracleConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");

// Load the JDBC driver
DriverManager.registerDriver( new org.gjt.mm.mysql.Driver()); // MySQL MM JDBC driver
// Create a connection to the database
// This database should already exist with the particular user and password setup too
MysqlConnection = DriverManager.getConnection("jdbc:mysql://localhost/testdatabase", "test", "test");

System.out.println("Created oracle and mysql connections ...");
//read oracle table structures
System.out.println("Reading Oracle table structures ...");
Statement sql=OracleConnection.createStatement();
Statement columnStmt=OracleConnection.createStatement();
Statement mySql=MysqlConnection.createStatement();

ResultSet rsColumns = null;
ResultSet rsTables = sql.executeQuery("select table_name from user_tables");

while (rsTables.next())
{
// map the column types
System.out.println("Mapping types...");
stmt = "select column_name, data_type, decode(nullable,'N','not null',' ') NULLABLE, data_length,nvl(data_scale,0) DATA_SCALE, DATA_PRECISION from cols where table_name='" + rsTables.getString("TABLE_NAME")+"'";
rsColumns = columnStmt.executeQuery(stmt);
columnNumber=1;

StringBuffer sbCreTable = new StringBuffer();
StringBuffer sbColumnList = new StringBuffer();

sbCreTable.append("CREATE TABLE ")
.append(rsTables.getString("TABLE_NAME"))
.append(" (");

while (rsColumns.next())
{
StringBuffer sbColumnDesc = new StringBuffer();
if (rsColumns.getString("DATA_TYPE").equals("NUMBER") && rsColumns.getInt("DATA_SCALE")<=0) dataType="INTEGER"; else if (rsColumns.getString("DATA_TYPE").equals("NUMBER") && rsColumns.getInt("DATA_SCALE")>0) dataType="FLOAT";
else if (rsColumns.getString("DATA_TYPE").equals("VARCHAR2"))
{
dataType="VARCHAR";
if (dataType=="VARCHAR" && rsColumns.getInt("DATA_LENGTH") > 255) dataType="BLOB";
}
else if (rsColumns.getString("DATA_TYPE").equals("LONG")) dataType="BLOB";
else if (rsColumns.getString("DATA_TYPE").equals("DATE")) dataType="DATETIME";
if (dataType=="DATETIME" || dataType=="BLOB")
sbColumnDesc.append(rsColumns.getString("COLUMN_NAME"))
.append(" ")
.append(dataType)
.append(" ")
.append(rsColumns.getString("NULLABLE"));
else
{
if (rsColumns.getInt("DATA_SCALE")==0)
{
sbColumnDesc.append(rsColumns.getString("COLUMN_NAME"))
.append(" ")
.append(dataType)
.append("(")
.append(rsColumns.getInt("DATA_LENGTH"))
.append(") ")
.append(rsColumns.getString("NULLABLE"));
}
else
sbColumnDesc.append(rsColumns.getString("COLUMN_NAME"))
.append(" ")
.append(dataType)
.append("(")
.append(rsColumns.getString("DATA_PRECISION"))
.append(",")
.append(rsColumns.getInt("DATA_SCALE"))
.append(") ")
.append(rsColumns.getString("NULLABLE"));
}
if (columnNumber == 1)
{
sbCreTable.append(sbColumnDesc);
// get column name
sbColumnList.append(rsColumns.getString("COLUMN_NAME"));
}
else
{
sbCreTable.append(", ")
.append(sbColumnDesc);
// get column name
sbColumnList.append(", ");
sbColumnList.append(rsColumns.getString("COLUMN_NAME"));
}

columnNumber++;
} // while (rsColumns.next())
sbCreTable.append(")");

// drop and create mysql table structure

try {
//isCreateOk =mySql.execute("DROP TABLE "+ rsTables.getString("TABLE_NAME"));
isCreateOk = mySql.execute(sbCreTable.toString());

}
catch (SQLException e)
{
System.out.println(e);
System.out.println(sbCreTable.toString());

}
insertRows(rsTables.getString("TABLE_NAME"),sbColumnList,OracleConnection,MysqlConnection);
// read oracle index structure
System.out.println("going for next table");
// create mysql index structure

} //while (rsTables.next())

rsTables.close();
//close connections

System.out.println("Closing connections ...");
OracleConnection.close();
MysqlConnection.close();
System.out.println("Connections closed - Goodbye!");

} catch (SQLException e) {
// some problem oocured at the database
System.out.println(e.getMessage());
}
}
public static void insertRows(String tableName, StringBuffer sbColumnList, Connection OracleConnection, Connection MysqlConnection)
{
try {
Statement oraStatement=OracleConnection.createStatement();
Statement mySqlStatement=MysqlConnection.createStatement();
Perl5Util regExpression = new Perl5Util();
StringBuffer sbQuery = new StringBuffer();
sbQuery.append("SELECT ")
.append(sbColumnList)
.append(" FROM ")
.append(tableName);

// Copy data
System.out.println("Copying " + tableName +" data from oracle ...");

try {
ResultSet rsRows = oraStatement.executeQuery(sbQuery.toString());
rsRows.setFetchSize(100);
ResultSetMetaData rsRowsMeta = rsRows.getMetaData();
while(rsRows.next())
{
StringBuffer sbInsertLine = new StringBuffer();
sbInsertLine.append("INSERT INTO ")
.append(tableName)
.append(" ( ")
.append(sbColumnList)
.append(" ) values (");
// Extract fields from the recordset
for(int i = 1;i <=rsRowsMeta.getColumnCount(); i++)
{
if (i==1)
{
//Determine whether to put quotation marks around the field
if(rsRows.getObject(i)!=null && (rsRowsMeta.getColumnType(i)==java.sql.Types.VARCHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.CHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.TIMESTAMP))
{
sbInsertLine.append("'")
// search object content for quotation marks and remove
.append(regExpression.substitute("s/\'//g",rsRows.getString(i)))
.append("'");
}
else
{
sbInsertLine.append(rsRows.getObject(i));
}

}
else
{
if(rsRows.getObject(i)!=null && (rsRowsMeta.getColumnType(i)==java.sql.Types.VARCHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.CHAR
|| rsRowsMeta.getColumnType(i)==java.sql.Types.TIMESTAMP))
{
sbInsertLine.append(", ")
.append("'")
//.append(rsRows.getObject(i))
.append(regExpression.substitute("s/\'//g",rsRows.getString(i)))
.append("'");
}
else
{
sbInsertLine.append(", ")
.append(rsRows.getObject(i));
}
}
}
sbInsertLine.append(")");
// Execute the generated string
System.out.println(sbInsertLine.toString());

mySqlStatement.executeUpdate(sbInsertLine.toString());
mySqlStatement.executeUpdate("commit");
}
//close the resultset
rsRows.close();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}
}

Red Hat Fedora core 3

After tinkering with FreeBSD 5.3 for a little bit I switched to my new Fedora Core 3 box and this is currently hosting my website until I get real problems with it. I am more familiar with RH and also I like the threading there especially with regards to java so now I am running Fedora Core 3 for my laptop and servers.

I will be putting up some draft java source for copying tables and data from Oracle to Mysql. Please feel free to modify and share this code I have used it to just copy tables and the data into my own instances of mysql try it out it should be fun! I will add some more features if enough interest is shown , index structures, stored procedures and functions for mysql 5.

Java on freeBSD 5.3

I am running freeBSD on one of my servers but I uncomfortable with the hoops you have to go through to set java up on this platform. I would like to deploy some java applications on the freeBSD and I am hoping that the threading issues that used are gone we will see.

I am thinking of going BSD with my desktop too. The only problem I think I will have with BSD is that I will no longer have fun trying to figure out what is wrong with the system because this thing runs solid, all I can say is impressive.