Java Code Examples for java.sql.CallableStatement#setTimestamp()

/**
 * Calls a SQL procedure that takes non-numeric IN and OUT parameters.
 * @throws SQLException 
 */
public void testNonNumericTypesInAndOutProc() throws SQLException
{
    CallableStatement cs = prepareCall
        ("call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");

    cs.setDate(1, Date.valueOf("2002-05-12"));
    cs.setTime(2, Time.valueOf("10:05:02"));
    cs.setTimestamp(3, Timestamp.valueOf("2002-05-12 10:05:02.000000000"));
    byte[] ba = new byte[2];
    ba[0] = 1;
    ba[1] = 2;
    cs.setBytes(4, ba);

    cs.registerOutParameter (5, java.sql.Types.DATE);
    cs.registerOutParameter (6, java.sql.Types.TIME);
    cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
    cs.registerOutParameter (8, java.sql.Types.VARBINARY);

    cs.execute();

    assertEquals("OUT date", Date.valueOf("2002-05-12"), cs.getDate(5));
    assertEquals("OUT time"  , Time.valueOf("10:05:02"), cs.getTime(6));
    assertEquals("OUT timestamp" , 
        Timestamp.valueOf("2002-05-12 10:05:02.000000000"), 
        cs.getTimestamp(7));
    assertTrue(Arrays.equals(ba, cs.getBytes(8)));
}
 

/**
 * End a run in the database
 *
 * @param timestamp
 * @param runId
 */
public void endRun(
                    long timestamp,
                    int runId,
                    boolean closeConnection ) throws DatabaseAccessException {

    if (isBatchMode) {
        flushCache();
    }

    final String errMsg = "Unable to end run with id " + runId;

    final int indexRowsInserted = 3;

    timestamp = inUTC(timestamp);

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_end_run(?, ?, ?) }");
        callableStatement.setInt(1, runId);
        callableStatement.setTimestamp(2, new Timestamp(timestamp));
        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);
        }
    }
}
 

public void endSuite(
                      long timestamp,
                      int suiteId,
                      boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to end suite with id " + suiteId;

    timestamp = inUTC(timestamp);

    final int indexRowsInserted = 3;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_end_suite(?, ?, ?) }");
        callableStatement.setInt(1, suiteId);
        callableStatement.setTimestamp(2, new Timestamp(timestamp));
        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);
        }
    }
}
 

