Java Code Examples for java.sql.CallableStatement.setString()

The following are Jave code examples for showing how to use setString() of the java.sql.CallableStatement class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
Example 1
Project: BibliotecaPS   File: CallableStatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * Tests fix for BUG#25379 - INOUT parameters in CallableStatements get
 * doubly-escaped.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug25379() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createTable("testBug25379", "(col char(40))");

    createProcedure("sp_testBug25379", "(INOUT invalue char(255))\nBEGIN" + "\ninsert into testBug25379(col) values(invalue);\nEND");

    CallableStatement cstmt = this.conn.prepareCall("{call sp_testBug25379(?)}");
    cstmt.setString(1, "'john'");
    cstmt.executeUpdate();
    assertEquals("'john'", cstmt.getString(1));
    assertEquals("'john'", getSingleValue("testBug25379", "col", "").toString());
}
 
Example 2
Project: OpenVertretung   File: CallableStatementRegressionTest.java   Source Code and License Vote up 6 votes
/**
 * Tests fix for BUG#25379 - INOUT parameters in CallableStatements get
 * doubly-escaped.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug25379() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createTable("testBug25379", "(col char(40))");

    createProcedure("sp_testBug25379", "(INOUT invalue char(255))\nBEGIN" + "\ninsert into testBug25379(col) values(invalue);\nEND");

    CallableStatement cstmt = this.conn.prepareCall("{call sp_testBug25379(?)}");
    cstmt.setString(1, "'john'");
    cstmt.executeUpdate();
    assertEquals("'john'", cstmt.getString(1));
    assertEquals("'john'", getSingleValue("testBug25379", "col", "").toString());
}
 
Example 3
Project: ats-framework   File: SQLServerDbReadAccess.java   Source Code and License Vote up 6 votes
public int getSuiteMessagesCount( String whereClause ) throws DatabaseAccessException {

        String sqlLog = new SqlRequestFormatter().add("where", whereClause).format();
        Connection connection = getConnection();
        CallableStatement callableStatement = null;
        ResultSet rs = null;
        try {

            callableStatement = connection.prepareCall("{ call sp_get_suite_messages_count(?) }");
            callableStatement.setString(1, whereClause);

            rs = callableStatement.executeQuery();
            int messagesCount = 0;
            if (rs.next()) {
                messagesCount = rs.getInt("messagesCount");
            }
            logQuerySuccess(sqlLog, "suite messages count", messagesCount);

            return messagesCount;
        } catch (Exception e) {
            throw new DatabaseAccessException("Error when " + sqlLog, e);
        } finally {
            DbUtils.closeResultSet(rs);
            DbUtils.close(connection, callableStatement);
        }
    }
 
Example 4
Project: ProyectoPacientes   File: CallableStatementRegressionTest.java   Source Code and License Vote up 6 votes
public void testBug35199() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    createFunction("test_function", "(a varchar(40), b bigint(20), c varchar(80)) RETURNS bigint(20) LANGUAGE SQL DETERMINISTIC "
            + "MODIFIES SQL DATA COMMENT 'bbb' BEGIN RETURN 1; END; ");

    CallableStatement callable = null;
    try {
        callable = this.conn.prepareCall("{? = call test_function(?,101,?)}");
        callable.registerOutParameter(1, Types.BIGINT);

        callable.setString(2, "FOO");
        callable.setString(3, "BAR");
        callable.executeUpdate();
    } finally {
        if (callable != null) {
            callable.close();
        }
    }
}
 
Example 5
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 6 votes
public void updateMachineInfo(
                               String machineName,
                               String machineInfo,
                               boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update the info about machine with name " + machineName;

    // then start the run
    final int indexRowsInserted = 3;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_machine_info(?, ?, ?) }");
        callableStatement.setString(1, machineName);
        callableStatement.setString(2, machineInfo);
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        }

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 
Example 6
Project: unitimes   File: BlobRoomAvailabilityService.java   Source Code and License Vote up 6 votes
protected void sendRequest(Document request) throws IOException {
    try {
        StringWriter writer = new StringWriter();
        (new XMLWriter(writer,OutputFormat.createPrettyPrint())).write(request);
        writer.flush(); writer.close();
        SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
        Connection connection = session.getJdbcConnectionAccess().obtainConnection();
        try {
            CallableStatement call = connection.prepareCall(iRequestSql);
            call.setString(1, writer.getBuffer().toString());
            call.execute();
            call.close();
        } finally {
        	session.getJdbcConnectionAccess().releaseConnection(connection);
        }
    } catch (Exception e) {
        sLog.error("Unable to send request: "+e.getMessage(),e);
    } finally {
        _RootDAO.closeCurrentThreadSessions();
    }
}
 
Example 7
Project: ats-framework   File: SQLServerDbReadAccess.java   Source Code and License Vote up 5 votes
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 8
Project: ats-framework   File: SQLServerDbReadAccess.java   Source Code and License Vote up 5 votes
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 9
Project: unitimes   File: ImportXmlFromDB.java   Source Code and License Vote up 5 votes
public static void importXml(String baseFileName){
    	Debug.info("filename = " + baseFileName);
   	try {
   		String fileReceiveSql =
   	        ApplicationProperties.getProperty("tmtbl.data.exchange.receive.file","{?= call timetable.receive_xml_file.receive_file(?, ?)}");
   		String exchangeDir = 
   	    	ApplicationProperties.getProperty("tmtbl.data.exchange.directory", "LOAD_SMASDEV");
           SessionImplementor session = (SessionImplementor)new _RootDAO().getSession();
           Connection connection = session.getJdbcConnectionAccess().obtainConnection();
           CallableStatement call = connection.prepareCall(fileReceiveSql);
           call.registerOutParameter(1, java.sql.Types.CLOB);
	    call.setString(2, exchangeDir);
           call.setString(3, baseFileName);
           call.execute();
           String response = call.getString(1);
           call.close();
           session.getJdbcConnectionAccess().releaseConnection(connection);
           if (response==null || response.length()==0) return;
           StringReader reader = new StringReader(response);
           Document document = (new SAXReader()).read(reader);
           reader.close();
           DataExchangeHelper.importDocument(document, null, null);           
	} catch (Exception e) {
		e.printStackTrace();
	} 
	
}
 
Example 10
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 5 votes
public int populateCheckpointSummary( int loadQueueId, String name, String transferRateUnit,
                                      boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to populate checkpoint summary '" + name + "' in load queue " + loadQueueId;

    final int indexCheckpointSummaryId = 4;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_populate_checkpoint_summary(?, ?, ?, ?) }");
        callableStatement.setInt(1, loadQueueId);
        callableStatement.setString(2, name);
        callableStatement.setString(3, transferRateUnit);
        callableStatement.registerOutParameter(indexCheckpointSummaryId, Types.INTEGER);

        callableStatement.execute();

        if (callableStatement.getInt(indexCheckpointSummaryId) == 0) {
            throw new DatabaseAccessException(errMsg + " - checkpoint summary ID returned was 0");
        }

        return callableStatement.getInt(indexCheckpointSummaryId);

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 
Example 11
Project: ats-framework   File: PGDbWriteAccess.java   Source Code and License Vote up 5 votes
@Override
public void insertUserActivityStatistics( int testCaseId, String machine, String statisticIds,
                                          String statisticValues, long timestamp,
                                          boolean closeConnection ) throws DatabaseAccessException {

    timestamp = inUTC(timestamp);

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_insert_user_activity_statistic_by_ids(?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testCaseId);
        callableStatement.setString(2, machine);
        callableStatement.setString(3, statisticIds);
        callableStatement.setString(4, statisticValues);
        callableStatement.setTimestamp(5, new Timestamp(timestamp));

        callableStatement.execute();

    } catch (Exception e) {
        String errMsg = "Unable to insert user activity statistics, statistic IDs '" + statisticIds
                        + "', statistic values '" + statisticValues + "', timestamp " + timestamp;
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 
Example 12
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 4 votes
/**
 * Update the static information about an existing run
 *
 * @param runId
 * @param runName
 * @param osName
 * @param productName
 * @param versionName
 * @param buildName
 * @param userNote
 * @param hostName
 * @param closeConnection
 * @throws DatabaseAccessException
 */
