Java Examples for org.postgresql.util.PSQLException

public static String getMessage(final Throwable t) {
    // grab the localized message for the source Throwable. If there or no other causes in the
    // stack, use the top-most message.
    String message = t.getLocalizedMessage();
    // flag indicating if we've uncovered a cause that is an instance of
    // org.postgresql.util.PSQLException.
    boolean psqlCauseFound = false;
    // start digging
    Throwable cause = t;
    while (cause != null) {
        // Get the type of the Throwable
        Class clazz = cause.getClass();
        // If we have an UnknownHostException, we're done
        if (UnknownHostException.class.isAssignableFrom(clazz)) {
            return "UnknownHostException: " + cause.getLocalizedMessage();
        }
        // if we haven't already found a PSQLException, see if this cause is one.
        if (!psqlCauseFound && PSQLException.class.isAssignableFrom(clazz)) {
            // it's a PSQLException, and it's the first one found
            psqlCauseFound = true;
            message = cause.getLocalizedMessage();
        }
        // get the next cause
        cause = cause.getCause();
    }
    return message;
}

protected int getMaxIndexKeys() throws SQLException {
    if (INDEX_MAX_KEYS == 0) {
        String sql;
        sql = "SELECT setting FROM pg_catalog.pg_settings WHERE name='max_index_keys'";
        Statement stmt = connection.createStatement();
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
            if (!rs.next()) {
                stmt.close();
                throw new PSQLException(GT.tr("Unable to determine a value for MaxIndexKeys due to missing system catalog data."), PSQLState.UNEXPECTED_ERROR);
            }
            INDEX_MAX_KEYS = rs.getInt(1);
        } finally {
            JdbcBlackHole.close(rs);
            JdbcBlackHole.close(stmt);
        }
    }
    return INDEX_MAX_KEYS;
}

private void createDb(Connection conn, String clusterName, String service) {
    try {
        Statement statement = conn.createStatement();
        statement.executeUpdate("CREATE DATABASE " + clusterName + service);
    } catch (PSQLException ex) {
        if (ex.getSQLState().equals("42P04")) {
            LOGGER.warn("The expected database already exist");
        } else {
            throw new BadRequestException("Failed to create database in RDS: " + ex.getMessage(), ex);
        }
    } catch (SQLException e) {
        throw new BadRequestException("Failed to connect to RDS: " + e.getMessage(), e);
    }
}

public Object retryOnFailure(ProceedingJoinPoint jp) throws Throwable {
    Object result = null;
    int retryCount = 0;
    do {
        try {
            Object[] args = jp.getArgs();
            if (args != null && args.length > 0)
                result = jp.proceed(jp.getArgs());
            else
                result = jp.proceed();
            break;
        } catch (DataAccessResourceFailureException ex) {
            if (ex.getCause() instanceof PSQLException) {
                PSQLException psqlEx = (PSQLException) ex.getCause();
                String sqlState = psqlEx.getSQLState();
                LOG.warn("Got exception with sqlState=" + sqlState + ": " + ex);
                if (sqlStatesToRetry.contains(sqlState) && retryCount < maxRetriesOnConnDrop) {
                    LOG.warn("Retrying...(retryCount=" + retryCount + ")");
                    sleep(retryWaitInMillis);
                } else {
                    throw ex;
                }
            }
        }
        retryCount++;
    } while (retryCount <= maxRetriesOnConnDrop);
    return result;
}

public static void insertTweet(Date date, String word, String geoLoc, String tweet) throws SQLException {
    PreparedStatement pst = null;
    String stm = Constants.TWEET_QUERY;
    pst = con.prepareStatement(stm);
    pst.setTimestamp(1, new Timestamp(date.getTime()));
    pst.setString(2, geoLoc);
    pst.setString(3, word);
    pst.setString(4, tweet);
    try {
        pst.executeUpdate();
    } catch (PSQLException e) {
        System.out.println("-------");
        System.out.println("Could not include current tweet. Msg: " + e.getMessage());
        System.out.println("Tweet content:");
        System.out.println("Tweet: " + tweet);
        System.out.println("Geo: " + geoLoc);
    }
}

@Test
public void testInsert() throws Exception {
    log.debug("insert sql for postgresql from manager: {}", manager.getQuery("test_table.insert"));
    DBI dbi = new DBI(TagJdbi.getDataSource());
    // driverName like "mysql" or "postgresql";  DBI makes such definitions available to every StatementContext... so we can get this in the mapper!
    dbi.define("driver", driverName);
    Handle h = dbi.open();
    h.registerArgumentFactory(new UUIDArgument());
    //        h.execute(manager.getQuery("mw_tag_selection.create")); // works but table must not exist or you'll get error.  TODO check if table exists before executing.
    //h.execute("insert into something (id, name) values (?, ?)", 1, "Brian");
    Update query = h.createStatement(manager.getQuery("test_table.insert"));
    // postgresql:
    //        query.bind("id", new UUID()); // org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: Exception while binding 'id' [statement:"insert into test_table (id, name, length, created, content, flag) values (:id, :name, :length, :created, :content, :flag)", located:"insert into test_table (id, name, length, created, content, flag) values (:id, :name, :length, :created, :content, :flag)", rewritten:"insert into test_table (id, name, length, created, content, flag) values (?, ?, ?, ?, ?, ?)", arguments:{ positional:{}, named:{content:[0, 1, 2, 3],id:63efbf44-fd76-4816-afec-3147e203b7e6,flag:true,created:Thu Mar 13 11:50:21 PDT 2014,name:'test',length:0}, finder:[]}] // Caused by: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of com.intel.dcsg.cpg.io.UUID. Use setObject() with an explicit Types value to specify the type to use.
    //        query.bind("id", new UUID().toByteArray().getBytes()); // org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: column "id" is of type uuid but expression is of type bytea
    //        query.bind("id", new UUID().toString()); // org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: ERROR: column "id" is of type uuid but expression is of type character varying
    //        query.bind("id", new UUID().uuidValue()); // works on postgresql with uuid field
    // mysql:
    //        query.bind("id", new UUID()); // org.skife.jdbi.v2.exceptions.UnableToCreateStatementException: Exception while binding 'id' [statement:"insert into test_table (id, name, length, created, content, flag) values (:id, :name, :length, :created, :content, :flag)", located:"insert into test_table (id, name, length, created, content, flag) values (:id, :name, :length, :created, :content, :flag)", rewritten:"insert into test_table (id, name, length, created, content, flag) values (?, ?, ?, ?, ?, ?)", arguments:{ positional:{}, named:{content:[0, 1, 2, 3],id:dc008d43-125d-494c-8033-58f0dfb97d43,flag:true,created:Thu Mar 13 12:11:38 PDT 2014,name:'test',length:0}, finder:[]}] // Caused by: java.io.NotSerializableException: com.intel.dcsg.cpg.io.UUID
    //        query.bind("id", new UUID().toByteArray().getBytes()); // works on mysql  with binary(16) field
    query.bind("id", new UUID());
    // both:
    query.bind("name", "test5");
    query.bind("content", new byte[] { 0, 1, 2, 3 });
    query.bind("length", 0);
    query.bind("flag", true);
    query.bind("created", new Date());
    query.execute();
    h.close();
}

@Test
public void testEmptyStatement() throws Exception {
    try (Connection conn = DriverManager.getConnection(JDBC_CRATE_URL, properties)) {
        assertThat(conn.createStatement().execute(""), is(false));
        try {
            conn.createStatement().executeQuery("");
            fail("executeQuery with empty query should throw a 'No results were returned by the query' error");
        } catch (PSQLException e) {
            assertThat(e.getSQLState(), is(PSQLState.NO_DATA.getState()));
        }
    }
}

/**
     * 例外を解析しエラーコードとエラーメッセージを設定する。
     *
     * @param e Exception
     */
