pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedexport CSV file through Java JDBC
2+ messages / 2 participants
[nested] [flat]
* export CSV file through Java JDBC
@ 2008-04-14 13:41 Emi Lu <emilu@encs.concordia.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Emi Lu @ 2008-04-14 13:41 UTC (permalink / raw)
To: pgsql-sql
Good morning,
Running the following command from command line is ok, but cannot export
a table into a csv file through java JDBC code.
Please help!
JAVA code:
===================
public static void exec(String command)
{
try{
Process p = Runtime.getRuntime().exec(command);
p.waitFor();
p.destroy();
}catch(Exception e) {
System.err.println("exec command Error: " + e.getMessage());
}
}
SQL Command:
=================
psql -U username -d dbName -c "\copy tableName to 'result.csv' with CSV "
When call exec(commands);
Nothing happens, result.csv was not created at all?
Thanks a lot!
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: export CSV file through Java JDBC
@ 2008-04-15 17:13 Steve Midgley <public@misuse.org>
0 siblings, 0 replies; 2+ messages in thread
From: Steve Midgley @ 2008-04-15 17:13 UTC (permalink / raw)
To: pgsql-sql
At 07:20 AM 4/15/2008, you wrote:
>Date: Mon, 14 Apr 2008 09:41:41 -0400
>From: Emi Lu <emilu@encs.concordia.ca>
>To: pgsql-sql@postgresql.org
>Subject: export CSV file through Java JDBC
>Message-ID: <48035F15.4030302@encs.concordia.ca>
>
>Good morning,
>
>Running the following command from command line is ok, but cannot
>export
>a table into a csv file through java JDBC code.
>
>Please help!
>
>
>JAVA code:
>===================
> public static void exec(String command)
> {
> try{
> Process p = Runtime.getRuntime().exec(command);
> p.waitFor();
> p.destroy();
> }catch(Exception e) {
> System.err.println("exec command Error: " +
> e.getMessage());
> }
> }
>
>
>
>SQL Command:
>=================
>psql -U username -d dbName -c "\copy tableName to 'result.csv' with
>CSV "
>
>When call exec(commands);
>
>Nothing happens, result.csv was not created at all?
>
>Thanks a lot!
A couple of thoughts. First, you aren't passing the password in, so
that seems like a problem. Of course, psql won't let you specify a
password on the command line but last I looked you can set an ENV var
before running psql: "PGPASSWORD=[your password here]"
Second, you don't specify a server/port, which means your Pg server is
localhost:5432?
Third, you are not specifying a path to pgsql, so you have to be sure
that it can be found in the path. Now this can be tricky: your Java
application may be running in a context DIFFERENT from your command
prompt. The user/env your Java app is running in will determine what
path vars are available to it - it may not be able to find psql. Try
running "which psql > /tmp/which.txt" in your code above and see what
happens (assuming you're on a box with "which" installed).
Fourth (minor), you don't specify column names in your export which
could result in variable results depending on the create statement -
it's better to specify to guarantee the same results every time.
Fifth, try capturing STDERR and STDOUT, so that if psql or command
shell generate errors you'll know what they are. Maybe Java gives you
that in e.getMessage or maybe you need to put it in your psql command
line.
I'm doing exactly the same thing you are doing but in Ruby/ActiveRecord
so I know this works. It works for me on Windows and Linux, fwiw.
I don't know enough Java to know if the command you are running is the
standard "shell execute" command in Java. If it's not, that's what you
want so change your code that way. You just want java to shell out to
the OS command processor. Be sure when you set your command shell env
var, that this env var persists long enough so that when you run your
psql command it's still in effect. For example this psuedo code might
not work b/c two different child shells are run:
system.exec("export PGPASSWORD=pass1234");
system.exec("psql my command here");
I think you want something more like this psuedo code:
system.set_environment("PGPASSWORD")="pass1234";
system.exec("psql my command here");
I hope this helps,
Steve
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-04-15 17:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-14 13:41 export CSV file through Java JDBC Emi Lu <emilu@encs.concordia.ca>
2008-04-15 17:13 Re: export CSV file through Java JDBC Steve Midgley <public@misuse.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox