Using v17.0.10.5923, I am trying to create a simple way of unloading a number of (unspecified) tables to JSON. The FOR JSON AUTO option is ideal for the purpose. The one problem I have is that I want to use QUOTES OFF (or QUOTE ''), otherwise UNLOAD puts single quotes round the data.

When I do this, the commas between the JSON data value pairs are escaped as \x2c, rendering the JSON invalid. If I use ESCAPES OFF, this doesn't happen, but then things that do need escaping, aren't!

The problem doesn't seem to specifically relate to JSON data, as it can be illustrated with two very simple examples:

create variable blah2 long varchar;
set blah2 = '"Jane Doe","Town"';


unload select blah2 to 'c:\\tmp\\junk26.txt' quotes off;


"Jane Doe"\x2c"Town"


unload select blah2 to 'c:\\tmp\\junk27.txt'


'"Jane Doe","Town"'

Using xp_write_file() works, but has numerous disadvantages compared with UNLOAD (client side files, character set control etc). Using iSQL and Save to File doesn't have the problem.

As a work-around I could strip out the excess quotes from the resulting file, but that makes a very simple process rather messier.

asked 20 Aug '20, 08:06

Justin%20Willey's gravatar image

Justin Willey
accept rate: 20%

edited 20 Aug '20, 08:08

The default column delimiter is a comma so if a value contains a comma it must be escaped. UNLOAD tries to ensure (the best it can) that the data you UNLOAD can be given to a LOAD with the amse options. Without escaping the comma, your output without quotes ("Jane Doe","Town") would represent two values if you attempted to LOAD it with the same options. ESCAPES OFF will likely give you what you want. Or, possibly, set your column delimiter to something that doesn't appear in your data.

permanent link

answered 20 Aug '20, 08:24

John%20Smirnios's gravatar image

John Smirnios
accept rate: 38%

Of course - my commas are inside the values as far as SQLA is concerned.

unload select blah2 to 'c:\\tmp\\junk26.txt' quotes off delimited by ''  ;

works fine.(Interestingly using the COLUMN DELIMITED BY variation gives "Syntax error near 'COLUMN'")

(20 Aug '20, 08:54) Justin Willey
Replies hidden

The doc does appear to be incorrect. IIRC, the syntax has always been just "DELIMITED BY" and that syntax predated the introduction of custom row delimiters that were set via "ROW DELIMITED BY".

(20 Aug '20, 09:12) John Smirnios

Like me, you probably prefer the old dcx. The SAP help system contains the correction (dcx is no longer updated):

(20 Aug '20, 09:17) John Smirnios

Of course for those like you and me, a DCX comment that COLUMN DELIMITED BY is not supported would be worthwhile :)

(20 Aug '20, 09:28) Volker Barth

> dcx is no longer updated

FWIW dcx is handy for creating A links to old doc topics (V10, etc), so even though it's not being updated, I hope the website remains up.

But... it's up to everyone to preserve everything they own since there is no guarantee cloud-based data won't disappear.

(20 Aug '20, 11:10) Breck Carter

As they say: The Cloud - other people's computers :)

(21 Aug '20, 06:16) Justin Willey

Many thanks John

(21 Aug '20, 06:17) Justin Willey

So how do I backup this Forum? :)

(21 Aug '20, 06:34) Volker Barth

There is a comment that COLUMN is not correct, added by yours truly, on 2016/10/19 13:44.

(21 Aug '20, 11:26) JBSchueler

Thanks Jack – apparently I had a look at v16 DCX, probably because I prefer even older docs :)

(21 Aug '20, 11:40) Volker Barth

(21 Aug '20, 13:36) Breck Carter

My UNLOAD DATABASE TO JSON now works fine - except when I hit a big table. It seems (unsurpringly) that 1GB of data in a table turns into rather more than 4GB of JSON. Before this, I never expected to meet SQLCODE -1313 Maximum string length exceeded!!

I'll just to have to unload in blocks, maybe 100k records at a time.

(21 Aug '20, 15:14) Justin Willey
showing 2 of 12 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 20 Aug '20, 08:06

question was seen: 188 times

last updated: 21 Aug '20, 15:14