1

I am very new to Apache Derby, and I have a need to export all of the rows from a table into a csv file.

I am using RazorSQL to connect to the database.

What I need to know is if there is an equivalent Derby SQL command to what MySQL does to export data below.

NOTE: VERY IMPORTANT. One of the columns in this database table is defined as an XML datatype. It will NOT work using any kind of wizard to export the data, because the select statement needs to use the 'xmlserialize' statement in the SQL command.

Here is what I want to do, but Derby does not understand the "INTO OUTFILE" statement I am used to using with MySQL.

select
message_id,
msg_schema_id,
user_id,
req_send_time,
destination,
xmlserialize(msg_content as clob) as message,
facility_id
from Audit
into outfile 'd:\csv\audit.csv'
fields terminated by ','
enclosed by '"'
lines terminated by '\n';
Net Admin
  • 181
  • 1
  • 2
  • 9
  • 2
    Have you tried using `SYSCS_UTIL.SYSCS_EXPORT_QUERY`? – Shadow May 06 '17 at 14:30
  • Hi Shadow, yes. I have tried that. Because of the one XML column data type, I get an error on the select statement. At least, I am guessing that is why I get "select" error message. But then again, I am NOT very well versed in using that ij interface. – Net Admin May 06 '17 at 16:20
  • Shadow, I should have included this in my comment. My apologies. Here is the error message I get when trying to run the ij export utility: ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null,'AUDIT','/root/audit-out.csv',null,null,null); ERROR 38000: The exception 'java.sql.SQLException: XML values are not allowed in top-level result sets; try using XMLSERIALIZE.' was thrown while evaluating an expression. ERROR 42Z71: XML values are not allowed in top-level result sets; try using XMLSERIALIZE. ij> – Net Admin May 06 '17 at 19:43

0 Answers0