Published on InterSystems Developer Community (https://community.intersystems.com)

Home > %SQL.StatementResult vs %Library.ResultSet

Question
Đặng Phú Lộc · Dec 30, 2019

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

https://cedocs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?PAGE=CLASS&LIBRARY=%25SYS&CLASSNAME=%25Library.ResultSet#METHOD_%Get

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. 
 

#Caché #InterSystems IRIS

Source URL:https://community.intersystems.com/post/sqlstatementresult-vs-libraryresultset