Обсуждение: COPY command works with older driver but not newer one ?

I have a COPY command that only works with 7.1.2 JDBC2 (jdbc7.1-1.2.jar)
and not 8.3 JDBC3 with SSL (build 603) (postgresql-8.3-603.jdbc3.jar).
Here is my test code and my debug and error messages are below it.
Database version is 8.1. import is a table with several char fields,
test.txt is a tab delimited file with the correct permissions on it and
the connection to the db is being made with the postgres superuser. What
do I need to do to get the newer driver to work?

Thanks,

Warren

Test code:

        try
        {
            Class.forName("org.postgresql.Driver");
            // Set for newer driver

//org.postgresql.Driver.setLogLevel(org.postgresql.Driver.DEBUG);
            System.out.println("version" +
org.postgresql.Driver.getVersion());
        }
        catch(Exception cnfe)
        {
            cnfe.printStackTrace();
        }
        try
        {
            java.sql.Connection conn =

java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/dbname?user=user&password=password&compatible=8.1");
            java.sql.PreparedStatement stmt =
conn.prepareStatement("COPY import FROM ?");
            stmt.setString(1, "/Users/test.txt");
            if(stmt.execute())
            {
                System.out.println("It worked");
            }
            stmt.close();
            conn.close();
        }
        catch(java.sql.SQLException e)
        {
            System.err.println("Error " + e.getErrorCode() + " (SQLSTATE
" + e.getSQLState() + "): " + e.getMessage());
            e.printStackTrace();
        }


Error and Debug Messsages:

versionPostgreSQL 8.3 JDBC3 with SSL (build 603)
19:18:00.947 (1) PostgreSQL 8.3 JDBC3 with SSL (build 603)
19:18:00.954 (1) Trying to establish a protocol version 3 connection to
localhost:5432
19:18:00.966 (1)  FE=> StartupPacket(user=postgres, database=clark,
client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
19:18:00.972 (1)  <=BE AuthenticationOk
19:18:00.984 (1)  <=BE ParameterStatus(client_encoding = UNICODE)
19:18:00.984 (1)  <=BE ParameterStatus(DateStyle = ISO, MDY)
19:18:00.984 (1)  <=BE ParameterStatus(integer_datetimes = off)
19:18:00.984 (1)  <=BE ParameterStatus(is_superuser = on)
19:18:00.984 (1)  <=BE ParameterStatus(server_encoding = SQL_ASCII)
19:18:00.985 (1)  <=BE ParameterStatus(server_version = 8.3.3)
19:18:00.985 (1)  <=BE ParameterStatus(session_authorization = postgres)
19:18:00.985 (1)  <=BE ParameterStatus(standard_conforming_strings = on)
19:18:00.985 (1)  <=BE ParameterStatus(TimeZone = US/Pacific)
19:18:00.985 (1)  <=BE BackendKeyData(pid=1420,ckey=952422623)
19:18:00.985 (1)  <=BE ReadyForQuery(I)
19:18:00.985 (1)     compatible = 8.1
19:18:00.985 (1)     loglevel = 2
19:18:00.985 (1)     prepare threshold = 5
getConnection returning
driver[className=org.postgresql.Driver,org.postgresql.Driver@eec612]
19:18:01.025 (1) simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@c56c60,
maxRows=0, fetchSize=0, flags=17
19:18:01.025 (1)  FE=> Parse(stmt=null,query="COPY import FROM
$1",oids={1043})
19:18:01.029 (1)  FE=>
Bind(stmt=null,portal=null,$1=</Users/Warren/Desktop/test.txt>)
19:18:01.034 (1)  FE=> Describe(portal=null)
19:18:01.034 (1)  FE=> Execute(portal=null,limit=0)
19:18:01.034 (1)  FE=> Sync
19:18:01.048 (1)  <=BE ErrorMessage(ERROR: syntax error at or near "$1"
  Position: 18)
org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 18
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
    at TestApostrophe.main(TestApostrophe.java:43)
SQLException: SQLState(42601)
19:18:01.050 (1)  <=BE ReadyForQuery(I)
Error 0 (SQLSTATE 42601): ERROR: syntax error at or near "$1"
  Position: 18
org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 18
    at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)
    at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:343)
    at TestApostrophe.main(TestApostrophe.java:43)

Warren Bell wrote:
> I have a COPY command that only works with 7.1.2 JDBC2 (jdbc7.1-1.2.jar)
> and not 8.3 JDBC3 with SSL (build 603) (postgresql-8.3-603.jdbc3.jar).
> Here is my test code and my debug and error messages are below it.
> Database version is 8.1. import is a table with several char fields,
> test.txt is a tab delimited file with the correct permissions on it and
> the connection to the db is being made with the postgres superuser. What
> do I need to do to get the newer driver to work?

You will need to synthesize the complete query string yourself instead
of using a parameter placeholder, as it appears that the server doesn't
support a parameter placeholder as the filename argument to COPY FROM.
This is similar to how you can't use parameter placeholders for table
names, etc - the driver is limited by where the server's query parser
will accept a $1, $2, etc in the query string.

Though I'm not sure why the server doesn't allow this case, it seems
like the argument to COPY FROM is data, not structural. Perhaps the FROM
STDIN case is the problem.

-O

> 19:18:01.025 (1)  FE=> Parse(stmt=null,query="COPY import FROM $1",oids={1043})
> 19:18:01.029 (1)  FE=> Bind(stmt=null,portal=null,$1=</Users/Warren/Desktop/test.txt>)

> 19:18:01.048 (1)  <=BE ErrorMessage(ERROR: syntax error at or near "$1"

When you say server I am assuming you are talking about the database, if
so, I am only talking about one 8.1 database. The older driver works on
8.1 and the newer driver does not work on 8.1. I also tried the same
scenario on a PostgresPlus 8.3 database on an Apple with the same
results, the older driver worked and the newer one does not. Why does it
work with the older driver and not the new one? Is this a capability
that was changed between drivers?

Oliver Jowett wrote:

You will need to synthesize the complete query string yourself instead
of using a parameter placeholder, as it appears that the server doesn't
support a parameter placeholder as the filename argument to COPY FROM.
This is similar to how you can't use parameter placeholders for table
names, etc - the driver is limited by where the server's query parser
will accept a $1, $2, etc in the query string.

Though I'm not sure why the server doesn't allow this case, it seems
like the argument to COPY FROM is data, not structural. Perhaps the FROM
STDIN case is the problem.

-O


    19:18:01.025 (1)  FE=> Parse(stmt=null,query="COPY import FROM
$1",oids={1043})
    19:18:01.029 (1)  FE=>
Bind(stmt=null,portal=null,$1=</Users/Warren/Desktop/test.txt>)

    19:18:01.048 (1)  <=BE ErrorMessage(ERROR: syntax error at or near "$1"

Warren Bell wrote:

> When you say server I am assuming you are talking about the database,

I mean the database server processes, yes.

> Why does it
> work with the older driver and not the new one?

Because the newer driver (here, "newer" means something like "anything
more recent than 2005") uses a newer version of the wire protocol and
passes parameter values out-of-line from the query string, rather than
substituting parameter values into the query string itself.

-O