Parameters are not being setted in a copy statement

From: Martin <cmmayor(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Parameters are not being setted in a copy statement
Date: 2012-10-17 15:52:25
Message-ID: CAH0TwOtzxNzqLgHQS3CMM8EamG-Udtt5f+gEAhUKEc0auXt6PQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi all

We need to copy from an arbitray query to a csv. We are using Postgresql
9.1 an we would use the copy statement
We need to trigger this query from a java application. We are using
Hibernate but you could find the problem to jdbc level

It's work with query with no parameters but if you try to set parameters
you get a * there is no parameter $1 Error*

Do you know if this is a bug and if there is a workarround to lead with
this?
I wirite a test to show this

SQL :
create schema test;
create table test.test_copy (
id serial primary key
,data1 varchar( 100)
,data2 varchar( 100)
);

insert into test.test_copy(data1,data2) values
('test1','test2');
insert into test.test_copy(data1,data2) values
('test3','test4');
insert into test.test_copy(data1,data2) values
('test5','test6');
insert into test.test_copy(data1,data2) values
('test7','test8');

Java test

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

import org.testng.annotations.Test;

public class PostgressTest {

private String DOES_NOT_WORK = "COPY (SELECT * " +
"FROM test.test_copy t "+
"WHERE data1 like (?) OR data2 ilike (?) " +
") TO '/data/export/fail.csv' WITH CSV HEADER DELIMITER ';'";

private String WORK = "COPY (SELECT * " +
"FROM test.test_copy t "+
"WHERE data1 like ('test1') OR data2 ilike ('test4') " +
") TO '/data/export/work.csv' WITH CSV HEADER DELIMITER ';'";
private Connection connection;

public PostgressTest() throws Exception {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection("jdbc:postgresql://
127.0.0.1:5432/test", "my_user",
"my_pass");
}
@Test
public void testDoesNotWork() throws Exception {

PreparedStatement statement = connection
.prepareStatement(DOES_NOT_WORK);
statement.setString(1, "test1");
statement.setString(2, "test4");
statement.execute();
}
@Test
public void testWork() throws Exception {

PreparedStatement statement = connection
.prepareStatement(WORK);
statement.execute();
}
}

Thanks!!
Martin

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message David Johnston 2012-10-17 18:17:47 Re: Parameters are not being setted in a copy statement
Previous Message Luis Flores 2012-10-17 11:35:57 Re: bug report: slow getColumnTypeName