New post
Question
· Apr 18, 2018

How to convert SQL persistent classes to JSON output

#JSON #Object Data Model #SQL #Caché

How to convert SQL persistent classes to JSON output - I have tried 1 option but appears to be very lengthy process.

I have many other SQL storage's defined and have to expose them as well to JSON. Can anyone please suggest a better approach.

My column names usually match with my Json properties.

My User.TestClass is the class with GetJsonList() as my method to return /output JSON formatted data.

The Code tried and SQL storage structure are as follows -

Class User.TestClass Extends %RegisteredObject
{

ClassMethod GetJsonList() As %Status
{
Set SQL="SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData"
Set Statement= ##class(%SQL.Statement).%New()
Set Status= Statement.%Prepare(SQL)
Quit:$$$ISERR(Status) Status
Set tResults=Statement.%Execute()
Quit:$$$ISERR(Status) Status

S List=##class(%Library.ListOfObjects).%New()
while tResults.%Next() {
s tResponse=##class(User.Sample.Response).%New()
s tResponse.ID=tResults.ID
s tResponse.Column1=tResults.Column1
s tResponse.Column2=tResults.Column2
s tResponse.DataID=tResults.DataID
d List.Insert(tResponse)
}
s Stream=##class(%GlobalCharacterStream).%New()
s Status=##class(Ens.Util.JSON).ObjectToJSONStream(List,.Stream)
While 'Stream.AtEnd { Write Stream.ReadLine(,.sc,.eol) If $$$ISERR(sc) { Write "Error" Quit } If eol { Write ! } }
}

}
 

Discussion (3)0
Log in or sign up to continue
Robert Cemper · Apr 18, 2018

Are looking for this?

SELECT JSON_OBJECT('ID':ID,'Column1':COLUMN1,'Column2':COLUMN2,'DataID':DataID) FROM  SQLUser.SampleData

Cache for Windows (x86-64) 2017.2.1 (Build 801U)

Sorry I don't have your data to show your result

somewhat similar. 

SELECT top 3 JSON_OBJECT('name:':Name,'home':HOME_CITY,'dob':DOB) FROm Sample.Person

{"name:":"Smyth,Valery B.","home":"Hialeah","dob":"2001-01-06"}
{"name:":"Jones,Valery E.","home":"Islip","dob":"1959-11-11"}
{"name:":"Newton,David H.","home":"Youngstown","dob":"1969-12-01"}

Or all together:

SELECT '{"result":'||List(xx)||'}' FROM
(SELECT top 3 JSON_OBJECT('name:':Name,'home':HOME_CITY,'dob':DOB) xx
FROM Sample.Person)

{"result":
  {"name:":"Smyth,Valery B.","home":"Hialeah","dob":"01/06/2001"}
 ,{"name:":"Jones,Valery E.","home":"Islip","dob":"11/11/1959"}
 ,{"name:":"Newton,David H.","home":"Youngstown","dob":"12/01/1969"}
}
 

The difference in DOB is just display mode vs. ODBC mode 

3 0
Bharathsimha Re... · Apr 18, 2018

Thank you Robert that did help me..
I also found these options below -

s Status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONFromSQL("json","SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData")
-- or --
    s Status=##class(%ZEN.Auxiliary.jsonSQLProvider).%WriteJSONStreamFromSQL(.pStream,"SELECT ID, Column1, Column2, DataID FROM SQLUser.SampleData")

    While 'pStream.AtEnd { Write pStream.ReadLine(,.sc,.eol) If $$$ISERR(sc) { Write "Error" Quit } If eol { Write ! } }

0 0