/** * Sync this column with the database * * @return */ public String syncDatabase() { MTable table = new MTable(getCtx(), getAD_Table_ID(), get_TrxName()); table.set_TrxName(get_TrxName()); // otherwise table.getSQLCreate may miss current column if (table.get_ID() == 0) throw new AdempiereException("@NotFound@ @AD_Table_ID@ " + getAD_Table_ID()); // Find Column in Database Connection conn = null; try { conn = DB.getConnectionRO(); DatabaseMetaData md = conn.getMetaData(); String catalog = DB.getDatabase().getCatalog(); String schema = DB.getDatabase().getSchema(); String tableName = table.getTableName(); if (md.storesUpperCaseIdentifiers()) { tableName = tableName.toUpperCase(); } else if (md.storesLowerCaseIdentifiers()) { tableName = tableName.toLowerCase(); } int noColumns = 0; String sql = null; // ResultSet rs = md.getColumns(catalog, schema, tableName, null); while (rs.next()) { noColumns++; String columnName = rs.getString("COLUMN_NAME"); if (!columnName.equalsIgnoreCase(getColumnName())) continue; // update existing column boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE"); sql = getSQLModify(table, isMandatory() != notNull); break; } rs.close(); rs = null; // No Table if (noColumns == 0) sql = table.getSQLCreate(); // No existing column else if (sql == null) sql = getSQLAdd(table); if (sql == null) return "No sql"; int no = 0; if (sql.indexOf(DB.SQLSTATEMENT_SEPARATOR) == -1) { DB.executeUpdateEx(sql, get_TrxName()); } else { String statements[] = sql.split(DB.SQLSTATEMENT_SEPARATOR); for (int i = 0; i < statements.length; i++) { DB.executeUpdateEx(statements[i], get_TrxName()); } } return sql; } catch (SQLException e) { throw new AdempiereException(e); } finally { if (conn != null) { try { conn.close(); } catch (Exception e) { } } } }
/** * Check if column exists in database and modify. If not create column. * * @param tablename * @param columnname * @param v_AD_Reference_ID * @param v_FieldLength * @param v_DefaultValue * @param v_IsMandatory */ private int createColumn(Properties ctx, MTable table, MColumn column, boolean doAlter) { int no = 0; String sql = null; ResultSet rst = null; ResultSet rsc = null; Connection conn = null; Trx trx = Trx.get(getTrxName(ctx), true); if (!trx.commit()) return 0; try { // Find Column in Database conn = trx.getConnection(); DatabaseMetaData md = conn.getMetaData(); String catalog = DB.getDatabase().getCatalog(); String schema = DB.getDatabase().getSchema(); String tableName = table.getTableName(); String columnName = column.getColumnName(); if (DB.isOracle()) { tableName = tableName.toUpperCase(); columnName = columnName.toUpperCase(); } else if (DB.isPostgreSQL()) { tableName = tableName.toLowerCase(); columnName = columnName.toLowerCase(); } rst = md.getTables(catalog, schema, tableName, new String[] {"TABLE"}); if (!rst.next()) { // table doesn't exist sql = table.getSQLCreate(); } else { // rsc = md.getColumns(catalog, schema, tableName, columnName); if (rsc.next()) { if (doAlter) { // update existing column boolean notNull = DatabaseMetaData.columnNoNulls == rsc.getInt("NULLABLE"); sql = column.getSQLModify(table, column.isMandatory() != notNull); } } else { // No existing column sql = column.getSQLAdd(table); } rsc.close(); rsc = null; } rst.close(); rst = null; // execute modify or add if needed if (sql != null && sql.trim().length() > 0) { log.info(sql); if (sql.indexOf(DB.SQLSTATEMENT_SEPARATOR) == -1) { no = DB.executeUpdate(sql, false, trx.getTrxName()); if (no == -1) return 0; } else { String statements[] = sql.split(DB.SQLSTATEMENT_SEPARATOR); for (int i = 0; i < statements.length; i++) { int count = DB.executeUpdate(statements[i], false, trx.getTrxName()); if (count == -1) { return 0; } no += count; } } } trx.commit(true); } catch (SQLException e) { log.log(Level.SEVERE, e.getLocalizedMessage(), e); if (rsc != null) { try { rsc.close(); } catch (SQLException e1) { } rsc = null; } if (rst != null) { try { rst.close(); } catch (SQLException e1) { } rst = null; } trx.rollback(); return 0; } return 1; }