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()
&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
Comments
check SQL Function JSON_OBJECT
https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_jsonobject
and JSON_ARRAY
https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_jsonarray
with the related examples
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.
{
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)
{
ClassMethod MyLIST(var...) As %String [ SqlName = MyLIST, SqlProc ]
{ set result="^"
for i=1:1:var set result=result_var(i)_"^"
quit result }
}
with SQL:
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
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")_""""
}Hey,
thanks for your quick answers.
I found the following solution for my problem and it works:
.png)
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.
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