@Override
public int insertTU(int headerId, String tuId, String creationId, String creationDate, String changeId,
		String changeDate, String creationTool, String creationToolVersion, String client, String projectRef,
		String jobRef) throws SQLException {
	CallableStatement stmt = null;
	ResultSet rs = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-tu");
		if (null == conn) {
			return -1;
		}
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setInt(i++, headerId);
		stmt.setString(i++, tuId);
		stmt.setString(i++, creationId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(creationDate));
		stmt.setString(i++, changeId);
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(changeDate));
		stmt.setString(i++, creationTool);
		stmt.setString(i++, creationToolVersion);
		stmt.setString(i++, client);
		stmt.setString(i++, projectRef);
		stmt.setString(i++, jobRef);
		stmt.registerOutParameter(i, Types.INTEGER);
		stmt.executeUpdate();
		return  stmt.getInt(i);
	} finally {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

/**
 * Private helper for {@link #testTimeAndDateWithCalendar()}. This method
 * calls a procedure that takes Date, Time and Timestamp arguments and
 * returns the exact same values. Call the setters with one calendar and
 * the getters with another calendar, and verify that the expected
 * conversion between time zones has happened.
 *
 * @param cal1 the calendar to use for the setter methods
 * @param cal2 the calendar to use for the getter methods
 */
private void testTimeAndDateWithCalendar(Calendar cal1, Calendar cal2)
        throws SQLException
{
    println("Running " + getName() + "() with " +
            cal1.getTimeZone().getDisplayName() + " and " +
            cal2.getTimeZone().getDisplayName());

    CallableStatement cs = prepareCall(
            "call NON_NUMERIC_TYPES_IN_AND_OUT_PROC(?,?,?,?,?,?,?,?)");

    Date d = Date.valueOf("2010-04-14");
    Time t = Time.valueOf("12:23:24");
    Timestamp ts = new Timestamp(System.currentTimeMillis());
    ts.setNanos(123456789);

    cs.setDate(1, d, cal1);
    cs.setTime(2, t, cal1);
    cs.setTimestamp(3, ts, cal1);
    cs.setNull(4, Types.VARBINARY); // we don't care about VARBINARY here

    cs.registerOutParameter (5, java.sql.Types.DATE);
    cs.registerOutParameter (6, java.sql.Types.TIME);
    cs.registerOutParameter (7, java.sql.Types.TIMESTAMP);
    cs.registerOutParameter (8, java.sql.Types.VARBINARY);

    cs.execute();

    assertSameDate(d, cal1, cs.getDate(5, cal2), cal2);
    assertSameTime(t, cal1, cs.getTime(6, cal2), cal2);
    assertSameTimestamp(ts, cal1, cs.getTimestamp(7, cal2), cal2);
}
 

protected static void callProcedureSellordersSGUpdate(Connection conn, String sql, 
   Timestamp orderTime, int tid) throws SQLException { 
  CallableStatement cs = null;
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info(sql + " with order_time: " + orderTime + " and with tid: " + tid );
  cs.setTimestamp(1, orderTime);
  cs.setInt(2, tid);
  cs.execute();
 
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 

/**
 * 将数据写入到MTU表中
 * @param params
 * @return
 * @throws SQLException
 *             ;
 */
public String insertTU(Hashtable<String, String> params) throws SQLException {
	CallableStatement stmt = null;
	ResultSet rs = null;
	try {
		String sql = dbConfig.getOperateDbSQL("insert-tu");
		stmt = conn.prepareCall(sql);
		int i = 1;
		stmt.setInt(i++, Integer.parseInt(params.get("HEADERID")));
		stmt.setString(i++, params.get("TUID"));
		stmt.setString(i++, params.get("CREATIONID"));
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(params.get("CREATIONDATE")));
		stmt.setString(i++, params.get("CHANGEID"));
		stmt.setTimestamp(i++, DateUtils.getTimestampFromUTC(params.get("CHANGEDATE")));
		stmt.setString(i++, params.get("CREATIONTOOL"));
		stmt.setString(i++, params.get("CREATIONTOOLVERSION"));
		stmt.setString(i++, params.get("CLIENT"));
		stmt.setString(i++, params.get("PROJECTREF"));
		stmt.setString(i++, params.get("JOBREF"));
		stmt.registerOutParameter(i++, Types.INTEGER);
		stmt.execute();
		return stmt.getString(i - 1);
	} finally {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
	}
}
 

@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);
        }
    }
}
 

public void insertSystemStatistics(
                                    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_system_statistic_by_ids(?, ?, ?, ?, ?) }");
        callableStatement.setString(3, statisticIds);
        callableStatement.setInt(1, testCaseId);
        callableStatement.setString(2, machine);
        callableStatement.setString(4, statisticValues);
        callableStatement.setTimestamp(5, new Timestamp(timestamp));

        callableStatement.execute();

    } catch (Exception e) {
        String errMsg = "Unable to insert system 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);
        }
    }
}
 

protected static void callProcedureSellordersSGUpdate(Connection conn, String sql, 
   Timestamp orderTime, int tid) throws SQLException { 
  CallableStatement cs = null;
  cs = conn.prepareCall(sql);
  Log.getLogWriter().info(sql + " with order_time: " + orderTime + " and with tid: " + tid );
  cs.setTimestamp(1, orderTime);
  cs.setInt(2, tid);
  cs.execute();
 
  SQLWarning warning = cs.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
}
 

public void endCheckpoint(
                           CheckpointInfo runningCheckpointInfo,
                           long endTimestamp,
                           long transferSize,
                           int result,
                           boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to end checkpoint with name '" + runningCheckpointInfo.getName()
                          + "', checkpoint summary id " + runningCheckpointInfo.getCheckpointSummaryId()
                          + ", id " + runningCheckpointInfo.getCheckpointId();

    endTimestamp = inUTC(endTimestamp);

    final int indexRowsInserted = 8;
    int responseTime = (int) (endTimestamp - runningCheckpointInfo.getStartTimestamp());

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_end_checkpoint(?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, runningCheckpointInfo.getCheckpointSummaryId());
        callableStatement.setLong(2, runningCheckpointInfo.getCheckpointId());
        callableStatement.setInt(3, responseTime >= 0
                                                      ? responseTime
                                                      : 0);
        callableStatement.setLong(4, transferSize);
        callableStatement.setInt(5, result);
        callableStatement.setInt(6, checkpointLogLevel.toInt());
        callableStatement.setTimestamp(7, new Timestamp(endTimestamp));
        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);
        }
    }
}
 

