Java Code Examples for java.sql.SQLException#getSQLState()

/**
 * Returns {@link ProbeResult#SUPPORTED} if the given storage appears to be supported by {@link SQLStore}.
 * Returning {@code SUPPORTED} from this method does not guarantee that reading or writing will succeed,
 * only that there appears to be a reasonable chance of success based on a brief inspection of the connection.
 *
 * @param  connector  information about the storage (data source).
 * @return {@code SUPPORTED} if the given storage seems to be usable by {@code SQLStore} instances.
 * @throws DataStoreException if an I/O error occurred.
 */
@Override
public ProbeResult probeContent(final StorageConnector connector) throws DataStoreException {
    final DataSource ds = connector.getStorageAs(DataSource.class);
    if (ds != null) {
        try (Connection c = ds.getConnection()) {
            return ProbeResult.SUPPORTED;
        } catch (SQLException e) {
            final String state = e.getSQLState();
            if (!"08001".equals(state) || !"3D000".equals(state)) {
                throw new DataStoreException(e);
            }
        }
    }
    return ProbeResult.UNSUPPORTED_STORAGE;
}
 

public String sqlExecutionException(Throwable th, String postErrorString)
{
     String msg = "Error: ";

     if(th instanceof SQLException)
     {
        SQLException sqlEx = (SQLException) th;
        sqlEx.getSQLState();
        sqlEx.getErrorCode();

        msg += sqlEx + ", SQL State: " + sqlEx.getSQLState() + ", Error Code: " + sqlEx.getErrorCode();
     }
     else
     {
        msg += th;
     }

     if(null != postErrorString)
     {
        msg += "\n" + postErrorString;
     }

     _session.showErrorMessage(msg);
     return msg;
  }
 

/**
 * Ensure that after hard upgrade (with the old version)
 * we can no longer connect to the database.
 */
public void noConnectionAfterHardUpgrade()
{              
    switch (getPhase())
    {
    case PH_POST_HARD_UPGRADE:
        try {
                getConnection();
            } catch (SQLException e) {
                // Check the innermost of the nested exceptions
                SQLException sqle = getLastSQLException(e);
                String sqlState = sqle.getSQLState();
            	// while beta, XSLAP is expected, if not beta, XSLAN
            	if (!(sqlState.equals("XSLAP")) && !(sqlState.equals("XSLAN")))
            		fail("expected an error indicating no connection");
            }
        break;
    }
}
 

protected boolean isRetriableWithoutRecursion(SQLException sqlException)
{
    String state = sqlException.getSQLState();
    int code = sqlException.getErrorCode();
    boolean retriable = (code == CODE_DEADLOCK || code == CODE_SCHEMA_CHANGE || STATE_CONNECTION_CLOSED.equals(state));

    if (!retriable && STATE_BATCH_ERROR.equals(state))
    {
        retriable = sqlException.getMessage().indexOf("encountered a deadlock situation. Please re-run your command.") >= 0;
    }
    if (!retriable)
    {
        retriable = JTDS_IO_ERROR.equals(state) && sqlException.getMessage().contains("DB server closed connection");
    }
    return retriable;
}
 

private Object executePreparedStatement(PreparedStatement pstmt)
    throws SQLException {
  try {
    logger.info("Executing query " + pstmt.toString());
    ResultSet result = pstmt.executeQuery();
    // even if this result set is empty (i.e. no row found), just return
    // the empty result set
    logger.info("Query succeeded");
    recyclePooledStatement(pstmt);
    return result;
  } catch (SQLException e) {
    // throw away the pooled statement, just in case it was the problem
    releasePooledStatement(pstmt);
    logGetRowError(e);
    throw new SQLException("Error executing query from archive database", e
        .getSQLState(), VENDOR_CODE_ARCHIVE_ERROR, e);
  }
}
 

/**
 * Shutdown the datbase
 * @param  dbName  Name of the database to shutdown.
 */
