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");
"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