Examples with SqlException org.jumpmind.db.sql.SqlException used on opensource projects

Example 46 with SqlException

use of org.jumpmind.db.sql.SqlException in project symmetric-ds by JumpMind.

the class DbFill method insertRandomRecord.

/**
 * Select a random row from the table and update all columns except for primary and foreign keys.
 *
 * @param sqlTemplate
 * @param table
 */
private void insertRandomRecord(Table table) {
    DmlStatement insertStatement = createInsertDmlStatement(table);
    Row row = createRandomInsertValues(insertStatement, table);
    try {
        platform.getSqlTemplate().update(insertStatement.getSql(), insertStatement.getValueArray(row.toArray(table.getColumnNames()), row.toArray(table.getPrimaryKeyColumnNames())));
        if (verbose) {
            log.info("Successful update in " + table.getName());
        }
    } catch (SqlException ex) {
        log.info("Failed to process {} with values of {}", insertStatement.getSql(), ArrayUtils.toString(row.toArray(table.getColumnNames())));
        if (continueOnError) {
            if (debug) {
                log.info("", ex);
            }
        } else {
            throw ex;
        }
    }
}

Example 47 with SqlException

use of org.jumpmind.db.sql.SqlException in project symmetric-ds by JumpMind.

the class DbFill method deleteRandomRecord.

/**
 * Delete a random row in the given table or delete all rows matching selectColumns
 * in the given table.
 *
 * @param table Table to delete from.
 * @param selectColumns If provided, the rows that match this criteria are deleted.
 */
private void deleteRandomRecord(Table table) {
    DmlStatement deleteStatement = createDeleteDmlStatement(table);
    Row row = selectRandomRow(table);
    try {
        platform.getSqlTemplate().update(deleteStatement.getSql(), row.toArray(table.getColumnNames()));
        if (verbose) {
            log.info("Successful update in " + table.getName());
        }
    } catch (SqlException ex) {
        log.info("Failed to process {} with values of {}", deleteStatement.getSql(), ArrayUtils.toString(row.toArray(table.getColumnNames())));
        if (continueOnError) {
            if (debug) {
                log.info("", ex);
            }
        } else {
            throw ex;
        }
    }
}

Example 48 with SqlException

use of org.jumpmind.db.sql.SqlException in project symmetric-ds by JumpMind.

the class SqliteSymmetricDialect method truncateTable.

@Override
public void truncateTable(String tableName) {
    String quote = platform.getDdlBuilder().isDelimitedIdentifierModeOn() ? platform.getDatabaseInfo().getDelimiterToken() : "";
    boolean success = false;
    int tryCount = 5;
    while (!success && tryCount > 0) {
        try {
            Table table = platform.getTableFromCache(tableName, false);
            if (table != null) {
                platform.getSqlTemplate().update(String.format("delete from %s%s%s", quote, table.getName(), quote));
                success = true;
            } else {
                throw new RuntimeException(String.format("Could not find %s to trunate", tableName));
            }
        } catch (SqlException ex) {
            log.warn("Failed to truncate the " + tableName + " table", ex);
            AppUtils.sleep(5000);
            tryCount--;
        }
    }
}

Example 49 with SqlException

use of org.jumpmind.db.sql.SqlException in project symmetric-ds by JumpMind.

the class DefaultDatabaseWriter method insert.