void shutdown(String dbName) {

    try{
        //shutdown
        TestUtil.getConnection(dbName, "shutdown=true");
    }catch(SQLException se){
        if (se.getSQLState() != null && se.getSQLState().equals("08006"))
            System.out.println("database shutdown properly");
        else
            dumpSQLException(se);
    }
}
 

/**
 * Constructor for UncategorizedSQLException.
 * @param task name of current task
 * @param sql the offending SQL statement
 * @param ex the root cause
 */
public UncategorizedSQLException(String task, @Nullable String sql, SQLException ex) {
	super(task + "; uncategorized SQLException" + (sql != null ? " for SQL [" + sql + "]" : "") +
			"; SQL state [" + ex.getSQLState() + "]; error code [" + ex.getErrorCode() + "]; " +
			ex.getMessage(), ex);
	this.sql = sql;
}
 

@Override
public boolean isUniqueConstraintError(SQLException e) {
    if (isConstraintError(e)) {
        String sqlstate = e.getSQLState();
        int errorCode = e.getErrorCode();
        return DUPLICATE_ENTRY == errorCode
            || SQLSTATE_UNIQUE_CONSTRAINT_VIOLATION.equals(sqlstate);
    }
    return false;
}
 

private String generateForeignKeyScript( String owner, String foreignKey, Connection connection ) {

        String query = "select DBMS_METADATA.GET_DDL('REF_CONSTRAINT','" + foreignKey + "','" + owner
                       + "') from dual";

        PreparedStatement stmnt = null;
        ResultSet rs = null;
        String createTableScript = new String();
        try {
            if (log.isTraceEnabled()) {
                log.trace("Executing SQL query: " + query);
            }
            stmnt = connection.prepareStatement(query);
            rs = stmnt.executeQuery();
            if (rs.next()) {
                createTableScript = rs.getString(1);
            }

            return createTableScript;
        } catch (SQLException e) {
            throw new DbException(
                                  "SQL errorCode=" + e.getErrorCode() + " sqlState=" + e.getSQLState() + " "
                                  + e.getMessage(), e);
        } finally {
            DbUtils.closeStatement(stmnt);
        }
    }
 

/**
 * Shutdown the datbase
 * @param  dbName  Name of the database to shutdown.
 */
private void shutdown(String dbName) {

	try{
		//shutdown
		TestUtil.getConnection(dbName, "shutdown=true");
	}catch(SQLException se){
		if (se.getSQLState() != null && se.getSQLState().equals("08006"))
			System.out.println("database shutdown properly");
		else
			dumpSQLException(se);
	}
}
 

static private boolean isNullSQLStringException(SQLException se)
{
	if ((se.getMessage() != null &&
		 se.getMessage().indexOf("Null SQL string passed.") >= 0)
		|| (se.getSQLState() != null &&
			(se.getSQLState().equals("XJ067"))))
		return true;
	return false;
}
 

static private boolean isInvalidMethodReturnException(SQLException se)
{
	if (((se.getMessage() != null &&
		  se.getMessage().indexOf("executeQuery method cannot be used for update.") >= 0)
		 ||  se.getMessage().indexOf("executeUpdate method cannot be used for query.") >= 0)
		|| (se.getSQLState() != null &&
			(se.getSQLState().equals("X0Y78")
			 || se.getSQLState().equals("X0Y79"))))
		return true;
	return false;
}
 

private ErrorType errorType(SQLException e) {
    String state = e.getSQLState();
    if (state == null) return null;
    // in batchExecute, driver throws BatchUpdateException and may not have SQLIntegrityConstraintViolationException as cause, so here use sql state as contract
    if (state.startsWith("23")) return ErrorType.INTEGRITY_CONSTRAINT_VIOLATION;
    if (state.startsWith("08")) return ErrorType.CONNECTION_ERROR;
    return null;
}
 

