Example usage for java.sql SQLException getSQLState

List of usage examples for java.sql SQLException getSQLState

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

Introduction

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

Prototype

public String getSQLState()

Source Link

Document

Retrieves the SQLState for this SQLException object.

Usage

From source file:com.nabla.wapp.server.basic.general.ExportService.java

private boolean exportFile(final String id, final UserSession userSession, final HttpServletResponse response)
        throws IOException, SQLException, InternalErrorException {
    final Connection conn = db.getConnection();
    try {/*ww w  .j a v  a  2  s .c  o  m*/
        final PreparedStatement stmt = StatementFormat.prepare(conn, "SELECT * FROM export WHERE id=?;", id);
        try {
            final ResultSet rs = stmt.executeQuery();
            try {
                if (!rs.next()) {
                    if (log.isDebugEnabled())
                        log.debug("failed to find file ID= " + id);
                    return false;
                }
                if (!userSession.getSessionId().equals(rs.getString("userSessionId"))) {
                    if (log.isTraceEnabled())
                        log.trace("invalid user session ID");
                    return false;
                }
                if (log.isTraceEnabled())
                    log.trace("exporting file " + id);
                response.reset();
                response.setBufferSize(DEFAULT_BUFFER_SIZE);
                response.setContentType(rs.getString("content_type"));
                if (rs.getBoolean("output_as_file")) {
                    // IMPORTANT:
                    // MUST be done before calling getOutputStream() otherwise no SaveAs dialogbox
                    response.setHeader("Content-Disposition",
                            MessageFormat.format("attachment; filename=\"{0}\"", rs.getString("name")));
                }
                IOUtils.copy(rs.getBinaryStream("content"), response.getOutputStream());
                /*   final BufferedInputStream input = new BufferedInputStream(rs.getBinaryStream("content"), DEFAULT_BUFFER_SIZE);
                   try {
                      final BufferedOutputStream output = new BufferedOutputStream(response.getOutputStream(), DEFAULT_BUFFER_SIZE);
                      try {
                         final byte[] buffer = new byte[DEFAULT_BUFFER_SIZE];
                         int length;
                         while ((length = input.read(buffer)) > 0)
                output.write(buffer, 0, length);
                      } finally {
                         output.close();
                      }
                   } finally {
                      input.close();
                   }*/
            } finally {
                rs.close();
                try {
                    Database.executeUpdate(conn, "DELETE FROM export WHERE id=?;", id);
                } catch (final SQLException e) {
                    if (log.isErrorEnabled())
                        log.error("failed to delete export record: " + e.getErrorCode() + "-" + e.getSQLState(),
                                e);
                }
            }
        } finally {
            stmt.close();
        }
    } finally {
        // remove any orphan export records i.e. older than 48h (beware of timezone!)
        final Calendar dt = Util.dateToCalendar(new Date());
        dt.add(GregorianCalendar.DATE, -2);
        try {
            Database.executeUpdate(conn, "DELETE FROM export WHERE created < ?;", Util.calendarToSqlDate(dt));
        } catch (final SQLException __) {
        }
        conn.close();
    }
    return true;
}

From source file:com.nabla.wapp.server.database.UpdateStatement.java

public void execute(final Connection conn, final T record)
        throws SQLException, DispatchException, ValidationException {
    Assert.argumentNotNull(conn);//from ww  w  . jav a  2  s. com

    final List<IStatementParameter> parametersToUpdate = new ArrayList<IStatementParameter>();
    final ArgumentList updates = new ArgumentList();
    for (IStatementParameter parameter : parameters) {
        if (!parameter.include(record))
            continue;
        parametersToUpdate.add(parameter);
        updates.add(parameter.getName() + "=?");
    }
    if (parametersToUpdate.isEmpty()) {
        if (log.isDebugEnabled())
            log.debug("no values to update");
        return;
    }
    final String sql = MessageFormat.format(sqlTemplate, updates.toString());
    if (log.isDebugEnabled())
        log.debug("SQL=" + sql);
    final PreparedStatement stmt = conn.prepareStatement(sql);
    try {
        int i = 1;
        for (IStatementParameter parameter : parametersToUpdate)
            parameter.write(stmt, i++, record);
        recordId.write(stmt, i++, record);
        if (stmt.executeUpdate() != 1)
            throw new DispatchException(CommonServerErrors.RECORD_HAS_BEEN_REMOVED);
    } catch (final SQLException e) {
        if (uniqueFieldName != null && SQLState.valueOf(e) == SQLState.INTEGRITY_CONSTRAINT_VIOLATION) {
            if (log.isErrorEnabled())
                log.error("SQL error " + e.getErrorCode() + "-" + e.getSQLState(), e);
            throw new ValidationException(uniqueFieldName, CommonServerErrors.DUPLICATE_ENTRY);
        }
        throw e;
    } finally {
        Database.close(stmt);
    }
}

