Example usage for java.sql PreparedStatement setTimestamp

List of usage examples for java.sql PreparedStatement setTimestamp

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setTimestamp

Introduction

In this page you can find the example usage for java.sql PreparedStatement setTimestamp.

Prototype

void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given java.sql.Timestamp value.

Usage

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void updateConfigChangeTime(int nodeId, long confUpdateTime) {
    Connection c = null;/*from  ww  w .  j a  v a 2  s .  c o  m*/
    PreparedStatement ps = null;

    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.UPDATE_CONFIG_UPDATE_TIME);
        ps.setTimestamp(1, (new Timestamp(confUpdateTime)));
        ps.setInt(2, nodeId);
        ps.executeUpdate();
    } catch (Exception ee) {
        logger.warn("Error while updating config change time for the device= " + nodeId, ee);
    } finally {
        try {
            ps.close();
        } catch (Exception ee) {
        }
        try {
            DBHelper.releaseConnection(c);
        } catch (Exception ex) {
        }
    }
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void endTerminalGatewaySession(int id) {
    PreparedStatement ps = null;
    Connection c = null;//from   ww w  .j av  a  2 s. c o  m
    c = DBHelper.getConnection();

    try {
        ps = c.prepareStatement("update terminal_gateway_sessions  SET END_TIME=? where SESSION_ID =" + id);

        ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
        ps.executeUpdate();
    } catch (Exception e) {
        logger.warn("Error with updating terminals session end time - " + e.getMessage(), e);
    } finally {
        DBHelper.closeAll(c, ps, null);
    }
}

From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java

/**
 * Update the database with the Alert properties stored in a memory record.
 *
 * @param rec_/*from  w ww. java2 s  . c om*/
 *        The Alert definition to be stored.
 * @return 0 if successful, otherwise the database error code.
 * @throws java.sql.SQLException
 *         On an error with rollback().
 */
private int updateProperties(AlertRec rec_) throws SQLException {
    // Define the update statement. Some columns are not updated as they are
    // controlled
    // by triggers, specifically date_created, date_modified, creator and
    // modifier.
    String update = "update sbrext.sn_alert_view_ext set " + "name = ?, " + "auto_freq_unit = ?, "
            + "al_status = ?, " + "begin_date = ?, " + "end_date = ?, " + "status_reason = ?, "
            + "auto_freq_value = ?, " + "modified_by = ? " + "where al_idseq = ?";

    cleanRec(rec_);
    PreparedStatement pstmt = null;
    int rc = 0;
    try {
        // Set all the SQL arguments.
        pstmt = _conn.prepareStatement(update);
        pstmt.setString(1, rec_.getName());
        pstmt.setString(2, rec_.getFreqString());
        pstmt.setString(3, rec_.getActiveString());
        pstmt.setTimestamp(4, rec_.getStart());
        pstmt.setTimestamp(5, rec_.getEnd());
        pstmt.setString(6, rec_.getInactiveReason(false));
        pstmt.setInt(7, rec_.getDay());
        pstmt.setString(8, _user);
        pstmt.setString(9, rec_.getAlertRecNum());

        // Send it to the database. And remember to flag a commit for later.
        pstmt.executeUpdate();
        _needCommit = true;
    } catch (SQLException ex) {
        // It's bad...
        _conn.rollback();
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + update + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
        rc = _errorCode;
    } finally {
        closeCursors(pstmt, null);
    }
    return rc;
}

From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java

/**
 * Pull rows changed in the date range specified. There are 3 different
 * patterns to handle://w w w .  ja  v a 2  s.  c  o m
 * <p>
 * <ul>
 * <li>[from/to, from/to] {2, 4} - This represents the from/to date pair
 * which may occur 2 or 4 times in the SQL.</li>
 * <li>[in, from/to, from/to] {2, 4} - This represents a single "in" clause
 * of creators or modifiers followed by the from/to pair which may occur 2
 * or 4 times in the SQL in this order. This pattern is handled by this
 * method argument list.</li>
 * <li>[in, in, from/to, from/to] {2,4} - This represents an "in" clause
 * for the creators and an "in" clause for the modifiers followed by the
 * from/to pair which in total may appear 1 or 2 times.</li>
 * </ul>
 * </p>
 *
 * @param select_
 *        The SQL select for the specific data and table.
 * @param start_
 *        The date to start.
 * @param end_
 *        The date to end.
 * @param pairs_
 *        The number of pairs of (start, end) that appear in the SQL.
 * @param vals_
 *        The additional values used by an "in" clause.
 * @return 0 if successful, otherwise the database error code.
 */
