/*
* 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 RowMetaInterface getRequiredFields(VariableSpace space) throws KettleException {
String realFilename = space.environmentSubstitute(filename);
File file = new File(realFilename);
Database db = null;
try {
if (!file.exists() || !file.isFile()) {
throw new KettleException(
BaseMessages.getString(
PKG, "AccessOutputMeta.Exception.FileDoesNotExist", realFilename));
}
// open the database and get the table
db = Database.open(file);
String realTablename = space.environmentSubstitute(tablename);
Table table = db.getTable(realTablename);
if (table == null) {
throw new KettleException(
BaseMessages.getString(
PKG, "AccessOutputMeta.Exception.TableDoesNotExist", realTablename));
}
RowMetaInterface layout = getLayout(table);
return layout;
} catch (Exception e) {
throw new KettleException(
BaseMessages.getString(PKG, "AccessOutputMeta.Exception.ErrorGettingFields"), e);
} finally {
try {
if (db != null) {
db.close();
}
} catch (IOException e) {
throw new KettleException(
BaseMessages.getString(PKG, "AccessOutputMeta.Exception.ErrorClosingDatabase"), e);
}
}
}