@Override
protected LoadStatus insert(CsvData data) {
    try {
        statistics.get(batch).startTimer(DataWriterStatisticConstants.DATABASEMILLIS);
        if (requireNewStatement(DmlType.INSERT, data, false, true, null)) {
            this.lastUseConflictDetection = true;
            this.currentDmlStatement = platform.createDmlStatement(DmlType.INSERT, targetTable, writerSettings.getTextColumnExpression());
            if (log.isDebugEnabled()) {
                log.debug("Preparing dml: " + this.currentDmlStatement.getSql());
            }
            transaction.prepare(this.currentDmlStatement.getSql());
        }
        try {
            Conflict conflict = writerSettings.pickConflict(this.targetTable, batch);
            String[] values = (String[]) ArrayUtils.addAll(getRowData(data, CsvData.ROW_DATA), this.currentDmlStatement.getLookupKeyData(getLookupDataMap(data, conflict)));
            long count = execute(data, values);
            statistics.get(batch).increment(DataWriterStatisticConstants.INSERTCOUNT, count);
            if (count > 0) {
                return LoadStatus.SUCCESS;
            } else {
                context.put(CUR_DATA, getCurData(transaction));
                return LoadStatus.CONFLICT;
            }
        } catch (SqlException ex) {
            if (platform.getSqlTemplate().isUniqueKeyViolation(ex)) {
                if (!platform.getDatabaseInfo().isRequiresSavePointsInTransaction()) {
                    context.put(CONFLICT_ERROR, ex);
                    context.put(CUR_DATA, getCurData(transaction));
                    return LoadStatus.CONFLICT;
                } else {
                    log.info("Detected a conflict via an exception, but cannot perform conflict resolution because the database in use requires savepoints");
                    throw ex;
                }
            } else {
                throw ex;
            }
        }
    } catch (SqlException ex) {
        logFailureDetails(ex, data, true);
        throw ex;
    } finally {
        statistics.get(batch).stopTimer(DataWriterStatisticConstants.DATABASEMILLIS);
    }
}

Example 50 with SqlException

use of org.jumpmind.db.sql.SqlException in project symmetric-ds by JumpMind.

the class DefaultDatabaseWriter method update.

