This program is in pretty rough shape, it evolved many times over, but i used it to migrate from a 2.5 Gig Cloudscape 4 database to Derby.
Worked fine last time I used it. YMMV
You run it once to export from some database using JDBC to the file system, e.g. without the -imp arg and pass in a file with the names of tables.
java ImpExpCloudSync -writeDerbyFormat -driver ...mysql.driver... -jdbcUrl jdbc:mysql:<etc> tablesInAFile exp.log
under the expdata/ dir will be files of the name <tablename>.dat
You then load these files into Derby using the ultrafast bulkImport (I once measured a 800% gain in performance, over row at a time Prepared inserts, a few years back, NOTE requires empty tables at the start):
http://incubator.apache.org/derby/manuals/tools/tools90.html#HDRSII-IMPORT-57005
java ImpExpCloudSync -imp tablesInAFile imp.log
You probably need to change/comment out patchDateType() as it was fixing illegal years from the old database, etc into the legal range for Derby.
The schema I had had columns of email pasted into them, and had every control character, quote, that you could imagine, embedded into the large columns, and I didn't have any problems loading with this tool.
you can pass a file containing the schema and it will IJ the commands to create your tables, first.
java ImpExpCloudSync -imp -schema mySqlSchema.sql tablesInAFile imp.log
check out parseArgs() as my usage() method doesn't print out all the options.
HTH. I had some out of memory errors on some huge tables, but using -Xm1024m, etc was able to get around them.
java ImpExpCloudSync -Z
Bad flag -Z
java ImpExpCloudSync [ -imp ] [ -driver jdbcdriver ] [ -jdbcUrl url ] \
[ -user u ] [ -password p ] [ -group grp ] \
[ -schema sqlfile ] \
[ -writeDerbyFormat ] \
table_names outfile
defaults to export
driver defaults to org.apache.derby.jdbc.EmbeddedDriver
jdbcurl defaults to jdbc:derby:c:/work/dbs/derbysource;create=true
user defaults to user
password defaults to password
writeDerbyFormat defaults to false
Christian Rodriguez <[EMAIL PROTECTED]>
03/22/2005 06:43 PM
Please respond to
"Derby Discussion"
To
Derby Discussion <derby-user@db.apache.org>
cc
Subject
Importing data from MySQL
Hello everyone,
I am trying to import data from a MySQL database. So far I havent been
able to do it.
What I have tried:
1. Ive used the mysql dump utility to generate a file with inserts and
then try to run that file from ij. One of my tables contain a large
"text" object and the inserts fail, probably because of quote or
double quote characters.
2. Ive written a basic java program that uses jdbc to connect to MySQL
and the Derby database and tries to import the data. The program fails
with an out of error message when trying to import that problematic
table that contains large character objects.
3. Ive tried to use db2db migration tool. It fails with the same
error: out of memory.
So, has anyone been able to do this kind of thing? What do you
recommend? Any hints, help, suggestions?
Thanks,
Christian
import java.util.zip.*; import java.io.*; import java.util.Date; import java.util.Vector; import java.util.Hashtable; import java.util.Enumeration; import java.util.StringTokenizer;
import java.text.SimpleDateFormat;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.lang.reflect.Array;
import java.lang.reflect.Method;
import java.lang.reflect.InvocationTargetException;
/**
*/
public class ImpExpCloudSync
{
private static final boolean Debug=false;
private Connection conn;
private String datadir = "expdata";
private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
private String jdbcUrl =
"jdbc:derby:c:/work/dbs/derbysource;create=true";
//private String driver = "com.ibm.db2.jcc.DB2Driver";
//private String jdbcUrl =
"jdbc:db2j:net://localhost:1527//work/dbs/msio.51";
private String schemaId = "APP";
private String user = "user";
private String password = "password";
private String outFile = "impexp.log";
private String tablesFile = "table_names";
private boolean readingPublicationDef = false;
private SimpleDateFormat formatter = null;
private boolean imp = false;
private boolean zip = false;
private boolean unzip = false;
private String schemaFile = null;
private String controlFile = null;
private String zipFileName = null;
private boolean writeDerbyFormat = false;
private static final String IJ = "com.ibm.db2j.tools.ijImpl.Main";
public static void main (String[] args)
{
ImpExpCloudSync example = new ImpExpCloudSync();
try
{
example.runApp(args);
}
catch (Exception e)
{
System.out.println("Failed " + e.toString());
// e.printStackTrace();
}
}
public void runApp(String[] args)
throws Exception
{
parseArgs(args);
if (imp)
importData();
else if (zip)
zipData(zipFileName);
else if (unzip)
unZipFile(zipFileName);
else
{
exportData();
}
}
public void exportData()
throws Exception
{
formatter = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss.S");
try
{
Class.forName(driver).newInstance();
}
catch (Exception e)
{
System.err.println("Could not load driver - please check your
CLASSPATH " + driver + " " + e.getMessage());
return;
}
try
{
conn = DriverManager.getConnection(jdbcUrl, user,
password);
}
catch (SQLException sqle)
{
System.out.println("Can't create connection to " +
jdbcUrl);
do
{
System.out.println("error: " + sqle.toString());
sqle = sqle.getNextException();
}
while (sqle != null);
return;
}
PrintWriter pw = createOutputFile(outFile);
Vector tablesV = readTables(tablesFile);
Statement stmt = null;
for (int t = 0; t < tablesV.size(); t++)
{
String tbl = (String) tablesV.elementAt(t);
try
{
if (stmt == null)
stmt = conn.createStatement();
Date currentTime_1 = new Date();
pw.println(formatter.format(currentTime_1) +
" start " + tbl);
String cmd;
/**************************************************************
Cannot write compatible formatted file with CS4/5 EXPORT
for CS 8 import
**************************************************************/
if (writeDerbyFormat)
{
cmd = "select * from " + tbl;
}
else
{
if (controlFile == null)
cmd = "CALL
FileExport::Export(getCurrentConnection(), "
+ "'APP." + tbl + "',
'" + datadir
+ "/" + tbl + ".dat')";
else
cmd = "CALL
FileExport::Export(getCurrentConnection(), "
+ "'APP." + tbl + "',
'" + datadir
+ "/" + tbl + ".dat',
'" + controlFile + "')";
}
if (Debug)
pw.println(cmd);
ResultSet rs = stmt.executeQuery(cmd);
writeTableToFile(rs, tbl, pw);
rs.close();
Date currentTime_2 = new Date();
pw.println(formatter.format(currentTime_2) + "
end " + tbl);
pw.println(tbl);
}
catch (SQLException e)
{
printSQLExceptions(e);
}
}
pw.close();
}
private void writeTableToFile(ResultSet rs, String tableName,
PrintWriter logPw)
throws IOException, SQLException
{
String charDelimiter = "\"";
// open output file
PrintWriter oPw = null;
try
{
logPw.println(datadir + "/" + tableName + ".dat");
oPw = createOutputFile(datadir + "/" + tableName +
".dat");
}
catch (Exception e)
{
throw new IOException(e.toString());
}
ResultSetMetaData rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();
// write resultset to output file.
while (rs.next())
{
for (int c = 1; c <= numCols; c++)
{
boolean bool, escapeQuote, dateType;
String val;
int type = rsmd.getColumnType(c);
bool = false;
dateType = false;
escapeQuote = false;
switch (type)
{
case Types.DATE:
case Types.TIME:
case Types.TIMESTAMP:
dateType = true;
break;
case Types.CHAR:
case Types.VARCHAR:
case Types.LONGVARCHAR:
escapeQuote = true;
break;
case -7:
// case Types.BOOLEAN:
bool = true;
break;
}
// no boolean support, map to smallint
if (bool)
{
boolean v = rs.getBoolean(c);
if (v)
val = "1";
else
val = "0";
}
else if (dateType)
val = patchDateType(rs, c, type);
else
val = rs.getString(c);
if (rs.wasNull())
{
if (c < numCols)
oPw.write(",");
continue;
}
if (escapeQuote)
{
// CHAR columns
val = doubleQuoteQuote(val,
charDelimiter);
val = charDelimiter + val +
charDelimiter;
if (c < numCols)
val += ",";
oPw.write(val);
}
else
{
// non-CHAR columns
if (c < numCols)
val += ",";
oPw.print(val);
}
}
oPw.println();
}
oPw.close();
}
private String doubleQuoteQuote(String inputString, String
charDelimiter)
{
int start = inputString.indexOf(charDelimiter);
// if delimeter is not found inside the string nothing to do
if (start < 0)
return inputString;
// escape delimiter
StringBuffer result = new StringBuffer(inputString);
int delLength = charDelimiter.length();
while (start != -1)
{
//insert delimter character
result = result.insert(start, charDelimiter);
int current = start + delLength + 1 ;
start = result.toString().indexOf(charDelimiter,
current);
}
return result.toString();
}
private String patchDateType(ResultSet rs, int c, int type)
throws SQLException
{
String val = rs.getString(c);
if (rs.wasNull())
return null;
StringTokenizer tok = new StringTokenizer(val, "-: ");
int firstMin, firstMax;
if (type == Types.DATE || type == Types.TIMESTAMP)
{
firstMin = 1900;
firstMax = 3500;
}
else // if (type == Types.TIME)
{
firstMin = 0;
firstMax = 24;
}
if (!tok.hasMoreTokens())
return val;
String firstNumber = tok.nextToken();
int first = 0;
try
{
first = Integer.parseInt(firstNumber);
}
catch (NumberFormatException nfe)
{
return fixedDateType(val, type);
}
if (first < firstMin || first > firstMax)
return fixedDateType(val, type);
return val;
}
private String fixedDateType(String val, int type)
{
System.out.println("PATCH datetime " + val);
long now = System.currentTimeMillis();
switch (type)
{
case Types.DATE:
return new java.sql.Date(now).toString();
case Types.TIME:
return new java.sql.Time(now).toString();
case Types.TIMESTAMP:
return new java.sql.Timestamp(now).toString();
}
return "1953-05-27";
}
public void importData()
throws Exception
{
formatter = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss.S");
try
{
Class.forName(driver).newInstance();
}
catch (Exception e)
{
System.err.println("Could not load driver - please check your
CLASSPATH " + driver + " " + e.toString());
return;
}
/*
Load the schema if a file was specified
*/
if (schemaFile != null)
loadSchema();
try
{
conn = DriverManager.getConnection(jdbcUrl, user,
password);
}
catch (SQLException sqle)
{
System.out.println("Can't create connection to " +
jdbcUrl);
printSQLExceptions(sqle);
return;
}
Vector tablesV = readTables(tablesFile);
Statement stmt = null;
/*
Need to create the database, load the schema
disable constraints and triggers
*/
try
{
stmt = conn.createStatement();
}
catch (SQLException sqle)
{
System.out.println("set constraints all disabled
failed");
printSQLExceptions(sqle);
throw sqle;
}
for (int t = 0; t < tablesV.size(); t++)
{
String tbl = (String) tablesV.elementAt(t);
try
{
Date currentTime_1 = new Date();
System.out.println(formatter.format(currentTime_1) +
" start " + tbl);
String cmd = "CALL
SYSCS_UTIL.SYSCS_IMPORT_TABLE ("
+ "'" + schemaId + "', '"
+ tbl + "', '" + datadir + "/" + tbl +
".dat', null, "
+ "null, null, 1)";
System.out.println(cmd);
stmt.execute(cmd);
Date currentTime_2 = new Date();
System.out.println(formatter.format(currentTime_2) +
" end " + tbl);
}
catch (SQLException sqle)
{
System.out.println("Failed on " + tbl);
printSQLExceptions(sqle);
sqle.printStackTrace();
}
catch (Exception e)
{
System.out.println("Failed on " + tbl);
e.printStackTrace();
}
}
}
private void parseArgs(String[] args)
throws Exception
{
if (args.length < 1)
{
usage("Too few arguments");
}
int posArg = 1;
for (int a = 0; a < args.length; a++)
{
if (args[a].charAt(0) == '-')
{
String sel = args[a].substring(1);
if (sel.equalsIgnoreCase("imp"))
{
imp = true;
}
else if
(sel.equalsIgnoreCase("writeDerbyFormat"))
{
writeDerbyFormat = true;
System.out.println("writeDerbyFormat is
"
+ writeDerbyFormat);
}
else if (sel.equalsIgnoreCase("zip"))
{
zip = true;
if (++a >= args.length)
usage ("Must supply file name
with -zip flag");
zipFileName = args[a];
System.out.println("zipFileName is " +
zipFileName);
}
else if (sel.equalsIgnoreCase("unzip"))
{
unzip = true;
if (++a >= args.length)
usage ("Must supply file name
with -zip flag");
zipFileName = args[a];
System.out.println("zipFileName is " +
zipFileName);
}
else if (sel.equalsIgnoreCase("ctlfile"))
{
if (++a >= args.length)
usage ("Must supply file name
with -ctlfile flag");
controlFile = args[a];
System.out.println("controlFile is " +
controlFile);
}
else if (sel.equalsIgnoreCase("schema"))
{
if (++a >= args.length)
usage ("Must supply file name
with -schema flag");
schemaFile = args[a];
System.out.println("schemaFile is " +
schemaFile);
}
else if (sel.equalsIgnoreCase("schemaid"))
{
if (++a >= args.length)
usage ("Must supply name with
-schemaid flag");
schemaId = args[a];
System.out.println("schemaId is " +
schemaId);
}
else if (sel.equalsIgnoreCase("user"))
{
if (++a >= args.length)
usage ("Must supply user name
with -user flag");
user = args[a];
System.out.println("user is " + user);
}
else if (sel.equalsIgnoreCase("password"))
{
if (++a >= args.length)
usage ("Must supply password
with -password flag");
password = args[a];
System.out.println("password is " +
password);
}
else if (sel.equalsIgnoreCase("driver"))
{
if (++a >= args.length)
usage ("Must supply value with
-driver flag");
driver = args[a];
System.out.println("driver is " +
driver);
}
else if (sel.equalsIgnoreCase("jdbcurl"))
{
if (++a >= args.length)
usage ("Must supply value with
-jdbcurl flag");
jdbcUrl = args[a];
System.out.println("jdbcUrl is " +
jdbcUrl);
}
else if (sel.equalsIgnoreCase("datadir"))
{
if (++a >= args.length)
usage ("Must supply value with
-datadir flag");
datadir = args[a];
System.out.println("datadir is " +
datadir);
}
else
usage("Bad flag -" + sel);
}
else if (posArg == 1)
{
tablesFile = args[a];
System.out.println("tablesFile " + tablesFile);
posArg++;
}
else if (posArg == 2)
{
outFile = args[a];
System.out.println("outfile " + outFile);
posArg++;
}
}
}
private void closeStatement(Statement stmt)
{
if (stmt == null)
return;
try
{
stmt.close();
}
catch (SQLException sqle) { }
}
private void printSQLExceptions(SQLException sqle)
{
for (; sqle != null; sqle = sqle.getNextException())
System.out.println("Error: " + sqle.toString());
}
private void usage(String msg) throws Exception
{
System.out.println(msg);
String usageMsg = "\n\tjava ImpExpCloudSync "
+ "[ -imp ] [ -driver jdbcdriver ] [ -jdbcUrl url ] \\ \n"
+ "[ -user u ] [ -password p ] [ -group grp ]
\\ \n"
+ "[ -schema sqlfile ] \\ \n"
+ "[ -writeDerbyFormat ] \\ \n"
+ " table_names outfile\n\n";
System.out.println(usageMsg);
System.out.println("defaults to export");
System.out.println("driver defaults to " + driver);
System.out.println("jdbcurl defaults to " + jdbcUrl);
System.out.println("user defaults to " + user);
System.out.println("password defaults to " + password);
System.out.println("writeDerbyFormat defaults to " +
writeDerbyFormat);
throw new Exception(usageMsg);
}
private PrintWriter createOutputFile(String outputFile) throws Exception
{
FileOutputStream stream = null;
try
{
stream = new FileOutputStream(outputFile);
}
catch (Exception e)
{
throw new Exception("Could not open output file: " +
outputFile
+ "\n\t" + e.toString());
}
return new PrintWriter(stream);
}
private Vector readTables(String name)
throws IOException
{
System.out.println(" readTables");
BufferedReader in = new BufferedReader(new FileReader(name));
Vector v = new Vector(100);
String s;
while ((s = in.readLine()) != null)
{
s = s.trim().toUpperCase();
if (!imp && readingPublicationDef)
{
if (!s.startsWith("ADD TABLE"))
continue;
if (s.endsWith(";"))
s = s.substring(0, s.length() - 1);
System.out.println("table " + s);
int c = s.lastIndexOf(' ');
if (c < 0)
continue;
s = s.substring(c + 1);
}
System.out.println("Add table '" + s + "'");
v.addElement(s);
}
return v;
}
private void loadSchema()
throws Exception
{
Method ijMainmain = null;
String [] strArgs = new String[1];
Class ijClass = null;
try
{
String impl = IJ;
ijClass = Class.forName(impl);
Class strArgsClass = strArgs.getClass();
Class classArgs[] = {strArgsClass};
ijMainmain = ijClass.getMethod("main", classArgs);
}
catch (Exception e)
{
System.err.println("Could not load " + IJ + " - please check your
CLASSPATH " + e.toString());
return;
}
if (ijMainmain != null)
{
try
{
// reset is static, no need for the class
instance arg
System.setProperty("database", jdbcUrl);
// strArgs[0] = "-Ddatabase=\"" + jdbcUrl +
"\"";
strArgs[0] = schemaFile;
Object resetArgs[] = {strArgs};
ijMainmain.invoke(ijClass, resetArgs);
}
catch (InvocationTargetException ite)
{
System.out.println(" ITE: Failed to invoke " +
ite.toString());
ite.printStackTrace();
}
catch (Throwable t)
{
System.out.println(" T: Failed to invoke " +
t.toString());
t.printStackTrace();
}
}
}
private void zipData(String zipFileName)
{
ZipOutputStream out = null;
Vector tablesV = null;
try
{
tablesV = readTables(tablesFile);
// Create the ZIP file
out = new ZipOutputStream(new
FileOutputStream(zipFileName));
}
catch (IOException e)
{
System.out.println("Could not create zip file " +
zipFileName
+ " " + e.toString());
return;
}
// Create a buffer for reading the files
byte[] buf = new byte[8 + 1024];
for (int t = 0; t < tablesV.size(); t++)
{
String tbl = (String) tablesV.elementAt(t);
tbl = datadir + "/" + tbl + ".dat";
try
{
FileInputStream in = new FileInputStream(tbl);
// Add ZIP entry to output stream.
out.putNextEntry(new ZipEntry(tbl));
// Transfer bytes from the file to the ZIP file
int len;
while ((len = in.read(buf)) > 0) {
out.write(buf, 0, len);
}
// Complete the entry
out.closeEntry();
in.close();
System.out.println("Zipped " + tbl);
}
catch (IOException e)
{
System.out.println("failed to zip " + tbl + " "
+ e.toString());
return;
}
}
try
{
// Complete the ZIP file
out.close();
}
catch (IOException ioe)
{
System.out.println("Can't close zip file " +
ioe.toString());
}
listZipFile(zipFileName);
}
private void listZipFile(String zipName)
{
System.out.println(" LIST ZIP FILE");
try {
// Open the ZIP file
ZipFile zf = new ZipFile(zipName);
// Enumerate each entry
for (Enumeration entries = zf.entries();
entries.hasMoreElements();)
{
// Get the entry name
String zipEntryName =
((ZipEntry)entries.nextElement()).getName();
}
}
catch (IOException ioe)
{
System.out.println("List zip failed "
+ zipName + " " + ioe.toString());
}
System.out.println(" DONE LIST ZIP FILE");
}
private void unZipFile(String zipName)
{
byte[] buf = new byte[10 * 1024];
try {
// Open the ZIP file
ZipInputStream in = new ZipInputStream(new
FileInputStream(zipName));
while (true)
{
// Get the next entry
ZipEntry entry = in.getNextEntry();
if (entry == null)
break;
String entryName = entry.getName();
System.out.println("entryName " + entryName);
// Open the output file
String outFilename = entryName;
OutputStream out = new
FileOutputStream(outFilename);
// Transfer bytes from the ZIP file to the
output file
int len;
while ((len = in.read(buf)) > 0)
{
out.write(buf, 0, len);
}
// Close the streams
out.close();
}
in.close();
}
catch (IOException ioe)
{
System.out.println("Unzip failed "
+ zipName + " " + ioe.toString());
}
}
}