private ACData[] selectAC(String select_, Timestamp start_, Timestamp end_, int pairs_, String vals_[]) {
    // Expand the "in" clause.
    int loop = pairs_ / 2;
    String markers = expandMarkers(vals_.length);

    String parts[] = select_.split("\\?");
    int pos = 0;
    String select = parts[pos++];
    for (int cnt = 0; cnt < loop; ++cnt) {
        select = select + markers + parts[pos++];
        for (int ndx = 0; ndx < 2; ++ndx) {
            select = select + "?" + parts[pos++] + "?" + parts[pos++];
        }
    }

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    ACData[] list = null;
    try {
        pstmt = _conn.prepareStatement(select);
        int arg = 1;
        for (int cnt = 0; cnt < loop; ++cnt) {
            for (int ndx = 0; ndx < vals_.length; ++ndx) {
                pstmt.setString(arg++, vals_[ndx]);
            }
            for (int ndx = 0; ndx < 2; ++ndx) {
                pstmt.setTimestamp(arg++, start_);
                pstmt.setTimestamp(arg++, end_);
            }
        }
        rs = pstmt.executeQuery();
        list = copyResults(rs);
    } catch (SQLException ex) {
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + select + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
    } finally {
        closeCursors(pstmt, rs);
    }
    return list;
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void updateJobModifierDetails(int jobId, int modifierId, long modifiedTime) throws Exception {
    Connection c = null;/* w  w w . j ava2  s. co  m*/
    PreparedStatement ps = null;

    String loginName = "";
    UserDetails ud = UsersFactory.getUser(modifierId);
    if (ud != null) {
        loginName = ud.getLogin();
    }

    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement(DBHelperConstants.UPDATE_QRTZ_JOB_MODIFIER_DETAILS);

        ps.setInt(1, modifierId);
        ps.setString(2, loginName);
        ps.setTimestamp(3, (new Timestamp(modifiedTime)));
        ps.setInt(4, jobId);

        ps.executeUpdate();

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            ps.close();
        } catch (Exception ex) {
        }
        DBHelper.releaseConnection(c);
    }
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

private static void updateWingSettingsDetailsModifiedTime(int customerId, String wingInstance) {
    Connection c = null;/*  w  w w  .  j  a  va 2s .c om*/
    ResultSet rs = null;
    PreparedStatement ps = null;

    try {
        c = DBHelper.getConnection();
        rs = DBHelper.executeQuery("select * from wing_settings_update_details where customer_id = "
                + customerId + " and instance_name='" + wingInstance + "'");
        if (rs.next()) {
            ps = c.prepareStatement(DBHelperConstants.UPDATE_WING_SETTINGS_UPDATE_DETAILS_MODIFIED);
            ps.setTimestamp(1, (new Timestamp(System.currentTimeMillis())));
            ps.setInt(2, customerId);
            ps.setString(3, wingInstance);
            ps.executeUpdate();
        } else {
            ps = c.prepareStatement(DBHelperConstants.INSERT_WING_SETTINGS_UPDATE_DETAILS_MODIFIED);
            ps.setInt(1, customerId);
            ps.setString(2, wingInstance);
            ps.setTimestamp(3, (new Timestamp(System.currentTimeMillis())));
            ps.executeUpdate();
        }
    } catch (Exception ee) {
        logger.warn("Error while trying to save wing_settings_update_details for customer", ee);
    } finally {
        try {
            rs.close();
        } catch (Exception ee) {
        }

        try {
            ps.close();
        } catch (Exception e) {
        }

        try {
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        } catch (Exception ee) {
        }
    }
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void updateWingSettingsDetails(int customerId, String instanceName, long updateTime) {
    Connection c = null;//  ww  w . ja v  a 2 s .  co m
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        c = DBHelper.getConnection();
        rs = DBHelper.executeQuery("select * from wing_settings_update_details where customer_id = "
                + customerId + " and instance_name='" + instanceName + "'");
        if (rs.next()) {
            ps = c.prepareStatement(DBHelperConstants.UPDATE_WING_SETTINGS_UPDATE_DETAILS_UPLOAD);
            ps.setTimestamp(1, (new Timestamp(System.currentTimeMillis())));
            ps.setInt(2, customerId);
            ps.setString(3, instanceName);
            ps.executeUpdate();
        } else {
            ps = c.prepareStatement(DBHelperConstants.INSERT_WING_SETTINGS_UPDATE_DETAILS_UPLOAD);
            ps.setInt(1, customerId);
            ps.setString(2, instanceName);
            ps.setTimestamp(3, (new Timestamp(System.currentTimeMillis())));
            ps.executeUpdate();
        }
    } catch (Exception ee) {
        logger.warn("Error while updating wing setting details for the customer: " + customerId, ee);
    } finally {
        try {
            rs.close();
        } catch (Exception ee) {
        }

        try {
            ps.close();
        } catch (Exception e) {
        }

        try {
            DBHelper.releaseConnection(c);
        } catch (Exception ee) {
        }
    }
}

