Re: Parameters are not being setted in a copy statement
| От | David Johnston |
|---|---|
| Тема | Re: Parameters are not being setted in a copy statement |
| Дата | |
| Msg-id | 034201cdac93$b68bd600$23a38200$@yahoo.com обсуждение исходный текст |
| Ответ на | Parameters are not being setted in a copy statement (Martin <cmmayor@gmail.com>) |
| Список | pgsql-jdbc |
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Martin
Sent: Wednesday, October 17, 2012 11:52 AM
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Parameters are not being setted in a copy statement
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
I am pretty sure this is PostgreSQL’s limitation and not JDBC but my quick search for confirmation in the documentation (under COPY, PREPARE, or EXECUTE) did not turn up anything.
Much like “CREATE TABLE” and similar statements do not provide for “parameters” the COPY command does not at well. Thus in order to do what you want you need to build a non-parameterized (i.e., no “?”) statement and execute that as-is; making sure you deal with SQL-injection attack vectors.
My personal option would be to create a function to encapsulate the desired query:
“COPY (SELECT * FROM some_function(“ & param_1_value & “ , ” & param_2_value & “ ) TO …”
Making sure the values of the parameters can be safely inserted as literals into the query (including adding single-quotes if necessary).
David J.
В списке pgsql-jdbc по дате отправления: