Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id B09FA2E0030 for ; Tue, 15 Apr 2008 14:19:44 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 94229-02 for ; Tue, 15 Apr 2008 14:19:35 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from starfish.geekisp.com (mail.geekisp.com [216.168.135.169]) by developer.postgresql.org (Postfix) with ESMTP id 9F6872E002E for ; Tue, 15 Apr 2008 14:19:36 -0300 (ADT) Received: (qmail 27351 invoked by uid 1003); 15 Apr 2008 17:19:32 -0000 Received: from adsl-76-253-94-54.dsl.pltn13.sbcglobal.net (HELO dino-1.misuse.org) (public@misuse.org@76.253.94.54) by mail.geekisp.com with (DHE-RSA-AES256-SHA encrypted) SMTP; 15 Apr 2008 17:19:32 -0000 X-Mailer: QUALCOMM Windows Eudora Version 7.1.0.9 Date: Tue, 15 Apr 2008 10:13:46 -0700 To: pgsql-sql@postgresql.org From: Steve Midgley Subject: Re: export CSV file through Java JDBC In-Reply-To: <20080415142023.8115B8F2133@maia-1.hub.org> References: <20080415142023.8115B8F2133@maia-1.hub.org> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=====================_2228046==.ALT" Message-Id: <20080415171936.9F6872E002E@developer.postgresql.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=-0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/85 X-Sequence-Number: 30624 --=====================_2228046==.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed At 07:20 AM 4/15/2008, you wrote: >Date: Mon, 14 Apr 2008 09:41:41 -0400 >From: Emi Lu >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 --=====================_2228046==.ALT Content-Type: text/html; charset="us-ascii" 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
--=====================_2228046==.ALT--