JDBC COPY with ant

Question

I have a project with Spring, Hibernate and PostgreSQL and have to use ANT to create schema with data:

        <sql driver="org.postgresql.Driver"
            classpath="src/main/webapp/WEB-INF/lib/postgresql-9.1-901.jdbc4.jar"
            url="jdbc:postgresql://localhost:5433/postgres"
            userid="postgres"
            password="pw123"
            autocommit="true"
            src="src/main/sql/dbbackup.sql">
        </sql>

but I get this error:

C:\Users\<user>\<workspace>\<Project>\antdb.xml:22: org.postgresql.util.PSQLException: ERROR: COPY from stdin failed: The JDBC driver currently does not support COPY operations.

Don't know if somehow we could use postgresql.copy class here?

Answer 1

PgJDBC doesn't support COPY directly, but it does via the CopyManager API you can get from the PGConnection interface of the java.sql.Connection returned by PgJDBC.

Unfortunately, you can't use that from a plain SQL file where you mix COPY operations in with other commands.

Personally, I'd shell out to psql to run .sql files using the Ant <exec> task. That way you can include COPY data in-line in your SQL files.

It'd be nice to enable PgJDBC to handle COPY, but it's not easy. It's effectively a different protocol mode in PostgreSQL, and it doesn't make much sense to use the usual JDBC interfaces with prepared statements, execute, etc, for it. We could provide an execSQLScript on the custom PGconnection but that wouldn't help you out much because things like Ant's <sql> task wouldn't use it. You'd have to write a custom task.

Instead, PgJDBC would have to pretty much lie to clients - when it entered COPY mode after a COPY command, it'd have to ignore the JDBC spec and not really do what it was supposed to in response to JDBC statement executes. This would be likely to break all sorts of things.

So - for now, by far the easiest option is to just exec the psql command to do what you want.