New post
Question
· Apr 16, 2020

Serialize JSON from SQL Row

#JSON #SQL #Caché

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()

       &sql(
            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

Discussion (7)0
Log in or sign up to continue
Robert Cemper · Apr 16, 2020

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_JSON, SqlProc ]
{  
    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

1 0
Robert Cemper · Apr 16, 2020

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)
Expression_1
^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)
Expression_1
^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
---------------------------------------------------------------------------
[SQL]CACHE>>

so you can generate your JSON  straght as ClassMethd

1 0
Julius Kavay · Apr 16, 2020

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)
        quit:SQLCODE
        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")_""""
}
0 0
Florian Hansmann · Apr 22, 2020

Solution:

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

0 0