Example usage for java.sql SQLException toString

List of usage examples for java.sql SQLException toString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. SQLException
  6. toString

Introduction

In this page you can find the example usage for java.sql SQLException toString.

Prototype

public String toString()

Source Link

Document

Returns a short description of this throwable.

Usage

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

/**
 * Perform an insert of a new record. The record number element of the class
 * is not used AND it is not returned by this method. All other elements
 * must be complete and correct.//from   w w w . ja v a  2s  . com
 *
 * @param rec_
 *        The Alert definition to insert into the database table.
 * @return 0 if successful, otherwise the Oracle error code.
 */
public int insertAlert(AlertRec rec_) {
    // Ensure required data dependancies.
    rec_.setDependancies();

    // Update the database.
    try {
        int xxx = insertProperties(rec_);
        if (xxx == 0) {
            xxx = insertReport(rec_);
            if (xxx == 0) {
                xxx = insertRecipients(rec_);
                if (xxx == 0) {
                    xxx = insertDisplay(rec_);
                    if (xxx == 0) {
                        xxx = insertQuery(rec_);
                        if (xxx != 0)
                            rec_.setAlertRecNum(null);
                    } else
                        rec_.setAlertRecNum(null);
                } else
                    rec_.setAlertRecNum(null);
            } else
                rec_.setAlertRecNum(null);
        } else
            rec_.setAlertRecNum(null);
        return xxx;
    } catch (SQLException ex) {
        // Ooops...
        rec_.setAlertRecNum(null);
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + ex.toString();
        _logger.error(_errorMsg);
        return _errorCode;
    }
}

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

/**
 * Update the Query details for the Alert.
 *
 * @param rec_/* ww w  . j  av  a2s  .c  om*/
 *        The Alert definition to be updated.
 * @return 0 if successful, otherwise the database error code.
 * @throws java.sql.SQLException
 *         On error with rollback().
 */
