1

I'm using postgresql to host my database. In my database, I have a table say xyz whose structure is as shown below

id    content    folder
1     hello      Inbox
2     hi         Sent

I want to export this table to CSV using my java program. The code snippet is below

Connection connection2  = new ServerDBConnect().getConnection();
PreparedStatement statement = connection2.prepareStatement("copy (SELECT * FROM xyz WHERE folder=? ) to 'C:/export.csv' delimiter ','");
statement.setString(1, FOLDER_SELECTED); //Here, FOLDER_SELECTED=Inbox
statement.execute();

When I execute this code, I'm getting SQLException saying

ERROR: there is no parameter $1

If I execute the code without folder specified ("copy (SELECT * FROM xyz) to 'C:/export.csv' delimiter ','")), the code works fine.

What am I doing wrong here? How to go about this problem?

Note: If I execute the query (copy (SELECT * FROM xyz WHERE folder='Inbox' ORDER BY time) to 'G:/export.csv' delimiter ',') directly in the Postgresql SQL console, I'm getting the desired output.

Please help

Sunil Kumar B M
  • 2,735
  • 1
  • 24
  • 31
  • Not sure if this question is relevant to you: http://stackoverflow.com/questions/5726362/does-postgresql-jdbc-driver-has-copy – Andrea Polci Mar 02 '12 at 09:35
  • @AndreaPolci: thanks, but that link is of no relevance to me. I'm trying to export the content of a table in my local database to my local system – Sunil Kumar B M Mar 02 '12 at 09:42
  • Looks relevant to me. The link is to a question asking how to use `copy` from JDBC. – skaffman Mar 02 '12 at 11:49
  • @skaffman: Using `copy` is not a problem to me, but I'm not able to specify the filter (`folder='Inbox'`) in the query – Sunil Kumar B M Mar 02 '12 at 12:05
  • You are not really using a sql statement in the prepared statement, you are using some psql command. I'm surprised it even works without a bind. I believe the proper way to do it is with CopyManager as shown in this link http://stackoverflow.com/questions/6958965/how-to-copy-a-data-from-file-to-postgresql-using-jdbc (and I think the other comments are relevant :) – Glenn Mar 04 '12 at 03:27

1 Answers1

2

Ah

I finally found the answer myself.

Small change in the query gave me the desired result

The query is suppose to be like this

Connection connection2  = new ServerDBConnect().getConnection();
PreparedStatement statement = connection2.prepareStatement("copy (SELECT * FROM xyz WHERE folder='" + FOLDER_SELECTED + "' ) to 'C:/export.csv' delimiter ','");

This was driving me crazy, but finally done :-)

Sunil Kumar B M
  • 2,735
  • 1
  • 24
  • 31
  • I know this is an old question, but for future readers: Only do this if `FOLDER_SELECTED` comes from a trusted source. Otherwise, you will be vulnerable to SQL injection. – Joao Delgado Mar 14 '18 at 20:59