public void commitGfxd (){
  try{
    Log.getLogWriter().info(" Gfxd - Commit  started " );
    gConn.commit();
    Log.getLogWriter().info(" Gfxd - Commit  Completed " );
  } catch (SQLException se) {
    throw new TestException(" Error while commiting the Gfxd database " + " sqlState : " + se.getSQLState()  + " error message : " + se.getMessage()  + TestHelper.getStackTrace(se));
  }
}
 

private static void verifyExecute(Connection c,
								  String sText,
								  int paramCount,
								  int[] args,
								  boolean shouldBeReadOnly,
								  int expectRowCount)
	 throws Exception
{

	PreparedStatement ps = null;
	try {
		ps = c.prepareStatement(sText);
		for (int ix=0;ix<paramCount; ix++)
			ps.setInt(ix+1,args[ix]);
		int rc = ps.executeUpdate();
		if (shouldBeReadOnly)
			throw new Exception("operation incorrectly allowed for read only connection "+sText);
		if (rc != expectRowCount)
		{
			StringBuilder argSb = new StringBuilder();
			for (int ix=0;ix<paramCount;ix++)
			{
				if (ix!=0) argSb.append(",");
				argSb.append(args[ix]);
			}
			throw new Exception("Incorrect row count "+rc+
								" for "+sText+
								" with args "+argSb);
			
		}
	}

	catch (SQLException sqle) {
		String sqlState = sqle.getSQLState();
		boolean authorizeError = sqlState.equals("25502") ||
								 sqlState.equals("25503") ||
								 sqlState.equals("25505");
		if (!(shouldBeReadOnly && authorizeError))
			throw new Exception("Unexpected exception for "+sText+
								" ("+sqle+")");
	}

	finally {
		if (ps != null)
			ps.close();
	}
}
 

/**
 * Executes SQL to obtain a result set.
 *
 * @param sql       the SQL statement to execute
 * @param spName    optional stored procedure name
 * @param params    optional parameters
 * @param useCursor whether a cursor should be created for the SQL
 * @return the result set generated by the query
 */
protected ResultSet executeSQLQuery(String sql,
                                    String spName,
                                    ParamInfo[] params,
                                    boolean useCursor)
        throws SQLException {
    String warningMessage = null;

    //
    // Try to open a cursor result set if required
    //
    if (useCursor) {
        try {
            if (connection.getServerType() == Driver.SQLSERVER) {
                currentResult =
                        new MSCursorResultSet(this,
                                sql,
                                spName,
                                params,
                                resultSetType,
                                resultSetConcurrency);

                return currentResult;
            } else {
                // Use client side cursor for Sybase
                currentResult =
                    new CachedResultSet(this,
                            sql,
                            spName,
                            params,
                            resultSetType,
                            resultSetConcurrency);

                return currentResult;
            }
        } catch (SQLException e) {
            checkCursorException(e);
            warningMessage = '[' + e.getSQLState() + "] " + e.getMessage();
        }
    }

    //
    // Could not open a cursor (or was not requested) so try a direct select
    //
    if (spName != null
            && connection.getUseMetadataCache()
            && connection.getPrepareSql() == TdsCore.PREPARE
            && colMetaData != null
            && connection.getServerType() == Driver.SQLSERVER) {
        // There is cached meta data available for this
        // prepared statement
        tds.setColumns(colMetaData);
        tds.executeSQL(sql, spName, params, true, queryTimeout, maxRows,
                maxFieldSize, true);
    } else {
        tds.executeSQL(sql, spName, params, false, queryTimeout, maxRows,
                maxFieldSize, true);
    }

    // Update warning chain if cursor was downgraded before processing results
    if (warningMessage != null) {
        addWarning(new SQLWarning(
                Messages.get("warning.cursordowngraded", warningMessage), "01000"));
    }

    // Ignore update counts preceding the result set. All drivers seem to
    // do this.
    while (!tds.getMoreResults() && !tds.isEndOfResponse());

    // check for server side errors
    messages.checkErrors();

    if (tds.isResultSet()) {
        currentResult = new JtdsResultSet(this,
                                          ResultSet.TYPE_FORWARD_ONLY,
                                          ResultSet.CONCUR_READ_ONLY,
                                          tds.getColumns());
    } else {
        throw new SQLException(
                Messages.get("error.statement.noresult"), "24000");
    }

    return currentResult;
}
 

