SQLAnywhere currently provides the ability to create a web service of TYPE 'JSON' that can return a JSON-serialized result set.

Sample output of a web service returning "select * from people":

[
    {"person_id":"1", "first_name":"John", "last_name":"Doe"},
    {"person_id":"2", "first_name":"Jane", "last_name":"Smith"}
]

Is there any way to utilize the underlying JSON serialization function so that the JSON output string can be manipulated before it is returned from the web service? This would be very useful for creating JSON that is more than just the array notation representation of a result set, so that it can be easily consumed by popular JavaScript library components from ExtJS or jQuery.

Example showing how a "JSON" function that operates in a similar manner to XMLELEMENT() might be used:

declare json_text long varchar;

set json_text = 
     '{' || 
          '"success": true' ||
          JSON('rows', select person_id, first_name, last_name from people); 
     '}';

could produce something like (formatted for readability):

{
    "success": true,
    "rows": [
        {"person_id":"1", "first_name":"John", "last_name":"Doe"},
        {"person_id":"2", "first_name":"Jane", "last_name":"Smith"}
    ]
}

I'd be happy with just getting the result set array notation string and constructing the JSON manually though if the serialization function were exposed.

asked 30 Mar '10, 17:35

Jim's gravatar image

Jim
86224
accept rate: 0%

edited 30 Mar '10, 17:43

I would like this feature too. A procudure/function would solve this issue too, is there any well tested function around here?

(18 Apr '12, 09:16) Ismael

While there is no native SQL support for doing this, you could make use of external environments and/or external functions. There are a whole bunch of existing libraries that generate and parse JSON that would work, depending on the language you choose.

If you just want to generate it, JSON is a very simple format that could be built without too much trouble with SQL in a few stored procedures.

Either way, you would need to make your service RAW and then build the JSON string yourself.

permanent link

answered 01 Apr '10, 14:39

Phil%20Mitchell's gravatar image

Phil Mitchell
1.9k1831
accept rate: 27%

There is currently no method (short of writing the SQL code to do it yourself) to generate JSON using the built-in code within SA ... but this feature is being consider for the next release.

permanent link

answered 30 Mar '10, 19:34

Mark%20Culp's gravatar image

Mark Culp
23.3k9132275
accept rate: 40%

Meaning Innsbruck, or the release after that?

(30 Mar '10, 19:57) Breck Carter

By "next release" I mean the release after Innsbruck.

(31 Mar '10, 12:52) Mark Culp
Replies hidden

Do You have new information, when it is expected to release with a parser for the JSON-format (like openxml)?

(10 Jan '13, 09:19) Ilia63
1

Download the SQL Anywhere 16 Beta and see for yourself :)

(10 Jan '13, 09:40) Breck Carter

You may consider having a look at the SQL Anywhere 16 beta called "Nagano".

I can't (and won't) tell whether there is support for anything you are looking for in v16 - but you may check for yourself.

(10 Jan '13, 09:43) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×32
×21
×5

question asked: 30 Mar '10, 17:35

question was seen: 2,965 times

last updated: 10 Jan '13, 09:43