protected void processError(Exception e) {
    java.util.logging.Logger.getLogger(this.getClass().getName()).warning(e.getMessage());
    StringBuilder sb = new StringBuilder();
    if (e instanceof org.postgresql.util.PSQLException) {
        setErrorCode(TT_CONNECTION_ERROR);
        String fmt = ClientContext.getMyBundle(DaoBean.class).getString("messageFormat.cannnotConnect");
        String errMsg = new MessageFormat(fmt).format(new String[] { appenExceptionInfo(e) });
        setErrorMessage(errMsg);
    } else if (e instanceof java.sql.SQLException) {
        setErrorCode(TT_DATABASE_ERROR);
        String fmt = ClientContext.getMyBundle(DaoBean.class).getString("messageFormat.dbAccessError");
        String errMsg = new MessageFormat(fmt).format(new String[] { appenExceptionInfo(e) });
        setErrorMessage(errMsg);
    } else {
        setErrorCode(TT_UNKNOWN_ERROR);
        String fmt = ClientContext.getMyBundle(DaoBean.class).getString("messageFormat.appError");
        String errMsg = new MessageFormat(fmt).format(new String[] { appenExceptionInfo(e) });
        setErrorMessage(errMsg);
    }
}

@Override
protected GenericApplicationContext createApplicationContext(String[] locations) {
    try {
        ServletContext ctx = createServletContext();
        GenericWebApplicationContext context = new GenericWebApplicationContext(new UnoverridingBeanFactory());
        context.setServletContext(ctx);
        prepareApplicationContext(context);
        customizeBeanFactory(context.getDefaultListableBeanFactory());
        createBeanDefinitionReader(context).loadBeanDefinitions(locations);
        context.refresh();
        return context;
    } catch (Exception orig) {
        for (Throwable loop = orig; loop != null; loop = loop.getCause()) {
            if (loop instanceof SQLException) {
                LOGGER.warning("Found a SQLException. Unrolling stacktrace.");
                for (SQLException sqle = (SQLException) loop; sqle != null; sqle = sqle.getNextException()) {
                    if (sqle instanceof PSQLException) {
                        PSQLException psqle = (PSQLException) sqle;
                        LOGGER.warning("Server msg: " + psqle.getServerErrorMessage());
                        if (psqle.getServerErrorMessage() != null) {
                            LOGGER.warning("Server iqry: " + psqle.getServerErrorMessage().getInternalQuery());
                            LOGGER.warning("Server hint: " + psqle.getServerErrorMessage().getHint());
                        }
                    }
                    StringWriter sw = new StringWriter();
                    PrintWriter pw = new PrintWriter(sw, true);
                    sqle.printStackTrace(pw);
                    pw.close();
                    sw.flush();
                    LOGGER.warning("Stacktrace --> " + sw.getBuffer().toString());
                }
                break;
            }
        }
        throw new RuntimeException("Rethrowing " + orig.getMessage(), orig);
    }
}

@Test
public void testGeneratePubIdInCreateProteinList() {
    UserProteinList proteinList = createUserProteinList("awesome", Sets.newHashSet("NX_P123"));
    Mockito.when(dao.createUserProteinList(proteinList)).thenThrow(new DuplicateKeyException("PreparedStatementCallback; SQL [INSERT INTO np_users.user_protein_lists (list_name, description, owner_id, public_id)\n" + "VALUES (?, ?, ?, ?);]; ERROR: duplicate key value violates unique constraint \"user_protein_lists_pubid_udx\"\n" + "  Detail: Key (public_id)=(00000001) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint \"user_protein_lists_pubid_udx\"\n" + "  Detail: Key (public_id)=(00000001) already exists.")).thenThrow(new DuplicateKeyException("PreparedStatementCallback; SQL [INSERT INTO np_users.user_protein_lists (list_name, description, owner_id, public_id)\n" + "VALUES (?, ?, ?, ?);]; ERROR: duplicate key value violates unique constraint \"user_protein_lists_pubid_udx\"\n" + "  Detail: Key (public_id)=(00000001) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint \"user_protein_lists_pubid_udx\"\n" + "  Detail: Key (public_id)=(00000001) already exists.")).thenReturn(1L);
    proteinListService.createUserProteinList(proteinList);
    Mockito.verify(stringGenerator, times(3)).generateString();
}

@Override
public Set<DiscoveredResourceDetails> discoverResources(ResourceDiscoveryContext context) {
    Set<DiscoveredResourceDetails> servers = new LinkedHashSet<DiscoveredResourceDetails>();
    // Process any auto-discovered resources.
    @SuppressWarnings("unchecked") List<ProcessScanResult> autoDiscoveryResults = context.getAutoDiscoveredProcesses();
    for (ProcessScanResult result : autoDiscoveryResults) {
        LOG.info("Discovered a postgres process: " + result);
        ProcessInfo procInfo = result.getProcessInfo();
        String pgDataPath = getDataDirPath(procInfo);
        if (pgDataPath == null) {
            LOG.error("Unable to obtain data directory for postgres process with pid " + procInfo.getPid() + " (tried checking both -D command line argument, as well as " + PGDATA_ENV_VAR + " environment variable).");
            continue;
        }
        File pgData = new File(pgDataPath);
        String configFilePath = getConfigFilePath(procInfo);
        PostgresqlConfFile confFile = null;
        if (!pgData.exists()) {
            if (LOG.isDebugEnabled()) {
                LOG.debug("PostgreSQL data directory (" + pgData + ") does not exist or is not readable. " + "Make sure the user the RHQ Agent is running as has read permissions on the directory and its parent directory.");
            }
        } else {
            if (LOG.isDebugEnabled()) {
                LOG.debug("PostgreSQL data directory: " + pgData);
            }
            File postgresConfFile = (configFilePath != null) ? new File(configFilePath) : new File(pgData, PostgresServerComponent.DEFAULT_CONFIG_FILE_NAME);
            if (LOG.isDebugEnabled()) {
                LOG.debug("PostgreSQL configuration file: " + postgresConfFile);
            }
            if (!postgresConfFile.exists()) {
                LOG.warn("PostgreSQL configuration file (" + postgresConfFile + ") does not exist.");
            } else {
                try {
                    confFile = new PostgresqlConfFile(postgresConfFile);
                } catch (IOException e) {
                    LOG.warn("Could not load PostgreSQL configuration file [" + postgresConfFile + "]: " + e);
                }
            }
        }
        Configuration pluginConfig = context.getDefaultPluginConfiguration();
        pluginConfig.put(new PropertySimple(PGDATA_DIR_CONFIGURATION_PROPERTY, pgData));
        pluginConfig.put(new PropertySimple(CONFIG_FILE_CONFIGURATION_PROPERTY, configFilePath));
        if (confFile != null) {
            String port = confFile.getPort();
            if (port != null) {
                // Override the default (5432) from the descriptor.
                pluginConfig.put(new PropertySimple(PORT_CONFIGURATION_PROPERTY, port));
            }
            List<String> listenAddresses = confFile.getPropertyList("listen_addresses");
            if (listenAddresses.size() > 0) {
                String listenAddress = listenAddresses.get(0).trim();
                if ("*".equals(listenAddress)) {
                    listenAddress = "127.0.0.1";
                }
                pluginConfig.put(new PropertySimple(HOST_CONFIGURATION_PROPERTY, listenAddress));
            }
        }
        DiscoveredResourceDetails resourceDetails = createResourceDetails(context, pluginConfig, procInfo, false);
        servers.add(resourceDetails);
    }
    return servers;
/* TODO GH: Deal with the different error types and inventory except in case of connection refused
         * Bad password org.postgresql.util.PSQLException: FATAL: password authentication failed for user "jon" Wrong
         * port org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and
         * that the postmaster is accepting TCP/IP connections. Wrong db org.postgresql.util.PSQLException: FATAL:
         * database "jon2" does not exist
         */
}

