%SQL.StatementResult vs %Library.ResultSet
Can anyone clarify to me about the differences between these two classes?
People in this topic https://community.intersystems.com/post/using-class-queries-sqlstatement-versus-libraryresultset stated that %SQL.Statement (I am assuming they mean to talk about %SQL.StatementResult) is newer than %Library.ResultSet. But I don't really know what are the advantages of %SQL.Statement over %Library.ResultSet as the people in the topic somehow didn't mention them.
Currently I have a case and it seems like the %SQL.StatementResult doesn't have enough function to cover it:
I have to select with leftjoin through some several tables and return the result to the user through CSP page. The returned result can be demonstrated like the table below:
| ID | Column A | Column B | Column C | Column D |
| 1 | xx | xx | xx | xx |
| 2 | xx | xx |
As you can see the record No.2 is lack of data in Column B and C as it's the result of left join. I want to check for the value of the missing column first, if the values are missing, then the CSP will display "" to user.
With Library.ResultSet, I can use the function %Get to get the data from the result set, if the column name is invalid, this function return empty string.
However, the equivalent %Get of %SQL.StatementResult will throw error when the column name is invalid, which will crash the page in my case.
https://cedocs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=%25SQL.StatementResult#METHOD_%Get
So far, I can't find any equivalent method in %SQL.StatementResult to do the action I want like %Library.ResultSet yet.
Any comment is appreciated.