Examples with Savepoint java.sql.Savepoint used on opensource projects

Example 6 with Savepoint

use of java.sql.Savepoint in project adempiere by adempiere.

the class Migrate method cleanupSecurity.

/**
	 * role access update
	 */
private void cleanupSecurity() {
    // only continue if we have required tables
    if (!m_source.isObjectExists("AD_Role", m_source.getTables()))
        return;
    // reset DB objects
    resetDBObjects(DBObject_Table.class);
    s_logger.log(Level.CONFIG, "");
    s_logger.log(Level.CONFIG, "cleanupSecurity", m_direction);
    String vendor = m_target.getVendor();
    String catalog = m_target.getCatalog();
    String schema = m_target.getSchema();
    // remember savepoint for rollback
    Savepoint sp = m_target.setSavepoint("set security levels");
    // load roles
    HashMap<Integer, String> roleAccess = new HashMap<Integer, String>();
    Statement stmt = m_target.setStatement();
    ResultSet rs = m_target.executeQuery(stmt, s_dbEngine.sql_select(vendor, catalog, schema, "AD_Role", "ismanual='N' ORDER BY ad_role_id"));
    while (m_target.getResultSetNext(rs)) {
        int ad_role_id = m_target.getResultSetInt(rs, "AD_Role_ID");
        String userlevel = m_target.getResultSetString(rs, "UserLevel");
        // map access priviliges to security clearance
        int securityMask = 0;
        if (userlevel.toUpperCase().contains("S"))
            // 100 - system
            securityMask = securityMask | 4;
        if (userlevel.toUpperCase().contains("C"))
            // 010 - client
            securityMask = securityMask | 2;
        if (userlevel.toUpperCase().contains("O"))
            // 001 - organization
            securityMask = securityMask | 1;
        StringBuffer securityClearance = new StringBuffer("(");
        for (int i = 1; i <= 7; i++) {
            if ((i & securityMask) != 0) {
                if (securityClearance.length() > 1)
                    securityClearance.append(", ");
                securityClearance.append("'").append(i).append("'");
            }
        }
        securityClearance.append(")");
        roleAccess.put(ad_role_id, securityClearance.toString());
    }
    m_target.releaseResultSet(rs);
    m_target.releaseStatement(stmt);
    // iterate through tables
    for (Iterator<String> it = m_objectList.iterator(); it.hasNext(); ) {
        String key = it.next();
        DBObject obj = m_sourceMap.get(key);
        // remaining in target
        if (obj == null) {
            obj = m_targetMap.get(key);
            // ignore target table if it is not customized
            if (obj.getCustomizationLevel() == s_parameters.CUSTOMNONE)
                obj = null;
        }
        // only continue if we have a valid table
        if (obj != null) {
            // find access control tables
            // (their name ends on _Access and they have an ad_role_id
            // column)
            String tableName = obj.getName();
            if (tableName.toUpperCase().endsWith("_ACCESS") && hasTableColumn(obj, "AD_Role_ID")) {
                // (they have an IsExclude field)
                if (!hasTableColumn(obj, "IsExclude")) {
                    // find the base table which is being controlled
                    // (same name without _Access at the end)
                    String baseKey = key.substring(0, tableName.toUpperCase().lastIndexOf("_ACCESS"));
                    // special base table for AD_Document_Action_Access
                    if (tableName.equalsIgnoreCase("AD_Document_Action_Access"))
                        baseKey = "AD_CLIENT";
                    DBObject baseObj = m_sourceMap.get(baseKey);
                    if (baseObj != null) {
                        String baseTableName = baseObj.getName();
                        // AD_Document_Action_Access, needs special handling
                        if (hasTableColumn(baseObj, "AccessLevel") || baseTableName.equalsIgnoreCase("AD_Window") || tableName.equalsIgnoreCase("AD_Document_Action_Access")) {
                            // re-create access controls
                            // iterate through roles
                            Vector<Integer> vRoles = new Vector<Integer>(roleAccess.keySet());
                            java.util.Collections.sort(vRoles);
                            for (Iterator<Integer> roleIterator = vRoles.iterator(); roleIterator.hasNext(); ) {
                                int ad_role_id = roleIterator.next();
                                // delete existing access records
                                PreparedStatementWrapper stmtDeleteRoleAccess = m_target.setPreparedStatement(s_dbEngine.sql_deleteByCondition(vendor, catalog, schema, tableName, "ad_role_id = ?"));
                                m_target.setPreparedStatementInt(stmtDeleteRoleAccess, 1, ad_role_id);
                                Integer sqlResult = m_target.executeUpdate(stmtDeleteRoleAccess, false);
                                if (sqlResult != null) {
                                    logDropDetail(sqlResult, null);
                                }
                                m_target.releasePreparedStatement(stmtDeleteRoleAccess);
                                // insert new access records
                                // we can not use a prepared statement but
                                // must dynamically build it for several
                                // reasons
                                // - INSERT INTO table1 (col1) SELECT ? FROM
                                // table2 would require typecasting of
                                // parameters
                                // - we do not how to set the parameter to a
                                // column of table2
                                // - we can not use WHERE table2.col1 IN ?
                                // and set the single parameter with a list
                                // of values
                                // base table ID
                                String baseTableID = new StringBuffer(baseTableName).append("_ID").toString();
                                // columns and values
                                ArrayList<String> insertColumnNames = new ArrayList<String>();
                                ArrayList<String> insertColumnValues = new ArrayList<String>();
                                Vector<Integer> vColumns = new Vector<Integer>(obj.getContents().keySet());
                                java.util.Collections.sort(vColumns);
                                for (Iterator<Integer> columnIterator = vColumns.iterator(); columnIterator.hasNext(); ) {
                                    Integer col = columnIterator.next();
                                    DBObject_Table_Column accessColumn = (DBObject_Table_Column) obj.getContents().get(col);
                                    String columnName = accessColumn.getName();
                                    if (columnName.equalsIgnoreCase(baseTableID)) {
                                        insertColumnNames.add(columnName);
                                        insertColumnValues.add(new StringBuffer("t.").append(baseTableID).toString());
                                    } else if (columnName.equalsIgnoreCase("C_DocType_ID")) {
                                        insertColumnNames.add(columnName);
                                        insertColumnValues.add("t0.C_DocType_ID");
                                    } else if (columnName.equalsIgnoreCase("AD_Ref_List_ID")) {
                                        // AD_Document_Action_Access:
                                        if (tableName.equalsIgnoreCase("AD_Document_Action_Access")) {
                                            insertColumnNames.add(columnName);
                                            insertColumnValues.add("t1.AD_Ref_List_ID");
                                        }
                                    } else if (columnName.equalsIgnoreCase("AD_Role_ID")) {
                                        insertColumnNames.add(columnName);
                                        if (tableName.equalsIgnoreCase("AD_Document_Action_Access")) {
                                            // special handling for
                                            // AD_Document_Action_Access:
                                            insertColumnValues.add("t2.AD_Role_ID");
                                        } else {
                                            // normally:
                                            insertColumnValues.add(new Integer(ad_role_id).toString());
                                        }
                                    } else if (columnName.equalsIgnoreCase("AD_Client_ID")) {
                                        // already handled by base table ID)
                                        if (!tableName.equalsIgnoreCase("AD_Document_Action_Access")) {
                                            insertColumnNames.add(columnName);
                                            insertColumnValues.add("0");
                                        }
                                    } else if (columnName.equalsIgnoreCase("AD_Org_ID") || columnName.equalsIgnoreCase("CreatedBy") || columnName.equalsIgnoreCase("UpdatedBy")) {
                                        insertColumnNames.add(columnName);
                                        insertColumnValues.add("0");
                                    } else if (columnName.equalsIgnoreCase("IsActive") || columnName.equalsIgnoreCase("isReadWrite")) {
                                        insertColumnNames.add(columnName);
                                        insertColumnValues.add("'Y'");
                                    } else if (columnName.equalsIgnoreCase("Created") || columnName.equalsIgnoreCase("Updated")) {
                                        insertColumnNames.add(columnName);
                                        insertColumnValues.add(s_dbEngine.translateExpression("POSTGRES", vendor, "now()"));
                                    }
                                }
                                // JOIN clause
                                ArrayList<String> joinTypes = new ArrayList<String>();
                                ArrayList<String> joinTables = new ArrayList<String>();
                                ArrayList<String> joinConditions = new ArrayList<String>();
                                // joins for AD_Window
                                if (tableName.equalsIgnoreCase("AD_Window_Access")) {
                                    // t0: join AD_Tab
                                    joinTypes.add("INNER JOIN");
                                    joinTables.add("AD_Tab");
                                    joinConditions.add("t.AD_Window_ID = t0.AD_Window_ID");
                                    // t1: join AD_Table
                                    joinTypes.add("INNER JOIN");
                                    joinTables.add("AD_Table");
                                    joinConditions.add("t0.AD_Table_ID = t1.AD_Table_ID");
                                }
                                // joins for AD_Document_Action_Access
                                if (tableName.equalsIgnoreCase("AD_Document_Action_Access")) {
                                    // t0: join C_Doctype
                                    joinTypes.add("INNER JOIN");
                                    joinTables.add("C_Doctype");
                                    joinConditions.add("t0.AD_Client_ID = t.AD_Client_ID");
                                    // t1: join AD_Ref_List
                                    joinTypes.add("INNER JOIN");
                                    joinTables.add("AD_Ref_List");
                                    joinConditions.add("t1.AD_Reference_ID = 135");
                                    // t2: join AD_Role
                                    joinTypes.add("INNER JOIN");
                                    joinTables.add("AD_Role");
                                    joinConditions.add(new StringBuffer("t2.AD_Client_ID = t.AD_Client_ID AND t2.AD_Role_ID = ").append(ad_role_id).toString());
                                }
                                // WHERE clause
                                String whereClause = new StringBuffer("t.AccessLevel IN ").append(roleAccess.get(ad_role_id)).toString();
                                // special conditions for AD_Window_Access
                                if (tableName.equalsIgnoreCase("AD_Window_Access")) {
                                    String winAccess = roleAccess.get(ad_role_id);
                                    // general access levels
                                    StringBuffer winWhere = new StringBuffer("t1.AccessLevel IN ").append(winAccess).append(" ");
                                    // org-Only access
                                    if (!(winAccess.contains("2") || winAccess.contains("4"))) {
                                        winWhere.append("AND t.Name NOT LIKE '%(all)%' ");
                                    }
                                    // limit search to first tab of window
                                    String sql = s_dbEngine.sql_select(vendor, catalog, schema, "AD_Tab", "tx.AD_Window_ID = t.AD_Window_ID").replace("*", "min(SeqNo)").replace(" t ", " tx ");
                                    winWhere.append("AND t0.SeqNo = (").append(sql).append(") ");
                                    whereClause = winWhere.toString();
                                }
                                // AD_Document_Action_Access
                                if (tableName.equalsIgnoreCase("AD_Document_Action_Access")) {
                                    whereClause = null;
                                }
                                // build full SQL string
                                String sqlCommand = s_dbEngine.sql_insertFromTable(vendor, catalog, schema, tableName, insertColumnNames, insertColumnValues, baseTableName, joinTypes, joinTables, joinConditions, whereClause);
                                stmt = m_target.setStatement();
                                sqlResult = m_target.executeUpdate(stmt, sqlCommand, false, false);
                                if (sqlResult != null) {
                                    logAddDetail(sqlResult, null);
                                }
                                m_target.releaseStatement(stmt);
                            }
                        }
                    }
                }
            }
        }
    }
    // release savepoint
    m_target.releaseSavepoint(sp);
    logResults();
}