public void testCallableStatementBatch() throws SQLException {

        println("Positive Callable Statement: " +
            "statement testing callable statement batch");
        CallableStatement cs = prepareCall("insert into t1 values(?)");

        cs.setInt(1, 1);
        cs.addBatch();
        cs.setInt(1,2);
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "t1");

        /* For 'beetle' bug 2813 - setDate/setTime/setTimestamp
         * calls on callableStatement throws ClassNotFoundException
         * verify setXXXX() works with Date, Time and Timestamp
         * on CallableStatement.
         */
        cs = prepareCall("insert into datetab values(?)");

        cs.setDate(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setDate(1,Date.valueOf("1990-06-06"));
        cs.addBatch();

        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "datetab");

        cs = prepareCall("insert into timetab values(?)");

        cs.setTime(1, Time.valueOf("11:11:11"));
        cs.addBatch();
        cs.setTime(1, Time.valueOf("12:12:12"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        cs = prepareCall("insert into timestamptab values(?)");

        cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
        cs.addBatch();
        cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        // Try with a user type
        cs = prepareCall("insert into usertypetab values(?)");

        cs.setObject(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setObject(1,Date.valueOf("1990-06-06"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "usertypetab");
    }
 

public void testCallableStatementBatch() throws SQLException {

        println("Positive Callable Statement: " +
            "statement testing callable statement batch");
        CallableStatement cs = prepareCall("insert into t1 values(?)");

        cs.setInt(1, 1);
        cs.addBatch();
        cs.setInt(1,2);
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "t1");

        /* For 'beetle' bug 2813 - setDate/setTime/setTimestamp
         * calls on callableStatement throws ClassNotFoundException 
         * verify setXXXX() works with Date, Time and Timestamp 
         * on CallableStatement.
         */
        cs = prepareCall("insert into datetab values(?)");

        cs.setDate(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setDate(1,Date.valueOf("1990-06-06"));
        cs.addBatch();

        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "datetab");

        cs = prepareCall("insert into timetab values(?)");

        cs.setTime(1, Time.valueOf("11:11:11"));
        cs.addBatch();
        cs.setTime(1, Time.valueOf("12:12:12"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        cs = prepareCall("insert into timestamptab values(?)");

        cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
        cs.addBatch();
        cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "timestamptab");

        // Try with a user type
        cs = prepareCall("insert into usertypetab values(?)");

        cs.setObject(1, Date.valueOf("1990-05-05"));
        cs.addBatch();
        cs.setObject(1,Date.valueOf("1990-06-06"));
        cs.addBatch();
        executeBatchCallableStatement(cs);

        cleanUpCallableStatement(cs, "usertypetab");
    }
 

/**
 * Utility method which registers in a CallableStatement object the different {@link amforeas.jdbc.StoredProcedureParam}
 * instances in the given list. Returns a List of {@link amforeas.jdbc.StoredProcedureParam} with all the OUT parameters
 * registered in the CallableStatement
 * @param cs the CallableStatement object where the parameters are registered.
 * @param params a list of {@link amforeas.jdbc.StoredProcedureParam}
 * @return a list of OUT {@link amforeas.jdbc.StoredProcedureParam} 
 * @throws SQLException if we fail to register any of the parameters in the CallableStatement
 * @throws AmforeasBadRequestException 
 */
private List<StoredProcedureParam> addParameters (final CallableStatement cs, final List<StoredProcedureParam> params) throws SQLException, AmforeasBadRequestException {
    final List<StoredProcedureParam> outParams = new ArrayList<StoredProcedureParam>();
    int i = 1;
    for (StoredProcedureParam p : params) {
        final Integer sqlType = p.getSqlType();
        if (p.isOutParameter()) {
            l.debug("Adding OUT parameter " + p.toString());
            cs.registerOutParameter(i++, sqlType);
            outParams.add(p);
        } else {
            l.debug("Adding IN parameter " + p.toString());
            switch (sqlType) {
                case Types.BIGINT:
                case Types.INTEGER:
                case Types.TINYINT:
                    // case Types.NUMERIC:
                    cs.setInt(i++, Integer.valueOf(p.getValue()));
                    break;
                case Types.DATE:
                    cs.setDate(i++, (Date) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.TIME:
                    cs.setTime(i++, (Time) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.TIMESTAMP:
                    cs.setTimestamp(i++, (Timestamp) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.DECIMAL:
                    cs.setBigDecimal(i++, (BigDecimal) AmforeasUtils.parseValue(p.getValue()));
                    break;
                case Types.DOUBLE:
                    cs.setDouble(i++, Double.valueOf(p.getValue()));
                    break;
                case Types.FLOAT:
                    cs.setLong(i++, Long.valueOf(p.getValue()));
                    break;
                default:
                    cs.setString(i++, p.getValue());
                    break;
            }
        }
    }
    return outParams;
}
 

public static String callStoredProcedureWithDbmsOutput(Connection connection, String procedureName, Object... parameters) throws SQLException {
	CallableStatement callableStatement = null;
	try {
		callableStatement = connection.prepareCall("begin dbms_output.enable(:1); end;");
		callableStatement.setLong(1, 10000);
		callableStatement.executeUpdate();
		callableStatement.close();
		callableStatement = null;

		if (parameters != null) {
			callableStatement = connection.prepareCall("{call " + procedureName + "(" + AgnUtils.repeatString("?", parameters.length, ", ") + ")}");
			for (int i = 0; i < parameters.length; i++) {
				if (parameters[i] instanceof Date) {
					callableStatement.setTimestamp(i + 1, new java.sql.Timestamp(((Date) parameters[i]).getTime()));
				} else {
					callableStatement.setObject(i + 1, parameters[i]);
				}
			}
		} else {
			callableStatement = connection.prepareCall("{call " + procedureName + "()}");
		}
		callableStatement.execute();
		callableStatement.close();
		callableStatement = null;

		callableStatement = connection
			.prepareCall(
				"declare "
				+ "    l_line varchar2(255); "
				+ "    l_done number; "
				+ "    l_buffer long; "
				+ "begin "
				+ "  loop "
				+ "    exit when length(l_buffer)+255 > :maxbytes OR l_done = 1; "
				+ "    dbms_output.get_line( l_line, l_done ); "
				+ "    l_buffer := l_buffer || l_line || chr(10); "
				+ "  end loop; " + " :done := l_done; "
				+ " :buffer := l_buffer; "
				+ "end;");

		callableStatement.registerOutParameter(2, Types.INTEGER);
		callableStatement.registerOutParameter(3, Types.VARCHAR);
		StringBuffer dbmsOutput = new StringBuffer(1024);
		while (true) {
			callableStatement.setInt(1, 32000);
			callableStatement.executeUpdate();
			dbmsOutput.append(callableStatement.getString(3).trim());
			if (callableStatement.getInt(2) == 1) {
				break;
			}
		}
		callableStatement.close();
		callableStatement = null;

		callableStatement = connection.prepareCall("begin dbms_output.disable; end;");
		callableStatement.executeUpdate();
		callableStatement.close();
		callableStatement = null;

		return dbmsOutput.toString();
	} finally {
		closeQuietly(callableStatement);
	}
}
 

public void endLoadQueue(
                          int result,
                          long timestamp,
                          int loadQueueId,
                          boolean closeConnection ) throws DatabaseAccessException {

    if (isBatchMode) {
        flushCache();
    }

    final String errMsg = "Unable to end load queue with id " + loadQueueId;

    timestamp = inUTC(timestamp);

    final int indexRowsInserted = 4;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_end_loadqueue(?, ?, ?, ?) }");
        callableStatement.setInt(1, loadQueueId);
        callableStatement.setInt(2, result);
        callableStatement.setTimestamp(3, new Timestamp(timestamp));
        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);
        }
    }
}
 

public int startLoadQueue(
                           String name,
                           int sequence,
                           String hostsList,
                           String threadingPattern,
                           int numberThreads,
                           String machine,
                           long timestamp,
                           int testcaseId,
                           boolean closeConnection ) throws DatabaseAccessException {

    if (testcaseId < 1) {
        log.getLog4jLogger()
           .warn("Load queue '" + name
                 + "' will not be registered because there is no database connection!");
        return -1;
    }

    timestamp = inUTC(timestamp);

    final String errMsg = "Unable to start load queue with name " + name;

    // create a new load queue
    final int indexRowsInserted = 9;
    final int indexLoadQueueId = 10;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_start_loadqueue(?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }");
        callableStatement.setInt(1, testcaseId);
        callableStatement.setString(2, name);
        callableStatement.setInt(3, sequence);
        callableStatement.setString(4, hostsList);
        callableStatement.setString(5, threadingPattern);
        callableStatement.setInt(6, numberThreads);
        callableStatement.setString(7, machine);
        callableStatement.setTimestamp(8, new Timestamp(timestamp));
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);
        callableStatement.registerOutParameter(indexLoadQueueId, Types.INTEGER);

        callableStatement.execute();

        if (callableStatement.getInt(indexRowsInserted) != 1) {
            throw new DatabaseAccessException(errMsg);
        } else {
            if (callableStatement.getInt(indexLoadQueueId) == 0) {
                throw new DatabaseAccessException(errMsg + " - load queue id returned was 0");
            }
        }

        // get the result
        return callableStatement.getInt(indexLoadQueueId);

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

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);
        }
    }

}
 

