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
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 |