private void updateComputedColumns(TableDefinition table, WbConnection conn) {
PreparedStatement stmt = null;
ResultSet rs = null;
String tablename = table.getTable().getRawTableName();
String schema = table.getTable().getRawSchema();
String sql =
"select column_name, \n"
+ " generation_type \n"
+ "from sys.table_columns \n"
+ "where table_name = ? \n"
+ "and schema_name = ? \n"
+ "and generation_type is not null";
Map<String, String> expressions = new HashMap<>();
try {
stmt = conn.getSqlConnection().prepareStatement(sql);
stmt.setString(1, tablename);
stmt.setString(2, schema);
rs = stmt.executeQuery();
while (rs.next()) {
String colname = rs.getString(1);
String generated = rs.getString(2);
if (StringUtil.isNonEmpty(generated)) {
expressions.put(colname, "GENERATED " + generated);
}
}
} catch (Exception e) {
LogMgr.logError("HanaColumnEnhancer.updateComputedColumns()", "Error retrieving remarks", e);
} finally {
SqlUtil.closeAll(rs, stmt);
}
for (ColumnIdentifier col : table.getColumns()) {
String expr = expressions.get(col.getColumnName());
if (StringUtil.isNonBlank(expr)) {
col.setDefaultValue(null);
col.setComputedColumnExpression(expr);
col.setIsAutoincrement(true);
}
}
}
private void updateDateColumns(TableDefinition table, WbConnection conn) {
String catalog = table.getTable().getRawCatalog();
String systemSchema = conn.getDbSettings().getProperty("systemschema", "informix");
TableIdentifier sysTabs = new TableIdentifier(catalog, systemSchema, "systables");
TableIdentifier sysCols = new TableIdentifier(catalog, systemSchema, "syscolumns");
String systables = sysTabs.getFullyQualifiedName(conn);
String syscolumns = sysCols.getFullyQualifiedName(conn);
String typeValues = conn.getDbSettings().getProperty("qualifier.typevalues", "10,14,266,270");
String sql =
"select c.colname, c.collength \n"
+ "from "
+ systables
+ " t \n"
+ " join "
+ syscolumns
+ " c on t.tabid = c.tabid \n"
+ "where t.tabname = ? \n"
+ " and t.owner = ? \n"
+ " and c.coltype in ("
+ typeValues
+ ")";
String tablename = table.getTable().getRawTableName();
String schema = table.getTable().getRawSchema();
LogMgr.logDebug(
"InformixColumnEnhancer.updateDateColumns()",
"Query to retrieve column details:\n" + SqlUtil.replaceParameters(sql, tablename, schema));
PreparedStatement stmt = null;
ResultSet rs = null;
Map<String, ColumnIdentifier> cols = new TreeMap<>(CaseInsensitiveComparator.INSTANCE);
for (ColumnIdentifier col : table.getColumns()) {
cols.put(col.getColumnName(), col);
}
try {
stmt = conn.getSqlConnection().prepareStatement(sql);
stmt.setString(1, tablename);
stmt.setString(2, schema);
rs = stmt.executeQuery();
while (rs.next()) {
String colname = rs.getString(1);
int colLength = rs.getInt(2);
ColumnIdentifier col = cols.get(colname);
if (col != null) {
String typeDesc = getQualifier(colLength);
String dbms = SqlUtil.getPlainTypeName(col.getDbmsType());
String newType = dbms + " " + typeDesc;
LogMgr.logDebug(
"InformixColumnEnhancer.updateDateColumns()",
"Column "
+ tablename
+ "."
+ colname
+ " has collength of: "
+ colLength
+ ". Changing type '"
+ col.getDbmsType()
+ "' to '"
+ newType
+ "'");
col.setDbmsType(newType);
} else {
LogMgr.logError(
"InformixColumnEnhancer.updateDateColumns()",
"The query returned a column name ("
+ colname
+ ") that was not part of the passed table definition!",
null);
}
}
} catch (Exception e) {
LogMgr.logError(
"InformixColumnEnhancer.updateDateColumns()",
"Error retrieving datetime qualifiers using:\n"
+ SqlUtil.replaceParameters(sql, tablename, schema),
e);
} finally {
SqlUtil.closeAll(rs, stmt);
}
}