Thread: Problem with COPY using PostgreSQL 9 and JDBC 4
Hi all,
I'm having some trouble using this command via JDBC. I have a
set of files with which I would like to populate database tables. If I
use the command, copy table from file with Delimiter ' ';, I get nothing
being added to the table should it be via JDBC but if I do it on the
command line I get all the results added as required.
I've tried to find if other people are having this problem and can't see
any problems related to the current version of PostgreSQL. Some of the
older versions I realised didn't support the copy command via JDBC but
as far as I can see version 9 does.
Could anyone shed some light on this as to what I am doing wrong?
Note: originally the files were binary and the query was complaining
about the file signature despite them being valid binary files. The
same command was used but with "with binary" being added to the query.
Any help would be much appreciated.
Regards,
Martin
What error messages are you seeing (either as JDBC exceptions or in the server logs)? --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
On Thu, Nov 25, 2010 at 8:00 AM, Martin Goodfellow <Martin.Goodfellow@cis.strath.ac.uk> wrote:
Hi all,
I'm having some trouble using this command via JDBC. I have a
set of files with which I would like to populate database tables. If I
use the command, copy table from file with Delimiter ' ';, I get nothing
being added to the table should it be via JDBC but if I do it on the
command line I get all the results added as required.
Are you actually trying to get postgres to read the data from a file on the db server's filesystem or are you trying to pass an inputstream/reader to the jdbc driver's CopyManager functionality in order to pass the data from the client to the db server? If the latter, you must use 'copy table from stdin' instead of 'copy table from file.' My code looks something like this:
StringBuilder sql = new StringBuilder();
sql.append("COPY ");
if (schema != null) {
sql.append(schema).append(".").append(tableName);
} else {
sql.append(tableName);
}
sql.append(" (").append(fields).append(")")
.append(" FROM stdin WITH CSV QUOTE AS ")
.append(StringUtils.isBlank(quote) ? "''''" : quote);
return ((PGConnection) con).getCopyAPI().copyIn(sql.toString(), this.fis);