AnsweredAssumed Answered

ExecuteSQL to json

Question asked by rivet on Mar 3, 2018
Latest reply on Mar 8, 2018 by Paul Jansen

I am playing with the following technique to have my sql query return a json result.  So far so good. Commas and carriage returns are not an issue.  Can anyone think of potential pitfalls?

 

QUERY

"{" &
ExecuteSQL ( "
SELECT

'\"id\":'|| id ||','||
'\"id_profile\":'|| id_profile||','||
'\"id_servicetype\":'|| id_servicetype||','||
'\"rate\":'|| rate ||','||
'\"status\":'|| status ||','||

'\"date_start\":\"'|| date_start||'\",'||
'\"date_end\":\"'|| date_end||'\",'||
'\"comment\":\"'|| comment||'\"'

FROM
service
WHERE
id = ?

" ; "" ; "" ; 11 )  
& "}"

 

RESULT

{
  "comment" : "this is line 1, of three lines\rline 2\rline 3",
  "date_end" : "",
  "date_start" : "1/1/2017",
  "id" : 11,
  "id_profile" : 1414,
  "id_servicetype" : 4,
  "rate" : 60,
  "status" : 1
}

Outcomes