public int startSuite(
                       String packageName,
                       String suiteName,
                       long timestamp,
                       int runId,
                       boolean closeConnection ) throws DatabaseAccessException {

    final String errMsg = "Unable to start suite with name " + suiteName;
    // create a new suite

    timestamp = inUTC(timestamp);

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        // TODO : remove me after 3.6.0
        String dbVersionString = getDatabaseVersion();
        int dbVersion = Integer.parseInt(dbVersionString.replace(".", ""));

        if (dbVersion >= 350) {
            callableStatement = connection.prepareCall("{ call sp_start_suite(?, ?, ?, ?, ?, ?) }");

            if (packageName == null) {
                packageName = "";
            }
            callableStatement.setString("@package", packageName);
        } else {
            callableStatement = connection.prepareCall("{ call sp_start_suite(?, ?, ?, ?, ?) }");
        }
        callableStatement.setString("@suiteName", suiteName);
        callableStatement.setInt("@runId", runId);
        callableStatement.setTimestamp("@dateStart", new Timestamp(timestamp));
        callableStatement.registerOutParameter("@RowsInserted", Types.INTEGER);
        callableStatement.registerOutParameter("@suiteId", Types.INTEGER);

        callableStatement.execute();

        if (callableStatement.getInt("@RowsInserted") != 1) {
            throw new DatabaseAccessException(errMsg);
        } else {
            if (callableStatement.getInt("@suiteId") == 0) {
                throw new DatabaseAccessException(errMsg + " - suite ID returned was 0");
            }
        }
        // get the result
        return callableStatement.getInt("@suiteId");

    } catch (Exception e) {
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}
 