public void testBasicDeleteOnPartitionedTable_Bug42863() throws Exception {
  Connection conn = getConnection();
  Statement s = conn.createStatement();
  s.execute("create table TESTTABLE (ID int primary key , "
      + "DESCRIPTION varchar(1024) , ADDRESS varchar(1024))");
  s.executeUpdate("insert into TESTTABLE values (1, 'First', 'J 604')");
  String query = "Delete from TESTTABLE  where ID = 1 ";
  GemFireXDQueryObserver old = null;
  final boolean[] foundDeleteActivation = new boolean[] { false };
  try {
    old = GemFireXDQueryObserverHolder
        .setInstance(new GemFireXDQueryObserverAdapter() {
          @Override
          public void queryInfoObjectFromOptmizedParsedTree(QueryInfo qInfo,
              GenericPreparedStatement gps, LanguageConnectionContext lcc) {
            if (qInfo instanceof DeleteQueryInfo) {
              callbackInvoked = true;
            }

          }

          @Override
          public void afterGemFireActivationCreate(
              AbstractGemFireActivation ac) {
            foundDeleteActivation[0] = ac instanceof GemFireDeleteActivation;
          }

        });

    // Now insert some data
    this.callbackInvoked = false;

    Statement s2 = conn.createStatement();
    try {
      assertEquals(1, s2.executeUpdate(query));
      assertTrue(foundDeleteActivation[0]);
      ResultSet rs = s2.executeQuery("Select * from TESTTABLE where ID = 1");
      assertFalse(rs.next());

    } catch (SQLException e) {
      e.printStackTrace();
      throw new SQLException(e.toString()
          + " Exception in executing query = " + query, e.getSQLState());
    }
    assertTrue(this.callbackInvoked);
  } finally {
    if (old != null) {
      GemFireXDQueryObserverHolder.setInstance(old);
    }
  }
}
 

public static void sqlControl4(int sqlc, String[] e1, String[] e2,
        String[] e3, String[] e4, String[] e5, String[] e6, String[] e7,
        String[] e8) throws SQLException {

    Connection conn = DriverManager
            .getConnection("jdbc:default:connection");

    String sql = "CALL SQLC.SQLCONTROL2_" + sqlc
            + " (?, ?, ?, ?, ?, ?, ?) ";

    e1[0] = sql;

    CallableStatement cs1 = conn.prepareCall(sql);
    try {
        for (int rop = 1; rop <= 7; rop++) {
            cs1.registerOutParameter(rop, Types.VARCHAR);
        }
        cs1.execute();

        e2[0] = cs1.getString(1);
        e3[0] = cs1.getString(2);
        e4[0] = cs1.getString(3);
        e5[0] = cs1.getString(4);
        e6[0] = cs1.getString(5);
        e7[0] = cs1.getString(6);
        e8[0] = cs1.getString(7);
    } catch (SQLException sqle) {
        StringBuilder sb = new StringBuilder(128);
        sb.append("STATE");
        do {
            sb.append("-");
            String ss = sqle.getSQLState();
            if (ss == null)
                ss = "?????";
            sb.append(ss);
            sqle = sqle.getNextException();
        } while (sqle != null);
        e2[0] = sb.toString();
    }

    cs1.close();

    conn.close();

}
 

/**
 * Tests if the where clause containing multiple fields with no primary key
 * defined behaves correctly ,using Statement
 * 
 */