private int updateQuery(AlertRec rec_) throws SQLException {
    // First we delete the existing Query details as it's easier to wipe out
    // the old and add
    // the new than trying to track individual changes.
    String delete = "delete " + "from sbrext.sn_query_view_ext " + "where al_idseq = ?";
    PreparedStatement pstmt = null;
    int rc = 0;
    try {
        pstmt = _conn.prepareStatement(delete);
        pstmt.setString(1, rec_.getAlertRecNum());
        pstmt.executeUpdate();
        pstmt.close();
        pstmt = null;

        rc = insertQuery(rec_);
    } catch (SQLException ex) {
        // Ooops...
        _conn.rollback();
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + delete + "\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

/**
 * Update the recipients list for the Alert report.
 *
 * @param rec_//from   w  w  w .j  a  va2 s.c om
 *        The Alert definition to be saved to the database.
 * @return 0 if successful, otherwise the database error code.
 * @throws java.sql.SQLException
 *         On error with rollback().
 */
private int updateRecipients(AlertRec rec_) throws SQLException {
    // We do not try to keep up with individual changes to the list. We
    // simply
    // wipe out the existing list and replace it with the new one.
    String delete = "delete " + "from sn_recipient_view_ext " + "where rep_idseq = ?";
    PreparedStatement pstmt = null;
    int rc = 0;
    try {
        pstmt = _conn.prepareStatement(delete);
        pstmt.setString(1, rec_.getReportRecNum());

        pstmt.executeUpdate();
        pstmt.close();
        pstmt = null;

        rc = insertRecipients(rec_);
    } catch (SQLException ex) {
        // Ooops...
        _conn.rollback();
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + delete + "\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

/**
 * Perform the database access for a simple query which results in a 4
 * column value per returned row./*from w  w  w  .jav  a  2s .c om*/
 *
 * @param select_
 *        The SQL select to run.
 * @param flag_ true if the list should be prefixed with "All".
 * @return 0 if successful, otherwise the database error code.
 */
private Results3 getBasicData3(String select_, boolean flag_) {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Vector<ResultsData3> results = new Vector<ResultsData3>();
    Results3 data = new Results3();

    try {
        // Prepare the statement.
        pstmt = _conn.prepareStatement(select_);

        // Get the list.
        rs = pstmt.executeQuery();
        ResultsData3 rec;
        while (rs.next()) {
            // Remember about the 1 (one) based indexing.
            rec = new ResultsData3();
            rec._id1 = rs.getString(1);
            rec._id2 = rs.getString(2);
            rec._id3 = rs.getInt(3);
            rec._label1 = rs.getString(4);
            rec._label2 = rs.getString(5);
            results.add(rec);
        }

        // Move the list from a Vector to an array and add "(All)" to
        // the beginning.
        int offset = (flag_) ? 1 : 0;
        int count = results.size() + offset;
        data._data = new ResultsData3[count];
        if (flag_) {
            data._data[0] = new ResultsData3();
            data._data[0]._label1 = Constants._STRALL;
            data._data[0]._label2 = Constants._STRALL;
            data._data[0]._id1 = Constants._STRALL;
            data._data[0]._id2 = Constants._STRALL;
            data._data[0]._id3 = 0;
        }
        int cnt = 0;
        for (int ndx = offset; ndx < count; ++ndx) {
            rec = (ResultsData3) results.get(cnt++);
            data._data[ndx] = new ResultsData3();
            data._data[ndx]._label1 = rec._label1.replaceAll("[\\s]", " ");
            data._data[ndx]._label2 = rec._label2.replaceAll("[\\s]", " ");
            data._data[ndx]._id1 = rec._id1;
            data._data[ndx]._id2 = rec._id2;
            data._data[ndx]._id3 = rec._id3;
        }
        data._rc = 0;
    } catch (SQLException ex) {
        // Bad...
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + select_ + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
        data._rc = _errorCode;
    } finally {
        closeCursors(pstmt, rs);
    }
    return data;
}

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

/**
 * Execute the specified SQL select query and return a label/value pair.
 *
 * @param select_//from   w w w .  ja v  a2s . co  m
 *        The SQL select statement.
 * @param flag_
 *        True to prefix "(All)" to the result set. False to return the
 *        result set unaltered.
 * @return 0 if successful, otherwise the Oracle error code.
 */
private Results1 getBasicData1(String select_, boolean flag_) {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Vector<ResultsData1> results = new Vector<ResultsData1>();
    Results1 data = new Results1();

    try {
        // Prepare the statement.
        pstmt = _conn.prepareStatement(select_);

        // Get the list.
        rs = pstmt.executeQuery();
        ResultsData1 rec;
        while (rs.next()) {
            // Remember about the 1 (one) based indexing.
            rec = new ResultsData1();
            rec._val = rs.getString(1);
            rec._label = rs.getString(2);
            results.add(rec);
        }

        // We know there will always be someone in the table but we should
        // follow good
        // programming.
        if (results.size() == 0) {
            data._data = null;
        } else {
            // Move the list from a Vector to an array and add "(All)" to
            // the beginning.
            int count = results.size() + ((flag_) ? 1 : 0);
            data._data = new ResultsData1[count];
            int ndx;
            if (flag_) {
                data._data[0] = new ResultsData1();
                data._data[0]._label = Constants._STRALL;
                data._data[0]._val = Constants._STRALL;
                ndx = 1;
            } else {
                ndx = 0;
            }
            int cnt = 0;
            for (; ndx < count; ++ndx) {
                rec = (ResultsData1) results.get(cnt++);
                data._data[ndx] = new ResultsData1();
                data._data[ndx]._label = rec._label.replaceAll("[\\s]", " ");
                data._data[ndx]._val = rec._val;
            }
        }
        data._rc = 0;
    } catch (SQLException ex) {
        // Bad...
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + select_ + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
        data._rc = _errorCode;
    } finally {
        closeCursors(pstmt, rs);
    }
    return data;
}

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

/**
 * Get the Alerts which are active for the target date provided.
 *
 * @param target_/*from   w  w  w.j  a va 2s. co  m*/
 *        The target date, typically the date an Auto Run process is
 *        started.
 * @return null if an error, otherwise the list of valid alert definitions.
 */
public AlertRec[] selectAlerts(Timestamp target_) {
    String select = "select al_idseq, name, created_by " + "from sbrext.sn_alert_view_ext "
            + "where al_status <> 'I' AND " + "(auto_freq_unit = 'D' OR "
            + "(auto_freq_unit = 'W' AND auto_freq_value = ?) OR "
            + "(auto_freq_unit = 'M' AND auto_freq_value = ?)) "
            + "order by upper(created_by) asc, upper(name) asc";

    // Get day and date from target to qualify the select.
    GregorianCalendar tdate = new GregorianCalendar();
    tdate.setTimeInMillis(target_.getTime());
    int dayWeek = tdate.get(Calendar.DAY_OF_WEEK);
    int dayMonth = tdate.get(Calendar.DAY_OF_MONTH);

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    AlertRec[] recs = null;
    try {
        // Set SQL arguments
        pstmt = _conn.prepareStatement(select);
        pstmt.setInt(1, dayWeek);
        pstmt.setInt(2, dayMonth);

        // Retrieve all applicable definition ids.
        rs = pstmt.executeQuery();
        Vector<String> list = new Vector<String>();
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        rs.close();
        rs = null;
        pstmt.close();
        pstmt = null;

        // There may be nothing to do.
        if (list.size() == 0)
            recs = new AlertRec[0];
        else {
            // retrieve the full alert definition, we will need it.
            recs = new AlertRec[list.size()];
            int keep = 0;
            int ndx;
            for (ndx = 0; ndx < recs.length; ++ndx) {
                // Be sure we can read the Alert Definition.
                recs[keep] = selectAlert((String) list.get(ndx));
                if (recs[keep] == null)
                    return null;

                // Check the date. We do this here and not in the SQL because
                // 99.99% of the time this will return true and complicating the
                // SQL isn't necessary.
                if (recs[keep].isActive(target_))
                    ++keep;

                // In the RARE situation that the alert is inactive at this
                // point,
                // we reset the object pointer to release the memory.
                else
                    recs[keep] = null;
            }

            // Return the results. It is possible that sometimes the last entry
            // in the
            // list will be null. Consequently the use of the list should be in
            // a loop
            // with the following condition: "cnt < recs.length && recs[cnt] !=
            // null"
            if (keep != ndx) {
                // Only process the ones that are Active.
                AlertRec trecs[] = new AlertRec[keep];
                for (ndx = 0; ndx < keep; ++ndx)
                    trecs[ndx] = recs[ndx];
                recs = trecs;
            }
        }
    } catch (SQLException ex) {
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + select + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
    } finally {
        closeCursors(pstmt, rs);
    }
    return recs;
}

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

/**
 * Pull the properties for a specific alert definition.
 *
 * @param rec_/*from  ww  w .  j  ava  2  s .c  om*/
 *        The alert for the desired property values. The alertRecNum must be
 *        set prior to this method.
 * @return 0 if successful, otherwise the database error code.
 */
private int selectProperties(AlertRec rec_) {
    // Define the SQL Select. The column names are expanded to ensure the
    // order of retrieval. If asterisk (*) is used
    // and the database definition changes it could rearrange the columns
    // and the subsequent ResultSet.get...() method
    // calls will fail.
    String select = "select a.name, a.last_auto_run, a.last_manual_run, a.auto_freq_unit, a.al_status, a.begin_date, a.end_date, "
            + "a.status_reason, a.date_created, nvl(a.date_modified, a.date_created) as mdate, nvl(a.modified_by, a.created_by) as mby, "
            + "a.created_by, a.auto_freq_value, u1.name, nvl(u2.name, u1.name) as name2 "
            + "from sbrext.sn_alert_view_ext a, sbr.user_accounts_view u1, sbr.user_accounts_view u2 "
            + "where a.al_idseq = ? and u1.ua_name = a.created_by and u2.ua_name(+) = a.modified_by";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int rc = 0;
    try {
        // Get ready...
        pstmt = _conn.prepareStatement(select);
        pstmt.setString(1, rec_.getAlertRecNum());

        // Go!
        rs = pstmt.executeQuery();
        if (rs.next()) {
            // As the where clause uses a specific ID we should only
            // retrieve one result. And there's the
            // one (1) based indexing again.
            rec_.setName(rs.getString(1));
            rec_.setAdate(rs.getTimestamp(2));
            rec_.setRdate(rs.getTimestamp(3));
            rec_.setFreq(rs.getString(4));
            rec_.setActive(rs.getString(5));
            rec_.setStart(rs.getTimestamp(6));
            rec_.setEnd(rs.getTimestamp(7));
            rec_.setInactiveReason(rs.getString(8));
            rec_.setCdate(rs.getTimestamp(9));
            rec_.setMdate(rs.getTimestamp(10));
            //rec_.setModifier(rs.getString(11));
            rec_.setCreator(rs.getString(12));
            rec_.setDay(rs.getInt(13));
            rec_.setCreatorName(rs.getString(14));
            //rec_.setModifierName(rs.getString(15));
        } else {
            // This shouldn't happen but just in case...
            rec_.setAlertRecNum(null);
        }
    } catch (SQLException ex) {
        // We've got trouble.
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + select + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
        rc = _errorCode;
    } finally {
        closeCursors(pstmt, rs);
    }

    return rc;
}

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

/**
 * Pull the list of recipients for a specific alert.
 *
 * @param rec_/* w  w w.j  av  a2  s. com*/
 *        The alert for the desired recipients. The alertRecNum must be set
 *        prior to this method.
 * @return 0 if successful, otherwise the database error code.
 */
private int selectRecipients(AlertRec rec_) {
    // A Report has a list of one or more recipients.
    String select = "select ua_name, email, conte_idseq " + "from sbrext.sn_recipient_view_ext "
            + "where rep_idseq = ?";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int rc = 0;
    try {
        // Get ready...
        pstmt = _conn.prepareStatement(select);
        pstmt.setString(1, rec_.getReportRecNum());

        // Go!
        Vector<String> rlist = new Vector<String>();
        rs = pstmt.executeQuery();
        while (rs.next()) {
            String temp = rs.getString(1);
            // We use the ua_name as is.
            if (temp != null)
                rlist.add(temp);
            else {
                temp = rs.getString(2);
                // The email address must have an "@" in it somewhere so no
                // change.
                if (temp != null)
                    rlist.add(temp);
                else {
                    temp = rs.getString(3);
                    // To distinguish groups from a ua_name we use a "/" as
                    // a prefix.
                    if (temp != null)
                        rlist.add("/" + temp);
                }
            }
        }

        // Move the data to an array and drop the Vector.
        if (rlist.size() > 0) {
            String temp[] = new String[rlist.size()];
            for (int ndx = 0; ndx < temp.length; ++ndx) {
                temp[ndx] = (String) rlist.get(ndx);
            }
            rec_.setRecipients(temp);
        } else {
            rec_.setRecipients(null);
        }
    } catch (SQLException ex) {
        // We've got trouble.
        _errorCode = DBAlertUtil.getSQLErrorCode(ex);
        _errorMsg = _errorCode + ": " + select + "\n\n" + ex.toString();
        _logger.error(_errorMsg);
        rc = _errorCode;
    } finally {
        closeCursors(pstmt, rs);
    }
    return rc;
}

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

/**
 * Update the Report information for an Alert within the database.
 *
 * @param rec_//w ww.  j  a  va 2s  .co m
 *        The Alert definition to be written to the database.
 * @return 0 if successful, otherwise the database error code.
 * @throws java.sql.SQLException
 *         On error with rollback().
 */
private int updateReport(AlertRec rec_) throws SQLException {
    // Update the related Report definition.
    String update = "update sbrext.sn_report_view_ext set "
            + "comments = ?, include_property_ind = ?, style = ?, send = ?, acknowledge_ind = ?, assoc_lvl_num = ?, "
            + "modified_by = ? " + "where rep_idseq = ?";
    PreparedStatement pstmt = null;
    int rc = 0;
    try {
        // Set all the SQL arguments.
        pstmt = _conn.prepareStatement(update);
        pstmt.setString(1, rec_.getIntro(false));
        pstmt.setString(2, rec_.getIncPropSectString());
        pstmt.setString(3, rec_.getReportStyleString());
        pstmt.setString(4, rec_.getReportEmptyString());
        pstmt.setString(5, rec_.getReportAckString());
        pstmt.setInt(6, rec_.getIAssocLvl());
        pstmt.setString(7, _user);

        pstmt.setString(8, rec_.getReportRecNum());

        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

/**
 * Update the database with the Alert properties stored in a memory record.
 *
 * @param rec_/*from ww w.  jav a2  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;
}