@Override
protected LoadStatus update(CsvData data, boolean applyChangesOnly, boolean useConflictDetection) {
    try {
        statistics.get(batch).startTimer(DataWriterStatisticConstants.DATABASEMILLIS);
        String[] rowData = getRowData(data, CsvData.ROW_DATA);
        String[] oldData = getRowData(data, CsvData.OLD_DATA);
        ArrayList<String> changedColumnNameList = new ArrayList<String>();
        ArrayList<String> changedColumnValueList = new ArrayList<String>();
        ArrayList<Column> changedColumnsList = new ArrayList<Column>();
        for (int i = 0; i < targetTable.getColumnCount(); i++) {
            Column column = targetTable.getColumn(i);
            if (column != null) {
                if (doesColumnNeedUpdated(i, column, data, rowData, oldData, applyChangesOnly)) {
                    changedColumnNameList.add(column.getName());
                    changedColumnsList.add(column);
                    changedColumnValueList.add(rowData[i]);
                }
            }
        }
        if (changedColumnNameList.size() > 0) {
            Map<String, String> lookupDataMap = null;
            Conflict conflict = writerSettings.pickConflict(this.targetTable, batch);
            if (requireNewStatement(DmlType.UPDATE, data, applyChangesOnly, useConflictDetection, conflict.getDetectType())) {
                lastApplyChangesOnly = applyChangesOnly;
                lastUseConflictDetection = useConflictDetection;
                List<Column> lookupKeys = null;
                if (!useConflictDetection) {
                    lookupKeys = targetTable.getPrimaryKeyColumnsAsList();
                } else {
                    switch(conflict.getDetectType()) {
                        case USE_CHANGED_DATA:
                            ArrayList<Column> lookupColumns = new ArrayList<Column>(changedColumnsList);
                            Column[] pks = targetTable.getPrimaryKeyColumns();
                            for (Column column : pks) {
                                // make sure all of the PK keys are in the
                                // list only once and are always at the end
                                // of the list
                                lookupColumns.remove(column);
                                lookupColumns.add(column);
                            }
                            removeExcludedColumns(conflict, lookupColumns);
                            lookupKeys = lookupColumns;
                            break;
                        case USE_OLD_DATA:
                            lookupKeys = targetTable.getColumnsAsList();
                            break;
                        case USE_VERSION:
                        case USE_TIMESTAMP:
                            lookupColumns = new ArrayList<Column>();
                            Column versionColumn = targetTable.getColumnWithName(conflict.getDetectExpression());
                            if (versionColumn != null) {
                                lookupColumns.add(versionColumn);
                            } else {
                                log.error("Could not find the timestamp/version column with the name {}.  Defaulting to using primary keys for the lookup.", conflict.getDetectExpression());
                            }
                            pks = targetTable.getPrimaryKeyColumns();
                            for (Column column : pks) {
                                // make sure all of the PK keys are in the
                                // list only once and are always at the end
                                // of the list
                                lookupColumns.remove(column);
                                lookupColumns.add(column);
                            }
                            lookupKeys = lookupColumns;
                            break;
                        case USE_PK_DATA:
                        default:
                            lookupKeys = targetTable.getPrimaryKeyColumnsAsList();
                            break;
                    }
                }
                if (lookupKeys == null || lookupKeys.size() == 0) {
                    lookupKeys = targetTable.getColumnsAsList();
                }
                int lookupKeyCountBeforeColumnRemoval = lookupKeys.size();
                Iterator<Column> it = lookupKeys.iterator();
                while (it.hasNext()) {
                    Column col = it.next();
                    if ((platform.isLob(col.getMappedTypeCode()) && data.isNoBinaryOldData()) || !platform.canColumnBeUsedInWhereClause(col)) {
                        it.remove();
                    }
                }
                if (lookupKeys.size() == 0) {
                    String msg = "There are no keys defined for " + targetTable.getFullyQualifiedTableName() + ".  Cannot build an update statement.  ";
                    if (lookupKeyCountBeforeColumnRemoval > 0) {
                        msg += "The only keys defined are binary and they have been removed.";
                    }
                    throw new IllegalStateException(msg);
                }
                lookupDataMap = getLookupDataMap(data, conflict);
                boolean[] nullKeyValues = new boolean[lookupKeys.size()];
                for (int i = 0; i < lookupKeys.size(); i++) {
                    Column column = lookupKeys.get(i);
                    // the isRequired is a bit of a hack. This nullKeyValues
                    // should really be checking against the object values
                    // because some null values get translated into empty
                    // strings
                    nullKeyValues[i] = !column.isRequired() && lookupDataMap.get(column.getName()) == null;
                }
                this.currentDmlStatement = platform.createDmlStatement(DmlType.UPDATE, targetTable.getCatalog(), targetTable.getSchema(), targetTable.getName(), lookupKeys.toArray(new Column[lookupKeys.size()]), changedColumnsList.toArray(new Column[changedColumnsList.size()]), nullKeyValues, writerSettings.getTextColumnExpression());
                if (log.isDebugEnabled()) {
                    log.debug("Preparing dml: " + this.currentDmlStatement.getSql());
                }
                transaction.prepare(this.currentDmlStatement.getSql());
            }
            rowData = (String[]) changedColumnValueList.toArray(new String[changedColumnValueList.size()]);
            lookupDataMap = lookupDataMap == null ? getLookupDataMap(data, conflict) : lookupDataMap;
            String[] values = (String[]) ArrayUtils.addAll(rowData, this.currentDmlStatement.getLookupKeyData(lookupDataMap));
            try {
                long count = execute(data, values);
                statistics.get(batch).increment(DataWriterStatisticConstants.UPDATECOUNT, count);
                if (count > 0) {
                    return LoadStatus.SUCCESS;
                } else {
                    context.put(CUR_DATA, getCurData(transaction));
                    return LoadStatus.CONFLICT;
                }
            } catch (SqlException ex) {
                if (platform.getSqlTemplate().isUniqueKeyViolation(ex) && !platform.getDatabaseInfo().isRequiresSavePointsInTransaction()) {
                    context.put(CUR_DATA, getCurData(transaction));
                    return LoadStatus.CONFLICT;
                } else {
                    throw ex;
                }
            }
        } else {
            if (log.isDebugEnabled()) {
                log.debug("Not running update for table {} with pk of {}.  There was no change to apply", targetTable.getFullyQualifiedTableName(), data.getCsvData(CsvData.PK_DATA));
            }
            // There was no change to apply
            return LoadStatus.SUCCESS;
        }
    } catch (SqlException ex) {
        logFailureDetails(ex, data, true);
        throw ex;
    } finally {
        statistics.get(batch).stopTimer(DataWriterStatisticConstants.DATABASEMILLIS);
    }
}