public void updateRun(
                       int runId,
                       String runName,
                       String osName,
                       String productName,
                       String versionName,
                       String buildName,
                       String userNote,
                       String hostName,
                       boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update run with name '" + runName + "' and id " + runId;

    // then start the run
    final int indexRowsUpdate = 9;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_run(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, runId);
        callableStatement.setString(2, productName);
        callableStatement.setString(3, versionName);
        callableStatement.setString(4, buildName);
        callableStatement.setString(5, runName);
        callableStatement.setString(6, osName);
        callableStatement.setString(7, userNote);
        callableStatement.setString(8, hostName);
        callableStatement.registerOutParameter(indexRowsUpdate, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsUpdate) != 1) {
            throw new DatabaseAccessException(errMsg);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 
Example 13
Project: ats-framework   File: SQLServerDbReadAccess.java   Source Code and License Vote up 4 votes
public List<Statistic> getSystemStatistics( float timeOffset,
                                            String testcaseIds,
                                            String machineIds,
                                            String statsTypeIds,
                                            int utcTimeOffset,
                                            boolean dayLightSavingOn )
                                                                       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();

    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.setDate(rs.getString("statsAxis"));

            long startTimestamp = rs.getInt("statsAxisTimestamp");
            if (dayLightSavingOn) {
                startTimestamp += 3600; // add 1h to time stamp
            }
            statistic.setStartTimestamp(startTimestamp);
            statistic.setTimeOffset(utcTimeOffset);

            statistic.machineId = rs.getInt("machineId");
            statistic.testcaseId = rs.getInt("testcaseId");

            numberRecords++;
            // add the combined statistics to the others
            allStatistics.add(statistic);
        }

        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 14
