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