1

So I've been working with postgre databases and I noticed that it throws an error sometimes when I try to read a csv file and copy to a table remotely. It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

Error occurs in this line:

st.execute("COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ',';");

Here's where I connect to database:

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");

I read online that maybe STDIN can possibly help me but I haven't been able to get it to work and most examples online are using pqsl. Is there a way I can get tthis to work or do I need to go back to buffered readers?

UPDATE:

So I've tried using the CopyManager API but it has an error when trying to make a connection using DBUtil as it is not recognized even though I downloaded the jar file from this website: http://commons.apache.org/proper/commons-dbutils/download_dbutils.cgi

Code:

    Connection connection = DBUtil.getConnection("POSTGRES");
    String sql = "COPY inputdata FROM 'C:\\Users\\JAMES\\Downloads\\V24_Mike_110217_RemovedReturnTrips\\RyderCombiner\\AllNonMatchingWithColNames.csv' CSV HEADER DELIMITER ','";
    CopyManager copyManager = new CopyManager((BaseConnection)connection); 
    copyManager.copyIn(sql);

I get the error in the first line with DBUtil...is importing the jar file the only way to make jdbc recognize this variable?

UPDATE2:

Thanks again guys I ended up getting the copyin to work by following the given link and a few adjustments.

public static void readInputData(String inputDataFile, Statement st) throws FileNotFoundException, IOException, SQLException {
    Connection connection = DriverManager.getConnection("jdbc:postgresql://192.168.1.15:5432/postgres", "postgres", "pass");
    String sql = "COPY INPUTDATA FROM stdin CSV HEADER DELIMITER ','";
    BaseConnection pgcon = (BaseConnection)connection;
    CopyManager mgr = new CopyManager(pgcon);
    try {
        Reader in = new BufferedReader(new FileReader(new File(inputDataFile)));
        long rowsaffected  = mgr.copyIn(sql, in);
    } catch (SQLException ex) {
        System.err.println(ex.getClass().getName() + ": " + ex.getMessage());
        System.exit(0);
    }
}
  • copy refers to a file on the server. If you want to use the copy functionality through JDBC you need to use the CopyManager API of the JDBC driver: stackoverflow.com/a/46991315/330315 – a_horse_with_no_name Nov 19 '17 at 7:22
  • mmhmm...I've been trying to get the CopyManager API to work but it has a problem with recognizing DBUtil even though I downloaded and added the jar file to my library. – JCole Nov 19 '17 at 18:26
  • You should use the answer in a @a_horse_with_no_name's link as a template, you're not using copymanager right. You're call to copyIn lacks a BufferedReader object – Evan Carroll Nov 19 '17 at 18:41
  • Following that template I was able to get it working perfectly. Now I'm having an issue writing that same csv file to a txt file on the client computer, it seems neither copyin or copyout are giving me what I want. – JCole Nov 20 '17 at 17:17
  • Figured it out, instead of a buffered reader you have to use filewrite instead with CopyOut and then yuo can copy onto a local file thats not on the server – JCole Nov 21 '17 at 0:30
0

It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get i to work using the databases COPY FROM method. It works perfectly on the computer where the database is located but my other computer gets an error.

No you have to use a BufferedReader, or InputStream

  1. COPY opens a file handle on the server. It just needs a path, because it can open and manage its own file handle, and that's more efficient.
  2. For client-side COPY, you have to use libpq PQputCopyData. Internally, client-side COPY is COPY FROM STDIN. The client has to send the data. This is what psql's \COPY does internally. In Java, the function that calls PQputCopyData is copyIn which requires a BufferedReader, or InputStream.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.