public static void handleSQLException(RollbackException re, String errorContext, Logger logger) {
    // traverse the cause to find a possible constraint violation
    Throwable t = re.getCause();
    while (null != t) {
        if (t instanceof PSQLException) {
            final String sqlState = ((SQLException) t).getSQLState();
            logger.error(errorContext + t.getMessage(), t);
            //check error belongs to integrity constraint class
            if (sqlState.startsWith("23")) {
                throw new ApplicationJsonException(re, errorContext + t.getMessage(), Response.Status.CONFLICT.getStatusCode());
            } else {
                throw new ApplicationJsonException(re, errorContext + t.getMessage(), Response.Status.BAD_REQUEST.getStatusCode());
            }
        }
        if (t instanceof SQLIntegrityConstraintViolationException) {
            logger.error(errorContext + t.getMessage(), t);
            throw new ApplicationJsonException(re, errorContext + t.getMessage(), Response.Status.CONFLICT.getStatusCode());
        }
        // IntegrityException, or DescriptorExcettion, etc)
        if (t instanceof ValidationException) {
            logger.error(errorContext + t.getMessage(), t);
            throw new ApplicationJsonException(re, errorContext + t.getMessage(), Response.Status.CONFLICT.getStatusCode());
        }
        if (t instanceof SQLException) {
            logger.error(errorContext + t.getMessage(), t);
            throw new ApplicationJsonException(re, errorContext + t.getMessage(), Response.Status.BAD_REQUEST.getStatusCode());
        }
        t = t.getCause();
    }
}

@Test
public void testConnPropertiesNegative() throws Exception {
    Map<String, String> connProperties = new HashMap<>();
    connProperties.put("user", "postgresX");
    connProperties.put("password", "");
    String uri = PgSQLTestServer.getInstance().getJdbcUrl().split("\\?")[0];
    Tablespace space = new PgSQLTablespace("t1", URI.create(uri), getJsonTablespace(connProperties));
    try {
        space.init(new TajoConf());
        fail("Must be failed");
    } catch (IOException ioe) {
        assertTrue(ioe.getCause() instanceof PSQLException);
    } finally {
        space.close();
    }
}

public void validConnection(DbSession dbSession) throws WaarpDatabaseNoConnectionException {
    // try to limit the number of check!
    synchronized (dbSession) {
        if (dbSession.getConn() == null) {
            throw new WaarpDatabaseNoConnectionException("Cannot connect to database");
        }
        try {
            if (!dbSession.getConn().isClosed()) {
                if (!dbSession.getConn().isValid(DbConstant.VALIDTESTDURATION)) {
                    // Give a try by closing the current connection
                    throw new SQLException("Cannot connect to database");
                }
            }
            dbSession.setDisActive(false);
            if (dbSession.getAdmin() != null)
                dbSession.getAdmin().setActive(true);
        } catch (SQLException e2) {
            dbSession.setDisActive(true);
            if (dbSession.getAdmin() != null)
                dbSession.getAdmin().setActive(false);
            if (e2 instanceof org.postgresql.util.PSQLException) {
                validConnectionSelect(dbSession);
                return;
            }
            try {
                try {
                    recreateSession(dbSession);
                } catch (WaarpDatabaseNoConnectionException e) {
                    closeInternalConnection(dbSession);
                    throw e;
                }
                try {
                    if (!dbSession.getConn().isValid(DbConstant.VALIDTESTDURATION)) {
                        closeInternalConnection(dbSession);
                        throw new WaarpDatabaseNoConnectionException("Cannot connect to database", e2);
                    }
                } catch (SQLException e) {
                    closeInternalConnection(dbSession);
                    throw new WaarpDatabaseNoConnectionException("Cannot connect to database", e);
                }
                dbSession.setDisActive(false);
                if (dbSession.getAdmin() != null)
                    dbSession.getAdmin().setActive(true);
                dbSession.recreateLongTermPreparedStatements();
                return;
            } catch (WaarpDatabaseSqlException e1) {
            }
            closeInternalConnection(dbSession);
            throw new WaarpDatabaseNoConnectionException("Cannot connect to database", e2);
        }
    }
}

/**
   * Upload features.
   * 
   * @param features
   *          the features
   * @param userId
   *          the user id
   * @param project
   * @return the string
   * @throws IOException
   *           Signals that an I/O exception has occurred.
   * @throws DataStoreCreationException
   *           the data store creation exception
   * @throws MiddlewarePersistentException
   */
private String shareUAZSnapshot(final SimpleFeatureCollection features, final String userId, final WifProject project) throws IOException, DataStoreCreationException, MiddlewarePersistentException {
    // TODO the following chunk of code is from Mohammed, we should have these
    // in a shared library
    final int decimals = WifKeys.GEOJSON_PRECISION;
    final GeometryJSON gjson = new GeometryJSON(decimals);
    final FeatureJSON featureJSON = new FeatureJSON(gjson);
    featureJSON.setEncodeFeatureCollectionBounds(true);
    featureJSON.setEncodeFeatureCollectionCRS(true);
    featureJSON.setEncodeNullValues(true);
    final String uuid = UUID.randomUUID().toString();
    // Get the temporary directory and print it.
    final String tempDir = System.getProperty("java.io.tmpdir");
    final File tmpFile = new File(tempDir + "/" + uuid + ".json");
    if (!tmpFile.exists()) {
        tmpFile.createNewFile();
        LOGGER.info(tmpFile.getAbsolutePath() + " has been created");
    }
    final OutputStream output = new BufferedOutputStream(new FileOutputStream(tmpFile));
    // TODO end the chunk code from Mohammed
    // FIXME the following operation tthrow a
    // org.geotools.data.postgis.PostGISDialect getOptimizedBounds
    // WARNING: Failed to use ST_Estimated_Extent, falling back on envelope
    // aggregation
    // org.postgresql.util.PSQLException: ERROR: LWGEOM_estimated_extent:
    // couldn't locate table within current schema
    // Nonetheless, this a known issue of geotools when working with psotgis of
    // earlier versions of 1.5
    // output
    featureJSON.writeFeatureCollection(features, output);
    LOGGER.info("done writing feature collection..." + tmpFile.length() / 1024 / 1024 + " MB.");
    final Map<String, Object> item = aurinServiceClient.shareAurinProject(tmpFile, userId, project);
    LOGGER.debug("response " + item.get("result"));
    return (String) item.get("result");
}