Project: the-vigilantes   File: CallableStatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for BUG#15464 - INOUT parameter does not store IN value.
 * 
 * @throws Exception
 *             if the test fails
 */

public void testBug15464() throws Exception {
    if (!serverSupportsStoredProcedures()) {
        return;
    }

    createProcedure("testInOutParam",
            "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\n" + "SET z = p2 + 1;\nSET p2 = z;\nSELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

    CallableStatement storedProc = null;

    storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

    storedProc.setString(1, "abcd");
    storedProc.setInt(2, 4);
    storedProc.registerOutParameter(2, Types.INTEGER);

    storedProc.execute();

    assertEquals(5, storedProc.getInt(2));
}
 
Example 15
Project: ats-framework   File: SQLServerDbWriteAccess.java   Source Code and License Vote up 4 votes
public void updateTestcase(
                            String suiteFullName,
                            String scenarioName,
                            String scenarioDescription,
                            String testcaseName,
                            String userNote,
                            int testcaseResult,
                            int testcaseId,
                            long timestamp,
                            boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to update testcase with name '" + testcaseName + "' and id " + testcaseId;

    timestamp = inUTC(timestamp);

    final int indexRowsUpdate = 9;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_update_testcase(?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testcaseId);
        callableStatement.setString(2, suiteFullName);
        callableStatement.setString(3, scenarioName);
        callableStatement.setString(4, scenarioDescription);
        callableStatement.setString(5, testcaseName);
        callableStatement.setString(6, userNote);
        callableStatement.setInt(7, testcaseResult);
        callableStatement.setTimestamp(8, new Timestamp(timestamp));
        callableStatement.registerOutParameter(indexRowsUpdate, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsUpdate) != 1) {
            throw new DatabaseAccessException(errMsg);
        }
    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }

}
 
Example 16
Project: the-vigilantes   File: CallableStatementTest.java   Source Code and License Vote up 4 votes
/**
 * Tests functioning of inout parameters
 * 
 * @throws Exception
 *             if the test fails
 */

public void testInOutParams() throws Exception {
    if (versionMeetsMinimum(5, 0)) {
        CallableStatement storedProc = null;

        createProcedure("testInOutParam", "(IN p1 VARCHAR(255), INOUT p2 INT)\nbegin\n DECLARE z INT;\nSET z = p2 + 1;\nSET p2 = z;\n"
                + "SELECT p1;\nSELECT CONCAT('zyxw', p1);\nend\n");

        storedProc = this.conn.prepareCall("{call testInOutParam(?, ?)}");

        storedProc.setString(1, "abcd");
        storedProc.setInt(2, 4);
        storedProc.registerOutParameter(2, Types.INTEGER);

        storedProc.execute();

        assertEquals(5, storedProc.getInt(2));

    }
}
 
