Re: export CSV file through Java JDBC

At 07:20 AM 4/15/2008, you wrote:<br /><blockquote cite="" class="cite" type="cite">Date: Mon, 14 Apr 2008 09:41:41
-0400<br/> From: Emi Lu <emilu@encs.concordia.ca><br /> To: pgsql-sql@postgresql.org<br /> Subject: export CSV
filethrough Java JDBC<br /> Message-ID: <48035F15.4030302@encs.concordia.ca><br /><br /> Good morning,<br /><br
/>Running the following command from command line is ok, but cannot export <br /> a table into a csv file through java
JDBCcode.<br /><br /> Please help!<br /><br /><br /> JAVA code:<br /> ===================<br />     public static void
exec(Stringcommand)<br />     {<br />        try{<br />           Process p   = Runtime.getRuntime().exec(command);<br
/>          p.waitFor();<br />           p.destroy();<br />        }catch(Exception e) {<br />          
System.err.println("execcommand Error:  " + e.getMessage());<br />        }<br />     }<br /><br /><br /><br /> SQL
Command:<br/> =================<br /> psql -U username -d dbName -c  "\copy tableName to 'result.csv'  with CSV "<br
/><br/> When call exec(commands);<br /><br /> Nothing happens, result.csv was not created at all?<br /><br /> Thanks a
lot!</blockquote><br/> A couple of thoughts. First, you aren't passing the password in, so that seems like a problem.
Ofcourse, psql won't let you specify a password on the command line but last I looked you can set an ENV var before
runningpsql: "<font color="#2A00FF">PGPASSWORD=[your password here]"<br /><br /></font>Second, you don't specify a
server/port,which means your Pg server is localhost:5432?<br /><br /> Third, you are not specifying a path to pgsql, so
youhave to be sure that it can be found in the path. Now this can be tricky: your Java application may be running in a
contextDIFFERENT from your command prompt. The user/env your Java app is running in will determine what path vars are
availableto it - it may not be able to find psql. Try running "which psql > /tmp/which.txt" in your code above and
seewhat happens (assuming you're on a box with "which" installed).<br /><br /> Fourth (minor), you don't specify column
namesin your export which could result in variable results depending on the create statement - it's better to specify
toguarantee the same results every time.<br /><br /> Fifth, try capturing STDERR and STDOUT, so that if psql or command
shellgenerate errors you'll know what they are. Maybe Java gives you that in e.getMessage or maybe you need to put it
inyour psql command line.<br /><br /> I'm doing exactly the same thing you are doing but in Ruby/ActiveRecord so I know
thisworks. It works for me on Windows and Linux, fwiw.<br /><br /> I don't know enough Java to know if the command you
arerunning 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
thisenv var persists long enough so that when you run your psql command it's still in effect. For example this psuedo
codemight not work b/c two different child shells are run:<br /><br /> system.exec("export <font
color="#2A00FF">PGPASSWORD=pass1234");<br/></font>system.exec("psql my command here");<br /><br /> I think you want
somethingmore like this psuedo code:<br /><br /> system.set_environment("PGPASSWORD")="pass1234";<br />
system.exec("psqlmy command here");<br /><br /> I hope this helps,<br /><br /> Steve<br />