/**
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
   *      response)
   */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    Util.allowCrossOriginRequests(response);
    // Parse the JSON message
    BufferedReader reader = new BufferedReader(new InputStreamReader(request.getInputStream(), "UTF-8"));
    final RPC.SignedRequest rpc = gson.fromJson(reader, RPC.SignedRequest.class);
    reader.close();
    if (rpc.platform == null) {
        if (rpc.clientIdentifier.contains("Android")) {
            rpc.platform = "ANDROID";
        } else if (rpc.clientIdentifier.contains("iOS")) {
            rpc.platform = "IOS";
        } else {
            rpc.platform = "UNKNOWN";
        }
    }
    logger.info("servlet={} id={} txn={} client={} platform={}", request.getServletPath(), rpc.identity, rpc.transactionId, rpc.clientIdentifier, rpc.platform);
    final LogMetadata logMetadata = new LogMetadata(request);
    Manager mgr = null;
    DBIdentity requestor = null;
    boolean commitAndCloseOnSuccess = isCloseTransactionOperation() || (rpc.implicitEndTransaction);
    boolean startTransaction = isBeginTransactionOperation() || rpc.implicitBeginTransaction;
    try {
        if (null == rpc.transactionId) {
            commitAndCloseOnSuccess = !startTransaction;
            // will allow our replicas to handle that traffic. (Client will re-try)
            if (!startTransaction && fractionReadRequestsToReject > 0.0 && // this readonly check will ensure we do not match AddIdentity
            isReadOnly()) {
                double random = insecureRandomGenerator.nextDouble();
                if (random < fractionReadRequestsToReject) {
                    throw new MitroServletException("rejecting request due to random reject");
                }
            }
            mgr = managerFactory.newManager();
            if (!commitAndCloseOnSuccess) {
                mgr.enableCaches();
            }
        } else {
            assert mgr == null;
            mgr = managerFactory.getTransaction(rpc.transactionId);
        }
        assert mgr != null : "no transaction found for id " + rpc.transactionId;
        mgr.setUserIp(request.getRemoteAddr());
        if (mgr.lock.tryLock()) {
            try {
                // Rate limit requests according to policy
                boolean isGroupSyncRequest = isGroupSyncRequestHack(mgr, rpc.identity, request.getServletPath(), mgr.getOperationName());
                if (!requestLimiter.isRequestPermitted(request.getRemoteAddr(), request.getServletPath())) {
                    // TODO: This is a hack! We should remove this
                    if (isGroupSyncRequest) {
                        logger.info("ignoring rate limit for group sync operation");
                    } else {
                        // TODO: Monitor rate limits that are triggered
                        // TODO: After a limit is triggered, throttle the IP?
                        logger.error("rate limited: ip={} endpoint={}", request.getRemoteAddr(), request.getServletPath());
                        throw new RateLimitedException();
                    }
                }
                boolean validSignature = this instanceof GetMyPrivateKey || this instanceof UpdateSecretFromAgent;
                if (rpc.identity != null && rpc.signature != null) {
                    requestor = DBIdentity.getIdentityForUserName(mgr, rpc.identity);
                    String publicKeyString = null;
                    if (requestor != null) {
                        publicKeyString = requestor.getPublicKeyString();
                        if (updateIdentityWithCookies(request, requestor)) {
                            mgr.identityDao.update(requestor);
                        }
                    // try to set the referrer and guid info if they're not already in the DB.
                    } else if (this instanceof AddIdentity) {
                        // Verifies that the user really does have access to this private
                        // key
                        RPC.AddIdentityRequest r = gson.fromJson(rpc.request, RPC.AddIdentityRequest.class);
                        publicKeyString = r.publicKey;
                    }
                    if (publicKeyString != null) {
                        // check the signature
                        PublicKeyInterface key = keyFactory.loadPublicKey(publicKeyString);
                        validSignature = key.verify(rpc.request, rpc.signature);
                        mgr.setRequestor(requestor, null);
                    }
                }
                if (!validSignature) {
                    // although there is almost certainly a timing attack here
                    throw new InvalidRequestException("Invalid identity or signature");
                }
                if (requestor != null) {
                    // verify that the user has not been logged out.
                    RPC.MitroRPC genericRpc = gson.fromJson(rpc.request, RPC.MitroRPC.class);
                    mgr.setRequestor(requestor, genericRpc.deviceId);
                    if (null == GetMyDeviceKey.maybeGetClientKeyForLogin(mgr, requestor, genericRpc.deviceId, rpc.platform)) {
                        // they need to retype their password before any requests will work
                        throw new DoLoginException();
                    }
                }
                String requestServerUrl = new URL(request.getScheme(), request.getServerName(), request.getServerPort(), "").toString();
                if (rpc.implicitBeginTransaction && rpc.transactionId != null) {
                    throw new MitroServletException("Cannot create a new transaction when you're in one");
                }
                // record the operation name now
                if (!isBeginTransactionOperation() && rpc.implicitBeginTransaction) {
                    BeginTransactionServlet.beginTransaction(mgr, rpc.operationName, requestor);
                }
                MitroRequestContext requestContext = new MitroRequestContext(requestor, rpc.request, mgr, requestServerUrl, rpc.platform, request.getRemoteAddr());
                requestContext.setIsGroupSyncRequest(isGroupSyncRequest);
                MitroRPC out = processCommand(requestContext);
                // connection.
                if (commitAndCloseOnSuccess) {
                    mgr.commitTransaction();
                    mgr.close();
                } else {
                    out.transactionId = mgr.getTransactionId();
                }
                Util.writeJsonResponse(out, response);
                logMetadata.setResponse(response.getStatus(), out);
                RPCLogger.log(rpc, logMetadata);
            } catch (Exception e) {
                try {
                    mgr.rollbackTransaction();
                } finally {
                    mgr.close();
                }
                throw e;
            } finally {
                mgr.lock.unlock();
            }
        } else {
            throw new InvalidRequestException("transaction id already in use: " + mgr.getTransactionId());
        }
    } catch (Throwable e) {
        int statusCode = HttpServletResponse.SC_INTERNAL_SERVER_ERROR;
        PSQLException p = Manager.extractPSQLException(e);
        if (null != p && POSTGRES_RETRY_SQLSTATES.contains(p.getSQLState())) {
            e = new RetryTransactionException(e);
        } else if (e instanceof SendableException) {
            statusCode = HttpServletResponse.SC_FORBIDDEN;
        }
        RPC.MitroException out = RPC.MitroException.createFromException(e, MitroExceptionReason.FOR_USER);
        logger.error("unhandled exception (exceptionId:{}); returning error code {}:", out.exceptionId, statusCode, e);
        response.setHeader("Content-Type", "application/json");
        response.setStatus(statusCode);
        response.getWriter().write(gson.toJson(out));
        logMetadata.setResponse(response.getStatus(), out);
        logMetadata.setException(e);
        RPCLogger.log(rpc, logMetadata);
        return;
    }
}

@Override
protected void saveErrors(SQLException e) {
    try {
        super.saveErrors(e);
        Class cl = Class.forName("org.postgresql.util.PSQLException");
        Method method = cl.getDeclaredMethod("getServerErrorMessage", (Class) null);
        _serverErrorMessage = method.invoke(e, new Object[] {});
    } catch (Exception ex) {
        log.log(Level.FINE, ex.toString(), ex);
    }
}

private static MolgenisDataException doTranslate(DataAccessException dataAccessException) {
    Throwable cause = dataAccessException.getCause();
    if (!(cause instanceof PSQLException)) {
        throw new RuntimeException(format("Unexpected exception class [%s]", cause.getClass().getSimpleName()));
    }
    PSQLException pSqlException = (PSQLException) cause;
    MolgenisDataException molgenisDataException = doTranslate(pSqlException);
    if (molgenisDataException == null) {
        molgenisDataException = new MolgenisDataException(dataAccessException);
    }
    return molgenisDataException;
}

/**
    * Tell is it a RecordAlreadyExistsException.
    * 
    * @param e
    *          SQLException
    * @return boolean
    */
private boolean isRecordAlreadyExistsException(SQLException e) {
    // Search in UPPER case
    // MySQL 5.0.x - com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
    // Duplicate entry '4f684b34c0a800030018c34f99165791-0' for key 1
    // HSQLDB 8.x - java.sql.SQLException: Violation of unique constraint $$: duplicate value(s) for
    // column(s) $$:
    // JCR_VCAS_PK in statement [INSERT INTO JCR_VCAS (PROPERTY_ID, ORDER_NUM, CAS_ID)
    // VALUES(?,?,?)]         String H2_PK_CONSTRAINT_DETECT_PATTERN = "(.*JdbcSQLException.*violation.*PRIMARY_KEY_.*)";
    // PostgreSQL 8.2.x - org.postgresql.util.PSQLException: ERROR: duplicate key violates unique
    // constraint "jcr_vcas_pk"
    // Oracle 9i x64 (on Fedora 7) - java.sql.SQLException: ORA-00001: unique constraint
    // (EXOADMIN.JCR_VCAS_PK) violated
    // H2 - org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: 
    // "PRIMARY_KEY_4 ON PUBLIC.JCR_VCAS_TEST(PROPERTY_ID, ORDER_NUM)"; 
    //
    String err = e.toString();
    if (dialect.startsWith(DBConstants.DB_DIALECT_MYSQL)) {
        // for MySQL will search
        return MYSQL_PK_CONSTRAINT_DETECT.matcher(err).find();
    } else if (err.toLowerCase().toUpperCase().indexOf(sqlConstraintPK.toLowerCase().toUpperCase()) >= 0) {
        // most of supported dbs prints PK name in exception
        return true;
    } else if (dialect.startsWith(DBConstants.DB_DIALECT_DB2)) {
        return DB2_PK_CONSTRAINT_DETECT.matcher(err).find();
    } else if (dialect.startsWith(DBConstants.DB_DIALECT_H2)) {
        return H2_PK_CONSTRAINT_DETECT.matcher(err).find();
    }
    return false;
}