Example 17
Project: the-vigilantes   File: CallableStatementRegressionTest.java   Source Code and License Vote up 4 votes
/**
 * Tests fix for Bug#84324 - CallableStatement.extractProcedureName() not work when catalog name with dash.
 */
public void testBug84324() throws Exception {
    createDatabase("`testBug84324-db`");

    /*
     * Test procedure.
     */
    createProcedure("`testBug84324-db`.`testBug84324-proc`", "(IN a INT, INOUT b VARCHAR(100)) BEGIN SELECT a, b; END");

    final CallableStatement cstmtP = this.conn.prepareCall("CALL testBug84324-db.testBug84324-proc(?, ?)");
    ParameterMetaData pmd = cstmtP.getParameterMetaData();

    assertEquals(2, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(2));
    assertEquals(Types.VARCHAR, pmd.getParameterType(2));
    assertEquals(String.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeInOut, pmd.getParameterMode(2));

    cstmtP.setInt(1, 1);
    cstmtP.setString(2, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtP.execute();
            return null;
        }
    }); // Although the procedure metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();

    /*
     * Test function.
     */
    createFunction("`testBug84324-db`.`testBug84324-func`", "(a INT, b VARCHAR(123)) RETURNS INT BEGIN RETURN a + LENGTH(b); END");

    final CallableStatement cstmtF = this.conn.prepareCall("{? = CALL testBug84324-db.testBug84324-func(?, ?)}");
    pmd = cstmtF.getParameterMetaData();

    assertEquals(3, pmd.getParameterCount());
    // 1st parameter
    assertEquals("INT", pmd.getParameterTypeName(1));
    assertEquals(Types.INTEGER, pmd.getParameterType(1));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(1));
    assertEquals(ParameterMetaData.parameterModeOut, pmd.getParameterMode(1));
    // 2nd parameter
    assertEquals("INT", pmd.getParameterTypeName(2));
    assertEquals(Types.INTEGER, pmd.getParameterType(2));
    assertEquals(Integer.class.getName(), pmd.getParameterClassName(2));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(2));
    // 3rd parameter
    assertEquals("VARCHAR", pmd.getParameterTypeName(3));
    assertEquals(Types.VARCHAR, pmd.getParameterType(3));
    assertEquals(String.class.getName(), pmd.getParameterClassName(3));
    assertEquals(ParameterMetaData.parameterModeIn, pmd.getParameterMode(3));

    cstmtF.registerOutParameter(1, Types.INTEGER);
    cstmtF.setInt(2, 1);
    cstmtF.setString(3, "foo");
    assertThrows(SQLException.class, new Callable<Void>() {
        public Void call() throws Exception {
            cstmtF.execute();
            return null;
        }
    }); // Although the function metadata could be obtained, the end query actually fails due to syntax errors.
    cstmtP.close();
    cstmtF.close();
}
 
