Postgres JDBC read TEXT column written by Hibernate-CodePudding

I've got to migrate a Postgres database with Flyway. One of the table contains a TEXT column that contains XML data written by by Hibernate. Since you've only a java.sql.Connection at hand when you're in Flyway, you can only use plain jdbc.

Here you see a snippet which tries to read the data:

    String query = "SELECT calc_proc_id, calc_key, parameters AS parameters FROM calculation WHERE parameters_json IS NULL";
    int counter = 0;
    try (Statement stmt = connection.createStatement())
    {
        try (ResultSet rs = stmt.executeQuery(query))
        {
            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            int columnType = resultSetMetaData.getColumnType(3);
            String columnTypeName = resultSetMetaData.getColumnTypeName(3);
            String columnName = resultSetMetaData.getColumnName(3);
            logger.info("%s has %d (%s)", columnName, columnType, columnTypeName);
            while (rs.next())
            {
                UUID calcProcId = rs.getObject("calc_proc_id", UUID.class);
                String calcKey = rs.getString("calc_key");
                long oid = rs.getLong("parameters");
                try (LargeObject large = lom.open(oid, LargeObjectManager.READ))

When reaching this open, I get the following exception:

org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:337) ~[postgresql-42.2.9.jar:42.2.9]
    at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:829) ~[postgresql-42.2.9.jar:42.2.9]
    at org.postgresql.jdbc.PgConnection.rollback(PgConnection.java:872) ~[postgresql-42.2.9.jar:42.2.9]
    at org.flywaydb.core.internal.jdbc.TransactionTemplate.execute(TransactionTemplate.java:90) ~[flyway-core-6.0.8.jar:?]
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:279) ~[flyway-core-6.0.8.jar:?]
    at org.flywaydb.core.internal.command.DbMigrate.migrateGroup(DbMigrate.java:244) ~[flyway-core-6.0.8.jar:?]
    at org.flywaydb.core.internal.command.DbMigrate.access$100(DbMigrate.java:54) ~[flyway-core-6.0.8.jar:?]
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:162) ~[flyway-core-6.0.8.jar:?]
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:159) ~[flyway-core-6.0.8.jar:?]
    at org.flywaydb.core.internal.database.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:71) ~[flyway-core-6.0.8.jar:?]
...
Caused by: java.io.IOException: Unexpected packet type: 0
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2390) ~[postgresql-42.2.9.jar:42.2.9]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310) ~[postgresql-42.2.9.jar:42.2.9]
    ... 147 more

Has anybody seen this before and knows how to solve that?

CodePudding user response：

I haven't seen this error before, but as far as I understand, you should be able to read the TEXT column by simply calling getClob("parameters")