public void testDataSourceOverrideUserPass() throws SQLException, NamingException {
    // use the datasource property to connect to the database
    Util.PropertyList properties = spy(TestContext.instance().getConnectionProperties().clone());
    final Dialect dialect = TestContext.instance().getDialect();
    if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.ACCESS) {
        // Access doesn't accept user/password, so this test is pointless.
        return;
    }
    final String jdbcUser = properties.get(RolapConnectionProperties.JdbcUser.name());
    final String jdbcPassword = properties.get(RolapConnectionProperties.JdbcPassword.name());
    if (jdbcUser == null || jdbcPassword == null) {
        // Can only run this test if username and password are explicit.
        return;
    }
    // Define a data source with bogus user and password.
    properties.put(RolapConnectionProperties.JdbcUser.name(), "bogususer");
    properties.put(RolapConnectionProperties.JdbcPassword.name(), "boguspassword");
    properties.put(RolapConnectionProperties.PoolNeeded.name(), "false");
    final StringBuilder buf = new StringBuilder();
    final DataSource dataSource = RolapConnection.createDataSource(null, properties, buf);
    final String desc = buf.toString();
    assertTrue(desc, desc.startsWith("Jdbc="));
    assertTrue(desc, desc.indexOf("JdbcUser=bogususer") >= 0);
    verify(properties, atLeastOnce()).get(RolapConnectionProperties.JdbcPassword.name());
    final String jndiName = "jndiDataSource";
    THREAD_INITIAL_CONTEXT.set(new InitialContext() {

        public Object lookup(String str) {
            return str.equals(jndiName) ? dataSource : null;
        }
    });
    // Create a property list that we will use for the actual mondrian
    // connection. Replace the original JDBC info with the data source we
    // just created.
    final Util.PropertyList properties2 = new Util.PropertyList();
    for (Pair<String, String> entry : properties) {
        properties2.put(entry.getKey(), entry.getValue());
    }
    properties2.remove(RolapConnectionProperties.Jdbc.name());
    properties2.put(RolapConnectionProperties.DataSource.name(), jndiName);
    // With JdbcUser and JdbcPassword credentials in the mondrian connect
    // string, the data source's "user" and "password" properties are
    // overridden and the connection succeeds.
    properties2.put(RolapConnectionProperties.JdbcUser.name(), jdbcUser);
    properties2.put(RolapConnectionProperties.JdbcPassword.name(), jdbcPassword);
    mondrian.olap.Connection connection = null;
    try {
        connection = DriverManager.getConnection(properties2, null);
        Query query = connection.parseQuery("select from [Sales]");
        final Result result = connection.execute(query);
        assertNotNull(result);
    } finally {
        if (connection != null) {
            connection.close();
            connection = null;
        }
    }
    // If we don't specify JdbcUser and JdbcPassword in the mondrian
    // connection properties, mondrian uses the data source's
    // bogus credentials, and the connection fails.
    properties2.remove(RolapConnectionProperties.JdbcUser.name());
    properties2.remove(RolapConnectionProperties.JdbcPassword.name());
    for (String poolNeeded : Arrays.asList("false", "true")) {
        // Important to test with & without pooling. Connection pools
        // typically do not let you change user, so it's important that
        // mondrian handles these right.
        properties2.put(RolapConnectionProperties.PoolNeeded.name(), poolNeeded);
        try {
            connection = DriverManager.getConnection(properties2, null);
            fail("Expected exception");
        } catch (MondrianException e) {
            final String s = TestContext.getStackTrace(e);
            assertTrue(s, s.indexOf("Error while creating SQL connection: " + "DataSource=jndiDataSource") >= 0);
            switch(dialect.getDatabaseProduct()) {
                case DERBY:
                    assertTrue(s, s.indexOf("Caused by: java.sql.SQLException: " + "Schema 'BOGUSUSER' does not exist") >= 0);
                    break;
                case ORACLE:
                    assertTrue(s, s.indexOf("Caused by: java.sql.SQLException: ORA-01017: " + "invalid username/password; logon denied") >= 0);
                    break;
                case MYSQL:
                case MARIADB:
                    assertTrue(s, s.indexOf("Caused by: java.sql.SQLException: Access denied " + "for user 'bogususer'") >= 0);
                    break;
                case POSTGRESQL:
                    assertTrue(s, s.indexOf("Caused by: org.postgresql.util.PSQLException: " + "FATAL: password authentication failed for " + "user \"bogususer\"") >= 0);
                    break;
            }
        } finally {
            if (connection != null) {
                connection.close();
                connection = null;
            }
        }
    }
}

/**
	 * @param c
	 * @param save1
	 * @param createCleanseSavepoint
	 * @throws SQLException
	 * @throws SagesEtlException
	 */
protected static void alterStagingTableAddFlagColumn(Connection c, Savepoint save1, Savepoint createCleanseSavepoint) throws SQLException, SagesEtlException {
    String sqlaltertableAddColumn;
    PreparedStatement PS_addcolumn_Flag;
    sqlaltertableAddColumn = addFlagColumn(dst_table_name);
    PS_addcolumn_Flag = c.prepareStatement(sqlaltertableAddColumn);
    System.out.println("ALTER STATEMENT: " + sqlaltertableAddColumn);
    try {
        PS_addcolumn_Flag.execute();
    } catch (PSQLException e) {
        if ("42701".equals(e.getSQLState())) {
            System.out.println("ETL_LOGGER:" + e.getSQLState() + ", " + e.getMessage());
            c.rollback(createCleanseSavepoint);
        } else {
            errorCleanup(save1, c, null, e);
            throw abort("Uh-oh, something happened trying to add column etl_flag to ETL_STAGING_DB.", e);
        }
    } catch (//TODO MS Access specific
    SQLException //TODO MS Access specific
    e) {
        if ("S0021".equals(e.getSQLState())) {
            System.out.println("ETL_LOGGER:" + e.getSQLState() + ", " + e.getMessage());
        } else {
            errorCleanup(save1, c, null, e);
            throw abort("Uh-oh, something happened trying to add column etl_flag to ETL_CLEANSING_TABLE.", e);
        }
    } catch (Exception e) {
        errorCleanup(save1, c, null, e);
        throw abort("Uh-oh, something happened trying to add column etl_flag to ETL_STAGING_DB.", e);
    }
}

public void handleCommandStatus(String status, int updateCount, long insertOID) {
    if (!sawBegin) {
        if (!status.equals("BEGIN"))
            handleError(new PSQLException(GT.tr("Expected command status BEGIN, got {0}.", status), PSQLState.PROTOCOL_VIOLATION));
        sawBegin = true;
    } else {
        handleError(new PSQLException(GT.tr("Unexpected command status: {0}.", status), PSQLState.PROTOCOL_VIOLATION));
    }
}

public void testGetParameterMetaData() {
    try {
        Select query_select = new Select(mDatasource);
        query_select.from("parametersbean").whereParameter("propertyString", "=");
        DbPreparedStatement statement_select = mDatasource.getConnection().getPreparedStatement(query_select);
        statement_select.setString("propertyString", "ok");
        ParameterMetaData metadata = null;
        try {
            metadata = statement_select.getParameterMetaData();
            assertNotNull(metadata);
        } catch (AbstractMethodError e) {
            assertTrue(mDatasource.getDriver().equals("oracle.jdbc.driver.OracleDriver") || mDatasource.getDriver().equals("org.apache.derby.jdbc.EmbeddedDriver"));
        }
        statement_select.close();
    } catch (DatabaseException e) {
        if (e.getCause() != null) {
            if (e.getCause().getClass().getName().equals("com.mysql.jdbc.NotImplemented")) {
                return;
            }
            if (e.getCause().getClass().getName().equals("org.postgresql.util.PSQLException") && e.getCause().getMessage().equals("This method is not yet implemented.")) {
                return;
            }
            if (e.getCause().getClass().getName().equals("com.mckoi.database.jdbc.MSQLException") && e.getCause().getMessage().startsWith("Not Supported")) {
                return;
            }
        }
        assertTrue(ExceptionUtils.getExceptionStackTrace(e), false);
    }
}