public void testUpdateHavingParameterizedExpression_Bug39646_1() throws Exception {
  Connection conn = getConnection();
  Statement s = conn.createStatement();
  s.execute("create table TESTTABLE (ID int primary key , "
      + "DESCRIPTION varchar(1024) , ADDRESS varchar(1024), type int)");
  String query = "Update TESTTABLE set type = type + ? where ID >= ? AND ID < ?";
  GemFireXDQueryObserver old = null;
  try {
    old = GemFireXDQueryObserverHolder
        .setInstance(new GemFireXDQueryObserverAdapter() {
          @Override
          public void queryInfoObjectFromOptmizedParsedTree(QueryInfo qInfo, GenericPreparedStatement gps, LanguageConnectionContext lcc) {
            if (qInfo instanceof UpdateQueryInfo) {              
              callbackInvoked = true;
              QueryInfoContext qic = ((UpdateQueryInfo)qInfo).qic;
              assertEquals(3,qic.getParamerCount());
            }

          }

        });      
    // Now insert some data
    this.callbackInvoked = false;
    s.executeUpdate("insert into TESTTABLE values (1, 'First', 'J 604',1)");
    PreparedStatement ps = conn.prepareStatement(query);
    ps.setInt(1, 1);
    ps.setInt(2, 1);
    ps.setInt(3, 2); 
    try {
      int n = ps.executeUpdate();
      assertEquals(n,1);
    }
    catch (SQLException e) {
      e.printStackTrace();
      throw new SQLException(e.toString()
          + " Exception in executing query = " + query, e.getSQLState());
    }
    assertTrue(this.callbackInvoked);
  }
  finally {
    if (old != null) {
      GemFireXDQueryObserverHolder.setInstance(old);
    }
  }
}
 

public static void handleMissedSQLException(List<SQLException> exceptionList) {
  //add the check to see if query cancellation exception or low memory exception thrown
  if (setCriticalHeap) {      
    boolean[] getCanceled = (boolean[]) SQLTest.getCanceled.get();
    if (getCanceled == null) {
      SQLTest.getCanceled.set(new boolean[1]);
    } else if (getCanceled[0] == true) {
      Log.getLogWriter().info("memory runs low -- avoiding the comparison of expected exceptions");
      return;  //do not check exception list if gfxd gets such exception
    }
  }
  
  if (SQLTest.setTx && isHATest) {
    boolean[] getNodeFailure = (boolean[]) SQLTest.getNodeFailure.get();
    if (getNodeFailure != null && getNodeFailure[0]) {
      Log.getLogWriter().info("getNodeFailure -- avoiding the comparison of expected exceptions");
      return; //do not check exception list      
    }
  }
  
  if (SQLTest.setTx && SQLTest.testEviction && SQLTest.workaround51582) {
    boolean[] getEvictionConflict = (boolean[]) SQLTest.getEvictionConflict.get();
    if (getEvictionConflict != null && getEvictionConflict[0]) {
      Log.getLogWriter().info("get conflict excpetion with eviction -- avoiding the comparison of expected exceptions");
      return; //do not check exception list      
    }
  }
      
  if (exceptionList.size() > 0) {
    int firstIndex = 0;
    SQLException derbySe = exceptionList.get(firstIndex);

    Log.getLogWriter().info("Here are additional derby exceptions that are missed by GFE");
    for (int i = 0; i < exceptionList.size(); i++) {
      printSQLException(exceptionList.get(i));
    }
    //Throwable sme = ResultSetHelper.sendResultMessage();
    //Throwable sbme = ResultSetHelper.sendBucketDumpMessage();
    throw new TestException("GemFireXD missed the expected SQL exceptions thrown by derby: "
        + "the expected deby SQLException: State: " + derbySe.getSQLState() + "\nseverity: " + derbySe.getErrorCode()
        + "\nmessage: " + derbySe.getMessage() + TestHelper.getStackTrace(exceptionList.get(0)) 
        /*+ getStack(sme) + getStack(sbme)*/);
  }
}