Example 18
Project: ats-framework   File: SQLServerDbReadAccess.java   Source Code and License Vote up 4 votes
public List<Message> getMessages( int startRecord, int recordsCount, String whereClause,
                                  String sortColumn, boolean ascending,
                                  int utcTimeOffset ) throws DatabaseAccessException {

    List<Message> messages = new ArrayList<Message>();

    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_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();
        Map<Integer, Message> splitMessages = new HashMap<Integer, Message>(); // <parentMessageId, Message>
        while (rs.next()) {
            Message message = new Message();
            message.messageId = rs.getInt("messageId");
            message.messageContent = rs.getString("message");
            message.messageType = rs.getString("typeName");

            if (rs.getTimestamp("timestamp") != null) {
                message.setStartTimestamp(rs.getTimestamp("timestamp").getTime());
            }

            message.setTimeOffset(utcTimeOffset);

            message.machineName = rs.getString("machineName");
            message.threadName = rs.getString("threadName");
            message.parentMessageId = rs.getInt("parentMessageId");

            if (message.parentMessageId != 0) {
                // split message
                if (splitMessages.containsKey(message.parentMessageId)) {
                    // append to the message - result set is ordered by message ID
                    Message splitMessage = splitMessages.get(message.parentMessageId);
                    if (splitMessage.messageId < message.messageId) {
                        // append at the end
                        splitMessage.messageContent = splitMessage.messageContent
                                                      + message.messageContent;
                    } else {
                        // append at the beginning
                        splitMessage.messageContent = message.messageContent
                                                      + splitMessage.messageContent;
                    }
                } else {
                    // first part of the split message
                    splitMessages.put(message.parentMessageId, message);
                    messages.add(message);
                }
            } else {
                // single message
                messages.add(message);
            }
            numberRecords++;
        }

        logQuerySuccess(sqlLog, "messages", numberRecords);
    } catch (Exception e) {
        throw new DatabaseAccessException("Error when " + sqlLog, e);
    } finally {
        DbUtils.closeResultSet(rs);
        DbUtils.close(connection, callableStatement);
    }
    return messages;
}
 
Example 19
Project: ats-framework   File: PGDbWriteAccess.java   Source Code and License Vote up 4 votes
public CheckpointInfo startCheckpoint(
                                       String name,
                                       long startTimestamp,
                                       String transferRateUnit,
                                       int loadQueueId,
                                       boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to start checkpoint '" + name + "' in load queue " + loadQueueId;

    startTimestamp = inUTC(startTimestamp);

    final int indexCheckpointSummaryId = 5;
    final int indexCheckpointId = 6;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_start_checkpoint(?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, loadQueueId);
        callableStatement.setString(2, name);
        callableStatement.setInt(3, checkpointLogLevel.toInt());
        callableStatement.setString(4, transferRateUnit);
        callableStatement.registerOutParameter(indexCheckpointSummaryId, Types.INTEGER);
        callableStatement.registerOutParameter(indexCheckpointId, Types.INTEGER);

        callableStatement.execute();

        // we always update the checkpoint summary table
        if (callableStatement.getInt(indexCheckpointSummaryId) == 0) {
            throw new DatabaseAccessException(errMsg + " - checkpoint summary ID returned was 0");
        }

        // we update the checkpoint table only in FULL mode
        if (checkpointLogLevel == CheckpointLogLevel.FULL
            && callableStatement.getInt(indexCheckpointId) == 0) {
            throw new DatabaseAccessException(errMsg + " - checkpoint ID returned was 0");
        }

        int checkpointSummaryId = callableStatement.getInt(indexCheckpointSummaryId);
        int checkpointId = callableStatement.getInt(indexCheckpointId);

        return new CheckpointInfo(name, checkpointSummaryId, checkpointId, startTimestamp);

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 
Example 20
Project: OpenVertretung   File: CallableStatementTest.java   Source Code and License Vote up 2 votes
/**
 * Tests parsing of stored procedures
 * 
 * @throws Exception
 *             if an error occurs.
 */
public void testSPCache() throws Exception {
    if (versionMeetsMinimum(5, 0)) {

        CallableStatement storedProc = null;

        createProcedure("testSpParse", "(IN FOO VARCHAR(15))\nBEGIN\nSELECT 1;\nend\n");

        int numIterations = 10;

        long startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = this.conn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        long elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Standard parsing/execution: " + elapsedTime + " ms");

        storedProc = this.conn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

        Properties props = new Properties();
        props.setProperty("cacheCallableStmts", "true");

        Connection cachedSpConn = getConnectionWithProps(props);

        startTime = System.currentTimeMillis();

        for (int i = 0; i < numIterations; i++) {
            storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
            storedProc.close();
        }

        elapsedTime = System.currentTimeMillis() - startTime;

        System.out.println("Cached parse stage: " + elapsedTime + " ms");

        storedProc = cachedSpConn.prepareCall("{call testSpParse(?)}");
        storedProc.setString(1, "abc");
        this.rs = storedProc.executeQuery();

        assertTrue(this.rs.next());
        assertTrue(this.rs.getInt(1) == 1);

    }
}