Code example to build a %Library.DynamicObject or a %Library.DynamicArray from Resultset followed by converting to JSON
Please, provide Code example to build a %Library.DynamicObject or a %Library.DynamicArray from Resultset followed by converting to JSON
Comments
To build a %Library.DynamicObject or %Library.DynamicArray from a ResultSet followed by converting to JSON, follow these steps:
- Loop through the ResultSet, constructing either a
%Library.DynamicObjector%Library.DynamicArrayas needed. - Use the
%ToJSON()method to convert the constructed dynamic object or array into JSON format.
Below is an example code snippet illustrating this process:
ClassMethod ResultSetToJSON() As %Status
{
// Get your ResultSet - replace with appropriate query or structure initialization
// For demonstration, let's assume we have a ResultSet named resultSet
Set dynArray = ##class(%Library.DynamicArray).%New()
// Example loop to process resultSet rows
While resultSet.%Next()
{
// Extract data from the ResultSet row
Set dynObj = ##class(%Library.DynamicObject).%New()
Do dynObj.%Set("Column1", resultSet.Column1)
Do dynObj.%Set("Column2", resultSet.Column2)
Do dynArray.%Push(dynObj)
}
// Convert to JSON string
Write dynArray.%ToJSON()
Quit $$$OK
}
In this example:
- Data is processed row by row from
resultSet. - A
%Library.DynamicObjectis created for each row, and properties are populated with column data. - Each dynamic object is added to a
%Library.DynamicArray. - The
%Library.DynamicArrayis converted to JSON using its%ToJSON()method.
Remember, similar steps can be followed for a %Library.DynamicObject if your JSON structure requires objects instead of arrays 1.
Sources:
I have a ResultSet but I don't know what are column names. How to substitute it's names in the example below?
What type of ResultSet? Can you please provide the exact class name of the ResultSet you use?
It looks like you are using Dynamic SQL Using Older Result Set Classes
Could you please specify which class you are using: %Library.ResultSet or %ResultSet.SQL?
So far "%SQL.StatementResult" is in use.
Thank you.
See
Here is a small example:
Class dc.test Extends %Persistent
{
Property pClob As %Stream.GlobalCharacter;
Property pBlob As %Stream.GlobalBinary;
ClassMethod RSToDynamicArray(ByRef rs As %SQL.StatementResult) As %DynamicArray
{
#dim metaData as %SQL.StatementMetadata = rs.%GetMetadata()
;d metaData.%Display()
f i=1:1:metaData.columns.Count() {
#dim column As %SQL.StatementColumn = metaData.columns.GetAt(i)
s colInfo(i,"label") = column.label,
colInfo(i,"ODBCType") = $$$GetOdbcTypeName(column.ODBCType)
}
s colInfo=i
s arr = []
while rs.%Next() {
s row = {}
f i = 1:1:colInfo {
s label=colInfo(i,"label"),
odbcType=colInfo(i,"ODBCType"),
val = rs.%GetData(i)
i val="" {
d row.%Set(label,"","null")
}elseif odbcType="BIT" {
d row.%Set(label,$num(val),"boolean")
}elseif odbcType="DATE" {
d row.%Set(label,$zd(val,3))
}elseif (odbcType="LONGVARCHAR")||(odbcType="LONGVARBINARY") {
d row.%Set(label,##class(%Stream.Object).%Open(val),"stream")
}elseif $IsValidNum(val) {
d row.%Set(label,$num(val),"number")
}else {
d row.%Set(label,val,"string")
}
}
d arr.%Push(row)
}
q arr
}
ClassMethod Test()
{
d ..%KillExtent()
s tmp=..%New()
d tmp.pClob.Write("Hello")
d tmp.pBlob.Write($c(0,1,7))
d tmp.%Save()
s sql="select 'hello' fStr" _
",2 fInt" _
",current_date fDate" _
",cast(1 as BIT) fBool" _
",null fNull" _
",(select top 1 pClob from dc.test) fClob" _
",(select top 1 pBlob from dc.test) fBlob"
#dim rs As %SQL.StatementResult=##class(%SQL.Statement).%ExecDirect(,sql)
d ..RSToDynamicArray(.rs).%ToJSON()
}
}Result:
USER>d ##class(dc.test).Test()
[{"fStr":"hello","fInt":2,"fDate":"2026-03-05","fBool":true,"fNull":null,"fClob":"Hello","fBlob":"\u0000\u0001\u0007"}]PS: By the way, in version 2016.3, this could be done much more easily:
write rs.$compose("%Array").$toJSON()Unfortunately, I have not found a way to replicate this code in version 2025.3
Thank you. Is there any "similar" utility to convert JSON back into ResultSet?
Check this post.
Thank you. Is there any "similar" utility to convert JSON back into ResultSet?
In OEX there are 2 examples to create a persistent (or temporary) class from JSON
Launching a related query to get a ResultSet is just a standard exercise then.
Sorry - I didn't find a code correlated to my question.
I am looking for code EXAMPLE to convert JSON string into ResultSet (%SQL.StatementResult).
Thank you
I'm just curious why you would want to convert from JSON to a result set? Everything I could think of that would make you want to do that would be better served by using a %Library.DynamicArray.
Per ISC documentation (don't remember correlated section), query ResultSet (%SQL.StatementResult) , executed in one namespace, can not be directly viewed/parsed in a different namespace. Upon ZN to the original namespace, ResultSet is empty.
I am trying to resolve it, by the following steps:
- ZN form original namespace (ONS) to execution namespace (ENS)
- Execute query in ENS and get ResultSet
- While in ENS, convert ResultSet to JSON
- ZN to ONS.
- JSON is still valid and not empty
- While in the original ONS, convert JSON to ResultSet to use it.
Steps 1 - 5 are done and tested.
I am looking for a code (step 6) to convert JSON to ResultSet.
Did I select a wrong approach?
Thank you
Once upon a time, there was no JSON support!
But your challenge could be resolved by these steps:
- ZN form original namespace (ONS) to execution namespace (ENS)
- Execute query in ENS and get ResultSet
- While in ENS, let %Display print the Resultset into SPOOL
- ZN to ONS.
- Loop over the spooled ResultSet to get your data line by line
A simplified example:
USER>ZN "SAMPLES"
SAMPLES>set sql="SELECT top 5 name, ssn from sample.employee"
SAMPLES>set sta=##class(%SQL.Statement).%New()
SAMPLES>write sta.%Prepare(.sql)
1
SAMPLES>set res=sta.%Execute()
SAMPLES>kill ^SPOOL($j)
SAMPLES>open 2:$JOB
SAMPLES>use 2 do res.%Display()
SAMPLES>close 2
SAMPLES>ZN "USER"
USER>Zwrite ^|"SAMPLES"|SPOOL($JOB)
^|"SAMPLES"|SPOOL(13488,1)="Name"_$c(9)_"SSN"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,2)="Emerson,Xavier O."_$c(9)_"217-28-9642"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,3)="Malkovich,Bob V."_$c(9)_"226-71-5540"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,4)="Joyce,Juanita V."_$c(9)_"396-77-7486"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,5)="Ingleman,Josephine Q."_$c(9)_"558-18-5848"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,6)="Page,Valery R."_$c(9)_"943-90-5082"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,7)=$c(13,10)
^|"SAMPLES"|SPOOL(13488,8)="5 Rows(s) Affected"
^|"SAMPLES"|SPOOL(13488,2147483647)="{67640,77930{9{"
USER> The content here is:
Name SSN
Emerson,Xavier O. 217-28-9642
Malkovich,Bob V. 226-71-5540
Joyce,Juanita V. 396-77-7486
Ingleman,Josephine Q. 558-18-5848
Page,Valery R. 943-90-5082
5 Rows(s) Affected
Now you can loop over your result as on any other Global
as you need
You could do as Robert said, or you could create an SQL gateway connection to the execution namespace and query it from the original namespace.
It's not a matter of ResultSet availability in another namespace; it's a matter of the data you have access to in one namespace and not in another.
if you need the access to data that is available in namespace A to namespace B, there are many ways to make it work:
- map the data for namespace B via global mappings and run the query and work with the ResultSet in namespace B directly.
- Parse the resultset in namespace A, put all the results into a global that is visible in namespace B via extended syntax Access the global in namespace B directly.
- Put the results into a file on a disc or %Stream and work with them in namespace B.
- any other way that is more suitable to your task.
But reconstructing the ResultSet, which is just an object in memory suitable to access the data that is still in Namespace A, I wouldn't follow the idea.
Thank you @Evgeny Shvarov. Transferring result from namespace A to namespace B is not a single action to handle. It is an application-wide utility to allow query execution in one namespace and handling a result in another for new and ALREADY exist queries. So, options 2,3,4 above can not be implemented.
But option 1 seems promising if it could be done programmatically. Please, provide code example to TEMPORARILY "map the data for namespace B via global mappings.." and kill such mapping upon getting a result. Thank you.
Hi @Evgeny Shvarov
I created a mix from #1 and #2 without global mapping.
Temporary tables are excellent for this challenge
as they drop at the end of the process. No clean-up needed !
It's kind of a Snapshot
A totally different approach, excluding JSON, pure SQL
- create temporary table
- Insert your result
- consume it within the same process
- eventually drop the table definition for a different query
I use the same query for my small code example
SELECT TOP 5 ssn, home_state,name from Sample.employee
USER>
USER>ZN "SAMPLES"
SAMPLES>:sql
SQL Command Line Shell
-----------------------------
[SQL]SAMPLES>> << entering multiline statement
1>>CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as
2>>SELECT TOP 5 ssn, home_state,name from Sample.employee
3>>go
17. CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as
SELECT TOP 5 ssn, home_state,name from Sample.employee
5 Rows Affected
[SQL]SAMPLES>>quit
SAMPLES>zn "USER"
USER>:sql
SQL Command Line Shell
-----------------------------
[SQL]USER>>SELECT * from %ZZ.resultset
18. SELECT * from %ZZ.resultset
| SSN | Home_State | Name |
| -- | -- | -- |
| 217-28-9642 | NC | Emerson,Xavier O. |
| 226-71-5540 | CO | Malkovich,Bob V. |
| 396-77-7486 | UT | Joyce,Juanita V. |
| 558-18-5848 | IA | Ingleman,Josephine Q. |
| 943-90-5082 | TN | Page,Valery R. |
5 Rows(s) AffectedNow it's all in your handsEventually run DROP TABLE %ZZ.resultsetbefore next use with different columns.Or just use different temp table for repeated use with other SELECT
CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset imply that the table will be accessible from any namespace?YES Sir!
- Process private globals live in IRISTEMP and are strictly bound to the process and its lifetime.
- The package with %ZZ.* makes sure it is visible from ALL namespaces
- At process start it is always empty
- you fill it implicitely by CREATE at definiftion time
- or if reused by
INSERT INTO %ZZ.resultset SELECT ....... - this SELECT must be the same as in CREATE ...
to fit column names
- Either CREATE once + INSERT later works
- Or DROP TABLE %ZZ..... and fresh CREATE ...
- It's a matter of taste, I tried both
Warning:
Test from MGMT Portal (SQL) doesn't work as the process
(and its ID) in background changes. The process stops and the content is gone.
IF your table must survive several processes, you need some
other tricky solution based on the same basic idea of %ZZ*
combined with a manually tuned Class definition.
A very dirty hack: possible not really a recommendation
IF Source table is not mapped to ONS
- copy class definition including <Storage> from ENS to ONS
- in <STORAGE> you see
-
<DataLocation>^Sample.EmployeeD</DataLocation> <IdLocation>^Sample.EmployeeD</IdLocation> <IndexLocation>^Sample.EmployeeI</IndexLocation> <StreamLocation>^Sample.EmployeeS</StreamLocation> - Now replace all ^ by ^|"ENS"| looks similar
For my example, it's ^|"SAMPLES"| -
<DataLocation>^|"SAMPLES"|Sample.EmployeeD</DataLocation> <IdLocation>^"|SAMPLES"|Sample.EmployeeD</IdLocation> <IndexLocation>^|"SAMPLES"|Sample.EmployeeI</IndexLocation> <StreamLocation>^|"SAMPLES"|Sample.EmployeeS</StreamLocation> - Extended Global references allow access to the other namespace. Nothing new, but risky
I fail to understand your question.
For the %ZZ*case it is all SQL. no Objectscript involved
as you see in this reply
The dirty hack is based on editing a copy of the class you want to query
as described in my other reply.
For a description of extended global reference syntax, see Extended Global References.
@Robert Cemper I understand about extended global reference - it is not an issue.
I have issue to execute "..CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset.." related steps from QobjectScript.
Creating a temp (%ZZ..) table, following by it's population and finally dropping it (as you described in your post) must be done programmatically from ObjectScript.
Thank you for guidance.
I composed a variant that doesn't require any code in ENS.
It can all run from your ONS namespace.
All you need is a valid SQL SELECT statement
First, the utility class:
Class User.ZZ [ Abstract ]
{
ClassMethod Export(nspace = "", query = "") As %Integer
{
new $namespace
zn:$l(nspace) nspace
&SQL(DROP TABLE %ZZ.resultset)
; default just for demo
set:'$l(query) query="SELECT TOP 5 ssn, home_state,name from Sample.employee"
;
set sql=2,
sql(1)="CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as ",
sql(2)=query
set statement=##class(%SQL.Statement).%New()
set result=statement.%ExecDirect(.statement,.sql)
quit result.%SQLCODE
} }a quick demo:
USER>set select="SELECT TOP 3 home_state,name,SSN from Sample.employee"
USER>w ##class(ZZ).Export("SAMPLES",select)
0
USER>:sql
SQL Command Line Shell
----------------------------------------------------
[SQL]USER>>select * from %ZZ.resultset
5. select * from %ZZ.resultset
| Home_State | Name | SSN |
| -- | -- | -- |
| NC | Emerson,Xavier O. | 217-28-9642 |
| CO | Malkovich,Bob V. | 226-71-5540 |
| UT | Joyce,Juanita V. | 396-77-7486 |
3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0579s/39,198/181,087/0ms
execute time(s)/globals/cmds/disk: 0.0002s/4/815/0ms
query class: %sqlcq.USER.cls73
----------------------------------------------------------
[SQL]USER>>cos ZN "SAMPLES"
[SQL]SAMPLES>>select * from %ZZ.resultset
6. select * from %ZZ.resultset
| Home_State | Name | SSN |
| -- | -- | -- |
| NC | Emerson,Xavier O. | 217-28-9642 |
| CO | Malkovich,Bob V. | 226-71-5540 |
| UT | Joyce,Juanita V. | 396-77-7486 |
3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0552s/39,198/181,087/0ms
execute time(s)/globals/cmds/disk: 0.0002s/4/815/0ms
query class: %sqlcq.SAMPLES.cls92
--------------------------------------------------
[SQL]SAMPLES>>q
SAMPLES>hope this helps:
The temporary table is visible from both namespaces
And the utiltiy is not tied to a specific query or table
All just runs and exists in your process.
Also no traces in the Journal
What am I missing? Thank you
=======
{
&SQL(DROP TABLE %ZZ.resultset)
Set sql=2,
sql(1)="CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as ",
sql(2) ="SELECT * FROM Ens.MessageHeader"
Set statement=##class(%SQL.Statement).%New()
Set result=statement.%ExecDirect(.statement,.sql)
Set a = result.%SQLCODE // a = 0
ZN "HSCUSTOM"
// Attempt to verify via MP>"HSACCESS">SQL:
// SELECT * FROM Ens.MessageHeader > Row count: 8..
// SELECT * FROM %ZZ.resultset > Row count: 0 ...
// Pay attention that there is NO error "Table '%ZZ.RESULTSET' not found"
// Attempt to verify via MP>"HCUSTOM">SQL:
// SELECT * FROM %ZZ.resultset > Row count: 0 ...
// Pay attention that there is NO error "Table '%ZZ.RESULTSET' not found"
}
- This example is using a TEMPORARY Table based on Process Private Globals.
- You can access the content of the table just from the process that created the table.
Not from any other process. - From SMP you see the Table Definition from any Namespace because ot the %ZZ. package.
so the table is FOUND but EMPTY - Though your SELECT from SMP runs in a totally different process with anything but your Process Private Global
- You have to get your final SELECT and generating your temporary table into the same Process,
-
To achieve this in SMP, declare your ClassMethod As SqlProcedure like this:
ClassMethod Export(nspace = "", query As %String = "") As %Integer [ SqlName = ZZEXPORT, SqlProc ] { new $namespace ;; and so on -
Then you can run it from SMP using this SQLprocedure as Static WHERE condition.
SELECT * from %ZZ.resultset WHERE ZZEXPORT('SAMPLES', 'SELECT TOP 5 Description, ErrorStatus, Priority from ENS.MessageHeader')=0 - For Static WHERE Conditions see my article just 6 years back.
-
Using a TEMPORARY table avoids problems if running in more than 1 instance in parallel.
Sorry,
The new Editor is not so well-suited for long line replies. I dislike it.