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

Example 16 with ISqlTemplate

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

the class RestService method queryNodeImpl.

private QueryResults queryNodeImpl(ISymmetricEngine engine, String sql, boolean isQuery) {
    QueryResults results = new QueryResults();
    org.jumpmind.symmetric.web.rest.model.Row xmlRow = null;
    org.jumpmind.symmetric.web.rest.model.Column xmlColumn = null;
    ISqlTemplate sqlTemplate = engine.getSqlTemplate();
    try {
        if (!isQuery) {
            int updates = sqlTemplate.update(sql);
            results.setNbrResults(updates);
            return results;
        }
        List<Row> rows = sqlTemplate.query(sql);
        int nbrRows = 0;
        for (Row row : rows) {
            xmlRow = new org.jumpmind.symmetric.web.rest.model.Row();
            Iterator<Map.Entry<String, Object>> itr = row.entrySet().iterator();
            int columnOrdinal = 0;
            while (itr.hasNext()) {
                xmlColumn = new org.jumpmind.symmetric.web.rest.model.Column();
                xmlColumn.setOrdinal(++columnOrdinal);
                Map.Entry<String, Object> pair = (Map.Entry<String, Object>) itr.next();
                xmlColumn.setName(pair.getKey());
                if (pair.getValue() != null) {
                    xmlColumn.setValue(pair.getValue().toString());
                }
                xmlRow.getColumnData().add(xmlColumn);
            }
            xmlRow.setRowNum(++nbrRows);
            results.getResults().add(xmlRow);
        }
        results.setNbrResults(nbrRows);
    } catch (Exception ex) {
        log.error("Exception while executing sql.", ex);
        throw new NotAllowedException("Error while executing sql %s.  Error is %s", sql, ex.getCause().getMessage());
    }
    return results;
}

Example 17 with ISqlTemplate

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

the class DataGapFastDetector method saveDataGaps.

protected long saveDataGaps(long ts, long printStats) {
    ISqlTemplate sqlTemplate = symmetricDialect.getPlatform().getSqlTemplate();
    int totalGapChanges = gapsDeleted.size() + gapsAdded.size();
    if (totalGapChanges > 0) {
        ISqlTransaction transaction = null;
        gaps = new ArrayList<DataGap>(gapsAll);
        Collections.sort(gaps);
        try {
            transaction = sqlTemplate.startSqlTransaction();
            int maxGapChanges = parameterService.getInt(ParameterConstants.ROUTING_MAX_GAP_CHANGES);
            if (!parameterService.is(ParameterConstants.CLUSTER_LOCKING_ENABLED) && (totalGapChanges > maxGapChanges || useInMemoryGaps)) {
                dataService.deleteAllDataGaps(transaction);
                if (useInMemoryGaps && totalGapChanges <= maxGapChanges) {
                    log.info("There are {} data gap changes, which is within the max of {}, so switching to database", totalGapChanges, maxGapChanges);
                    useInMemoryGaps = false;
                    printStats = insertDataGaps(transaction, ts, printStats);
                } else {
                    if (!useInMemoryGaps) {
                        log.info("There are {} data gap changes, which exceeds the max of {}, so switching to in-memory", totalGapChanges, maxGapChanges);
                        useInMemoryGaps = true;
                    }
                    DataGap newGap = new DataGap(gaps.get(0).getStartId(), gaps.get(gaps.size() - 1).getEndId());
                    dataService.insertDataGap(transaction, newGap);
                }
            } else {
                printStats = deleteDataGaps(transaction, ts, printStats);
                printStats = insertDataGaps(transaction, ts, printStats);
            }
            transaction.commit();
        } catch (Error ex) {
            if (transaction != null) {
                transaction.rollback();
            }
            throw ex;
        } catch (RuntimeException ex) {
            if (transaction != null) {
                transaction.rollback();
            }
            throw ex;
        } finally {
            if (transaction != null) {
                transaction.close();
            }
        }
    }
    return printStats;
}

Example 18 with ISqlTemplate

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

the class DataGapFastDetector method queryDataIdMap.

protected void queryDataIdMap() {
    String sql = routerService.getSql("selectDistinctDataIdFromDataEventUsingGapsSql");
    ISqlTemplate sqlTemplate = symmetricDialect.getPlatform().getSqlTemplate();
    for (DataGap dataGap : gaps) {
        long queryForIdsTs = System.currentTimeMillis();
        Object[] params = new Object[] { dataGap.getStartId(), dataGap.getEndId() };
        List<Long> ids = sqlTemplate.query(sql, this, params);
        dataIds.addAll(ids);
        if (System.currentTimeMillis() - queryForIdsTs > Constants.LONG_OPERATION_THRESHOLD) {
            log.info("It took longer than {}ms to run the following sql for gap from {} to {}.  {}", new Object[] { Constants.LONG_OPERATION_THRESHOLD, dataGap.getStartId(), dataGap.getEndId(), sql });
        }
    }
}

Example 19 with ISqlTemplate

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

the class TransformationTest method testDeletesWithTransformedIdWork.

protected void testDeletesWithTransformedIdWork(ISymmetricEngine rootServer, ISymmetricEngine clientServer) throws Exception {
    String rootTableName = rootServer.getDatabasePlatform().getTableFromCache("TRANSFORM_TABLE_A_SRC", false).getName();
    String clientTableName = clientServer.getDatabasePlatform().getTableFromCache("TRANSFORM_TABLE_A_TGT", false).getName();
    ISqlTemplate rootTemplate = rootServer.getDatabasePlatform().getSqlTemplate();
    ISqlTemplate clientTemplate = clientServer.getDatabasePlatform().getSqlTemplate();
    rootTemplate.update(String.format("insert into %s values(?,?)", rootTableName), "1", 1);
    assertEquals(0, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
    pull("client");
    assertEquals(1, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
    rootTemplate.update(String.format("delete from %s", rootTableName));
    assertEquals(1, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
    pull("client");
    assertEquals(0, clientTemplate.queryForInt(String.format("select count(*) from %s", clientTableName)));
}

Example 20 with ISqlTemplate

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

the class MsSqlSymmetricDialect method verifyDatabaseIsCompatible.

@Override
public void verifyDatabaseIsCompatible() {
    super.verifyDatabaseIsCompatible();
    ISqlTemplate template = getPlatform().getSqlTemplate();
    if (template.queryForInt("select case when (512 & @@options) = 512 then 1 else 0 end") == 1) {
        throw new SymmetricException("NOCOUNT is currently turned ON.  SymmetricDS will not function with NOCOUNT turned ON.");
    }
}