public DbIfServiceEntry[] getServices(Connection db) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rset = null;
    final DBUtils d = new DBUtils(getClass());
    List<DbIfServiceEntry> l;

    try {
      stmt = db.prepareStatement(SQL_LOAD_IFSVC_LIST);
      d.watch(stmt);
      stmt.setLong(1, m_nodeId);
      stmt.setString(2, InetAddressUtils.str(m_ipAddr));

      rset = stmt.executeQuery();
      d.watch(rset);
      l = new ArrayList<DbIfServiceEntry>();

      while (rset.next()) {
        int sid = rset.getInt(1);
        DbIfServiceEntry entry = DbIfServiceEntry.get(db, m_nodeId, m_ipAddr, sid);
        if (entry != null) {
          l.add(entry);
        }
      }
    } finally {
      d.cleanUp();
    }

    DbIfServiceEntry[] entries = new DbIfServiceEntry[l.size()];
    return l.toArray(entries);
  }
  /**
   * This method queries the database in search of a service id for a given serivice name
   *
   * @param service the name of the service
   * @return the serviceID of the service
   */
  private int getServiceId(final String service) throws SQLException {
    int serviceID = 0;

    Connection connection = null;
    final DBUtils d = new DBUtils(getClass());
    try {
      connection = getConnection();
      d.watch(connection);

      final PreparedStatement statement =
          connection.prepareStatement("SELECT serviceID from service where serviceName = ?");
      d.watch(statement);
      statement.setString(1, service);

      final ResultSet results = statement.executeQuery();
      d.watch(results);
      results.next();

      serviceID = results.getInt(1);

      return serviceID;
    } finally {
      d.cleanUp();
    }
  }
Beispiel #3
0
  /**
   * Return the count of current outages.
   *
   * <p>Note: This method has been optimized for the simplest query.
   *
   * @return a int.
   * @throws java.sql.SQLException if any.
   */
  public static int getOutageCount() throws SQLException {
    int outageCount = 0;
    final Connection conn = Vault.getDbConnection();
    final DBUtils d = new DBUtils(OutageFactory.class, conn);

    try {
      final Statement stmt = conn.createStatement();
      d.watch(stmt);

      final ResultSet rs =
          stmt.executeQuery(
              "SELECT COUNT(OUTAGEID) AS OUTAGECOUNT FROM OUTAGES "
                  + "JOIN NODE USING(NODEID) "
                  + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR "
                  + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID "
                  + "WHERE IFREGAINEDSERVICE IS NULL "
                  + "AND (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') ");
      d.watch(rs);

      if (rs.next()) {
        outageCount = rs.getInt("OUTAGECOUNT");
      }
    } finally {
      d.cleanUp();
    }

    return outageCount;
  }
  /**
   * getActiveNodes
   *
   * @return a {@link java.util.List} object.
   * @throws java.sql.SQLException if any.
   */
  public List<Integer> getActiveNodes() throws SQLException {
    String NODE_QUERY =
        "SELECT   n.nodeid "
            + "FROM     node n "
            + "WHERE    n.nodetype != 'D' "
            + "ORDER BY n.nodelabel";

    java.sql.Connection connection = null;
    final List<Integer> allNodes = new ArrayList<Integer>();
    final DBUtils d = new DBUtils(getClass());

    try {
      connection = getConnection();
      d.watch(connection);

      final Statement stmt = connection.createStatement();
      d.watch(stmt);
      final ResultSet rset = stmt.executeQuery(NODE_QUERY);
      d.watch(rset);

      if (rset != null) {
        // Iterate through the result and build the array list
        while (rset.next()) {
          int nodeID = rset.getInt(1);

          allNodes.add(nodeID);
        }
      }
      return allNodes;
    } finally {
      d.cleanUp();
    }
  }
Beispiel #5
0
  /**
   * getOutage
   *
   * @param outageId a int.
   * @return a {@link org.opennms.web.outage.Outage} object.
   * @throws java.sql.SQLException if any.
   */
  public static Outage getOutage(int outageId) throws SQLException {
    Outage outage = null;
    final Connection conn = Vault.getDbConnection();
    final DBUtils d = new DBUtils(OutageFactory.class, conn);

    try {
      final PreparedStatement stmt =
          conn.prepareStatement(
              "SELECT OUTAGES.*, NODE.NODELABEL, IPINTERFACE.IPHOSTNAME, SERVICE.SERVICENAME, NOTIFICATIONS.NOTIFYID, NOTIFICATIONS.ANSWEREDBY FROM OUTAGES "
                  + "JOIN NODE USING(NODEID) "
                  + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR "
                  + "LEFT OUTER JOIN SERVICE USING(SERVICEID) "
                  + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.EVENTID "
                  + "WHERE OUTAGEID=?");
      d.watch(stmt);
      stmt.setInt(1, outageId);

      final ResultSet rs = stmt.executeQuery();
      d.watch(rs);

      Outage[] outages = rs2Outages(rs);

      if (outages != null && outages.length > 0) {
        outage = outages[0];
      }
    } finally {
      d.cleanUp();
    }

    return outage;
  }
  /**
   * getServiceNoticeStatus
   *
   * @param nodeID a {@link java.lang.String} object.
   * @param ipaddr a {@link java.lang.String} object.
   * @param service a {@link java.lang.String} object.
   * @return a {@link java.lang.String} object.
   * @throws java.sql.SQLException if any.
   */
  public String getServiceNoticeStatus(
      final String nodeID, final String ipaddr, final String service) throws SQLException {
    String notify = "Y";

    final String query =
        "SELECT notify FROM ifservices, service WHERE nodeid=? AND ipaddr=? AND ifservices.serviceid=service.serviceid AND service.servicename=?";
    java.sql.Connection connection = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      connection = getConnection();
      d.watch(connection);

      final PreparedStatement statement = connection.prepareStatement(query);
      d.watch(statement);
      statement.setInt(1, Integer.parseInt(nodeID));
      statement.setString(2, ipaddr);
      statement.setString(3, service);

      final ResultSet rs = statement.executeQuery();
      d.watch(rs);

      if (rs.next() && rs.getString("notify") != null) {
        notify = rs.getString("notify");
        if (notify == null) notify = "Y";
      }
      return notify;
    } finally {
      d.cleanUp();
    }
  }
  /** {@inheritDoc} */
  public boolean isInterfaceInDB(Connection dbConn, InetAddress ifAddress) throws SQLException {
    boolean result = false;

    if (log().isDebugEnabled()) {
      log()
          .debug(
              "isInterfaceInDB: attempting to lookup interface "
                  + InetAddressUtils.str(ifAddress)
                  + " in the database.");
    }

    // Set connection as read-only
    //
    // dbConn.setReadOnly(true);

    ResultSet rs = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      PreparedStatement s = dbConn.prepareStatement(RETRIEVE_IPADDR_SQL);
      d.watch(s);
      s.setString(1, InetAddressUtils.str(ifAddress));

      rs = s.executeQuery();
      d.watch(rs);
      result = rs.next();
    } finally {
      d.cleanUp();
    }

    return result;
  }
  /**
   * This method determines what label should be associated with a particular node. A database
   * connection is retrieved from the Vault.
   *
   * <p>WARNING: A properly instantiated and initialized Vault class object is required prior to
   * calling this method. This method will initially only be called from the WEB UI.
   *
   * @param nodeID Unique identifier of the node to be updated.
   * @return NodeLabel Object containing label and source values
   * @throws java.sql.SQLException if any.
   * @deprecated Update this to use modern DAO methods instead of raw SQL
   */
  public NodeLabelJDBCImpl computeLabel(final int nodeID) throws SQLException {
    final Connection dbConnection = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(NodeLabelJDBCImpl.class, dbConnection);

    try {
      return computeLabel(nodeID, null);
    } finally {
      d.cleanUp();
    }
  }
  /**
   * This method updates the 'nodelabel' and 'nodelabelsource' fields of the 'node' table for the
   * specified nodeID. A database connection is retrieved from the Vault.
   *
   * <p>WARNING: A properly instantiated and initialized Vault class object is required prior to
   * calling this method. This method will initially only be called from the WEB UI.
   *
   * @param nodeID Unique identifier of the node to be updated.
   * @param nodeLabel Object containing label and source values.
   * @throws java.sql.SQLException if any.
   * @deprecated Use a {@link NodeDao#update(org.opennms.netmgt.model.OnmsNode)} method call instead
   */
  public void assignLabel(final int nodeID, final NodeLabelJDBCImpl nodeLabel) throws SQLException {
    final Connection dbConnection = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(NodeLabelJDBCImpl.class, dbConnection);

    try {
      assignLabel(nodeID, nodeLabel, dbConnection);
    } finally {
      d.cleanUp();
    }
  }
  /**
   * syncServices
   *
   * @param conn a {@link java.sql.Connection} object.
   * @throws java.sql.SQLException if any.
   */
  public void syncServices(Connection conn) throws SQLException {

    List<String> serviceNames = syncServicesTable(conn);

    PreparedStatement delFromIfServicesStmt = null;
    final DBUtils d = new DBUtils(getClass());
    try {

      List<String> protocols = getCapsdConfig().getConfiguredProtocols();

      /*
       * now iterate over the services from the 'service' table
       * and determine if any no longer exist in the list of
       * configured protocols
       */
      for (String service : serviceNames) {
        if (!protocols.contains(service)) {
          if (log().isDebugEnabled()) {
            log()
                .debug(
                    "syncServices: service "
                        + service
                        + " exists in the database but not in the Capsd config file.");
          }

          Integer id = m_serviceNameToId.get(service);

          // Delete 'ifServices' table entries which refer to the
          // service
          if (log().isDebugEnabled()) {
            log()
                .debug(
                    "syncServices: deleting all references to service id "
                        + id
                        + " from the IfServices table.");
          }
          delFromIfServicesStmt = conn.prepareStatement(DELETE_IFSERVICES_SQL);
          d.watch(delFromIfServicesStmt);
          delFromIfServicesStmt.setInt(1, id.intValue());
          delFromIfServicesStmt.executeUpdate();
          log()
              .info(
                  "syncServices: deleted service id "
                      + id
                      + " for service '"
                      + service
                      + "' from the IfServices table.");
        }
      }
    } finally {
      d.cleanUp();
    }
  }
  /**
   * updateNoticeWithUserInfo
   *
   * @throws java.io.IOException if any.
   * @throws org.exolab.castor.xml.ValidationException if any.
   * @throws org.exolab.castor.xml.MarshalException if any.
   * @param userId a {@link java.lang.String} object.
   * @param noticeId a int.
   * @param media a {@link java.lang.String} object.
   * @param contactInfo a {@link java.lang.String} object.
   * @param autoNotify a {@link java.lang.String} object.
   * @throws java.sql.SQLException if any.
   */
  public void updateNoticeWithUserInfo(
      final String userId,
      final int noticeId,
      final String media,
      final String contactInfo,
      final String autoNotify)
      throws SQLException, MarshalException, ValidationException, IOException {
    if (noticeId < 0) return;
    int userNotifId = getUserNotifId();
    ThreadCategory log = this.log();
    if (log.isDebugEnabled()) {
      log.debug(
          "updating usersnotified: ID = "
              + userNotifId
              + " User = "******", notice ID = "
              + noticeId
              + ", conctactinfo = "
              + contactInfo
              + ", media = "
              + media
              + ", autoNotify = "
              + autoNotify);
    }
    Connection connection = null;
    final DBUtils d = new DBUtils(getClass());
    try {
      connection = getConnection();
      d.watch(connection);
      final PreparedStatement insert =
          connection.prepareStatement(
              "INSERT INTO usersNotified (id, userid, notifyid, notifytime, media, contactinfo, autonotify) values (?,?,?,?,?,?,?)");
      d.watch(insert);

      insert.setInt(1, userNotifId);
      insert.setString(2, userId);
      insert.setInt(3, noticeId);

      insert.setTimestamp(4, new Timestamp((new Date()).getTime()));

      insert.setString(5, media);
      insert.setString(6, contactInfo);
      insert.setString(7, autoNotify);

      insert.executeUpdate();
    } finally {
      d.cleanUp();
    }
  }