/**
     * Internal method to be used by subclasses to perform any extra checks on
     * the listed of {@link CheckedPath} instances before allowing the creation
     * of directories.
     *
     * @param paths Not null, not empty. (Will be emptied by this method.)
     * @param parents "mkdir -p" like flag.
     * @param s The session
     * @param sf
     * @param sql
     * @param effectiveEventContext
     */
protected void makeCheckedDirs(final LinkedList<CheckedPath> paths, boolean parents, Session s, ServiceFactory sf, SqlAction sql, ome.system.EventContext effectiveEventContext) throws ServerError {
    CheckedPath checked;
    // exist, it gets created.
    while (paths.size() > 1) {
        // Only possible if `parents`
        checked = paths.removeFirst();
        if (checked.exists()) {
            if (!checked.isDirectory()) {
                throw new omero.ResourceError(null, null, "Path is not a directory.");
            } else if (!checked.canRead()) {
                throw new omero.ResourceError(null, null, "Directory is not readable");
            }
            assertFindDir(checked, s, sf, sql);
        } else {
            // This will fail if the directory already exists
            try {
                repositoryDao.register(repoUuid, checked, DIRECTORY_MIMETYPE, sf, sql);
            } catch (ValidationException ve) {
                if (ve.getCause() instanceof PSQLException) {
                    SleepTimer.sleepFor(1000);
                    if (checked.exists()) {
                        log.warn("retrying after exception in registering directory " + checked + ": " + ve.getCause());
                        paths.add(0, checked);
                        continue;
                    }
                }
                throw ve;
            }
        }
    }
    // Now we are ready to work on the actual intended path.
    // Size is now empty
    checked = paths.removeFirst();
    if (checked.exists()) {
        if (parents) {
            assertFindDir(checked, s, sf, sql);
        } else {
            throw new omero.ResourceError(null, null, "Path exists on disk: " + checked.fsFile);
        }
    }
    repositoryDao.register(repoUuid, checked, DIRECTORY_MIMETYPE, sf, sql);
}

/** Used to upgrade old databases. */
private boolean upgradeProjectsTable() throws Exception {
    // Upgrade database if necessary: set a version field, create the TemplateThing entries in the database for each project from its XML template file, and delete the xml_template column
    // Check columns: see if trakem2_version is there
    ResultSet r = connection.prepareStatement("SELECT column_name FROM information_schema.columns WHERE table_name='ab_projects' AND column_name='xml_template'").executeQuery();
    if (r.next()) {
        YesNoCancelDialog yn = new YesNoCancelDialog(IJ.getInstance(), "Upgrade", "Need to upgrade table projects.\nNo data will be lost, but reorganized.\nProceed?");
        if (!yn.yesPressed()) {
            return false;
        }
        // retrieve and parse XML template from each project
        ResultSet r1 = connection.prepareStatement("SELECT * FROM ab_projects").executeQuery();
        while (r1.next()) {
            long project_id = r1.getLong("id");
            // parse the XML file stored in the db and save the TemplateThing into the ab_things table
            InputStream xml_stream = null;
            try {
                String query = "SELECT xml_template FROM ab_projects WHERE id=" + project_id;
                ResultSet result = connection.prepareStatement(query).executeQuery();
                if (result.next()) {
                    xml_stream = result.getBinaryStream("xml_template");
                }
                result.close();
            } catch (Exception e) {
                IJError.print(e);
                return false;
            }
            if (null == xml_stream) {
                Utils.showMessage("Failed to upgrade the database schema: XML template stream is null.");
                return false;
            }
            TemplateThing template_root = new TrakEM2MLParser(xml_stream).getTemplateRoot();
            if (null == template_root) {
                Utils.showMessage("Failed to upgrade the database schema: root TemplateThing is null.");
                return false;
            }
            Project project = new Project(project_id, r1.getString("title"));
            project.setTempLoader(this);
            template_root.addToDatabase(project);
        }
        r1.close();
        // remove the XML column
        connection.prepareStatement("ALTER TABLE ab_projects DROP xml_template").execute();
        // org.postgresql.util.PSQLException: ERROR: adding columns with defaults is not implemented in 7.4.* (only in 8.1.4+)
        // connection.prepareStatement("ALTER TABLE ab_projects ADD version text default '" + Utils.version + "'").execute();
        // so: workaround
        connection.prepareStatement("ALTER TABLE ab_projects ADD version TEXT").execute();
        connection.prepareStatement("ALTER TABLE ab_projects ALTER COLUMN version SET DEFAULT '" + Utils.version + "'").execute();
    }
    r.close();
    // success!
    return true;
}

/**
     * Parse of value of result set
     *
     * @param result the result set
     * @param i      the offset in the result set
     * @param type   the JDBC type
     * @param locale the locale to use for parsing
     * @return The parse value
     * @throws SQLException when SQL execution gives an error
     * @throws IOException  when input/output error occurs
     */
