COPY command works with older driver but not newer one ? - Mailing list pgsql-jdbc
From | Warren Bell |
---|---|
Subject | COPY command works with older driver but not newer one ? |
Date | |
Msg-id | 496A4A12.5090004@clarksnutrition.com Whole thread Raw |
Responses |
Re: COPY command works with older driver but not newer one
?
|
List | pgsql-jdbc |
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)
pgsql-jdbc by date: