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

Example 71 with CallableStatement

use of java.sql.CallableStatement in project ats-framework by Axway.

the class DbReadAccess method getLoadQueues.

public List<LoadQueue> getLoadQueues(String whereClause, String sortColumn, boolean ascending, boolean dateFormatNoYear) throws DatabaseAccessException {
    List<LoadQueue> loadQueues = new ArrayList<LoadQueue>();
    String sqlLog = new SqlRequestFormatter().add("where", whereClause).add("sort by", sortColumn).add("asc", ascending).format();
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {
        callableStatement = connection.prepareCall("{ call sp_get_loadqueues(?, ?, ?) }");
        callableStatement.setString(1, "where " + whereClause);
        callableStatement.setString(2, sortColumn);
        callableStatement.setString(3, (ascending ? "ASC" : "DESC"));
        rs = callableStatement.executeQuery();
        int numberRecords = 0;
        while (rs.next()) {
            LoadQueue loadQueue = new LoadQueue();
            loadQueue.loadQueueId = rs.getInt("loadQueueId");
            loadQueue.name = rs.getString("name");
            loadQueue.sequence = rs.getInt("sequence");
            loadQueue.hostsList = rs.getString("hostsList");
            loadQueue.threadingPattern = rs.getString("threadingPattern");
            loadQueue.numberThreads = rs.getInt("numberThreads");
            if (loadQueue.threadingPattern != null) {
                loadQueue.threadingPattern = loadQueue.threadingPattern.replace("<number_threads>", String.valueOf(loadQueue.numberThreads));
            }
            if (dateFormatNoYear) {
                loadQueue.dateStart = formatDateNoYear(rs.getTimestamp("dateStart"));
                loadQueue.dateEnd = formatDateNoYear(rs.getTimestamp("dateEnd"));
            } else {
                loadQueue.dateStart = formatDate(rs.getTimestamp("dateStart"));
                loadQueue.dateEnd = formatDate(rs.getTimestamp("dateEnd"));
            }
            int duration = rs.getInt("duration");
            if (duration < 0) {
                // this may happen when the load queue is not ended and the time of the log server
                // is behind with the time of the test executor host
                duration = 0;
            }
            loadQueue.duration = formatTimeDiffereceFromSecondsToString(duration);
            loadQueue.result = rs.getInt("result");
            /*
                 *   -- 0 FAILED
                 *   -- 1 PASSED
                 *   -- 2 SKIPPED
                 *   -- 4 RUNNING
                 */
            switch(loadQueue.result) {
                case 0:
                    loadQueue.state = "FAILED";
                    break;
                case 1:
                    loadQueue.state = "PASSED";
                    break;
                case 2:
                    loadQueue.state = "SKIPPED";
                    break;
                case 4:
                    loadQueue.state = "RUNNING";
                    break;
                default:
                    //TODO: add warning
                    loadQueue.state = "unknown";
            }
            loadQueues.add(loadQueue);
            numberRecords++;
        }
        logQuerySuccess(sqlLog, "loadqueues", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }
    return loadQueues;
}

Example 72 with CallableStatement

use of java.sql.CallableStatement in project ats-framework by Axway.

the class DbReadAccess method getSystemStatistics.

public List<Statistic> getSystemStatistics(float timeOffset, String testcaseIds, String machineIds, String statsTypeIds, Set<String> expectedStatisticUIDs, Set<Integer> expectedSingleStatisticIDs, Set<Integer> expectedCombinedStatisticIDs) throws DatabaseAccessException {
    List<Statistic> allStatistics = new ArrayList<Statistic>();
    String sqlLog = new SqlRequestFormatter().add("fdate", formatDateFromEpoch(timeOffset)).add("testcase ids", testcaseIds).add("machine ids", machineIds).add("stats type ids", statsTypeIds).format();
    /*
         * Combined statistics do not have real statistic IDs, but Test Explorer needs some.
         * We must provide unique IDs
         */
    Map<String, Integer> statisticFakeIds = new HashMap<String, Integer>();
    /*
         * The DB does not contain combined statistics, so we must create them.
         * All values of statistic with same statistic ID and timestamp are summed into 1 statistic
         */
    Map<String, Statistic> combinedStatistics = new HashMap<String, Statistic>();
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {
        callableStatement = connection.prepareCall("{ call sp_get_system_statistics(?, ?, ?, ?) }");
        callableStatement.setString(1, formatDateFromEpoch(timeOffset));
        callableStatement.setString(2, testcaseIds);
        callableStatement.setString(3, machineIds);
        callableStatement.setString(4, statsTypeIds);
        rs = callableStatement.executeQuery();
        int numberRecords = 0;
        while (rs.next()) {
            Statistic statistic = new Statistic();
            statistic.statisticTypeId = rs.getInt("statsTypeId");
            statistic.name = rs.getString("statsName");
            statistic.parentName = rs.getString("statsParent");
            statistic.unit = rs.getString("statsUnit");
            statistic.value = rs.getFloat("value");
            statistic.date = rs.getString("statsAxis");
            statistic.timestamp = rs.getInt("statsAxisTimestamp");
            statistic.machineId = rs.getInt("machineId");
            statistic.testcaseId = rs.getInt("testcaseId");
            boolean theUidIsExpected = false;
            if (expectedStatisticUIDs == null) {
                // used when copying a testcase
                theUidIsExpected = true;
            } else {
                String statisticUidToMatch = statistic.getUid().replace("[", "").replace("]", "");
                for (String expectedStatisticUID : expectedStatisticUIDs) {
                    // we use matchers for combining statistics into virtual containers
                    if (statisticUidToMatch.matches(expectedStatisticUID.replace("[", "").replace("]", ""))) {
                        theUidIsExpected = true;
                        break;
                    }
                }
            }
            if (theUidIsExpected) {
                // add to single statistics
                if (expectedSingleStatisticIDs == null) {
                    // used when copying a testcase
                    allStatistics.add(statistic);
                } else if (expectedSingleStatisticIDs.contains(statistic.statisticTypeId)) {
                    allStatistics.add(statistic);
                }
                // add to combined statistics
                if (expectedCombinedStatisticIDs != null && expectedCombinedStatisticIDs.contains(statistic.statisticTypeId)) {
                    String statisticTempKey = statistic.statisticTypeId + "->" + statistic.timestamp;
                    Statistic combinedStatistic = combinedStatistics.get(statisticTempKey);
                    if (combinedStatistic == null) {
                        // create a new combined statistic
                        combinedStatistic = new Statistic();
                        combinedStatistic.name = statistic.name;
                        combinedStatistic.parentName = Statistic.COMBINED_STATISTICS_CONTAINER;
                        combinedStatistic.unit = statistic.unit;
                        combinedStatistic.timestamp = statistic.timestamp;
                        combinedStatistic.machineId = statistic.machineId;
                        combinedStatistic.testcaseId = statistic.testcaseId;
                        combinedStatistic.statisticTypeId = getStatisticFakeId(DbReadAccess.START_FAKE_ID_VALUE_FOR_AGGREGATED_SYSTEM_STATISTICS, statisticFakeIds, combinedStatistic);
                        combinedStatistics.put(statisticTempKey, combinedStatistic);
                    }
                    // calculate the combined value
                    combinedStatistic.value = combinedStatistic.value + statistic.value;
                }
                numberRecords++;
            }
        }
        if (combinedStatistics.size() > 0) {
            // sort the combined statistics by their timestamps
            List<Statistic> sortedStatistics = new ArrayList<Statistic>(combinedStatistics.values());
            Collections.sort(sortedStatistics, new Comparator<Statistic>() {

                @Override
                public int compare(Statistic stat1, Statistic stat2) {
                    return (int) (stat1.timestamp - stat2.timestamp);
                }
            });
            // add the combined statistics to the others
            allStatistics.addAll(sortedStatistics);
        }
        logQuerySuccess(sqlLog, "system statistics", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }
    return allStatistics;
}

Example 73 with CallableStatement

use of java.sql.CallableStatement in project ats-framework by Axway.

the class DbReadAccess method getSuitesCount.

public int getSuitesCount(String whereClause) throws DatabaseAccessException {
    Connection connection = getConnection();
    String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {
        callableStatement = connection.prepareCall("{ call sp_get_suites_count(?) }");
        callableStatement.setString(1, whereClause);
        rs = callableStatement.executeQuery();
        int suitesCount = 0;
        while (rs.next()) {
            suitesCount = rs.getInt("suitesCount");
            logQuerySuccess(sqlLog, "suites", suitesCount);
            break;
        }
        return suitesCount;
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }
}

Example 74 with CallableStatement

use of java.sql.CallableStatement in project ats-framework by Axway.

the class DbReadAccess method getRunsCount.

public int getRunsCount(String whereClause) throws DatabaseAccessException {
    Connection connection = getConnection();
    String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {
        callableStatement = connection.prepareCall("{ call sp_get_runs_count(?) }");
        callableStatement.setString(1, whereClause);
        rs = callableStatement.executeQuery();
        int runsCount = 0;
        while (rs.next()) {
            runsCount = rs.getInt("runsCount");
            logQuerySuccess(sqlLog, "runs", runsCount);
            break;
        }
        return runsCount;
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }
}

Example 75 with CallableStatement

use of java.sql.CallableStatement in project ats-framework by Axway.

the class DbReadAccess method getSuiteMessages.

public List<Message> getSuiteMessages(int startRecord, int recordsCount, String whereClause, String sortColumn, boolean ascending) throws DatabaseAccessException {
    List<Message> suiteMessages = new ArrayList<Message>();
    SimpleDateFormat dateFormat = new SimpleDateFormat("MMM dd");
    SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss:S");
    String sqlLog = new SqlRequestFormatter().add("start record", startRecord).add("records", recordsCount).add("where", whereClause).add("sort by", sortColumn).add("asc", ascending).format();
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    ResultSet rs = null;
    try {
        callableStatement = connection.prepareCall("{ call sp_get_suite_messages(?, ?, ?, ?, ?) }");
        callableStatement.setString(1, String.valueOf(startRecord));
        callableStatement.setString(2, String.valueOf(recordsCount));
        callableStatement.setString(3, whereClause);
        callableStatement.setString(4, sortColumn);
        callableStatement.setString(5, (ascending ? "ASC" : "DESC"));
        int numberRecords = 0;
        rs = callableStatement.executeQuery();
        while (rs.next()) {
            Message suiteMessage = new Message();
            suiteMessage.messageId = rs.getInt("suiteMessageId");
            suiteMessage.messageContent = rs.getString("message");
            suiteMessage.messageType = rs.getString("typeName");
            Timestamp timestamp = rs.getTimestamp("timestamp");
            suiteMessage.date = dateFormat.format(timestamp);
            suiteMessage.time = timeFormat.format(timestamp);
            suiteMessage.machineName = rs.getString("machineName");
            suiteMessage.threadName = rs.getString("threadName");
            suiteMessages.add(suiteMessage);
            numberRecords++;
        }
        logQuerySuccess(sqlLog, "suite messages", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }
    return suiteMessages;
}