/**
 * Insert a new run in the database
 *
 * @param runName
 *            name of the run
 * @param osName
 *            name of the OS
 * @param productName
 *            name of the product
 * @param versionName
 *            version of the product
 * @param buildName
 *            build version
 * @param timestamp
 * @param hostName
 *            name/IP of the machine , from which the run was started
 * @return
 */
public int startRun(
                     String runName,
                     String osName,
                     String productName,
                     String versionName,
                     String buildName,
                     long timestamp,
                     String hostName,
                     boolean closeConnection ) throws DatabaseAccessException {

    timestamp = inUTC(timestamp);

    // then start the run
    final int indexRowsInserted = 8;
    final int indexRunId = 9;

    String errMsg = "Unable to insert run with name " + runName;

    CallableStatement callableStatement = null;
    try {
        refreshInternalConnection();

        callableStatement = connection.prepareCall("{ call sp_start_run(?, ?, ?, ?, ?, ?, ?, ? ,?) }");
        callableStatement.setString(1, productName);
        callableStatement.setString(2, versionName);
        callableStatement.setString(3, buildName);
        callableStatement.setString(4, runName);
        callableStatement.setString(5, osName);
        callableStatement.setTimestamp(6, new Timestamp(timestamp));
        callableStatement.setString(7, hostName);
        callableStatement.registerOutParameter(indexRowsInserted, Types.INTEGER);
        callableStatement.registerOutParameter(indexRunId, Types.INTEGER);

        callableStatement.execute();
        if (callableStatement.getInt(indexRowsInserted) == 1) {

            // check if the run ID is correct
            if (callableStatement.getInt(indexRunId) == 0) {
                throw new DatabaseAccessException(errMsg
                                                  + " - run ID returned was 0");
            }
        } else {
            throw new DatabaseAccessException(errMsg);
        }

        // get the result
        return callableStatement.getInt(indexRunId);

    } catch (Exception e) {
        String procedureName = "sp_start_run";
        List<Object> argValues = new ArrayList<Object>();
        argValues.add(procedureName);
        argValues.add(versionName);
        argValues.add(buildName);
        argValues.add(runName);
        argValues.add(osName);
        argValues.add(timestamp);
        argValues.add(hostName);

        errMsg += " using the following statement: "
                  + constructStoredProcedureArgumentsMap(procedureName, argValues);
        throw new DatabaseAccessException(errMsg, e);
    } finally {
        if (closeConnection) {
            DbUtils.close(connection, callableStatement);
        } else {
            DbUtils.closeStatement(callableStatement);
        }
    }
}