unload file dumping directory

[Former Member]

Hi

I am attempting to extract a table into csv format. I came across the unload statement in the documentation. The command appeared to be successful, but the file was not present.

$ cat unload_order.sh
/opt/sqlanywhere17/bin64s/dbisql -c "uid=sadb;pwd=sadb;eng=ftnode_sa;links=tcpip(host=10.11.12.17;port=49153);LOG=conn.log;dbn=ftnode_sa" "UNLOAD TABLE t3 to 't3_file.txt'"
$ ./unload_order.sh 
63 row(s) affected
$ ls -ltr t3_file.txt
ls: cannot access 't3_file.txt': No such file or directory

I was expecting to view the file in the same directory as where the script is executed, but there is no such file. What is the default directory that unload uses?

[justin_willey]

By default UNLOAD TABLE will try to write to a file on the database server (the file location is relative to the database server's starting directory) so rights may be an issue here.

For a client side unload you can use INTO CLIENT FILE if you have set the necessary database permissions - see the documentation on the UNLOAD statement for details.

An alternative is to use the dbisql OUTPUT TO command;

[Former Member]

thanks, that worked nicely, after running

set option   allow_write_client_file ='On'

first.

Is there a way to specify column headers as part of the csv output?

[justin_willey]

There's no built in functionality in that format, but you could do:

SELECT 'ColA','ColB', .... from dummy
  UNION 
SELECT .... your select statement

I forgot the WITH COLUMN NAMES functionality that came in v12 (that was a while ago 🙂 Thank you Breck for the correction! However that only applies to OUTPUT, with UNLOAD you're stuck with the UNION approach.

[Breck_Carter]

SELECT 'Hello' AS column_1, 'World' AS column_2;

OUTPUT TO 'c:\\\\temp\\\\local.txt' FORMAT TEXT WITH COLUMN NAMES;

'column_1','column_2'
'Hello','World'


SELECT row_num AS column_1,
       2 * row_num AS column_2
  FROM RowGenerator
 WHERE row_num <= 10
 ORDER BY row_num;

OUTPUT TO 'c:\\\\temp\\\\local.txt' FORMAT TEXT WITH COLUMN NAMES;

'column_1','column_2'
1,2
2,4
3,6
4,8
5,10
6,12
7,14
8,16
9,18
10,20

[Former Member]

ok, thanks. Is there a way to output without single quotes around varchar data ?

[VolkerBarth]

Add the QUOTE '' clause, i.e. with an empty string.