@Override
public Object parseType(ResultSet result, Integer i, int type, Locale locale) throws SQLException, IOException, ParseException {
    logger.trace("i={} type={}", i, type);
    switch(type) {
        /**
             * The JDBC types CHAR, VARCHAR, and LONGVARCHAR are closely
             * related. CHAR represents a small, fixed-length character string,
             * VARCHAR represents a small, variable-length character string, and
             * LONGVARCHAR represents a large, variable-length character string.
             */
        case Types.CHAR:
        case Types.VARCHAR:
        case Types.LONGVARCHAR:
            {
                return result.getString(i);
            }
        case Types.NCHAR:
        case Types.NVARCHAR:
        case Types.LONGNVARCHAR:
            {
                return result.getNString(i);
            }
        /**
             * The JDBC types BINARY, VARBINARY, and LONGVARBINARY are closely
             * related. BINARY represents a small, fixed-length binary value,
             * VARBINARY represents a small, variable-length binary value, and
             * LONGVARBINARY represents a large, variable-length binary value
             */
        case Types.BINARY:
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
            {
                byte[] b = result.getBytes(i);
                return shouldTreatBinaryAsString() ? (b != null ? new String(b) : null) : b;
            }
        /**
             * The JDBC type ARRAY represents the SQL3 type ARRAY.
             *
             * An ARRAY value is mapped to an instance of the Array interface in
             * the Java programming language. If a driver follows the standard
             * implementation, an Array object logically points to an ARRAY
             * value on the server rather than containing the elements of the
             * ARRAY object, which can greatly increase efficiency. The Array
             * interface contains methods for materializing the elements of the
             * ARRAY object on the client in the form of either an array or a
             * ResultSet object.
             */
        case Types.ARRAY:
            {
                Array arr = result.getArray(i);
                return arr == null ? null : arr.getArray();
            }
        /**
             * The JDBC type BIGINT represents a 64-bit signed integer value
             * between -9223372036854775808 and 9223372036854775807.
             *
             * The corresponding SQL type BIGINT is a nonstandard extension to
             * SQL. In practice the SQL BIGINT type is not yet currently
             * implemented by any of the major databases, and we recommend that
             * its use be avoided in code that is intended to be portable.
             *
             * The recommended Java mapping for the BIGINT type is as a Java
             * long.
             */
        case Types.BIGINT:
            {
                Object o = result.getLong(i);
                return result.wasNull() ? null : o;
            }
        /**
             * The JDBC type BIT represents a single bit value that can be zero
             * or one.
             *
             * SQL-92 defines an SQL BIT type. However, unlike the JDBC BIT
             * type, this SQL-92 BIT type can be used as a parameterized type to
             * define a fixed-length binary string. Fortunately, SQL-92 also
             * permits the use of the simple non-parameterized BIT type to
             * represent a single binary digit, and this usage corresponds to
             * the JDBC BIT type. Unfortunately, the SQL-92 BIT type is only
             * required in "full" SQL-92 and is currently supported by only a
             * subset of the major databases. Portable code may therefore prefer
             * to use the JDBC SMALLINT type, which is widely supported.
             */
        case Types.BIT:
            {
                try {
                    Object o = result.getInt(i);
                    return result.wasNull() ? null : o;
                } catch (Exception e) {
                    String exceptionClassName = e.getClass().getName();
                    if ("org.postgresql.util.PSQLException".equals(exceptionClassName)) {
                        return "t".equals(result.getString(i));
                    }
                    throw new IOException(e);
                }
            }
        /**
             * The JDBC type BOOLEAN, which is new in the JDBC 3.0 API, maps to
             * a boolean in the Java programming language. It provides a
             * representation of true and false, and therefore is a better match
             * than the JDBC type BIT, which is either 1 or 0.
             */
        case Types.BOOLEAN:
            {
                return result.getBoolean(i);
            }
        /**
             * The JDBC type BLOB represents an SQL3 BLOB (Binary Large Object).
             *
             * A JDBC BLOB value is mapped to an instance of the Blob interface
             * in the Java programming language. If a driver follows the
             * standard implementation, a Blob object logically points to the
             * BLOB value on the server rather than containing its binary data,
             * greatly improving efficiency. The Blob interface provides methods
             * for materializing the BLOB data on the client when that is
             * desired.
             */
        case Types.BLOB:
            {
                Blob blob = result.getBlob(i);
                if (blob != null) {
                    long n = blob.length();
                    if (n > Integer.MAX_VALUE) {
                        throw new IOException("can't process blob larger than Integer.MAX_VALUE");
                    }
                    byte[] tab = blob.getBytes(1, (int) n);
                    blob.free();
                    return tab;
                }
                break;
            }
        /**
             * The JDBC type CLOB represents the SQL3 type CLOB (Character Large
             * Object).
             *
             * A JDBC CLOB value is mapped to an instance of the Clob interface
             * in the Java programming language. If a driver follows the
             * standard implementation, a Clob object logically points to the
             * CLOB value on the server rather than containing its character
             * data, greatly improving efficiency. Two of the methods on the
             * Clob interface materialize the data of a CLOB object on the
             * client.
             */
        case Types.CLOB:
            {
                Clob clob = result.getClob(i);
                if (clob != null) {
                    long n = clob.length();
                    if (n > Integer.MAX_VALUE) {
                        throw new IOException("can't process clob larger than Integer.MAX_VALUE");
                    }
                    String str = clob.getSubString(1, (int) n);
                    clob.free();
                    return str;
                }
                break;
            }
        case Types.NCLOB:
            {
                NClob nclob = result.getNClob(i);
                if (nclob != null) {
                    long n = nclob.length();
                    if (n > Integer.MAX_VALUE) {
                        throw new IOException("can't process nclob larger than Integer.MAX_VALUE");
                    }
                    String str = nclob.getSubString(1, (int) n);
                    nclob.free();
                    return str;
                }
                break;
            }
        /**
             * The JDBC type DATALINK, new in the JDBC 3.0 API, is a column
             * value that references a file that is outside of a data source but
             * is managed by the data source. It maps to the Java type
             * java.net.URL and provides a way to manage external files. For
             * instance, if the data source is a DBMS, the concurrency controls
             * it enforces on its own data can be applied to the external file
             * as well.
             *
             * A DATALINK value is retrieved from a ResultSet object with the
             * ResultSet methods getURL or getObject. If the Java platform does
             * not support the type of URL returned by getURL or getObject, a
             * DATALINK value can be retrieved as a String object with the
             * method getString.
             *
             * java.net.URL values are stored in a database using the method
             * setURL. If the Java platform does not support the type of URL
             * being set, the method setString can be used instead.
             *
             *
             */
        case Types.DATALINK:
            {
                return result.getURL(i);
            }
        /**
             * The JDBC DATE type represents a date consisting of day, month,
             * and year. The corresponding SQL DATE type is defined in SQL-92,
             * but it is implemented by only a subset of the major databases.
             * Some databases offer alternative SQL types that support similar
             * semantics.
             */
        case Types.DATE:
            {
                try {
                    Date d = result.getDate(i, calendar);
                    return d != null ? formatDate(d.getTime()) : null;
                } catch (SQLException e) {
                    return null;
                }
            }
        case Types.TIME:
            {
                try {
                    Time t = result.getTime(i, calendar);
                    return t != null ? formatDate(t.getTime()) : null;
                } catch (SQLException e) {
                    return null;
                }
            }
        case Types.TIMESTAMP:
            {
                try {
                    Timestamp t = result.getTimestamp(i, calendar);
                    return t != null ? formatDate(t.getTime()) : null;
                } catch (SQLException e) {
                    return null;
                }
            }
        /**
             * The JDBC types DECIMAL and NUMERIC are very similar. They both
             * represent fixed-precision decimal values.
             *
             * The corresponding SQL types DECIMAL and NUMERIC are defined in
             * SQL-92 and are very widely implemented. These SQL types take
             * precision and scale parameters. The precision is the total number
             * of decimal digits supported, and the scale is the number of
             * decimal digits after the decimal point. For most DBMSs, the scale
             * is less than or equal to the precision. So for example, the value
             * "12.345" has a precision of 5 and a scale of 3, and the value
             * ".11" has a precision of 2 and a scale of 2. JDBC requires that
             * all DECIMAL and NUMERIC types support both a precision and a
             * scale of at least 15.
             *
             * The sole distinction between DECIMAL and NUMERIC is that the
             * SQL-92 specification requires that NUMERIC types be represented
             * with exactly the specified precision, whereas for DECIMAL types,
             * it allows an implementation to add additional precision beyond
             * that specified when the type was created. Thus a column created
             * with type NUMERIC(12,4) will always be represented with exactly
             * 12 digits, whereas a column created with type DECIMAL(12,4) might
             * be represented by some larger number of digits.
             *
             * The recommended Java mapping for the DECIMAL and NUMERIC types is
             * java.math.BigDecimal. The java.math.BigDecimal type provides math
             * operations to allow BigDecimal types to be added, subtracted,
             * multiplied, and divided with other BigDecimal types, with integer
             * types, and with floating point types.
             *
             * The method recommended for retrieving DECIMAL and NUMERIC values
             * is ResultSet.getBigDecimal. JDBC also allows access to these SQL
             * types as simple Strings or arrays of char. Thus, Java programmers
             * can use getString to receive a DECIMAL or NUMERIC result.
             * However, this makes the common case where DECIMAL or NUMERIC are
             * used for currency values rather awkward, since it means that
             * application writers have to perform math on strings. It is also
             * possible to retrieve these SQL types as any of the Java numeric
             * types.
             */
        case Types.DECIMAL:
        case Types.NUMERIC:
            {
                BigDecimal bd = null;
                try {
                    // getBigDecimal() should get obsolete. Most seem to use getString/getObject anyway...
                    bd = result.getBigDecimal(i);
                } catch (NullPointerException e) {
                }
                if (bd == null || result.wasNull()) {
                    return null;
                }
                if (getScale() >= 0) {
                    bd = bd.setScale(getScale(), getRounding());
                    try {
                        long l = bd.longValueExact();
                        if (Long.toString(l).equals(result.getString(i))) {
                            // convert to long if possible
                            return l;
                        } else {
                            // convert to double (with precision loss)
                            return bd.doubleValue();
                        }
                    } catch (ArithmeticException e) {
                        return bd.doubleValue();
                    }
                } else {
                    return bd.toPlainString();
                }
            }
        /**
             * The JDBC type DOUBLE represents a "double precision" floating
             * point number that supports 15 digits of mantissa.
             *
             * The corresponding SQL type is DOUBLE PRECISION, which is defined
             * in SQL-92 and is widely supported by the major databases. The
             * SQL-92 standard leaves the precision of DOUBLE PRECISION up to
             * the implementation, but in practice all the major databases
             * supporting DOUBLE PRECISION support a mantissa precision of at
             * least 15 digits.
             *
             * The recommended Java mapping for the DOUBLE type is as a Java
             * double.
             */
        case Types.DOUBLE:
            {
                String s = result.getString(i);
                if (result.wasNull() || s == null) {
                    return null;
                }
                NumberFormat format = NumberFormat.getInstance(locale);
                Number number = format.parse(s);
                return number.doubleValue();
            }
        /**
             * The JDBC type FLOAT is basically equivalent to the JDBC type
             * DOUBLE. We provided both FLOAT and DOUBLE in a possibly misguided
             * attempt at consistency with previous database APIs. FLOAT
             * represents a "double precision" floating point number that
             * supports 15 digits of mantissa.
             *
             * The corresponding SQL type FLOAT is defined in SQL-92. The SQL-92
             * standard leaves the precision of FLOAT up to the implementation,
             * but in practice all the major databases supporting FLOAT support
             * a mantissa precision of at least 15 digits.
             *
             * The recommended Java mapping for the FLOAT type is as a Java
             * double. However, because of the potential confusion between the
             * double precision SQL FLOAT and the single precision Java float,
             * we recommend that JDBC programmers should normally use the JDBC
             * DOUBLE type in preference to FLOAT.
             */
        case Types.FLOAT:
            {
                String s = result.getString(i);
                if (result.wasNull() || s == null) {
                    return null;
                }
                NumberFormat format = NumberFormat.getInstance(locale);
                Number number = format.parse(s);
                return number.doubleValue();
            }
        /**
             * The JDBC type JAVA_OBJECT, added in the JDBC 2.0 core API, makes
             * it easier to use objects in the Java programming language as
             * values in a database. JAVA_OBJECT is simply a type code for an
             * instance of a class defined in the Java programming language that
             * is stored as a database object. The type JAVA_OBJECT is used by a
             * database whose type system has been extended so that it can store
             * Java objects directly. The JAVA_OBJECT value may be stored as a
             * serialized Java object, or it may be stored in some
             * vendor-specific format.
             *
             * The type JAVA_OBJECT is one of the possible values for the column
             * DATA_TYPE in the ResultSet objects returned by various
             * DatabaseMetaData methods, including getTypeInfo, getColumns, and
             * getUDTs. The method getUDTs, part of the new JDBC 2.0 core API,
             * will return information about the Java objects contained in a
             * particular schema when it is given the appropriate parameters.
             * Having this information available facilitates using a Java class
             * as a database type.
             */
        case Types.OTHER:
        case Types.JAVA_OBJECT:
            {
                return result.getObject(i);
            }
        /**
             * The JDBC type REAL represents a "single precision" floating point
             * number that supports seven digits of mantissa.
             *
             * The corresponding SQL type REAL is defined in SQL-92 and is
             * widely, though not universally, supported by the major databases.
             * The SQL-92 standard leaves the precision of REAL up to the
             * implementation, but in practice all the major databases
             * supporting REAL support a mantissa precision of at least seven
             * digits.
             *
             * The recommended Java mapping for the REAL type is as a Java
             * float.
             */
        case Types.REAL:
            {
                String s = result.getString(i);
                if (result.wasNull() || s == null) {
                    return null;
                }
                NumberFormat format = NumberFormat.getInstance(locale);
                Number number = format.parse(s);
                return number.doubleValue();
            }
        /**
             * The JDBC type INTEGER represents a 32-bit signed integer value
             * ranging between -2147483648 and 2147483647.
             *
             * The corresponding SQL type, INTEGER, is defined in SQL-92 and is
             * widely supported by all the major databases. The SQL-92 standard
             * leaves the precision of INTEGER up to the implementation, but in
             * practice all the major databases support at least 32 bits.
             *
             * The recommended Java mapping for the INTEGER type is as a Java
             * int.
             */
        case Types.TINYINT:
        case Types.SMALLINT:
        case Types.INTEGER:
            {
                try {
                    Integer integer = result.getInt(i);
                    return result.wasNull() ? null : integer;
                } catch (SQLDataException e) {
                    Long l = result.getLong(i);
                    return result.wasNull() ? null : l;
                }
            }
        case Types.SQLXML:
            {
                SQLXML xml = result.getSQLXML(i);
                return xml != null ? xml.getString() : null;
            }
        case Types.NULL:
            {
                return null;
            }
        /**
             * The JDBC type DISTINCT field (Types class)>DISTINCT represents
             * the SQL3 type DISTINCT.
             *
             * The standard mapping for a DISTINCT type is to the Java type to
             * which the base type of a DISTINCT object would be mapped. For
             * example, a DISTINCT type based on a CHAR would be mapped to a
             * String object, and a DISTINCT type based on an SQL INTEGER would
             * be mapped to an int.
             *
             * The DISTINCT type may optionally have a custom mapping to a class
             * in the Java programming language. A custom mapping consists of a
             * class that implements the interface SQLData and an entry in a
             * java.util.Map object.
             */
        case Types.DISTINCT:
            {
                logger.warn("JDBC type not implemented: {}", type);
                return null;
            }
        /**
             * The JDBC type STRUCT represents the SQL99 structured type. An SQL
             * structured type, which is defined by a user with a CREATE TYPE
             * statement, consists of one or more attributes. These attributes
             * may be any SQL data type, built-in or user-defined.
             *
             * The standard mapping for the SQL type STRUCT is to a Struct
             * object in the Java programming language. A Struct object contains
             * a value for each attribute of the STRUCT value it represents.
             *
             * A STRUCT value may optionally be custom mapped to a class in the
             * Java programming language, and each attribute in the STRUCT may
             * be mapped to a field in the class. A custom mapping consists of a
             * class that implements the interface SQLData and an entry in a
             * java.util.Map object.
             *
             *
             */
        case Types.STRUCT:
            {
                logger.warn("JDBC type not implemented: {}", type);
                return null;
            }
        case Types.REF:
            {
                logger.warn("JDBC type not implemented: {}", type);
                return null;
            }
        case Types.ROWID:
            {
                logger.warn("JDBC type not implemented: {}", type);
                return null;
            }
        default:
            {
                logger.warn("unknown JDBC type ignored: {}", type);
                return null;
            }
    }
    return null;
}

public void test_pg() throws Exception {
    PGExceptionSorter exSorter = new PGExceptionSorter();
    PSQLException ex = new PSQLException(GT.tr("Expected an EOF from server, got: {0}", new Integer(0)), PSQLState.COMMUNICATION_ERROR);
    Assert.assertTrue(exSorter.isExceptionFatal(ex));
}

protected void assertSQLCode(DataIntegrityViolationException dive, SQLCODE sqlCode) {
    Assertions.assertThat(dive.getCause()).isInstanceOf(PSQLException.class);
    Assertions.assertThat(((PSQLException) (dive.getCause())).getSQLState()).isEqualTo(sqlCode.getSqlCode());
}

@Test(expected = PSQLException.class)
public void testListWithInvalidEnums() throws Exception {
    final PreparedStatement ps = connection.prepareStatement("SELECT 'str' as str, ARRAY['VALUE_3'::enumeration]::enumeration[] as enum_arr");
    ps.executeQuery();
}

@Test(expected = PSQLException.class)
public void testListWithInvalidEnums() throws Exception {
    final PreparedStatement ps = connection.prepareStatement("SELECT 'str' as str, ARRAY['VALUE_3'::enumeration]::enumeration[] as enum_arr");
    ps.executeQuery();
}