我想按日期导出表中的数据,因此我使用jdbc编写了一个Java程序。
public void exportData(Connection conn,String filename) {
Statement stmt;
String query;
try {
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
SimpleDateFormat dateFormat = new SimpleDateFormat ("yyyy-MM-dd hh:mm:ss");
Date startDate = dateFormat.parse("2014-08-21 00:00:00");
Date endDate = dateFormat.parse("2014-08-22 00:00:00");
Calendar startCalendar = Calendar.getInstance();
startCalendar.setTime(startDate);
Calendar endCalendar = Calendar.getInstance();
endCalendar.setTime(endDate);
Calendar thisDayCalendar = startCalendar;
while(!thisDayCalendar.after(endCalendar)){
Date thisDayDate = thisDayCalendar.getTime();
thisDayCalendar.add(Calendar.DATE, 1);
Date nextDayDate = thisDayCalendar.getTime();
String thisDayString = dateFormat.format(thisDayDate);
String nextDayString = dateFormat.format(nextDayDate);
SimpleDateFormat dateFormat1 = new SimpleDateFormat ("yyyy-MM-dd");
String fileDateString = dateFormat1.format(thisDayDate);
//For comma separated file
query ="COPY (SELECT * FROM signals WHERE date_time >= '" + thisDayString +
"' AND date_time < '" +nextDayString + "' ) TO " +filename + fileDateString + ".csv delimiter ','";
stmt.executeQuery(query);
//System.out.println(query);
}
} catch(Exception e) {
e.printStackTrace();
stmt = null;
}
}
该程序在服务器上运行,而数据库又是另一个服务器,我在该程序中连接到数据库。但是,当我在服务器上运行该程序时,出现了一个错误,只有超级用户才能从文件中复制或复制文件:
org.postgresql.util.PSQLException: ERROR: must be superuser to COPY to or from a file
Hint: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
at DBase.exportData(ExportSignal.java:85)
at ExportSignal.main(ExportSignal.java:23)
我在线搜索了解决方案,并尝试使用psql'\ copy'命令,但是在编译时,另一个错误提示'\'是Java中的无效转义符,因此我写了“双斜杠COPY”,这在运行程序时出现语法错误。
query ="\COPY (SELECT * FROM signals WHERE date_time >= '" + thisDayString +
"' AND date_time < '" +nextDayString + "' ) TO " +filename + fileDateString + ".csv delimiter ','";
有谁知道如何使程序执行或解决“ \ copy”中无效转义符的问题?
错误显示错误:必须是超级用户才能复制文件或从文件复制
似乎当前用户没有足够的权限来写入文件。为用户提供对写入位置的权限,或从具有足够特权来写入文件的用户帐户执行程序。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句