Dynamic SQL to Dynamic Object
Hello community! I have to work with queries using all kinds of methods like embedded sql and class queries. But my favorite is dynamic sql, simply because of how easy it is to manipulate them at runtime. The downside to writing a lot of these is the maintenance of the code and interacting with the output in a meaningful way. In an effort to have as much dynamism as possible so that we're not rewriting code constantly, and so that we have as little code possible (while still making sense and getting the job done), I made a function that takes in any dynamic query and outputs a dynamic object.
The dynamic object has a key/value pair according to the result set's field names and values, which makes it very useful for my needs. Most of the time I will say %ToJSON() on these returned values and it's good to go for an api call response.
I'll say that one thing I'm most disgusted by in programming is hard-coded values. This may not be the case for you, but I see variants of this when code is interacting with a result set returned by a dynamic object:
set tResults = []
while rSet.%Next() {
set tRow = {}
set tRow.ProcessedDateTime = rSet.ProcessedDateTime
set tRow.Destination = rSet.Destination
set tRow.SourceOID = rSet.SourceOID
set tRow.ResultType = rSet.ResultType
set tRow.MessageDateTime = rSet.MessageDateTime
set tRow.PatientLastName = rSet.PatientLastName
set tRow.PatientFirstName = rSet.PatientFirstName
set tRow.PatientDOB = rSet.PatientDOB
set tRow.MRN = rSet.MRN
set tRow.ResultStatus = rSet.ResultStatus
set tRow.PatientClass = rSet.PatientClass
set tRow.DocumentType = rSet.DocumentType
set tRow.SessionID = rSet.SessionID
set tRow.DelayEntry = rSet.DelayEntry
set tRow.DestinationStatus = rSet.DestinationStatus
set tRow.Report = rSet.Report
set tRow.Provider = rSet.Provider
do tResults.%Push(tRow)
}There has to be a better way of doing this, because we're effectively controlling what data we want returned in two different places: the query, and the loop populating the object. I like things to be done in one place, and the query makes the most sense to me. So instead of naming each field, I want the loop to just figure it out on its own. We can accomplish this by using *gasp* one more loop:
set tResults = []
while rSet.%Next() {
set tRow = {}
set tMetadata = rSet.%GetMetadata()
set tColumnCount = tMetadata.columns.Count()
for x=1:1:tColumnCount {
set tColumn = tMetadata.columns.GetAt(x)
set tColumnName = tColumn.colName
set $PROPERTY(tRow,tColumnName) = $PROPERTY(rSet,tColumnName)
}
do tResults.%Push(tRow)
}The magic is in getting the correct metadata information, and then using the $PROPERTY function to indirectly manipulate the key/value pair. This lets us control what data we're getting out the query using the query itself, so if something is missing we know exactly where to look.
My code on Open Exchange has a couple other goodies to make life easier, such as parameters to select the mode, dialect, and namespace of each function call. Please let me know what you think! This is my first contribution and I'm eager to improve how I code in this language.
Thank you!
Comments
Tip.
Metadata does not change from one row to the other and accessing it takes time, so removing it from the row loop would improve the execution speed:
Class User.Test
{
Query TestQuery() As %SQLQuery
{
SELECT * FROM Sample.Person
}
/// do ##class(User.Test).Test()
ClassMethod Test()
{
for method = "EveryRow", "Once" {
set rSet = ..TestQueryFunc()
set start = $zh
do $classmethod(, method, rSet)
set end = $zh
write $$$FormatText("Method %1 took %2 sec.", method, end - start), !
}
}
ClassMethod EveryRow(rSet As %SQL.ISelectResult)
{
set tResults = []
while rSet.%Next() {
set tRow = {}
set tMetadata = rSet.%GetMetadata()
set tColumnCount = tMetadata.columns.Count()
for x=1:1:tColumnCount {
set tColumn = tMetadata.columns.GetAt(x)
set tColumnName = tColumn.colName
//do tRow.%Set(tColumnName, rSet.%GetData(x) )
set $PROPERTY(tRow,tColumnName) = $PROPERTY(rSet,tColumnName)
}
do tResults.%Push(tRow)
}
}
ClassMethod Once(rSet As %SQL.ISelectResult)
{
set tResults = []
set tColumns = ""
set tMetadata = rSet.%GetMetadata()
set tColumnCount = tMetadata.columns.Count()
for x=1:1:tColumnCount {
set tColumn = tMetadata.columns.GetAt(x)
set tColumnName = tColumn.colName
set tColumns = tColumns _ $lb(tColumnName)
}
while rSet.%Next() {
set tRow = {}
for x=1:1:tColumnCount {
do tRow.%Set($lg(tColumns, x), rSet.%GetData(x) )
}
do tResults.%Push(tRow)
}
}
}
Results for me:
>do ##class(User.Test).Test()
Method EveryRow took .017803 sec.
Method Once took .01076 sec.Fantastic! Thank you for looking at the speed, those changes do help. I guess I thought metadata was retrieved on a row basis, but no!
Hi David,
In general +1 for the generic boiler plate approach.
In terms of a generic JSON solution is could do with some additional type checks to make sure they are output correctly, e.g. booleans as true / false / null, empty numbers being returned as null and not empty string etc.
So just for booleans as an untested example you might replace...
with this..
The alternative, if just going from SQL to a serialised JSON string could be to just use SQL and JSON_ARRAYAGG as per the examples here...
https://community.intersystems.com/post/how-do-i-return-json-database-sql-call
A lovely and very enriching discussion
Hi.
That is a good approach.
However, have you ever tried JSON_OBJECT function (https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?…)? If your need is to return an JSON to an API call, this may do the trick for you.
Have a good one.
The problem with JSON_OBJECT is that you need to write each field twice for each query.
It's good for one-off things, but for generalized solution authors approach looks better.
Yes, as much as I'd like to use that method, my queries can get very complicated and it never worked the way I needed it to, and I didn't want to spend the majority of my time working on query syntax. There's a lot of people who write normal queries that I work with as well, who would need to update a ton of sql!