Florian Hansmann · Apr 16, 2020

Serialize JSON from SQL Row

Hey Community,

my Caché Version is 2013.1 and I can't update now.

I want to serialize a SQL Answer row into an Array filled with objects and then convert it to json.

Actually I use the following, which is very error prone when I have to do that often:

set list = ##class(%Library.ListOfDataTypes).%New()

            declare queryONLWK01 cursor for 
            select F0103, F0104
            into :articlenumber,  :amount
            from GL.ONLWK01
            where F0001 = :BET and F0002 = :DEB and F0003 = :MIT
        &sql(open queryONLWK01)
        &sql(fetch queryONLWK01)
        while (SQLCODE = 0) {
            set articleObject = ##class(%ZEN.proxyObject).%New()
            set articleObject.articlenumber = articlenumber
            set articleObject.amount = amount
                do list.insert(articleObject)

I want to have the answer DIRECTLY in a list filled with objects from the select command like that:

&sql(declare queryONLWK01 cursor for
select F0103 as Articlenumber, F0104             
into myList        
from GL.ONLWK01
where F0001 = :BET and F0002 = :DEB and F0003 = :MIT)

myList = [{"Articlenumber": "15058455","F0104": "1"},{"Articlenumber": "95058458","F0104": "2"}]

set x = ##class(%ZEN.Auxiliary.jsonArrayProvider).%WriteJSONStreamFromObject(.json,myList)


Is it possible to get the answer I want direct into myList or a JsonString ?


Best regards


0 396
Discussion (7)1
Log in or sign up to continue

Hey Robert,

thanks for your fast answer. I edited my Question, so I have Caché 2013.1 and can't update now.

The solution ist what i need, but can I use with my version?

And can I use it with SQL like in my example or should I use SQL-Statement?

With 2013.* it is not built-in,  but you can mimic it:

every Classmethod can be projected as SQLprocedure.  

ClassMethod MyJSON(par1 As %String, par2 As %String As %String(MAXLEN==")  SqlName My_JSONSqlProc ]

    set result = ""   /* now you assemble your JSON  in this string */
quit result 

I'm not sure if you can pass a variable list of arguments from SQL.
But this is the basic mechanic behind it

I just tested a variable parameter list in SQL. It works: (simplified example)

Class User.SQLvar
ClassMethod MyLIST(var...) As %String [ SqlName = MyLIST, SqlProc ]
{ set result="^"
 for i=1:1:var set result=result_var(i)_"^"
  quit result }

with SQL:

 CACHE>do $system.SQL.Shell()
SQL Command Line Shell
[SQL]CACHE>>select MyList(1,2,3)
3.      select MyList(1,2,3)
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0538s/33292/149491/0ms
          execute time(s)/globals/cmds/disk: 0.0002s/0/428/0ms
                          cached query class: %sqlcq.CACHE.cls1
[SQL]CACHE>>select MyList(1,2,3,4,5,6)
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0545s/33591/158533/4ms
          execute time(s)/globals/cmds/disk: 0.0002s/0/440/0ms
                          cached query class: %sqlcq.CACHE.cls2

so you can generate your JSON  straght as ClassMethd

Why do you do make things such complicated?  You neither need  %ListOfDataTypes nor  %ZEN.proxyObject. If I see correctly, at the end of the query you have a characterstream, filled with JSON. Right? So just create this stream on-the-fly!

set json = ##class(%Stream.TmpCharacter).%New(), del=""

  &sql(declare ... )
  &sql(open queryONLWK01)
  do json.Write("[")
     for  {
        &sql(fetch queryONLWK01)
        do json.Write(del_"{")
        do json.Write("""articlenumber"":" _ articlenumber _ ",") // (*)
        do json.Write("""amount"":" _ amount)
        do json.Write("}")
        set del=","
     do json.WriteLine("]")

(*) if articlenumber is an alphanumeric value then you should do something like this

    do json.Write("""Aarticlenumber"":"_$$json(article)_",")

 where $$json(x) could be something like this ($zconvert() has  no JSON-mode in Cache-2013)

json(val) {
   for c="\","/","""",$c(8),$c(9),$c(10),$c(12),$c(13) set val=$replace(val,c,"\"_$tr(c,$c(8,9,10,12,13),"btnfr"))
   for  quit:'$locate(val,"[:cntrl:]",0,j,v)  set $e(val,j-1)="\u"_$e($zh($a(v)+65536),2,5)
   quit """"_$zcvt(val,"O","UTF8")_""""


thanks for your quick answers.

I found the following solution for my problem and it works:

But now I'm not protected against SQL-Injection, because customer is dynamic input from the client.

How I can I use this safe? Can I use something like parameterized queries? And how?

Best regards.


First thanks for your help!

Now I wrote my own class to get JSON direclty from SQL with custom keys declared with as in SQL:

ClassMethod GetArrayFromSQL(pVar As %String = "", pSQL As %String = "") As %Library.ListOfDataTypes

    #dim parameter = 0
    for {
        set value=$piece(pVar,"#",$increment(parameter))
        if ( value = "" ) { quit }
        set parameter(parameter) = value
    set parameter = parameter - 1
    #dim list            = ##class(%Library.ListOfDataTypes).%New()
    #dim tStatement        = ##class(%SQL.Statement).%New()
    #dim tStatus        = tStatement.%Prepare(pSQL)
    #dim tResult        = tStatement.%Execute(parameter...)
    #dim columnCount    = tResult.%ResultColumnCount

    while( tResult.%Next() ) {
        set offsetName = -8
        set object = ##class(%ZEN.proxyObject).%New()
        for i = 1:1:columnCount {
            set newKeyName = $list(tResult.%Metadata(0),$increment(offsetName,10))
            set command = "set object.%data("_$char(34)_newKeyName_$char(34)_") = tResult."_newKeyName
            xecute command
        do list.Insert(object)

    return list

Best regards