COPY command works with older driver but not newer one ?

From: Warren Bell <warren(at)clarksnutrition(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: COPY command works with older driver but not newer one ?
Date: 2009-01-11 19:35:46
Message-ID: 496A4A12.5090004@clarksnutrition.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)postgresql(dot)Driver(at)eec612]
19:18:01.025 (1) simple execute,
handler=org(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)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)

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2009-01-11 21:22:24 Re: COPY command works with older driver but not newer one ?
Previous Message Peter Eisentraut 2009-01-08 08:58:22 Re: German translation fixes