private static void exportTables(
Database database, String namesep, String genericOutputPath, String fieldsep, String rowsep) {
try {
for (Table table : database) {
// Prepare the full csv file path, an integer is added if the file already exists
String outputPath = constructOutputPath(genericOutputPath, namesep, table);
FileWriter outputFileWriter = new FileWriter(outputPath);
BufferedWriter outputBufferedWriter = new BufferedWriter(outputFileWriter);
// Write the column names to the csv file
ArrayList<String> columns = new ArrayList<String>();
for (Column column : table.getColumns()) {
columns.add(column.getName());
}
outputBufferedWriter.write(StringUtils.join(columns, fieldsep));
outputBufferedWriter.write(rowsep);
// Write the rows to the csv file
for (Row row : table) {
outputBufferedWriter.write(StringUtils.join(row.values(), fieldsep));
outputBufferedWriter.write(rowsep);
}
outputBufferedWriter.close();
outputFileWriter.close();
}
} catch (IOException ioe) {
System.out.println("ERROR: unable to write the csv file to the file system");
closeDatabase(database);
System.exit(1);
}
}
/*
* Copies a table in the MDB into the destination.
* @param name of the table in the MDB file
* @param dest JdbcLink. Where you want to create the imported table
* @return number of rows imported
* @see The name in destination may be prefixed using setPrefixForImportedTableNames
*/
public int convertTable(String name, JdbcLink dest) throws IOException, SQLException {
Table table = db.getTable(name);
String insertName = ImportPrefix + name;
List<Column> cols = table.getColumns();
try {
dest.exec("DROP TABLE IF EXISTS " + insertName); // $NON-NLS-1$
} catch (Exception ex) {
// don¨t mind
}
StringBuilder sb = new StringBuilder();
sb.append("CREATE TABLE ").append(insertName).append("("); // $NON-NLS-1$ //$NON-NLS-2$
for (Column c : cols) {
sb.append(c.getName()).append(" ");
switch (c.getType()) {
case MEMO:
sb.append("TEXT"); // $NON-NLS-1$
break;
case INT:
case LONG:
sb.append("INTEGER"); // $NON-NLS-1$
break;
case TEXT:
sb.append("VARCHAR(255)"); // $NON-NLS-1$
break;
default:
sb.append("VARCHAR(255)"); // $NON-NLS-1$
}
sb.append(","); // $NON-NLS-1$
}
sb.deleteCharAt(sb.length() - 1);
sb.append(");"); // $NON-NLS-1$
dest.exec(sb.toString());
Map<String, Object> row = null;
int nrRows = 0;
while ((row = table.getNextRow()) != null) {
nrRows++;
StringBuilder left = new StringBuilder();
left.append("INSERT INTO ").append(insertName).append("("); // $NON-NLS-1$ //$NON-NLS-2$
StringBuilder right = new StringBuilder();
right.append(" VALUES("); // $NON-NLS-1$
for (String key : row.keySet()) {
left.append(key).append(","); // $NON-NLS-1$
right.append("?,"); // $NON-NLS-1$
}
left.deleteCharAt(left.length() - 1);
right.deleteCharAt(right.length() - 1);
left.append(") ").append(right).append(");"); // $NON-NLS-1$ //$NON-NLS-2$
PreparedStatement ps = dest.prepareStatement(left.toString());
int i = 1;
for (String key : row.keySet()) {
ps.setObject(i++, row.get(key));
}
ps.execute();
}
return nrRows;
}
public static final RowMetaInterface getLayout(Table table) throws SQLException {
RowMetaInterface row = new RowMeta();
List<Column> columns = table.getColumns();
for (int i = 0; i < columns.size(); i++) {
Column column = columns.get(i);
int valtype = ValueMetaInterface.TYPE_STRING;
int length = -1;
int precision = -1;
int type = column.getType().getSQLType();
switch (type) {
case java.sql.Types.CHAR:
case java.sql.Types.VARCHAR:
case java.sql.Types.LONGVARCHAR: // Character Large Object
valtype = ValueMetaInterface.TYPE_STRING;
length = column.getLength();
break;
case java.sql.Types.CLOB:
valtype = ValueMetaInterface.TYPE_STRING;
length = DatabaseMeta.CLOB_LENGTH;
break;
case java.sql.Types.BIGINT:
valtype = ValueMetaInterface.TYPE_INTEGER;
precision = 0; // Max 9.223.372.036.854.775.807
length = 15;
break;
case java.sql.Types.INTEGER:
valtype = ValueMetaInterface.TYPE_INTEGER;
precision = 0; // Max 2.147.483.647
length = 9;
break;
case java.sql.Types.SMALLINT:
valtype = ValueMetaInterface.TYPE_INTEGER;
precision = 0; // Max 32.767
length = 4;
break;
case java.sql.Types.TINYINT:
valtype = ValueMetaInterface.TYPE_INTEGER;
precision = 0; // Max 127
length = 2;
break;
case java.sql.Types.DECIMAL:
case java.sql.Types.DOUBLE:
case java.sql.Types.FLOAT:
case java.sql.Types.REAL:
case java.sql.Types.NUMERIC:
valtype = ValueMetaInterface.TYPE_NUMBER;
length = column.getLength();
precision = column.getPrecision();
if (length >= 126) {
length = -1;
}
if (precision >= 126) {
precision = -1;
}
if (type == java.sql.Types.DOUBLE
|| type == java.sql.Types.FLOAT
|| type == java.sql.Types.REAL) {
if (precision == 0) {
precision = -1; // precision is obviously incorrect if the type if Double/Float/Real
}
} else {
if (precision == 0
&& length < 18
&& length > 0) { // Among others Oracle is affected here.
valtype = ValueMetaInterface.TYPE_INTEGER;
}
}
if (length > 18 || precision > 18) {
valtype = ValueMetaInterface.TYPE_BIGNUMBER;
}
break;
case java.sql.Types.DATE:
case java.sql.Types.TIME:
case java.sql.Types.TIMESTAMP:
valtype = ValueMetaInterface.TYPE_DATE;
break;
case java.sql.Types.BOOLEAN:
case java.sql.Types.BIT:
valtype = ValueMetaInterface.TYPE_BOOLEAN;
break;
case java.sql.Types.BINARY:
case java.sql.Types.BLOB:
case java.sql.Types.VARBINARY:
case java.sql.Types.LONGVARBINARY:
valtype = ValueMetaInterface.TYPE_BINARY;
break;
default:
valtype = ValueMetaInterface.TYPE_STRING;
length = column.getLength();
break;
}
ValueMetaInterface v = new ValueMeta(column.getName(), valtype);
v.setLength(length, precision);
row.addValueMeta(v);
}
return row;
}