From source file:org.apache.hadoop.chukwa.util.DatabaseWriter.java

public ResultSet query(String query) {
    try {//from w  w  w  .j a  va 2s. c om
        stmt = conn.createStatement();
        rs = stmt.executeQuery(query);
    } catch (SQLException ex) {
        // handle any errors
        log.error(ex, ex);
        log.error("SQL Statement:" + query);
        log.error("SQLException: " + ex.getMessage());
        log.error("SQLState: " + ex.getSQLState());
        log.error("VendorError: " + ex.getErrorCode());
    } finally {
    }
    return rs;
}

From source file:org.apache.hadoop.chukwa.util.DatabaseWriter.java

public void execute(String query) {
    try {//from   ww  w.j  a v  a 2  s . c  o  m
        stmt = conn.createStatement();
        stmt.execute(query);
    } catch (SQLException ex) {
        // handle any errors
        log.error(ex, ex);
        log.error("SQL Statement:" + query);
        log.error("SQLException: " + ex.getMessage());
        log.error("SQLState: " + ex.getSQLState());
        log.error("VendorError: " + ex.getErrorCode());
    } finally {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException sqlEx) {
                // ignore
            }
            stmt = null;
        }
    }
}

From source file:eclserver.db.objects.ContactsDao.java

public ContactsDao(Connection connection, String addressListName) {
    this.objectName = addressListName;

    try {//from  w ww  . j a v a 2  s .co m
        System.out.println("Received connection from factory... building Addresses List. \n");
        dbConnection = connection;
        stmtGetListEntries = dbConnection.prepareStatement(strGetListEntries);
        stmtSaveNewRecord = dbConnection.prepareStatement(strSaveAddress, Statement.RETURN_GENERATED_KEYS);
        stmtUpdateExistingRecord = dbConnection.prepareStatement(strUpdateAddress);
        stmtGetAddress = dbConnection.prepareStatement(strGetAddress);
        stmtDeleteAddress = dbConnection.prepareStatement(strDeleteAddress);
        stmtNukeAddresses = dbConnection.prepareStatement(strNukeAddresses);

    } catch (SQLException ex) {
        System.out.println("Exception creating AddressDAO:  " + ex.getSQLState() + ex.getMessage());
    }
}

From source file:org.nuxeo.ecm.core.storage.sql.LockManager.java

/**
 * Does the exception mean that we should retry the transaction?
 *///from   ww w .j  av  a  2s . c om
protected boolean shouldRetry(SQLException e) {
    String sqlState = e.getSQLState();
    if ("23000".equals(sqlState)) {
        // MySQL: Duplicate entry ... for key ...
        // Oracle: unique constraint ... violated
        // SQL Server: Violation of PRIMARY KEY constraint
        return true;
    }
    if ("23001".equals(sqlState)) {
        // H2: Unique index or primary key violation
        return true;
    }
    if ("23505".equals(sqlState)) {
        // PostgreSQL: duplicate key value violates unique constraint
        return true;
    }
    if ("S0005".equals(sqlState)) {
        // SQL Server: Snapshot isolation transaction aborted due to update
        // conflict
        return true;
    }
    return false;
}

From source file:org.wso2.mobile.utils.persistence.EMMDBInitializer.java

/**
 * executes given sql/*from  ww  w. j a  v a  2s .co  m*/
 *
 * @param sql
 * @throws Exception
 */
private void executeSQL(String sql) throws Exception {
    // Check and ignore empty statements
    if ("".equals(sql.trim())) {
        return;
    }

    ResultSet resultSet = null;
    try {
        if (log.isDebugEnabled()) {
            log.debug("SQL : " + sql);
        }

        boolean ret;
        int updateCount, updateCountTotal = 0;
        ret = statement.execute(sql);
        updateCount = statement.getUpdateCount();
        resultSet = statement.getResultSet();
        do {
            if (!ret) {
                if (updateCount != -1) {
                    updateCountTotal += updateCount;
                }
            }
            ret = statement.getMoreResults();
            if (ret) {
                updateCount = statement.getUpdateCount();
                resultSet = statement.getResultSet();
            }
        } while (ret);

        if (log.isDebugEnabled()) {
            log.debug(sql + " : " + updateCountTotal + " rows affected");
        }
        Connection conn = dataSource.getConnection();
        SQLWarning warning = conn.getWarnings();
        while (warning != null) {
            log.debug(warning + " sql warning");
            warning = warning.getNextWarning();
        }
        conn.clearWarnings();
    } catch (SQLException e) {
        if (e.getSQLState().equals("X0Y32") || e.getSQLState().equals("42710")) {
            // eliminating the table already exception for the derby and DB2 database types
            if (log.isDebugEnabled()) {
                log.info("Table Already Exists. Hence, skipping table creation");
            }
        } else {
            throw new Exception("Error occurred while executing : " + sql, e);
        }
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                log.error("Error occurred while closing result set.", e);
            }
        }
    }
}

