Open Exchange App 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!

Comentarios

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...

set $PROPERTY(tRow,tColumnName) = $PROPERTY(rSet,tColumnName)


with this..

do tRow.%Set(tColumnName,$PROPERTY(rSet,tColumnName),$Select(tColumn.clientType=16:"boolean",1:""))


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

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!