Beispiel #12
0
  /**
   * Count the number of outages for a given outage type.
   *
   * @param outageType a {@link org.opennms.web.outage.OutageType} object.
   * @param filters an array of org$opennms$web$filter$Filter objects.
   * @return a int.
   * @throws java.sql.SQLException if any.
   */
  public static int getOutageCount(OutageType outageType, Filter[] filters) throws SQLException {
    if (outageType == null || filters == null) {
      throw new IllegalArgumentException("Cannot take null parameters.");
    }

    int outageCount = 0;
    final Connection conn = Vault.getDbConnection();
    final DBUtils d = new DBUtils(OutageFactory.class, conn);

    try {
      StringBuffer select =
          new StringBuffer(
              "SELECT COUNT(OUTAGEID) AS OUTAGECOUNT FROM OUTAGES "
                  + "JOIN NODE USING(NODEID) "
                  + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR "
                  + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID "
                  + "LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID "
                  + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.NOTIFYID "
                  + "WHERE (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') "
                  + "AND ");
      select.append(outageType.getClause());

      for (Filter filter : filters) {
        select.append(" AND ");
        select.append(filter.getParamSql());
      }

      final PreparedStatement stmt = conn.prepareStatement(select.toString());
      d.watch(stmt);

      int parameterIndex = 1;
      for (Filter filter : filters) {
        parameterIndex += filter.bindParam(stmt, parameterIndex);
      }

      final ResultSet rs = stmt.executeQuery();
      d.watch(rs);

      if (rs.next()) {
        outageCount = rs.getInt("OUTAGECOUNT");
      }
    } finally {
      d.cleanUp();
    }

    return outageCount;
  }
  /**
   * getInterfaceDbNodeId
   *
   * @param dbConn a {@link java.sql.Connection} object.
   * @param ifAddress a {@link java.net.InetAddress} object.
   * @param ifIndex a int.
   * @return a int.
   * @throws java.sql.SQLException if any.
   */
  public int getInterfaceDbNodeId(Connection dbConn, InetAddress ifAddress, int ifIndex)
      throws SQLException {
    if (log().isDebugEnabled()) {
      log()
          .debug(
              "getInterfaceDbNodeId: attempting to lookup interface "
                  + InetAddressUtils.str(ifAddress)
                  + "/ifindex: "
                  + ifIndex
                  + " in the database.");
    }

    // Set connection as read-only
    // dbConn.setReadOnly(true);

    StringBuffer qs = new StringBuffer(RETRIEVE_IPADDR_NODEID_SQL);
    if (ifIndex != -1) {
      qs.append(" AND ifindex=?");
    }

    int nodeid = -1;

    final DBUtils d = new DBUtils(getClass());
    try {
      PreparedStatement s = dbConn.prepareStatement(qs.toString());
      d.watch(s);
      s.setString(1, InetAddressUtils.str(ifAddress));

      if (ifIndex != -1) {
        s.setInt(2, ifIndex);
      }

      ResultSet rs = s.executeQuery();
      d.watch(rs);
      if (rs.next()) {
        nodeid = rs.getInt(1);
      }
    } finally {
      d.cleanUp();
    }

    return nodeid;
  }
  /**
   * This method queries the 'node' table for the value of the 'nodelabel' and 'nodelabelsource'
   * fields for the node with the provided nodeID. A NodeLabel object is returned initialized with
   * the retrieved values.
   *
   * @param nodeID Unique ID of node whose label info is to be retrieved
   * @param dbConnection SQL database connection
   * @return object initialized with node label & source flag
   * @throws java.sql.SQLException if any.
   * @deprecated Use a {@link NodeDao#load(Integer)} method call instead
   */
  public NodeLabelJDBCImpl retrieveLabel(int nodeID, Connection dbConnection) throws SQLException {
    String nodeLabel = null;
    String nodeLabelSource = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    final DBUtils d = new DBUtils(NodeLabelJDBCImpl.class);

    LOG.debug("NodeLabel.retrieveLabel: sql: {} node id: {}", SQL_DB_RETRIEVE_NODELABEL, nodeID);

    try {
      stmt = dbConnection.prepareStatement(SQL_DB_RETRIEVE_NODELABEL);
      d.watch(stmt);
      stmt.setInt(1, nodeID);

      // Issue database query
      rs = stmt.executeQuery();
      d.watch(rs);

      // Process result set, retrieve node's sysname
      if (rs.next()) {
        nodeLabel = rs.getString(1);
        nodeLabelSource = rs.getString(2);
      }
    } finally {
      d.cleanUp();
    }

    if (NodeLabelSource.ADDRESS.toString().equals(nodeLabelSource)) {
      return new NodeLabelJDBCImpl(nodeLabel, NodeLabelSource.ADDRESS);
    } else if (NodeLabelSource.HOSTNAME.toString().equals(nodeLabelSource)) {
      return new NodeLabelJDBCImpl(nodeLabel, NodeLabelSource.HOSTNAME);
    } else if (NodeLabelSource.NETBIOS.toString().equals(nodeLabelSource)) {
      return new NodeLabelJDBCImpl(nodeLabel, NodeLabelSource.NETBIOS);
    } else if (NodeLabelSource.SYSNAME.toString().equals(nodeLabelSource)) {
      return new NodeLabelJDBCImpl(nodeLabel, NodeLabelSource.SYSNAME);
    } else if (NodeLabelSource.UNKNOWN.toString().equals(nodeLabelSource)) {
      return new NodeLabelJDBCImpl(nodeLabel, NodeLabelSource.UNKNOWN);
    } else if (NodeLabelSource.USER.toString().equals(nodeLabelSource)) {
      return new NodeLabelJDBCImpl(nodeLabel, NodeLabelSource.USER);
    } else {
      return new NodeLabelJDBCImpl(nodeLabel, NodeLabelSource.UNKNOWN);
    }
  }
  /**
   * This method updates the 'nodelabel' and 'nodelabelsource' fields of the 'node' table for the
   * specified nodeID.
   *
   * <p>If nodeLabel parameter is NULL the method will first call computeLabel() and use the
   * resulting NodeLabel object to update the database.
   *
   * @param nodeID Unique identifier of the node to be updated.
   * @param nodeLabel Object containing label and source values.
   * @param dbConnection SQL database connection
   * @throws java.sql.SQLException if any.
   * @deprecated Use a {@link NodeDao#update(org.opennms.netmgt.model.OnmsNode)} method call instead
   */
  public void assignLabel(
      final int nodeID, NodeLabelJDBCImpl nodeLabel, final Connection dbConnection)
      throws SQLException {
    final DBUtils d = new DBUtils(NodeLabelJDBCImpl.class);

    try {
      // Issue SQL update to assign the 'nodelabel' && 'nodelabelsource' fields of the 'node' table
      PreparedStatement stmt = dbConnection.prepareStatement(SQL_DB_UPDATE_NODE_LABEL);
      d.watch(stmt);
      int column = 1;

      // Node Label
      LOG.debug(
          "NodeLabel.assignLabel: Node label: {} source: {}",
          nodeLabel.getLabel(),
          nodeLabel.getSource());

      if (nodeLabel.getLabel() != null) {
        // nodeLabel may not exceed MAX_NODELABEL_LEN.if it does truncate it
        String label = nodeLabel.getLabel();
        if (label.length() > MAX_NODE_LABEL_LENGTH) {
          label = label.substring(0, MAX_NODE_LABEL_LENGTH);
        }
        stmt.setString(column++, label);
      } else {
        stmt.setNull(column++, java.sql.Types.VARCHAR);
      }
      // Node Label Source
      stmt.setString(column++, String.valueOf(nodeLabel.getSource()));

      // Node ID
      stmt.setInt(column++, nodeID);

      stmt.executeUpdate();
    } finally {
      d.cleanUp();
    }
  }
  /**
   * This method returns a boolean indicating if the page has been responded to by any member of the
   * group the page was sent to.
   *
   * @param noticeId a int.
   * @return a boolean.
   * @throws java.io.IOException if any.
   * @throws org.exolab.castor.xml.MarshalException if any.
   * @throws org.exolab.castor.xml.ValidationException if any.
   */
  public boolean noticeOutstanding(final int noticeId)
      throws IOException, MarshalException, ValidationException {
    boolean outstanding = false;

    Connection connection = null;
    final DBUtils d = new DBUtils(getClass());
    try {
      connection = getConnection();
      d.watch(connection);
      final PreparedStatement statement =
          connection.prepareStatement(
              getConfigManager().getConfiguration().getOutstandingNoticesSql());
      d.watch(statement);

      statement.setInt(1, noticeId);

      ResultSet results = statement.executeQuery();
      d.watch(results);

      // count how many rows were returned, if there is even one then the
      // page
      // has been responded too.
      int count = 0;
      while (results.next()) {
        count++;
      }

      if (count == 0) {
        outstanding = true;
      }
    } catch (SQLException e) {
      log().error("Error getting notice status: " + e.getMessage(), e);
    } finally {
      d.cleanUp();
    }

    return outstanding;
  }
  /**
   * getCurrentAssetNodesList
   *
   * @return a {@link java.util.List} object.
   * @throws java.sql.SQLException if any.
   */
  public List<Integer> getCurrentAssetNodesList() throws SQLException {
    List<Integer> list = new ArrayList<Integer>();

    final DBUtils d = new DBUtils(getClass());
    try {
      Connection conn = DataSourceFactory.getInstance().getConnection();
      d.watch(conn);

      Statement stmt = conn.createStatement();
      d.watch(stmt);

      ResultSet rs = stmt.executeQuery("SELECT NODEID FROM ASSETS");
      d.watch(rs);

      while (rs.next()) {
        list.add(Integer.valueOf(rs.getInt("NODEID")));
      }
    } finally {
      d.cleanUp();
    }

    return list;
  }
  /**
   * Retrieve all the interfaces and services from the database, and keep them in the user session.
   *
   * @param userSession Current user working session
   * @param nodeId Id of the node to manage
   */
  private List<ManagedInterface> getInterfaces(HttpSession userSession, int nodeId)
      throws SQLException {
    Connection connection = null;
    List<ManagedInterface> allInterfaces = new ArrayList<ManagedInterface>();
    int lineCount = 0;

    final DBUtils d = new DBUtils(getClass());
    try {
      connection = DataSourceFactory.getInstance().getConnection();
      d.watch(connection);

      PreparedStatement ifaceStmt = connection.prepareStatement(INTERFACE_QUERY);
      d.watch(ifaceStmt);
      ifaceStmt.setInt(1, nodeId);

      ResultSet ifaceResults = ifaceStmt.executeQuery();
      d.watch(ifaceResults);
      while (ifaceResults.next()) {
        lineCount++;
        ManagedInterface newInterface = new ManagedInterface();
        newInterface.setNodeid(nodeId);
        newInterface.setAddress(ifaceResults.getString(1));
        newInterface.setStatus(ifaceResults.getString(2));
        allInterfaces.add(newInterface);

        PreparedStatement svcStmt = connection.prepareStatement(SERVICE_QUERY);
        d.watch(svcStmt);
        svcStmt.setInt(1, nodeId);
        svcStmt.setString(2, newInterface.getAddress());

        ResultSet svcResults = svcStmt.executeQuery();
        d.watch(svcResults);
        while (svcResults.next()) {
          lineCount++;
          ManagedService newService = new ManagedService();
          newService.setId(svcResults.getInt(1));
          newService.setName(svcResults.getString(2));
          newService.setStatus(svcResults.getString(3));
          newInterface.addService(newService);
        }
      }
      userSession.setAttribute("lineItems.nodemanagement", Integer.valueOf(lineCount));
    } finally {
      d.cleanUp();
    }
    return allInterfaces;
  }