From source file:org.wso2.carbon.social.sql.SocialDBInitilizer.java

/**
 * executes given sql//from  w ww.j  a  v  a2 s  . c o  m
 *
 * @param sql
 * @throws Exception
 */
private void executeSQL(String sql) throws Exception {
    // Check and ignore empty statements
    if ("".equals(sql.trim())) {
        return;
    }

    ResultSet resultSet = null;
    try {
        if (log.isDebugEnabled()) {
            log.debug("SQL : " + sql);
        }

        boolean ret;
        int updateCount, updateCountTotal = 0;
        ret = statement.execute(sql);
        updateCount = statement.getUpdateCount();
        resultSet = statement.getResultSet();
        do {
            if (!ret) {
                if (updateCount != -1) {
                    updateCountTotal += updateCount;
                }
            }
            ret = statement.getMoreResults();
            if (ret) {
                updateCount = statement.getUpdateCount();
                resultSet = statement.getResultSet();
            }
        } while (ret);

        if (log.isDebugEnabled()) {
            log.debug(sql + " : " + updateCountTotal + " rows affected");
        }
        Connection conn = dataSource.getConnection();
        SQLWarning warning = conn.getWarnings();
        while (warning != null) {
            log.debug(warning + " sql warning");
            warning = warning.getNextWarning();
        }
        conn.clearWarnings();
    } catch (SQLException e) {
        if (e.getSQLState().equals("X0Y32") || e.getSQLState().equals("42710")) {
            // eliminating the table already exception for the derby and DB2 database types
            //if (log.isDebugEnabled()) {
            log.info("Table Already Exists. Hence, skipping table creation");
            //}
        } else {
            throw new Exception("Error occurred while executing : " + sql, e);
        }
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                log.error("Error occurred while closing result set.", e);
            }
        }
    }
}

From source file:com.yahoo.dba.perf.myperf.springmvc.QueryController.java