From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java

/**
 * Pull rows changed in the date range specified. There are 3 different
 * patterns to handle:/*from  ww  w .  j ava  2s .  c o  m*/
 * <p>
 * <ul>
 * <li>[from/to, from/to] {2, 4} - This represents the from/to date pair
 * which may occur 2 or 4 times in the SQL.</li>
 * <li>[in, from/to, from/to] {2, 4} - This represents a single "in" clause
 * of creators or modifiers followed by the from/to pair which may occur 2
 * or 4 times in the SQL in this order.</li>
 * <li>[in, in, from/to, from/to] {2,4} - This represents an "in" clause
 * for the creators and an "in" clause for the modifiers followed by the
 * from/to pair which in total may appear 1 or 2 times. This pattern is
 * handled by this method argument list.</li>
 * </ul>
 * </p>
 *
 * @param select_
 *        The SQL select for the specific data and table.
 * @param start_
 *        The date to start.
 * @param end_
 *        The date to end.
 * @param pairs_
 *        The number of pairs of (start, end) that appear in the SQL.
 * @param vals1_
 *        The additional values used by an "in" clause.
 * @param vals2_
 *        The additional values used by a second "in" clause.
 * @return 0 if successful, otherwise the database error code.
 */
private ACData[] selectAC(String select_, Timestamp start_, Timestamp end_, int pairs_, String vals1_[],
        String vals2_[]) {
    // Expand the "in" clauses.
    String parts[] = select_.split("\\?");
    int loop = pairs_ / 2;

    String markers1 = expandMarkers(vals1_.length);
    String markers2 = expandMarkers(vals2_.length);
    int pos = 0;
    String select = parts[pos++];

    for (int cnt = 0; cnt < loop; ++cnt) {
        select = select + markers1 + parts[pos++] + markers2 + parts[pos++];
        for (int ndx = 0; ndx < 2; ++ndx) {
            select = select + "?" + parts[pos++] + "?" + parts[pos++];
        }
    }

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    ACData[] list = null;
    try {
        pstmt = _conn.prepareStatement(select);
        int arg = 1;
        for (int cnt = 0; cnt < loop; ++cnt) {
            for (int ndx = 0; ndx < vals1_.length; ++ndx) {
                pstmt.setString(arg++, vals1_[ndx]);
            }
            for (int ndx = 0; ndx < vals2_.length; ++ndx) {
                pstmt.setString(arg++, vals2_[ndx]);
            }
            for (int ndx = 0; ndx < 2; ++ndx) {
                pstmt.setTimestamp(arg++, start_);
                pstmt.setTimestamp(arg++, end_);
            }
        }
        rs = pstmt.executeQuery();
        list = copyResults(rs);
    } catch (SQLException ex) {
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + select + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
    } finally {
        closeCursors(pstmt, rs);
    }
    return list;
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void createTerminalGatewaySessionEntry(Integer connectionId, int user_id, int customerId,
        String deviceIp, String deviceName, long startTime, String protocol) {
    logger.debug("Creating tg session entry for customerId:" + customerId + ", deviceIp:" + deviceIp);
    PreparedStatement ps = null;
    Connection c = null;//from w w  w  . j  ava  2 s  .c om
    try {
        c = DBHelper.getConnection();
        ps = c.prepareStatement(
                "insert into terminal_gateway_sessions (SESSION_ID, CREATOR_ID, CUSTOMER_ID, HOST_IP, HOST_NAME, START_TIME, PROTOCOL) VALUES (?,?,?,?,?,?,?)");
        ps.setInt(1, connectionId);
        ps.setInt(2, user_id);
        ps.setInt(3, customerId);
        ps.setString(4, deviceIp);
        ps.setString(5, deviceName);
        ps.setTimestamp(6, new Timestamp(startTime));
        ps.setString(7, protocol);
        ps.executeUpdate();
    } catch (Exception ex) {
        logger.error("Exception while inserting terminal gateway session for node: " + deviceIp, ex);
    } finally {
        DBHelper.closeAll(c, ps, null);
    }
}

From source file:com.pari.nm.utils.db.InventoryDBHelper.java

public static void modifyCollectionProfile(CollectionProfile profile, int creatorId, boolean updateSchedule) {
    Connection c = null;//from  w w  w.j a v  a2s  . c  o  m
    PreparedStatement ps = null;
    PreparedStatement ps1 = null;

    try {
        c = DBHelper.getConnection();

        ps = c.prepareStatement(DBHelperConstants.MODIFY_COLLECTION_PROFILE);
        ps.setString(1, profile.getProfileDescr());
        ps.setString(2, profile.getDatasetsString());
        ps.setString(3, profile.isAllDevicesSelected() ? "y" : "n");
        ps.setString(4, profile.getGroupsStr());
        ps.setTimestamp(5, (new Timestamp(profile.getModifiedAt())));
        ps.setInt(6, creatorId);
        ps.setString(7, profile.getCommandCLI());
        ps.setInt(8, profile.isRunDiscovery() ? 1 : 0);
        ps.setInt(9, profile.isRunDAV() ? 1 : 0);

        ps.setInt(10, profile.getProfId());
        try {
            ps.executeUpdate();

            if (profile.getFtpDetails() != null) {
                ServerDBHelper.saveFtpServerDetails(profile.getFtpDetails(), profile.getProfileName());
            }
        } catch (Exception ex) {
            logger.warn("Unable to modify collection profile", ex);
        }

        try {
            DBHelper.executeUpdate(
                    "delete from collection_prof_devices where profile_id=" + profile.getProfId());

            int size = profile.getNodesStr().length();
            if (size > 4000) {
                int count = size / 4000 + 1;
                String idsStr = profile.getNodesStr();

                for (int i = 0; i < count; i++) {
                    String ids = (idsStr.length() > 4000) ? idsStr.substring(0, 4000) : idsStr;
                    if (ids.length() > 0) {
                        DBHelper.executeUpdate("insert into collection_prof_devices VALUES ("
                                + profile.getProfId() + "," + i + ",'" + ids + "')");

                        idsStr = (idsStr.length() > 4000) ? idsStr.substring(4001) : "";
                    }
                }
            } else if (size > 0) {
                DBHelper.executeUpdate("insert into collection_prof_devices VALUES (" + profile.getProfId()
                        + "," + 0 + ",'" + profile.getNodesStr() + "')");

            }
        } catch (Exception ee) {
            logger.warn("Unable to update device details in collection profile", ee);
        }
        if (updateSchedule) {
            if (profile.getJobId() == -1) {
                int jobId = JobMgr.getInstance().scheduleCollectionProfileJob(profile, creatorId);
                profile.setJobId(jobId);
                setCollectionProfileJobId(profile.getProfId(), jobId);
            } else {
                String[] jobAndGrp = ServerDBHelper.getJobAndGrpNames(profile.getJobId());
                if (jobAndGrp != null && jobAndGrp.length == 2) {
                    JobMgr.getInstance().editJob(jobAndGrp[0], jobAndGrp[1], profile.getProfileDescr(),
                            profile.getScheduleDetails(), profile.getNotificationPrefs());
                }
            }
        }
    } catch (Exception ee) {
        logger.warn("Error while inserting collection profile", ee);
    } finally {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (Exception e) {
            logger.warn("Exception while closing the statement in inserting collection profile", e);
        }

        try {
            if (ps1 != null) {
                ps1.close();
            }
        } catch (Exception e) {
            logger.warn("Exception while closing the statement in inserting collection profile", e);
        }
        try {
            if (c != null) {
                DBHelper.releaseConnection(c);
            }
        } catch (Exception e) {
            logger.warn("Exception while closing the connection in inserting collection profile", e);
        }
    }
}