Example 7 with Savepoint

use of java.sql.Savepoint in project adempiere by adempiere.

the class Migrate method synchronizePrimaryKeys.

/**
	 * adjust primary keys of Application Dictionary records to those in source
	 */
private void synchronizePrimaryKeys() {
    resetDBObjects(DBObject_PrimaryKey.class);
    s_logger.log(Level.CONFIG, "");
    s_logger.log(Level.CONFIG, "synchronizePrimaryKeys", new Object[] { m_objectTypes, m_direction });
    m_counterUpd = new Integer(0);
    m_totalUpd = new Integer(0);
    m_counterDrp = new Integer(0);
    // target now has source's structure, so we need to access source's
    // metadata
    String sourceVendor = m_source.getVendor();
    String sourceCatalog = m_source.getCatalog();
    String sourceSchema = m_source.getSchema();
    String targetVendor = m_target.getVendor();
    String targetCatalog = m_target.getCatalog();
    String targetSchema = m_target.getSchema();
    // iterate through primary keys
    if (m_sourceMap != null) {
        for (Iterator<String> pkIt = m_sourceMap.keySet().iterator(); pkIt.hasNext(); ) {
            // reset array of prepared statements to update foreign keys
            ArrayList<PreparedStatementWrapper> targetStmtUpdateFKs = null;
            ArrayList<ArrayList<String>> targetFKColumns = null;
            ArrayList<String> targetFKNames = null;
            String pkKey = pkIt.next();
            DBObject pk = m_sourceMap.get(pkKey);
            DBObject_PrimaryKey_Table pkHeader = (DBObject_PrimaryKey_Table) pk.getHeaders().get(0);
            String tableName = pkHeader.getTable();
            String pkTableName = tableName.toUpperCase();
            // but exclude translations and logs
            if (pkTableName.startsWith("AD_") && (!pkTableName.endsWith("_TRL")) && (!pkTableName.endsWith("LOG"))) {
                // find unique indexes defined for this table
                for (Iterator<String> ndxIt = m_source.getIndexes().keySet().iterator(); ndxIt.hasNext(); ) {
                    String ndxKey = ndxIt.next();
                    DBObject ndx = m_source.getObjectByName(ndxKey, m_source.getIndexes());
                    DBObject_Index_Table ndxHeader = (DBObject_Index_Table) ndx.getHeaders().get(0);
                    String ndxTableName = ndxHeader.getTable().toUpperCase();
                    boolean ndxIsUnique = ndxHeader.isUnique();
                    if (ndxTableName.equals(pkTableName) && ndxIsUnique) {
                        // columns
                        DBObject table = m_source.getObjectByName(pkTableName, m_source.getTables());
                        HashMap<String, Integer> columnTypeMap = new HashMap<String, Integer>();
                        Vector<Integer> v = new Vector<Integer>(table.getContents().keySet());
                        java.util.Collections.sort(v);
                        for (Iterator<Integer> i = v.iterator(); i.hasNext(); ) {
                            Integer j = i.next();
                            DBObject_Table_Column col = (DBObject_Table_Column) table.getContents().get(j);
                            String columnName = col.getName();
                            int columnType = s_dbEngine.getDataTypeID(sourceVendor, col.getType());
                            columnTypeMap.put(columnName.toUpperCase(), columnType);
                        }
                        // list of Primary Key Columns
                        ArrayList<String> pkColumns = new ArrayList<String>();
                        ArrayList<Integer> pkTypes = new ArrayList<Integer>();
                        v = new Vector<Integer>(pk.getContents().keySet());
                        java.util.Collections.sort(v);
                        for (Iterator<Integer> i = v.iterator(); i.hasNext(); ) {
                            Integer j = i.next();
                            DBObject_PrimaryKey_Column col = (DBObject_PrimaryKey_Column) pk.getContents().get(j);
                            pkColumns.add(col.getColumn());
                            pkTypes.add(columnTypeMap.get(col.getColumn().toUpperCase()));
                        }
                        // list of Index Columns
                        boolean isIndexValid = true;
                        ArrayList<String> ndxColumns = new ArrayList<String>();
                        ArrayList<Integer> ndxTypes = new ArrayList<Integer>();
                        v = new Vector<Integer>(ndx.getContents().keySet());
                        java.util.Collections.sort(v);
                        for (Iterator<Integer> i = v.iterator(); i.hasNext(); ) {
                            Integer j = i.next();
                            DBObject_Index_Column col = (DBObject_Index_Column) ndx.getContents().get(j);
                            String ndxColName = col.getColumn();
                            String ndxColNameUpper = ndxColName.toUpperCase();
                            Integer ndxColType = columnTypeMap.get(ndxColNameUpper);
                            // function
                            if (ndxColType == null) {
                                // normal column name at this stage
                                if (ndxColNameUpper.startsWith("UPPER") || ndxColNameUpper.startsWith("LOWER")) {
                                    // extract columnname from formula
                                    ndxColName = ndxColName.replaceAll(".*\\((.*?)\\).*", "$1");
                                    ndxColNameUpper = ndxColName.toUpperCase();
                                    ndxColType = columnTypeMap.get(ndxColNameUpper);
                                }
                            }
                            // column
                            if (ndxColType != null) {
                                ndxTypes.add(ndxColType);
                                ndxColumns.add(ndxColName);
                            } else {
                                // otherwise this index is invalid
                                isIndexValid = false;
                            }
                        }
                        // only proceed if we have a valid index
                        if (isIndexValid) {
                            // remember savepoint for rollback
                            Savepoint sp = m_target.setSavepoint(tableName);
                            // statement to find primary key in source
                            PreparedStatementWrapper sourceStmtFindPK = m_source.setPreparedStatement(s_dbEngine.sql_selectPreparedStatement(sourceVendor, sourceCatalog, sourceSchema, tableName, ndxColumns));
                            // statement to update primary key in target
                            PreparedStatementWrapper targetStmtUpdatePK = m_target.setPreparedStatement(s_dbEngine.sql_updatePreparedStatement(targetVendor, targetCatalog, targetSchema, tableName, pkColumns, ndxColumns));
                            // iterate through target table's rows to read
                            // columns of unique index
                            Statement targetStmt = m_target.setStatement();
                            ResultSet targetRs = m_target.executeQuery(targetStmt, s_dbEngine.sql_select(targetVendor, targetCatalog, targetSchema, tableName));
                            while (m_target.getResultSetNext(targetRs)) {
                                // unique index values
                                for (int i = 0; i < ndxColumns.size(); i++) {
                                    String colName = ndxColumns.get(i);
                                    int colType = ndxTypes.get(i);
                                    int colIndex = i + 1;
                                    if (colType >= s_dbEngine.CLOB && colType <= s_dbEngine.NCLOB) {
                                        m_source.setPreparedStatementClob(sourceStmtFindPK, colIndex, m_target.getResultSetClob(targetRs, colName));
                                    } else if (colType >= s_dbEngine.BINTYPE_START && colType <= s_dbEngine.BINTYPE_END) {
                                        m_source.setPreparedStatementBytes(sourceStmtFindPK, colIndex, m_target.getResultSetBytes(targetRs, colName));
                                    } else {
                                        m_source.setPreparedStatementObject(sourceStmtFindPK, colIndex, m_target.getResultSetObject(targetRs, colName));
                                    }
                                }
                                ResultSet sourceRs = m_source.executeQuery(sourceStmtFindPK);
                                // if there is a matching source record
                                if (m_source.getResultSetNext(sourceRs)) {
                                    // compare the primary keys
                                    boolean pkmatch = true;
                                    for (Iterator<String> i = pkColumns.iterator(); i.hasNext(); ) {
                                        String columnName = i.next();
                                        if (!m_source.getResultSetString(sourceRs, columnName).equals(m_target.getResultSetString(targetRs, columnName)))
                                            pkmatch = false;
                                    }
                                    // differs from source
                                    if (!pkmatch) {
                                        // primary key
                                        if (targetStmtUpdateFKs == null) {
                                            targetStmtUpdateFKs = new ArrayList<PreparedStatementWrapper>();
                                            targetFKColumns = new ArrayList<ArrayList<String>>();
                                            targetFKNames = new ArrayList<String>();
                                            if (m_source.getForeignKeys() != null) {
                                                for (Iterator<String> it = m_source.getForeignKeys().keySet().iterator(); it.hasNext(); ) {
                                                    String key = it.next();
                                                    DBObject obj = m_source.getForeignKeys().get(key);
                                                    String fkName = obj.getName();
                                                    DBObject_ForeignKey_Table objHeader = (DBObject_ForeignKey_Table) obj.getHeaders().get(0);
                                                    DBObject localTable = m_source.getObjectByName(objHeader.getFTable(), m_source.getTables());
                                                    DBObject foreignTable = m_source.getObjectByName(objHeader.getTable(), m_source.getTables());
                                                    // match this table?
                                                    if (localTable.getName().equalsIgnoreCase(tableName)) {
                                                        // constraints
                                                        if (!localTable.getName().equalsIgnoreCase(foreignTable.getName())) {
                                                            String fkForeignTableName = foreignTable.getName();
                                                            ArrayList<String> fkForeignColumns = new ArrayList<String>();
                                                            ArrayList<String> fkLocalColumns = new ArrayList<String>();
                                                            // column
                                                            for (Iterator<Integer> it2 = obj.getContents().keySet().iterator(); it2.hasNext(); ) {
                                                                Integer key2 = it2.next();
                                                                DBObject_ForeignKey_Column objContents = (DBObject_ForeignKey_Column) obj.getContents().get(key2);
                                                                fkForeignColumns.add(objContents.getColumn());
                                                                fkLocalColumns.add(objContents.getFColumn());
                                                            }
                                                            // create
                                                            // prepared
                                                            // statement
                                                            PreparedStatementWrapper targetStmtUpdateFK = m_target.setPreparedStatement(s_dbEngine.sqlAction_updateChildRecord(targetVendor, targetCatalog, targetSchema, fkForeignTableName, fkForeignColumns, tableName, fkLocalColumns, ndxColumns));
                                                            targetStmtUpdateFKs.add(targetStmtUpdateFK);
                                                            targetFKColumns.add(fkLocalColumns);
                                                            targetFKNames.add(fkName);
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                        for (int j = 0; j < targetStmtUpdateFKs.size(); j++) {
                                            PreparedStatementWrapper targetStmtUpdateFK = targetStmtUpdateFKs.get(j);
                                            ArrayList<String> fkLocalColumns = targetFKColumns.get(j);
                                            // load PK values into statement
                                            for (int i = 0; i < fkLocalColumns.size(); i++) {
                                                String colName = fkLocalColumns.get(i);
                                                int colType = pkTypes.get(i);
                                                int colIndex = i + 1;
                                                // to target
                                                if (colType >= s_dbEngine.CLOB && colType <= s_dbEngine.NCLOB) {
                                                    m_target.setPreparedStatementClob(targetStmtUpdateFK, colIndex, m_source.getResultSetClob(sourceRs, colName));
                                                } else if (colType >= s_dbEngine.BINTYPE_START && colType <= s_dbEngine.BINTYPE_END) {
                                                    m_target.setPreparedStatementBytes(targetStmtUpdateFK, colIndex, m_source.getResultSetBytes(sourceRs, colName));
                                                } else {
                                                    m_target.setPreparedStatementObject(targetStmtUpdateFK, colIndex, m_source.getResultSetObject(sourceRs, colName));
                                                }
                                            }
                                            // statement
                                            for (int i = 0; i < ndxColumns.size(); i++) {
                                                String colName = ndxColumns.get(i);
                                                int colType = ndxTypes.get(i);
                                                int colIndex = fkLocalColumns.size() + i + 1;
                                                if (colType >= s_dbEngine.CLOB && colType <= s_dbEngine.NCLOB) {
                                                    m_target.setPreparedStatementClob(targetStmtUpdateFK, colIndex, m_source.getResultSetClob(sourceRs, colName));
                                                } else if (colType >= s_dbEngine.BINTYPE_START && colType <= s_dbEngine.BINTYPE_END) {
                                                    m_target.setPreparedStatementBytes(targetStmtUpdateFK, colIndex, m_source.getResultSetBytes(sourceRs, colName));
                                                } else {
                                                    m_target.setPreparedStatementObject(targetStmtUpdateFK, colIndex, m_source.getResultSetObject(sourceRs, colName));
                                                }
                                            }
                                            Integer sqlResult = m_target.executeUpdate(targetStmtUpdateFK, false);
                                            if (sqlResult != null) {
                                                logUpdateDetail(sqlResult, null);
                                            }
                                        }
                                        // load PK values into statement
                                        for (int i = 0; i < pkColumns.size(); i++) {
                                            String colName = pkColumns.get(i);
                                            int colType = pkTypes.get(i);
                                            int colIndex = i + 1;
                                            // target
                                            if (colType >= s_dbEngine.CLOB && colType <= s_dbEngine.NCLOB) {
                                                m_target.setPreparedStatementClob(targetStmtUpdatePK, colIndex, m_source.getResultSetClob(sourceRs, colName));
                                            } else if (colType >= s_dbEngine.BINTYPE_START && colType <= s_dbEngine.BINTYPE_END) {
                                                m_target.setPreparedStatementBytes(targetStmtUpdatePK, colIndex, m_source.getResultSetBytes(sourceRs, colName));
                                            } else {
                                                m_target.setPreparedStatementObject(targetStmtUpdatePK, colIndex, m_source.getResultSetObject(sourceRs, colName));
                                            }
                                        }
                                        // statement
                                        for (int i = 0; i < ndxColumns.size(); i++) {
                                            String colName = ndxColumns.get(i);
                                            int colType = ndxTypes.get(i);
                                            int colIndex = pkColumns.size() + i + 1;
                                            if (colType >= s_dbEngine.CLOB && colType <= s_dbEngine.NCLOB) {
                                                m_target.setPreparedStatementClob(targetStmtUpdatePK, colIndex, m_source.getResultSetClob(sourceRs, colName));
                                            } else if (colType >= s_dbEngine.BINTYPE_START && colType <= s_dbEngine.BINTYPE_END) {
                                                m_target.setPreparedStatementBytes(targetStmtUpdatePK, colIndex, m_source.getResultSetBytes(sourceRs, colName));
                                            } else {
                                                m_target.setPreparedStatementObject(targetStmtUpdatePK, colIndex, m_source.getResultSetObject(sourceRs, colName));
                                            }
                                        }
                                        // update the PK of this record
                                        Integer sqlResult = m_target.executeUpdate(targetStmtUpdatePK, false);
                                        if (sqlResult != null) {
                                            logUpdateDetail(sqlResult, null);
                                            m_counterUpd = new Integer(m_counterUpd.intValue() + 1);
                                        }
                                        m_totalUpd = new Integer(m_totalUpd.intValue() + 1);
                                    }
                                    m_source.releaseResultSet(sourceRs);
                                }
                            }
                            m_source.releasePreparedStatement(sourceStmtFindPK);
                            m_target.releaseResultSet(targetRs);
                            m_target.releaseStatement(targetStmt);
                            m_target.releasePreparedStatement(targetStmtUpdatePK);
                            if (targetStmtUpdateFKs != null) {
                                for (Iterator<PreparedStatementWrapper> i = targetStmtUpdateFKs.iterator(); i.hasNext(); ) {
                                    m_target.releasePreparedStatement(i.next());
                                }
                                targetStmtUpdateFKs = null;
                                targetFKColumns = null;
                            }
                            // release savepoint
                            m_target.releaseSavepoint(sp);
                        }
                    }
                }
            }
        }
        // iterate through primary keys again and delete any duplicates
        // (duplicate keys can be created above if, for example, target uses
        // one element
        // where source uses different elements.
        // Example:
        // in Adempiere, there is a discount schema and a price list schema
        // in Compiere, there is only a discount schema
        // When converting from Adempiere to Compiere, both windows would
        // get the same
        // ad_window_id which would be a duplicate key. But as really only
        // one window
        // is needed in Compiere, we can safely delete the duplicate record)
        // remember savepoint for rollback
        Savepoint sp = m_target.setSavepoint("deleteDuplicatePKs");
        for (Iterator<String> pkIt = m_sourceMap.keySet().iterator(); pkIt.hasNext(); ) {
            String pkKey = pkIt.next();
            DBObject pk = m_sourceMap.get(pkKey);
            DBObject_PrimaryKey_Table pkHeader = (DBObject_PrimaryKey_Table) pk.getHeaders().get(0);
            String tableName = pkHeader.getTable();
            String pkTableName = tableName.toUpperCase();
            // but exclude translations and logs
            if (pkTableName.startsWith("AD_") && (!pkTableName.endsWith("_TRL")) && (!pkTableName.endsWith("LOG"))) {
                // list of Primary Key Columns
                ArrayList<String> pkColumns = new ArrayList<String>();
                Vector<Integer> v = new Vector<Integer>(pk.getContents().keySet());
                java.util.Collections.sort(v);
                for (Iterator<Integer> i = v.iterator(); i.hasNext(); ) {
                    Integer j = i.next();
                    DBObject_PrimaryKey_Column col = (DBObject_PrimaryKey_Column) pk.getContents().get(j);
                    pkColumns.add(col.getColumn());
                }
                String sqlCommand = s_dbEngine.sqlAction_dropDuplicates(targetVendor, targetCatalog, targetSchema, tableName, pkColumns);
                if (sqlCommand != null) {
                    Statement stmt = m_target.setStatement();
                    Integer sqlResult = m_target.executeUpdate(stmt, sqlCommand, false, false);
                    if (sqlResult != null) {
                        logDropDetail(sqlResult, null);
                        m_counterDrp = new Integer(m_counterDrp.intValue() + 1);
                    }
                    m_target.releaseStatement(stmt);
                }
            }
        }
        // release savepoint
        m_target.releaseSavepoint(sp);
    }
    logResults();
}

Example 8 with Savepoint

use of java.sql.Savepoint in project adempiere by adempiere.

the class DBObject method drop.

/**
	 * drop this object
	 * @return whether or not the object was successfully dropped
	 */
public boolean drop() {
    boolean result = false;
    if (isPopulated()) {
        s_logger.log(Level.FINE, "dropThisObject", new Object[] { getObjectType(), m_name, m_parent.getDirection() });
        // remember savepoint for rollback
        Savepoint sp = m_parent.setSavepoint("drop_object");
        // drop the object
        result = m_interface.dropObject(s_parameters, s_logger, s_dbEngine, m_parent, m_name, m_headers);
        // release savepoint
        m_parent.releaseSavepoint(sp);
    }
    return result;
}

Example 9 with Savepoint

use of java.sql.Savepoint in project robovm by robovm.

the class OldConnectionTest method testReleaseSavepoint_Savepoint.

// TODO Savepoint is not supported
public void testReleaseSavepoint_Savepoint() throws SQLException {
    Savepoint sp = new DummySavePoint();
    conn.setAutoCommit(false);
    try {
        conn.releaseSavepoint(sp);
    } catch (SQLException e) {
    //ok
    }
/*

        Statement st = null;
        Statement st1 = null;
        ResultSet rs1 = null;
        try {
            conn.setAutoCommit(false);

            st = conn.createStatement();
            st
                    .execute("insert into zoo (id, name, family) values (3, 'Vorobey', 'sparrow');");
            st
                    .execute("insert into zoo (id, name, family) values (4, 'Orel', 'eagle');");

            if (!conn.getAutoCommit()) {
                st1 = conn.createStatement();
                st1.execute("select * from zoo");
                rs1 = st1.getResultSet();
                assertEquals(4, getCount(rs1));
                Statement st2 = null;
                ResultSet rs2 = null;
                try {
                    Savepoint sp = conn.setSavepoint("one");
                    st
                            .execute("insert into zoo (id, name, family) values (5, 'chayka', 'gull');");
                    conn.rollback(sp);
                    st2 = conn.createStatement();
                    st2.execute("select * from zoo");
                    rs2 = st2.getResultSet();
                    assertEquals(4, getCount(rs2));
                    st
                            .execute("insert into zoo (id, name, family) values (5, 'chayka', 'gull');");
                    conn.releaseSavepoint(sp);
                    try {
                        conn.rollback(sp);
                        fail("SQLException is not thrown");
                    } catch (SQLException sqle) {
                        // expected
                    }
                    conn.rollback();
                } catch (SQLException e) {
                    fail("SQLException is thrown: " + e.toString());
                } finally {
                    try {
                        rs2.close();
                        st2.close();
                    } catch (Exception ee) {
                    }
                }

                try {
                    Savepoint sp1 = conn.setSavepoint("one");
                    st
                            .execute("insert into zoo (id, name, family) values (5, 'chayka', 'gull');");
                    Savepoint sp2 = conn.setSavepoint("two");
                    st
                            .execute("insert into zoo (id, name, family) values (6, 'grach', 'rook');");
                    conn.releaseSavepoint(sp1);
                    try {
                        conn.rollback(sp1);
                        fail("SQLException is not thrown");
                    } catch (SQLException sqle) {
                        // expected
                    }
                    conn.commit();
                    conn.rollback(sp2);
                    st2 = conn.createStatement();
                    st2.execute("select * from zoo");
                    rs2 = st2.getResultSet();
                    assertEquals(4, getCount(rs2));
                } catch (SQLException e) {
                    fail("SQLException is thrown: " + e.toString());
                } finally {
                    try {
                        rs2.close();
                        st2.close();
                    } catch (SQLException ee) {
                    }
                }

            } else {
                st1 = conn.createStatement();
                st1.execute("select * from zoo");
                rs1 = st1.getResultSet();
                assertEquals(4, getCount(rs1));
                try {
                    Savepoint sp = conn.setSavepoint("five");
                    st
                            .execute("insert into zoo (id, name, family) values (5, 'chayka', 'gull');");
                    conn.releaseSavepoint(sp);
                    fail("SQLException is not thrown");
                } catch (SQLException sqle) {
                    // expected
                }
            }
        } catch (SQLException sqle) {
            fail("SQLException is thrown: " + sqle.toString());
        } finally {
            try {
                rs1.close();
                st.close();
                st1.close();
            } catch (SQLException ee) {
            }
        }
        */
}

Example 10 with Savepoint

use of java.sql.Savepoint in project flyway by flyway.

the class TransactionTemplate method execute.

/**
     * Executes this callback within a transaction.
     *
     * @param transactionCallback The callback to execute.
     * @return The result of the transaction code.
     */
public <T> T execute(Callable<T> transactionCallback) {
    boolean oldAutocommit = true;
    try {
        oldAutocommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        T result = transactionCallback.call();
        connection.commit();
        return result;
    } catch (SQLException e) {
        throw new FlywaySqlException("Unable to commit transaction", e);
    } catch (Exception e) {
        Savepoint savepoint = null;
        RuntimeException rethrow;
        if (e instanceof RollbackWithSavepointException) {
            savepoint = ((RollbackWithSavepointException) e).getSavepoint();
            rethrow = (RuntimeException) e.getCause();
        } else {
            if (e instanceof RuntimeException) {
                rethrow = (RuntimeException) e;
            } else {
                rethrow = new FlywayException(e);
            }
        }
        if (rollbackOnException) {
            try {
                LOG.debug("Rolling back transaction...");
                if (savepoint == null) {
                    connection.rollback();
                } else {
                    connection.rollback(savepoint);
                }
                LOG.debug("Transaction rolled back");
            } catch (SQLException se) {
                LOG.error("Unable to rollback transaction", se);
            }
        } else {
            try {
                connection.commit();
            } catch (SQLException se) {
                LOG.error("Unable to commit transaction", se);
            }
        }
        throw rethrow;
    } finally {
        try {
            connection.setAutoCommit(oldAutocommit);
        } catch (SQLException e) {
            LOG.error("Unable to restore autocommit to original value for connection", e);
        }
    }
}