@Override
protected ModelAndView handleRequestImpl(HttpServletRequest req, HttpServletResponse resp) throws Exception {
    int status = Constants.STATUS_OK;
    String message = "OK";

    logger.info("receive url " + req.getQueryString());
    QueryParameters qps = null;// www .  j av a  2s.  c om
    DBInstanceInfo dbinfo = null;
    ModelAndView mv = null;
    ResultList rList = null;
    LinkedHashMap<String, ResultList> listMap = null; //if multiple result set

    qps = WebAppUtil.parseRequestParameter(req);
    //validation input
    String validation = qps.validate();
    if (validation == null || validation.isEmpty()) {
        //do we have such query?
        try {
            QueryInputValidator.validateSql(this.frameworkContext.getSqlManager(), qps);
        } catch (Exception ex) {
            validation = ex.getMessage();
        }
    }
    if (validation != null && !validation.isEmpty())
        return this.respondFailure("Input validation: " + validation, req);

    //valid DB?
    dbinfo = this.frameworkContext.getDbInfoManager().findDB(qps.getGroup(), qps.getHost());
    if (dbinfo == null)
        return this.respondFailure("Cannot find database (" + qps.getGroup() + ", " + qps.getHost() + ")", req);

    //create connection if needed
    DBConnectionWrapper connWrapper = null;
    Sql sql = this.frameworkContext.getSqlManager().getSql(qps.getSql());
    if (sql == null || sql.getQueryProcessor() == null
            || !CUSTOMER_PROCESSOR.containsKey(sql.getQueryProcessor())
            || CUSTOMER_PROCESSOR.get(sql.getQueryProcessor()).requireDBConnection()) {
        //connect to db
        try {
            connWrapper = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo);

            if (connWrapper == null)
                return this.respondFailure("Failed to connecto to database (" + dbinfo + ")", req);
        } catch (Throwable th) {
            logger.log(Level.SEVERE, "Exception", th);
            status = Constants.STATUS_BAD;
            return this.respondFailure("Failed to connecto to database (" + dbinfo + "): " + th.getMessage(),
                    req);
        }
    }

    //when we reach here, at least we have valid query and can connect to db if needed   
    WebAppUtil.storeLastDbInfoRequest(qps.getGroup(), qps.getHost(), req);

    try {
        logger.info("execute query " + qps.getSql());
        if (sql != null && sql.getQueryProcessor() != null
                && CUSTOMER_PROCESSOR.containsKey(sql.getQueryProcessor())) //custom processor
        {
            CustomQueryProcessor prc = CUSTOMER_PROCESSOR.get(sql.getQueryProcessor());
            if (prc.isMultiple()) {
                listMap = new LinkedHashMap<String, ResultList>();
                prc.queryMultiple(frameworkContext, dbinfo, findUserFromRequest(req), connWrapper, qps,
                        listMap);
            } else
                rList = prc.querySingle(this.frameworkContext, dbinfo, findUserFromRequest(req), connWrapper,
                        qps);
        } else
            rList = this.frameworkContext.getQueryEngine().executeQueryGeneric(qps, connWrapper,
                    qps.getMaxRows());
        logger.info("Done query " + qps.getSql() + " with " + (rList != null ? rList.getRows().size() : 0)
                + " records.");
        if (connWrapper != null)
            WebAppUtil.closeDBConnection(req, connWrapper, false);
    } catch (Throwable ex) {
        logger.log(Level.SEVERE, "Exception", ex);
        if (connWrapper != null) {
            if (ex instanceof SQLException) {
                SQLException sqlEx = SQLException.class.cast(ex);
                String msg = ex.getMessage();
                logger.info(sqlEx.getSQLState() + ", " + sqlEx.getErrorCode() + ", " + msg);
                //check if the connection is still good
                if (!DBUtils.checkConnection(connWrapper.getConnection())) {
                    WebAppUtil.closeDBConnection(req, connWrapper, true);
                } else
                    WebAppUtil.closeDBConnection(req, connWrapper, true);
            } else {
                WebAppUtil.closeDBConnection(req, connWrapper, false);
            }
        }
        status = Constants.STATUS_BAD;
        message = "Exception: " + ex.getMessage();
    }

    if (status != Constants.STATUS_OK)
        return this.respondFailure(message, req);

    if (rList != null && POST_PROCESSOR.containsKey(qps.getSql()))
        rList = POST_PROCESSOR.get(qps.getSql()).process(rList);

    mv = new ModelAndView(this.jsonView);
    if (listMap != null)
        mv.addObject("json_result", ResultListUtil.toMultiListJSONStringUpper(listMap, qps, status, message));
    else
        mv.addObject("json_result",
                ResultListUtil.toJSONString(filterResultList(rList, req), qps, status, message));
    return mv;
}

From source file:com.nabla.wapp.server.database.InsertStatement.java

public int execute(final Connection conn, final T record)
        throws SQLException, ValidationException, InternalErrorException {
    Assert.argumentNotNull(conn);//from   w w w. j a va 2 s.co m

    final List<IStatementParameter> parametersToInsert = new ArrayList<IStatementParameter>();
    final ArgumentList names = new ArgumentList();
    final ArgumentList values = new ArgumentList();
    for (IStatementParameter parameter : parameters) {
        if (!parameter.include(record))
            continue;
        parametersToInsert.add(parameter);
        names.add(parameter.getName());
        values.add("?");
    }
    if (parametersToInsert.isEmpty()) {
        if (log.isErrorEnabled())
            log.error("no values to insert!!!!");
        throw new InternalErrorException(
                Util.formatInternalErrorDescription("no parameter values given for SQL statement"));
    }
    final String sql = MessageFormat.format(sqlTemplate, names.toString(), values.toString());
    if (log.isDebugEnabled())
        log.debug("SQL=" + sql);
    final PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    try {
        int i = 1;
        for (IStatementParameter parameter : parametersToInsert)
            parameter.write(stmt, i++, record);
        if (stmt.executeUpdate() != 1) {
            if (log.isErrorEnabled())
                log.error("failed to add record");
            throw new InternalErrorException(
                    Util.formatInternalErrorDescription("failed to execute SQL statement"));
        }
        final ResultSet rsKey = stmt.getGeneratedKeys();
        try {
            rsKey.next();
            return rsKey.getInt(1);
        } finally {
            Database.close(rsKey);
        }
    } catch (final SQLException e) {
        if (uniqueFieldName != null && SQLState.valueOf(e) == SQLState.INTEGRITY_CONSTRAINT_VIOLATION) {
            if (log.isErrorEnabled())
                log.error("SQL error " + e.getErrorCode() + "-" + e.getSQLState(), e);
            throw new ValidationException(uniqueFieldName, CommonServerErrors.DUPLICATE_ENTRY);
        }
        throw e;
    } finally {
        Database.close(stmt);
    }
}