Resultset to JSON

[GGandhi]

I am trying to send data in JSON format from a result set and it is not possible for me to do, I would appreciate any help on this.

flag = cn.open("::Name::AlphasportsXbasic")
 cn.Execute("select firstname, lastname, bill_city from customer where bill_state_region = 'ca'")
 rs = cn.ResultSet
 ?rs.ToJSONObjectSyntax()
 {FIRSTNAME : 'Willy', LASTNAME : 'Winka', BILL_CITY : 'Los Angeles'}
 {FIRSTNAME : 'Yvonne', LASTNAME : 'Harrington', BILL_CITY : 'Los Angeles'}
 {FIRSTNAME : 'Joan', LASTNAME : 'McAndrews', BILL_CITY : 'Los Angeles'}
 {FIRSTNAME : 'Leonard', LASTNAME : 'Burtonski', BILL_CITY : 'Santa Clarita'}
 {FIRSTNAME : 'Peter', LASTNAME : 'Harrison', BILL_CITY : 'Los Angeles'}
 {FIRSTNAME : 'James', LASTNAME : 'Mahoney', BILL_CITY : 'Long Beach'}

here is an example from alphapedia. when I do, I get the same, not nicely arranged in separate lines, in a single line, that does not matter. there is no comma between the two neighboring objects and I don't see enveloping square brackets either. I can do this manually myself, but looking for easier way ( or lazy way)

I would like the following format

[COLOR="#FF0000"][[/COLOR]
 {FIRSTNAME : 'Willy', LASTNAME : 'Winka', BILL_CITY : 'Los Angeles'}[COLOR="#FF0000"],[/COLOR]
 {FIRSTNAME : 'Yvonne', LASTNAME : 'Harrington', BILL_CITY : 'Los Angeles'}[COLOR="#FF0000"],[/COLOR]
 {FIRSTNAME : 'Joan', LASTNAME : 'McAndrews', BILL_CITY : 'Los Angeles'}[COLOR="#FF0000"],[/COLOR]
 {FIRSTNAME : 'Leonard', LASTNAME : 'Burtonski', BILL_CITY : 'Santa Clarita'}[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"],[/COLOR]
 {FIRSTNAME : 'Peter', LASTNAME : 'Harrison', BILL_CITY : 'Los Angeles'}[COLOR="#FF0000"][/COLOR][COLOR="#FF0000"],[/COLOR]
 {FIRSTNAME : 'James', LASTNAME : 'Mahoney', BILL_CITY : 'Long Beach'}
[COLOR="#FF0000"]][/COLOR]

am I wrong in this? has anyone successfully transmitted a resultset across in JSON format and if so would be kind enough to help me out?
thank you in advance.

edit: the actual result comes with double quotes as required but not shown in the alphapedia example, I am more interested in array representation of the objects.

[Jim Coltz]

Re: Resultset to JSON

Here is a short code snippet of a working function. This should get you going. The main area to notice is the crlf_to_comma() function to put the crlf list that A5 gives you into a comma separated list for JSON use. Then just add your brackets.

    if flag 
        js = rs.ToJSONObjectSyntax()
    end if
    js = crlf_to_comma(js)
    js =  "\"tickets\":[" +js + "]"

[GGandhi]

Re: Resultset to JSON

thank you very much, I have coded most of it, but this seems better approach, I will try tonight.

js = crlf_to_comma(js)

I don't know this crlf() exists, I have to look.
what alphapedia shows and what I get in reality are not the same.
mine comes out as a single line instead of what is show in alphapedia.

[kkfin]

Re: Resultset to JSON

This may also work.

dim json as c
json = rs.ToJSONObjectSyntax()
json = "[" + stritran(json,crlf(),"," + crlf()) + "]"