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!