Beispiel #19
0
  /**
   * Insert values into the EVENTS table
   *
   * @exception java.sql.SQLException Thrown if there is an error adding the event to the database.
   * @exception java.lang.NullPointerException Thrown if a required resource cannot be found in the
   *     properties file.
   */
  private void insertEvent(final Header eventHeader, final Event event, final Connection connection)
      throws SQLException {
    // Execute the statement to get the next event id
    final int eventID = getNextId();

    LOG.debug("DBID: {}", eventID);

    synchronized (event) {
      event.setDbid(eventID);
    }
    final DBUtils d = new DBUtils(getClass());

    try {
      final PreparedStatement insStmt =
          connection.prepareStatement(EventdConstants.SQL_DB_INS_EVENT);
      d.watch(insStmt);

      // eventID
      insStmt.setInt(1, eventID);

      // eventUEI
      insStmt.setString(2, Constants.format(event.getUei(), EVENT_UEI_FIELD_SIZE));

      // nodeID
      final Long nodeid = event.getNodeid();
      set(insStmt, 3, event.hasNodeid() ? nodeid.intValue() : -1);

      // eventTime
      insStmt.setTimestamp(4, getEventTime(event));

      // Resolve the event host to a hostname using the ipInterface table
      String hostname = getEventHost(event);

      // eventHost
      set(insStmt, 5, Constants.format(hostname, EVENT_HOST_FIELD_SIZE));

      // ipAddr
      set(insStmt, 6, Constants.format(event.getInterface(), EVENT_INTERFACE_FIELD_SIZE));

      // eventDpName
      String dpName = "localhost";
      if (eventHeader != null && eventHeader.getDpName() != null) {
        dpName = Constants.format(eventHeader.getDpName(), EVENT_DPNAME_FIELD_SIZE);
      } else if (event.getDistPoller() != null) {
        dpName = Constants.format(event.getDistPoller(), EVENT_DPNAME_FIELD_SIZE);
      }
      insStmt.setString(7, dpName);

      // eventSnmpHost
      set(insStmt, 8, Constants.format(event.getSnmphost(), EVENT_SNMPHOST_FIELD_SIZE));

      // service identifier - convert the service name to a service id
      set(insStmt, 9, getEventServiceId(event));

      // eventSnmp
      if (event.getSnmp() != null) {
        insStmt.setString(10, SnmpInfo.format(event.getSnmp(), EVENT_SNMP_FIELD_SIZE));
      } else {
        insStmt.setNull(10, Types.VARCHAR);
      }

      // eventParms

      // Replace any null bytes with a space, otherwise postgres will complain about encoding in
      // UNICODE
      final String parametersString = Parameter.format(event);
      set(insStmt, 11, Constants.format(parametersString, 0));

      // eventCreateTime
      final Timestamp eventCreateTime = new Timestamp(System.currentTimeMillis());
      insStmt.setTimestamp(12, eventCreateTime);

      // eventDescr
      set(insStmt, 13, Constants.format(event.getDescr(), 0));

      // eventLoggroup
      set(
          insStmt,
          14,
          (event.getLoggroupCount() > 0)
              ? Constants.format(event.getLoggroup(), EVENT_LOGGRP_FIELD_SIZE)
              : null);

      // eventLogMsg
      // eventLog
      // eventDisplay
      if (event.getLogmsg() != null) {
        // set log message
        set(insStmt, 15, Constants.format(event.getLogmsg().getContent(), 0));
        String logdest = event.getLogmsg().getDest();
        if (logdest.equals("logndisplay")) {
          // if 'logndisplay' set both log and display column to yes
          set(insStmt, 16, MSG_YES);
          set(insStmt, 17, MSG_YES);
        } else if (logdest.equals("logonly")) {
          // if 'logonly' set log column to true
          set(insStmt, 16, MSG_YES);
          set(insStmt, 17, MSG_NO);
        } else if (logdest.equals("displayonly")) {
          // if 'displayonly' set display column to true
          set(insStmt, 16, MSG_NO);
          set(insStmt, 17, MSG_YES);
        } else if (logdest.equals("suppress")) {
          // if 'suppress' set both log and display to false
          set(insStmt, 16, MSG_NO);
          set(insStmt, 17, MSG_NO);
        }
      } else {
        insStmt.setNull(15, Types.VARCHAR);

        /*
         * If this is an event that had no match in the event conf
         * mark it as to be logged and displayed so that there
         * are no events that slip through the system
         * without the user knowing about them
         */
        set(insStmt, 17, MSG_YES);
      }

      // eventSeverity
      set(insStmt, 18, OnmsSeverity.get(event.getSeverity()).getId());

      // eventPathOutage
      set(
          insStmt,
          19,
          (event.getPathoutage() != null)
              ? Constants.format(event.getPathoutage(), EVENT_PATHOUTAGE_FIELD_SIZE)
              : null);

      // eventCorrelation
      set(
          insStmt,
          20,
          (event.getCorrelation() != null)
              ? org.opennms.netmgt.dao.util.Correlation.format(
                  event.getCorrelation(), EVENT_CORRELATION_FIELD_SIZE)
              : null);

      // eventSuppressedCount
      insStmt.setNull(21, Types.INTEGER);

      // eventOperInstruct
      set(insStmt, 22, Constants.format(event.getOperinstruct(), EVENT_OPERINSTRUCT_FIELD_SIZE));

      // eventAutoAction
      set(
          insStmt,
          23,
          (event.getAutoactionCount() > 0)
              ? AutoAction.format(event.getAutoaction(), EVENT_AUTOACTION_FIELD_SIZE)
              : null);

      // eventOperAction / eventOperActionMenuText
      if (event.getOperactionCount() > 0) {
        final List<Operaction> a = new ArrayList<Operaction>();
        final List<String> b = new ArrayList<String>();

        for (final Operaction eoa : event.getOperactionCollection()) {
          a.add(eoa);
          b.add(eoa.getMenutext());
        }

        set(insStmt, 24, OperatorAction.format(a, EVENT_OPERACTION_FIELD_SIZE));
        set(insStmt, 25, Constants.format(b, EVENT_OPERACTION_MENU_FIELD_SIZE));
      } else {
        insStmt.setNull(24, Types.VARCHAR);
        insStmt.setNull(25, Types.VARCHAR);
      }

      // eventNotification, this column no longer needed
      insStmt.setNull(26, Types.VARCHAR);

      // eventTroubleTicket / eventTroubleTicket state
      if (event.getTticket() != null) {
        set(
            insStmt,
            27,
            Constants.format(event.getTticket().getContent(), EVENT_TTICKET_FIELD_SIZE));
        set(insStmt, 28, event.getTticket().getState().equals("on") ? 1 : 0);
      } else {
        insStmt.setNull(27, Types.VARCHAR);
        insStmt.setNull(28, Types.INTEGER);
      }

      // eventForward
      set(
          insStmt,
          29,
          (event.getForwardCount() > 0)
              ? org.opennms.netmgt.dao.util.Forward.format(
                  event.getForward(), EVENT_FORWARD_FIELD_SIZE)
              : null);

      // event mouseOverText
      set(insStmt, 30, Constants.format(event.getMouseovertext(), EVENT_MOUSEOVERTEXT_FIELD_SIZE));

      // eventAckUser
      if (event.getAutoacknowledge() != null
          && event.getAutoacknowledge().getState().equals("on")) {
        set(
            insStmt,
            31,
            Constants.format(event.getAutoacknowledge().getContent(), EVENT_ACKUSER_FIELD_SIZE));

        // eventAckTime - if autoacknowledge is present,
        // set time to event create time
        set(insStmt, 32, eventCreateTime);
      } else {
        insStmt.setNull(31, Types.INTEGER);
        insStmt.setNull(32, Types.TIMESTAMP);
      }

      // eventSource
      set(insStmt, 33, Constants.format(event.getSource(), EVENT_SOURCE_FIELD_SIZE));

      // ifindex
      if (event.hasIfIndex()) {
        set(insStmt, 34, event.getIfIndex());
      } else {
        insStmt.setNull(34, Types.INTEGER);
      }

      // execute
      insStmt.executeUpdate();
    } finally {
      d.cleanUp();
    }

    LOG.debug("SUCCESSFULLY added {} related  data into the EVENTS table.", event.getUei());
  }
  /**
   * This method inserts a row into the notifications table in the database. This row indicates that
   * the page has been sent out.
   *
   * @param queueID a {@link java.lang.String} object.
   * @param notification TODO
   * @param notifyId a int.
   * @param params a {@link java.util.Map} object.
   * @throws java.sql.SQLException if any.
   */
  public void insertNotice(
      final int notifyId,
      final Map<String, String> params,
      final String queueID,
      final Notification notification)
      throws SQLException {
    Connection connection = null;
    final DBUtils d = new DBUtils(getClass());
    try {
      connection = getConnection();
      d.watch(connection);
      final PreparedStatement statement =
          connection.prepareStatement(
              "INSERT INTO notifications ("
                  + "textmsg, numericmsg, notifyid, pagetime, nodeid, interfaceid, serviceid, eventid, "
                  + "eventuei, subject, queueID, notifConfigName) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
      d.watch(statement);

      // notifications textMsg field
      statement.setString(1, params.get(NotificationManager.PARAM_TEXT_MSG));

      // notifications numericMsg field
      String numMsg = params.get(NotificationManager.PARAM_NUM_MSG);
      if (numMsg != null && numMsg.length() > 256) {
        log().warn("numericmsg too long, it will be truncated");
        numMsg = numMsg.substring(0, 256);
      }
      statement.setString(2, numMsg);

      // notifications notifyID field
      statement.setInt(3, notifyId);

      // notifications pageTime field
      statement.setTimestamp(4, new Timestamp((new Date()).getTime()));

      // notifications nodeID field
      String node = params.get(NotificationManager.PARAM_NODE);
      if (node != null
          && !node.trim().equals("")
          && !node.equalsIgnoreCase("null")
          && !node.equalsIgnoreCase("%nodeid%")) {
        statement.setInt(5, Integer.parseInt(node));
      } else {
        statement.setNull(5, Types.INTEGER);
      }

      // notifications interfaceID field
      String ipaddr = params.get(NotificationManager.PARAM_INTERFACE);
      if (ipaddr != null
          && !ipaddr.trim().equals("")
          && !ipaddr.equalsIgnoreCase("null")
          && !ipaddr.equalsIgnoreCase("%interface%")) {
        statement.setString(6, ipaddr);
      } else {
        statement.setString(6, null);
      }

      // notifications serviceID field
      String service = params.get(NotificationManager.PARAM_SERVICE);
      if (service != null
          && !service.trim().equals("")
          && !service.equalsIgnoreCase("null")
          && !service.equalsIgnoreCase("%service%")) {
        statement.setInt(7, getServiceId(service));
      } else {
        statement.setNull(7, Types.INTEGER);
      }

      // eventID field
      final String eventID = params.get("eventID");
      if (eventID != null
          && !eventID.trim().equals("")
          && !eventID.trim().equals("0")
          && !eventID.equalsIgnoreCase("null")
          && !eventID.equalsIgnoreCase("%eventid%")) {
        statement.setInt(8, Integer.parseInt(eventID));
      } else {
        statement.setNull(8, Types.INTEGER);
      }

      statement.setString(9, params.get("eventUEI"));

      // notifications subject field
      statement.setString(10, params.get(NotificationManager.PARAM_SUBJECT));

      // the queue this will be sent on
      statement.setString(11, queueID);

      statement.setString(12, notification.getName());

      statement.executeUpdate();
    } finally {
      d.cleanUp();
    }
  }
  /**
   * syncSnmpPrimaryState
   *
   * @param conn a {@link java.sql.Connection} object.
   * @throws java.sql.SQLException if any.
   */
  public synchronized void syncSnmpPrimaryState(Connection conn) throws SQLException {
    if (conn == null) {
      throw new IllegalArgumentException("Sync failed...must have valid database connection.");
    }

    /*
     * Retrieve all non-deleted SNMP-supporting IP interfaces from the
     * ipInterface table and build a map of nodes to interface entry list
     */
    log().debug("syncSnmpPrimaryState: building map of nodes to interfaces...");

    Map<Integer, List<LightWeightIfEntry>> nodes = new HashMap<Integer, List<LightWeightIfEntry>>();

    final DBUtils d = new DBUtils(getClass());
    try {
      // prepare the SQL statement to query the database
      PreparedStatement ipRetStmt = conn.prepareStatement(SQL_DB_RETRIEVE_SNMP_IP_INTERFACES);
      d.watch(ipRetStmt);
      ResultSet result = ipRetStmt.executeQuery();
      d.watch(result);

      // Iterate over result set and build map of interface
      // entries keyed by node id.
      List<LightWeightIfEntry> ifList = new ArrayList<LightWeightIfEntry>();
      while (result.next()) {
        // Node Id
        int nodeId = result.getInt(1);

        // IP address
        String address = result.getString(2);
        if (address == null) {
          log().warn("invalid ipInterface table entry, no IP address, skipping...");
          continue;
        }

        // ifIndex
        int ifIndex = result.getInt(6);
        if (result.wasNull()) {
          if (log().isDebugEnabled()) {
            log()
                .debug(
                    "ipInterface table entry for address "
                        + address
                        + " does not have a valid ifIndex ");
          }
          ifIndex = LightWeightIfEntry.NULL_IFINDEX;
        } else if (ifIndex < 1) {
          if (ifIndex == CapsdConfig.LAME_SNMP_HOST_IFINDEX) {
            if (log().isDebugEnabled()) {
              log()
                  .debug(
                      "Using ifIndex = "
                          + CapsdConfig.LAME_SNMP_HOST_IFINDEX
                          + " for address "
                          + address);
            }
          } else {
            if (log().isDebugEnabled()) {
              log()
                  .debug(
                      "ipInterface table entry for address "
                          + address
                          + " does not have a valid ifIndex ");
            }
            ifIndex = LightWeightIfEntry.NULL_IFINDEX;
          }
        }

        // Primary SNMP State
        char primarySnmpState = DbIpInterfaceEntry.SNMP_UNKNOWN;
        String str = result.getString(4);
        if (str != null) {
          primarySnmpState = str.charAt(0);
        }

        // ifType
        int ifType = result.getInt(5);
        if (result.wasNull()) {
          if (log().isDebugEnabled()) {
            log()
                .debug(
                    "snmpInterface table entry for address "
                        + address
                        + " does not have a valid ifType");
          }
          ifType = LightWeightIfEntry.NULL_IFTYPE;
        }

        // New node or existing node?
        ifList = nodes.get(new Integer(nodeId));
        if (ifList == null) {
          // Create new interface entry list
          ifList = new ArrayList<LightWeightIfEntry>();
          ifList.add(
              new LightWeightIfEntry(
                  nodeId,
                  ifIndex,
                  address,
                  DbIpInterfaceEntry.STATE_UNKNOWN,
                  primarySnmpState,
                  ifType));

          // Add interface entry list to the map
          nodes.put(nodeId, ifList);
        } else {
          // Just add the current interface to the
          // node's interface list
          ifList.add(
              new LightWeightIfEntry(
                  nodeId,
                  ifIndex,
                  address,
                  DbIpInterfaceEntry.STATE_UNKNOWN,
                  primarySnmpState,
                  ifType));
        }
      }
    } finally {
      d.cleanUp();
    }

    /*
     * Iterate over the nodes in the map and determine what the primary
     * SNMP interface for each node should be. Keep track of those
     * interfaces whose primary SNMP interface state has changed so that
     * the database can be updated accordingly.
     */
    if (log().isDebugEnabled()) {
      log()
          .debug(
              "syncSnmpPrimaryState: iterating over nodes in map and checking primary SNMP interface, node count: "
                  + nodes.size());
    }
    Iterator<Integer> niter = nodes.keySet().iterator();
    while (niter.hasNext()) {
      // Get the nodeid (key)
      Integer nId = niter.next();
      if (log().isDebugEnabled()) {
        log().debug("building SNMP address list for node " + nId);
      }

      // Lookup the interface list (value)
      List<LightWeightIfEntry> ifEntries = nodes.get(nId);

      /*
       * From the interface entries build a list of InetAddress objects
       * eligible to be the primary SNMP interface for the node, and a
       * list of loopback InetAddress objects eligible to be the primary
       * SNMP interface for the node.
       */
      List<InetAddress> addressList = new ArrayList<InetAddress>();
      List<InetAddress> lbAddressList = new ArrayList<InetAddress>();
      for (LightWeightIfEntry lwIf : ifEntries) {
        /*
         * Skip interfaces which do not have a valid (non-null) ifIndex
         * as they are not eligible to be the primary SNMP interface
         */
        if (lwIf.getIfIndex() == LightWeightIfEntry.NULL_IFINDEX) {
          if (log().isDebugEnabled()) {
            log()
                .debug(
                    "skipping address " + lwIf.getAddress() + ": does not have a valid ifIndex.");
          }
          continue;
        }

        InetAddress addr = InetAddressUtils.addr(lwIf.getAddress());
        addressList.add(addr);
        if (lwIf.getIfType() == LightWeightIfEntry.LOOPBACK_IFTYPE) {
          lbAddressList.add(addr);
        }
      }

      /*
       * Determine primary SNMP interface from the lists of possible addresses
       * in this order: loopback interfaces in collectd-configuration.xml,
       * other interfaces in collectd-configuration.xml, loopback interfaces in
       * the database, other interfaces in the database.
       */
      boolean strict = true;
      InetAddress primarySnmpIf = null;
      String psiType = null;
      if (lbAddressList != null) {
        primarySnmpIf = getCapsdConfig().determinePrimarySnmpInterface(lbAddressList, strict);
        psiType =
            ConfigFileConstants.getFileName(ConfigFileConstants.COLLECTD_CONFIG_FILE_NAME)
                + " loopback addresses";
      }
      if (primarySnmpIf == null) {
        primarySnmpIf = getCapsdConfig().determinePrimarySnmpInterface(addressList, strict);
        psiType =
            ConfigFileConstants.getFileName(ConfigFileConstants.COLLECTD_CONFIG_FILE_NAME)
                + " addresses";
      }
      strict = false;
      if ((primarySnmpIf == null) && (lbAddressList != null)) {
        primarySnmpIf = getCapsdConfig().determinePrimarySnmpInterface(lbAddressList, strict);
        psiType = "DB loopback addresses";
      }
      if (primarySnmpIf == null) {
        primarySnmpIf = getCapsdConfig().determinePrimarySnmpInterface(addressList, strict);
        psiType = "DB addresses";
      }

      if (log().isDebugEnabled()) {
        if (primarySnmpIf == null) {
          log().debug("syncSnmpPrimaryState: No primary SNMP interface found for node " + nId);
        } else {
          log()
              .debug(
                  "syncSnmpPrimaryState: primary SNMP interface for node "
                      + nId
                      + " is: "
                      + primarySnmpIf
                      + ", selected from "
                      + psiType);
        }
      }

      /*
       * Iterate back over interface list and update primary SNMP
       * iinterface state for this node...if the primary SNMP interface
       * state has changed, update the database to reflect the new state.
       */
      for (LightWeightIfEntry lwIf : ifEntries) {
        if (lwIf.getIfIndex() == LightWeightIfEntry.NULL_IFINDEX) {
          lwIf.setSnmpPrimaryState(DbIpInterfaceEntry.SNMP_NOT_ELIGIBLE);
        } else if (primarySnmpIf == null
            || !lwIf.getAddress().equals(InetAddressUtils.str(primarySnmpIf))) {
          if (getCollectdConfig().isServiceCollectionEnabled(lwIf.getAddress(), "SNMP")) {
            lwIf.setSnmpPrimaryState(DbIpInterfaceEntry.SNMP_SECONDARY);
          } else {
            lwIf.setSnmpPrimaryState(DbIpInterfaceEntry.SNMP_NOT_ELIGIBLE);
          }
        } else {
          lwIf.setSnmpPrimaryState(DbIpInterfaceEntry.SNMP_PRIMARY);
        }

        // Has SNMP primary state changed?
        if (lwIf.hasSnmpPrimaryStateChanged()) {
          if (log().isDebugEnabled()) {
            log()
                .debug(
                    "syncSnmpPrimaryState: updating "
                        + lwIf.getNodeId()
                        + "/"
                        + lwIf.getAddress()
                        + ", marking with state: "
                        + lwIf.getSnmpPrimaryState());
          }

          try {
            // prepare the SQL statement to query the database
            PreparedStatement updateStmt = conn.prepareStatement(SQL_DB_UPDATE_SNMP_PRIMARY_STATE);
            d.watch(updateStmt);
            updateStmt.setString(1, new String(new char[] {lwIf.getSnmpPrimaryState()}));
            updateStmt.setInt(2, lwIf.getNodeId());
            updateStmt.setString(3, lwIf.getAddress());

            updateStmt.executeUpdate();
          } finally {
            d.cleanUp();
          }
        }
      }
    }

    log().debug("syncSnmpPrimaryState: sync completed.");
  }
Beispiel #22
0
  /**
   * Return all notices (optionally only unacknowledged notices) sorted by the given sort style.
   *
   * <p><strong>Note: </strong> This limit/offset code is <em>Postgres specific!</em> Per <a
   * href="mailto:[email protected]">Shane </a>, this is okay for now until we can come up with an
   * Oracle alternative too.
   *
   * @param limit if -1 or zero, no limit or offset is used
   * @param offset if -1, no limit or offset if used
   * @param sortStyle a {@link org.opennms.web.outage.SortStyle} object.
   * @param outType a {@link org.opennms.web.outage.OutageType} object.
   * @param filters an array of org$opennms$web$filter$Filter objects.
   * @return an array of {@link org.opennms.web.outage.Outage} objects.
   * @throws java.sql.SQLException if any.
   */
  public static Outage[] getOutages(
      SortStyle sortStyle, OutageType outType, Filter[] filters, int limit, int offset)
      throws SQLException {
    if (sortStyle == null || outType == null || filters == null) {
      throw new IllegalArgumentException("Cannot take null parameters.");
    }

    boolean useLimits = false;
    if (limit > 0 && offset > -1) {
      useLimits = true;
    }

    Outage[] outages = null;
    final Connection conn = Vault.getDbConnection();
    final DBUtils d = new DBUtils(OutageFactory.class, conn);

    try {
      StringBuffer select =
          new StringBuffer(
              "SELECT OUTAGES.*, NODE.NODELABEL, IPINTERFACE.IPHOSTNAME, SERVICE.SERVICENAME, NOTIFICATIONS.NOTIFYID, NOTIFICATIONS.ANSWEREDBY FROM OUTAGES "
                  + "JOIN NODE USING(NODEID) "
                  + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR "
                  + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID "
                  + "LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID "
                  + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.EVENTID "
                  + "WHERE (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') "
                  + "AND ");
      select.append(outType.getClause());

      for (Filter filter : filters) {
        select.append(" AND ");
        select.append(filter.getParamSql());
      }

      select.append(sortStyle.getOrderByClause());

      if (useLimits) {
        select.append(" LIMIT ");
        select.append(limit);
        select.append(" OFFSET ");
        select.append(offset);
      }

      log.debug(select.toString());

      final PreparedStatement stmt = conn.prepareStatement(select.toString());
      d.watch(stmt);

      int parameterIndex = 1;
      for (Filter filter : filters) {
        parameterIndex += filter.bindParam(stmt, parameterIndex);
      }

      final ResultSet rs = stmt.executeQuery();
      d.watch(rs);

      outages = rs2Outages(rs);
    } finally {
      d.cleanUp();
    }

    return outages;
  }
  /**
   * Inserts the new interface into the ipInterface table of the OpenNMS databasee.
   *
   * @param c The connection to the database.
   * @throws java.sql.SQLException Thrown if an error occurs with the connection
   */
  private void insert(Connection c) throws SQLException {
    if (m_fromDb) {
      throw new IllegalStateException("The record already exists in the database");
    }

    // first extract the next node identifier
    StringBuffer names = new StringBuffer("INSERT INTO ipInterface (nodeID,ipAddr");
    StringBuffer values = new StringBuffer("?,?");

    if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) {
      values.append(",?");
      names.append(",ifIndex");
    }

    if ((m_changed & CHANGED_HOSTNAME) == CHANGED_HOSTNAME) {
      values.append(",?");
      names.append(",ipHostname");
    }

    if ((m_changed & CHANGED_MANAGED) == CHANGED_MANAGED) {
      values.append(",?");
      names.append(",isManaged");
    }

    if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS) {
      values.append(",?");
      names.append(",ipStatus");
    }

    if ((m_changed & CHANGED_POLLTIME) == CHANGED_POLLTIME) {
      values.append(",?");
      names.append(",ipLastCapsdPoll");
    }

    if ((m_changed & CHANGED_PRIMARY) == CHANGED_PRIMARY) {
      values.append(",?");
      names.append(",isSnmpPrimary");
    }

    names.append(") VALUES (").append(values).append(')');
    if (log().isDebugEnabled()) {
      log()
          .debug(
              "DbIpInterfaceEntry.insert: SQL insert statement for interface ["
                  + m_nodeId
                  + ","
                  + m_ipAddr
                  + "] = "
                  + names.toString());
    }

    // create the Prepared statement and then start setting the result values
    PreparedStatement stmt = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      stmt = c.prepareStatement(names.toString());
      d.watch(stmt);
      names = null;
      int ndx = 1;
      stmt.setLong(ndx++, m_nodeId);
      stmt.setString(ndx++, InetAddressUtils.str(m_ipAddr));
      if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) {
        stmt.setInt(ndx++, m_ifIndex);
      }
      if ((m_changed & CHANGED_HOSTNAME) == CHANGED_HOSTNAME) {
        stmt.setString(ndx++, m_hostname);
      }
      if ((m_changed & CHANGED_MANAGED) == CHANGED_MANAGED) {
        stmt.setString(ndx++, new String(new char[] {m_managedState}));
      }
      if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS) {
        stmt.setInt(ndx++, m_status);
      }
      if ((m_changed & CHANGED_POLLTIME) == CHANGED_POLLTIME) {
        stmt.setTimestamp(ndx++, m_lastPoll);
      }
      if ((m_changed & CHANGED_PRIMARY) == CHANGED_PRIMARY) {
        stmt.setString(ndx++, new String(new char[] {m_primaryState}));
      }
      // Run the insert
      int rc = stmt.executeUpdate();
      log().debug("DbIpInterfaceEntry.insert: SQL update result = " + rc);
    } finally {
      d.cleanUp();
    }

    // clear the mask and mark as backed by the database
    m_fromDb = true;
    m_changed = 0;
  }
  /**
   * acknowledgeNotice
   *
   * @param event a {@link org.opennms.netmgt.xml.event.Event} object.
   * @param uei a {@link java.lang.String} object.
   * @param matchList an array of {@link java.lang.String} objects.
   * @return a {@link java.util.Collection} object.
   * @throws java.sql.SQLException if any.
   * @throws java.io.IOException if any.
   * @throws org.exolab.castor.xml.MarshalException if any.
   * @throws org.exolab.castor.xml.ValidationException if any.
   */
  public Collection<Integer> acknowledgeNotice(
      final Event event, final String uei, final String[] matchList)
      throws SQLException, IOException, MarshalException, ValidationException {
    Connection connection = null;
    List<Integer> notifIDs = new LinkedList<Integer>();
    final DBUtils d = new DBUtils(getClass());
    ThreadCategory log = this.log();

    try {
      // First get most recent event ID from notifications
      // that match the matchList, then get all notifications
      // with this event ID
      connection = getConnection();
      d.watch(connection);
      int eventID = 0;
      boolean wasAcked = false;
      StringBuffer sql = new StringBuffer("SELECT eventid FROM notifications WHERE eventuei=? ");
      for (int i = 0; i < matchList.length; i++) {
        sql.append("AND ").append(matchList[i]).append("=? ");
      }
      sql.append("ORDER BY eventid desc limit 1");
      PreparedStatement statement = connection.prepareStatement(sql.toString());
      d.watch(statement);
      statement.setString(1, uei);

      for (int i = 0; i < matchList.length; i++) {
        if (matchList[i].equals("nodeid")) {
          statement.setLong(i + 2, event.getNodeid());
        }

        if (matchList[i].equals("interfaceid")) {
          statement.setString(i + 2, event.getInterface());
        }

        if (matchList[i].equals("serviceid")) {
          statement.setInt(i + 2, getServiceId(event.getService()));
        }
      }

      ResultSet results = statement.executeQuery();
      d.watch(results);
      if (results != null && results.next()) {
        eventID = results.getInt(1);
        if (log.isDebugEnabled()) log.debug("EventID for notice(s) to be acked: " + eventID);

        sql =
            new StringBuffer(
                "SELECT notifyid, answeredby, respondtime FROM notifications WHERE eventID=?");

        statement = connection.prepareStatement(sql.toString());
        statement.setInt(1, eventID);

        results = statement.executeQuery();

        if (results != null) {
          while (results.next()) {
            int notifID = results.getInt(1);
            String ansBy = results.getString(2);
            Timestamp ts = results.getTimestamp(3);
            if (ansBy == null) {
              ansBy = "auto-acknowledged";
              ts = new Timestamp((new Date()).getTime());
            } else if (ansBy.indexOf("auto-acknowledged") > -1) {
              if (log.isDebugEnabled())
                log.debug("Notice has previously been auto-acknowledged. Skipping...");
              continue;
            } else {
              wasAcked = true;
              ansBy = ansBy + "/auto-acknowledged";
            }
            if (log.isDebugEnabled())
              log.debug(
                  "Matching DOWN notifyID = "
                      + notifID
                      + ", was acked by user = "******", ansBy = "
                      + ansBy);
            final PreparedStatement update =
                connection.prepareStatement(
                    getConfigManager().getConfiguration().getAcknowledgeUpdateSql());
            d.watch(update);

            update.setString(1, ansBy);
            update.setTimestamp(2, ts);
            update.setInt(3, notifID);

            update.executeUpdate();
            update.close();
            if (wasAcked) {
              notifIDs.add(-1 * notifID);
            } else {
              notifIDs.add(notifID);
            }
          }
        }
      } else {
        if (log.isDebugEnabled()) log.debug("No matching DOWN eventID found");
      }
    } finally {
      d.cleanUp();
    }
    return notifIDs;
  }
  /**
   * Load the current interface from the database. If the interface was modified, the modifications
   * are lost. The nodeid and ip address must be set prior to this call.
   *
   * @param c The connection used to load the data.
   * @throws java.sql.SQLException Thrown if an error occurs with the connection
   */
  private boolean load(Connection c) throws SQLException {
    if (!m_fromDb) throw new IllegalStateException("The record does not exists in the database");

    PreparedStatement stmt = null;
    ResultSet rset = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      // create the Prepared statement and then
      // start setting the result values
      stmt = c.prepareStatement(SQL_LOAD_REC);
      d.watch(stmt);
      stmt.setLong(1, m_nodeId);
      stmt.setString(2, InetAddressUtils.str(m_ipAddr));
      stmt.setInt(3, m_serviceId);

      // Run the insert
      //
      rset = stmt.executeQuery();
      d.watch(rset);
      if (!rset.next()) {
        return false;
      }

      // extract the values.
      //
      int ndx = 1;

      // get the ifIndex
      //
      m_ifIndex = rset.getInt(ndx++);
      if (rset.wasNull()) m_ifIndex = -1;

      // get the last good time
      //
      m_lastGood = rset.getTimestamp(ndx++);

      // get the last fail time
      //
      m_lastFail = rset.getTimestamp(ndx++);

      // get the qualifier
      //
      m_qualifier = rset.getString(ndx++);
      if (rset.wasNull()) m_qualifier = null;

      // get the status
      //
      String str = rset.getString(ndx++);
      if (str != null && !rset.wasNull()) m_status = str.charAt(0);
      else m_status = STATUS_UNKNOWN;

      // get the source
      //
      str = rset.getString(ndx++);
      if (str != null && !rset.wasNull()) m_source = str.charAt(0);
      else m_source = SOURCE_UNKNOWN;

      // get the notify
      //
      str = rset.getString(ndx++);
      if (str != null && !rset.wasNull()) m_notify = str.charAt(0);
      else m_notify = NOTIFY_UNKNOWN;
    } finally {
      d.cleanUp();
    }

    // clear the mask and mark as backed by the database
    m_changed = 0;
    return true;
  }
  /**
   * Updates an existing record in the OpenNMS ifServices table.
   *
   * @param c The connection used for the update.
   * @throws java.sql.SQLException Thrown if an error occurs with the connection
   */
  private void update(Connection c) throws SQLException {
    if (!m_fromDb) throw new IllegalStateException("The record does not exists in the database");

    StringBuffer sqlText = new StringBuffer("UPDATE ifServices SET ");

    char comma = ' ';
    if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) {
      sqlText.append(comma).append("ifIndex = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS) {
      sqlText.append(comma).append("status = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_LASTGOOD) == CHANGED_LASTGOOD) {
      sqlText.append(comma).append("lastGood = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_LASTFAIL) == CHANGED_LASTFAIL) {
      sqlText.append(comma).append("lastFail = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_SOURCE) == CHANGED_SOURCE) {
      sqlText.append(comma).append("source = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_NOTIFY) == CHANGED_NOTIFY) {
      sqlText.append(comma).append("notify = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_QUALIFIER) == CHANGED_QUALIFIER) {
      sqlText.append(comma).append("qualifier = ?");
      comma = ',';
    }

    sqlText.append(" WHERE nodeID = ? AND ipAddr = ? AND serviceID = ? and status <> 'D'");

    LOG.debug("DbIfServiceEntry.update: SQL update statment = {}", sqlText.toString());

    // create the Prepared statement and then
    // start setting the result values
    PreparedStatement stmt = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      stmt = c.prepareStatement(sqlText.toString());
      d.watch(stmt);
      sqlText = null;

      int ndx = 1;
      if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) {
        if (m_ifIndex == -1) stmt.setNull(ndx++, Types.INTEGER);
        else stmt.setInt(ndx++, m_ifIndex);
      }

      if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS) {
        if (m_status != STATUS_UNKNOWN) stmt.setString(ndx++, new String(new char[] {m_status}));
        else stmt.setNull(ndx++, Types.CHAR);
      }

      if ((m_changed & CHANGED_LASTGOOD) == CHANGED_LASTGOOD) {
        if (m_lastGood != null) {
          stmt.setTimestamp(ndx++, m_lastGood);
        } else stmt.setNull(ndx++, Types.TIMESTAMP);
      }

      if ((m_changed & CHANGED_LASTFAIL) == CHANGED_LASTFAIL) {
        if (m_lastFail != null) {
          stmt.setTimestamp(ndx++, m_lastFail);
        } else stmt.setNull(ndx++, Types.TIMESTAMP);
      }

      if ((m_changed & CHANGED_SOURCE) == CHANGED_SOURCE) {
        if (m_source == SOURCE_UNKNOWN) stmt.setNull(ndx++, Types.CHAR);
        else stmt.setString(ndx++, new String(new char[] {m_source}));
      }

      if ((m_changed & CHANGED_NOTIFY) == CHANGED_NOTIFY) {
        if (m_notify == NOTIFY_UNKNOWN) stmt.setNull(ndx++, Types.CHAR);
        else stmt.setString(ndx++, new String(new char[] {m_notify}));
      }

      stmt.setLong(ndx++, m_nodeId);
      stmt.setString(ndx++, InetAddressUtils.str(m_ipAddr));
      stmt.setInt(ndx++, m_serviceId);

      // Run the insert
      int rc = stmt.executeUpdate();
      LOG.debug("DbIfServiceEntry.update: update result = {}", rc);
    } finally {
      d.cleanUp();
    }

    // clear the mask and mark as backed
    // by the database
    //
    m_changed = 0;
  }
  /**
   * Inserts the new interface into the ipInterface table of the OpenNMS database.
   *
   * @param c The connection to the database.
   * @throws java.sql.SQLException Thrown if an error occurs with the connection
   */
  private void insert(Connection c, boolean noRollback) throws SQLException {
    if (m_fromDb) throw new IllegalStateException("The record already exists in the database");

    // first extract the next node identifier
    //
    StringBuffer names = new StringBuffer("INSERT INTO ifServices (nodeID,ipAddr,serviceID");
    StringBuffer values = new StringBuffer("?,?,?");

    if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) {
      values.append(",?");
      names.append(",ifIndex");
    }

    if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS) {
      values.append(",?");
      names.append(",status");
    }

    if ((m_changed & CHANGED_LASTGOOD) == CHANGED_LASTGOOD) {
      values.append(",?");
      names.append(",lastGood");
    }

    if ((m_changed & CHANGED_LASTFAIL) == CHANGED_LASTFAIL) {
      values.append(",?");
      names.append(",lastFail");
    }

    if ((m_changed & CHANGED_SOURCE) == CHANGED_SOURCE) {
      values.append(",?");
      names.append(",source");
    }

    if ((m_changed & CHANGED_NOTIFY) == CHANGED_NOTIFY) {
      values.append(",?");
      names.append(",notify");
    }

    if ((m_changed & CHANGED_QUALIFIER) == CHANGED_QUALIFIER) {
      values.append(",?");
      names.append(",qualifier");
    }

    names.append(") VALUES (").append(values).append(')');

    LOG.debug("DbIfServiceEntry.insert: SQL insert statment = {}", names.toString());

    // create the Prepared statement and then
    // start setting the result values
    //
    PreparedStatement stmt = null;
    PreparedStatement delStmt = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      // Delete any conflicting services marked as deleted
      // before inserting this one
      String delCmd =
          "DELETE FROM ifServices WHERE status = 'D' "
              + "AND nodeid = ? AND ipAddr = ? AND serviceID = ?";

      delStmt = c.prepareStatement(delCmd);
      d.watch(delStmt);
      delStmt.setLong(1, m_nodeId);
      delStmt.setString(2, InetAddressUtils.str(m_ipAddr));
      delStmt.setInt(3, m_serviceId);

      delStmt.executeUpdate();

      stmt = c.prepareStatement(names.toString());
      d.watch(stmt);
      names = null;

      int ndx = 1;
      stmt.setLong(ndx++, m_nodeId);
      stmt.setString(ndx++, InetAddressUtils.str(m_ipAddr));
      stmt.setInt(ndx++, m_serviceId);

      if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) stmt.setInt(ndx++, m_ifIndex);

      if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS)
        stmt.setString(ndx++, new String(new char[] {m_status}));

      if ((m_changed & CHANGED_LASTGOOD) == CHANGED_LASTGOOD) {
        stmt.setTimestamp(ndx++, m_lastGood);
      }

      if ((m_changed & CHANGED_LASTFAIL) == CHANGED_LASTFAIL) {
        stmt.setTimestamp(ndx++, m_lastFail);
      }

      if ((m_changed & CHANGED_SOURCE) == CHANGED_SOURCE)
        stmt.setString(ndx++, new String(new char[] {m_source}));

      if ((m_changed & CHANGED_NOTIFY) == CHANGED_NOTIFY)
        stmt.setString(ndx++, new String(new char[] {m_notify}));

      if ((m_changed & CHANGED_QUALIFIER) == CHANGED_QUALIFIER) stmt.setString(ndx++, m_qualifier);

      // Run the insert
      int rc;
      try {
        rc = stmt.executeUpdate();
      } catch (SQLException e) {
        if (noRollback) {
          throw e;
        } else {
          LOG.warn(
              "ifServices DB insert got exception; will retry after deletion of any existing records for this ifService that are marked for deletion.",
              e);

          /*
           * Maybe there's already an entry for this (service, node, IP address)
           * in the table, but it's marked for deletion. Delete it and try
           * the insertion again.
           */
          c.rollback();

          delStmt = c.prepareStatement(delCmd);
          d.watch(delStmt);
          delStmt.setLong(1, m_nodeId);
          delStmt.setString(2, InetAddressUtils.str(m_ipAddr));
          delStmt.setInt(3, m_serviceId);

          delStmt.executeUpdate();

          rc = stmt.executeUpdate();
        }
      }
      LOG.debug("insert(): SQL update result = {}", rc);
    } finally {
      d.cleanUp();
    }

    // clear the mask and mark as backed by the database
    m_fromDb = true;
    m_changed = 0;
  }
  /**
   * {@inheritDoc}
   *
   * <p>Responsible for performing all necessary initialization for the specified interface in
   * preparation for thresholding.
   */
  public void initialize(ThresholdNetworkInterface iface, Map<?, ?> parameters) {
    // Get interface address from NetworkInterface
    //
    if (iface.getType() != NetworkInterface.TYPE_INET)
      throw new RuntimeException("Unsupported interface type, only TYPE_INET currently supported");
    InetAddress ipAddr = (InetAddress) iface.getAddress();
    String groupName = ParameterMap.getKeyedString(parameters, "thresholding-group", "default");

    // Get the threshold group's RRD repository path
    //
    String repository = null;
    try {
      repository = ThresholdingConfigFactory.getInstance().getRrdRepository(groupName);
    } catch (IllegalArgumentException e) {
      throw new RuntimeException("Thresholding group '" + groupName + "' does not exist.");
    }

    // Add RRD repository as an attribute of the interface for retrieval
    // by the check() method.
    //
    iface.setAttribute(RRD_REPOSITORY_KEY, repository);

    // Get database connection in order to retrieve the nodeid and
    // ifIndex from the database for this interface.
    //
    java.sql.Connection dbConn = null;
    final DBUtils d = new DBUtils(getClass());
    try {
      dbConn = DataSourceFactory.getInstance().getConnection();
      d.watch(dbConn);
    } catch (SQLException sqlE) {
      if (log().isEnabledFor(ThreadCategory.Level.ERROR))
        log().error("initialize: Failed getting connection to the database.", sqlE);
      throw new UndeclaredThrowableException(sqlE);
    }

    // Use IP address to lookup the node id
    //
    // NOTE: All database calls wrapped in try/finally block so we make
    // certain that the connection will be closed when we are
    // finished.
    //
    int nodeId = -1;

    final String hostAddress = InetAddressUtils.str(ipAddr);
    try {
      // Prepare & execute the SQL statement to get the 'nodeid',
      // 'ifIndex' and 'isSnmpPrimary' fields from the ipInterface table.
      //
      PreparedStatement stmt = null;
      try {
        stmt = dbConn.prepareStatement(SQL_GET_NODEID);
        d.watch(stmt);
        stmt.setString(1, hostAddress); // interface address
        ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        if (rs.next()) {
          nodeId = rs.getInt(1);
          if (rs.wasNull()) nodeId = -1;
        }
      } catch (SQLException sqle) {
        if (log().isDebugEnabled()) log().debug("initialize: SQL exception!!", sqle);
        throw new RuntimeException(
            "SQL exception while attempting to retrieve node id for interface " + hostAddress);
      }

      if (log().isDebugEnabled())
        log()
            .debug(
                "initialize: db retrieval info: nodeid = " + nodeId + ", address = " + hostAddress);

      if (nodeId == -1)
        throw new RuntimeException("Unable to retrieve node id for interface " + hostAddress);
    } finally {
      d.cleanUp();
    }

    // Add nodeId as an attribute of the interface for retrieval
    // by the check() method.
    //
    iface.setAttribute(NODE_ID_KEY, new Integer(nodeId));

    // Retrieve the collection of Threshold objects associated with
    // the defined thresholding group and build maps of
    // ThresholdEntity objects keyed by datasource name. The
    // datasource type of the threshold determines which
    // map the threshold entity is added to.
    //
    // Each ThresholdEntity can wrap one high Threshold and one low
    // Threshold castor-generated object for a single datasource.
    // If more than one high or more than one low threshold is defined
    // for a single datasource a warning messages is generated. Only
    // the first threshold in such a scenario will be used for thresholding.
    //

    // Create empty map for storing threshold entities
    Map<String, ThresholdEntity> thresholdMap = new HashMap<String, ThresholdEntity>();

    try {
      for (Basethresholddef thresh :
          ThresholdingConfigFactory.getInstance().getThresholds(groupName)) {
        // See if map entry already exists for this datasource
        // If not, create a new one.
        boolean newEntity = false;
        ThresholdEntity thresholdEntity = null;

        // All latency thresholds are per interface so confirm that
        // the datasource type is set to "if"
        //
        if (!thresh.getDsType().equals("if") && !thresh.getDsType().equals("expr")) {
          log()
              .warn(
                  "initialize: invalid datasource type, latency thresholder only supports interface level datasources.");
          continue; // continue with the next threshold...
        }
        try {
          BaseThresholdDefConfigWrapper wrapper =
              BaseThresholdDefConfigWrapper.getConfigWrapper(thresh);
          // First attempt to lookup the entry in the map
          thresholdEntity = thresholdMap.get(wrapper.getDatasourceExpression());

          // Found entry?
          if (thresholdEntity == null) {
            // Nope, create a new one
            newEntity = true;
            thresholdEntity = new ThresholdEntity();
          }

          try {
            thresholdEntity.addThreshold(wrapper);
          } catch (IllegalStateException e) {
            log()
                .warn(
                    "Encountered duplicate "
                        + thresh.getType()
                        + " for datasource "
                        + wrapper.getDatasourceExpression()
                        + ": "
                        + e,
                    e);
          }

          // Add new entity to the map
          if (newEntity) {
            thresholdMap.put(wrapper.getDatasourceExpression(), thresholdEntity);
          }
        } catch (ThresholdExpressionException e) {
          log().warn("Could not parse threshold expression: " + e.getMessage(), e);
        }
      }
    } catch (IllegalArgumentException e) {
      throw new RuntimeException("Thresholding group '" + groupName + "' does not exist.");
    }

    // Add threshold maps as attributes for retrieval by the check() method.
    //
    iface.setAttribute(THRESHOLD_MAP_KEY, thresholdMap);

    // Debug
    //
    if (log().isDebugEnabled()) {
      log()
          .debug(
              "initialize: dumping interface thresholds defined for "
                  + hostAddress
                  + "/"
                  + groupName
                  + ":");
      Iterator<ThresholdEntity> iter = thresholdMap.values().iterator();
      while (iter.hasNext()) log().debug(iter.next().toString());
    }

    if (log().isDebugEnabled())
      log().debug("initialize: initialization completed for " + hostAddress);
    return;
  }
  /**
   * Updates an existing record in the OpenNMS ipInterface table.
   *
   * @param c The connection used for the update.
   * @throws java.sql.SQLException Thrown if an error occurs with the connection
   */
  private void update(Connection c) throws SQLException {
    if (!m_fromDb) {
      throw new IllegalStateException("The record does not exists in the database");
    }

    // first extract the next node identifier
    StringBuffer sqlText = new StringBuffer("UPDATE ipInterface SET ");

    char comma = ' ';
    if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) {
      sqlText.append(comma).append("ifIndex = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_HOSTNAME) == CHANGED_HOSTNAME) {
      sqlText.append(comma).append("ipHostname = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_MANAGED) == CHANGED_MANAGED) {
      sqlText.append(comma).append("isManaged = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS) {
      sqlText.append(comma).append("ipStatus = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_POLLTIME) == CHANGED_POLLTIME) {
      sqlText.append(comma).append("ipLastCapsdPoll = ?");
      comma = ',';
    }

    if ((m_changed & CHANGED_PRIMARY) == CHANGED_PRIMARY) {
      sqlText.append(comma).append("isSnmpPrimary = ?");
      comma = ',';
    }

    if (m_useIfIndexAsKey) {
      sqlText.append(" WHERE nodeID = ? AND ipAddr = ? AND ifIndex = ?");
    } else {
      sqlText.append(" WHERE nodeID = ? AND ipAddr = ?");
    }

    if ((m_changed & CHANGED_PRIMARY) == CHANGED_PRIMARY && m_primaryState == 'N') {
      sqlText.append(" AND isSnmpPrimary != 'C'");
    }

    sqlText.append(" AND isManaged <> 'D'");

    log().debug("DbIpInterfaceEntry.update: SQL update statment = " + sqlText.toString());

    // create the Prepared statement and then start setting the result values
    PreparedStatement stmt = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      stmt = c.prepareStatement(sqlText.toString());
      d.watch(stmt);
      sqlText = null;
      int ndx = 1;
      if ((m_changed & CHANGED_IFINDEX) == CHANGED_IFINDEX) {
        if (m_ifIndex == -1) {
          stmt.setNull(ndx++, Types.INTEGER);
        } else {
          stmt.setInt(ndx++, m_ifIndex);
        }
      }
      if ((m_changed & CHANGED_HOSTNAME) == CHANGED_HOSTNAME) {
        if (m_hostname != null) {
          stmt.setString(ndx++, m_hostname);
        } else {
          stmt.setNull(ndx++, Types.VARCHAR);
        }
      }
      if ((m_changed & CHANGED_MANAGED) == CHANGED_MANAGED) {
        if (m_managedState == STATE_UNKNOWN) {
          stmt.setString(ndx++, "N");
        } else {
          stmt.setString(ndx++, new String(new char[] {m_managedState}));
        }
      }
      if ((m_changed & CHANGED_STATUS) == CHANGED_STATUS) {
        if (m_status == -1) {
          stmt.setNull(ndx++, Types.INTEGER);
        } else {
          stmt.setInt(ndx++, m_status);
        }
      }
      if ((m_changed & CHANGED_POLLTIME) == CHANGED_POLLTIME) {
        if (m_lastPoll != null) {
          stmt.setTimestamp(ndx++, m_lastPoll);
        } else {
          stmt.setNull(ndx++, Types.TIMESTAMP);
        }
      }
      if ((m_changed & CHANGED_PRIMARY) == CHANGED_PRIMARY) {
        if (m_primaryState == SNMP_UNKNOWN) {
          stmt.setNull(ndx++, Types.CHAR);
        } else {
          stmt.setString(ndx++, new String(new char[] {m_primaryState}));
        }
      }
      stmt.setLong(ndx++, m_nodeId);
      stmt.setString(ndx++, InetAddressUtils.str(m_ipAddr));
      if (m_useIfIndexAsKey) {
        if (m_ifIndex == -1) {
          stmt.setNull(ndx++, Types.INTEGER);
        } else {
          stmt.setInt(ndx++, m_originalIfIndex);
        }
      }
      // Run the insert
      int rc = stmt.executeUpdate();
      log().debug("DbIpInterfaceEntry.update: update result = " + rc);
    } finally {
      d.cleanUp();
    }

    // clear the mask and mark as backed by the database
    m_changed = 0;
  }
  /**
   * Load the current interface from the database. If the interface was modified, the modifications
   * are lost. The nodeid and ip address must be set prior to this call.
   *
   * @param c The connection used to load the data.
   * @throws java.sql.SQLException Thrown if an error occurs with the connection
   */
  private boolean load(Connection c) throws SQLException {
    if (!m_fromDb) {
      throw new IllegalStateException("The record does not exists in the database");
    }

    // create the Prepared statement and then start setting the query values
    PreparedStatement stmt = null;
    ResultSet rset = null;
    final DBUtils d = new DBUtils(getClass());

    try {
      if (m_useIfIndexAsKey) {
        stmt = c.prepareStatement(SQL_LOAD_REC_IFINDEX);
        d.watch(stmt);
        stmt.setLong(1, m_nodeId);
        stmt.setString(2, InetAddressUtils.str(m_ipAddr));
        stmt.setInt(3, m_ifIndex);
      } else {
        stmt = c.prepareStatement(SQL_LOAD_REC);
        d.watch(stmt);
        stmt.setLong(1, m_nodeId);
        stmt.setString(2, InetAddressUtils.str(m_ipAddr));
      }

      // Execute the query
      rset = stmt.executeQuery();
      d.watch(rset);
      if (!rset.next()) {
        return false;
      }

      // extract the values
      int ndx = 1;

      // get the ifIndex
      m_ifIndex = rset.getInt(ndx++);
      if (rset.wasNull()) {
        m_ifIndex = -1;
      }

      // get the host name
      m_hostname = rset.getString(ndx++);
      if (rset.wasNull()) {
        m_hostname = null;
      }

      // get the managed status
      String str = rset.getString(ndx++);
      if (str != null && rset.wasNull() == false) {
        m_managedState = str.charAt(0);
      } else {
        m_managedState = STATE_UNKNOWN;
      }

      // get the status
      m_status = rset.getInt(ndx++);
      if (rset.wasNull()) {
        m_status = -1;
      }

      // get the time
      m_lastPoll = rset.getTimestamp(ndx++);

      // get the SNMP primary state
      str = rset.getString(ndx++);
      if (str != null && rset.wasNull() == false) {
        m_primaryState = str.charAt(0);
      } else {
        m_primaryState = STATE_UNKNOWN;
      }
    } finally {
      d.cleanUp();
    }

    // clear the mask and mark as backed by the database
    m_changed = 0;
    return true;
  }