%SQL.StatementResult vs %Library.ResultSet

Primary tabs

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. 
 

Answers

The noted difference between %SQL.StatementResult.%Get() and %Library.ResultSet.%Get() is regarding the actual column names, not the values they contain.

Given that I am using InterSystems IRIS, but here is how I reproduced your example (I don't use a left-join, but they should behave the same)

CREATE TABLE testTable("Column A" INT, "Column B" INT, "Column C" INT, "Column D" INT)
INSERT INTO testTable VALUES(1,2,3,4)
INSERT INTO testTable VALUES(1 NULL, NULL,4)

-------------------------

USER>set stmt = ##class(%SQL.Statement).%New()
USER>set query = 1, query(1) = "SELECT * FROM testTable"
USER>write stmt.%Prepare(.query)
1
USER>set rs = stmt.%Execute()
USER>write rs.%Next()
1
USER>write rs.%Get("Column B")
2
USER>write rs.%Next()
1
USER>zwrite rs.%Get("Column B")
""

USER>do $system.Status.DisplayError(rs.%Get("Column E"))
 
DO $SYSTEM.Status.DisplayError(rs.%Get("Column E"))
^
<PROPERTY DOES NOT EXIST> *Column E,%sqlcq.USER.cls15

As you can see, %SQL.StatementResult.%Get() should only throw an error if you pass in an invalid column name such as "Column E" in the code snippet above.

I recommend that you check the %SQL.StatementMetadata to check if the columns exist in the first place.

USER>set rsmd = rs.%GetMetadata()
USER>set columns = $LB()
USER>for i=0:1:rsmd.columnCount-1 set $li(columns,i+1)=rsmd.columns.GetNext(i).colName

Once you have columns populated, you can use $LISTFIND to determine whether or not the column exists.

If you cannot do the above then you can wrap the use of %SQL.StatementResult.%Get() with a try-catch block as seen below:

while(rs.%Next()) {
     try {
         set var = rs.%Get(columnName)
     } catch {
         set var = ""
     }
}

I do not recommend the try-catch fallback assignment

Comments

What are you referring as invalid column name in this scenario?

Why are you checking with invalid columns in the first place? If you know you can have invalid columns then you'd need to add proper error handling for it. You either check the metadata first for valid columns or add exception handling for it.

As for why %SQL.Statement* classes are preferred is due to the improvements these classes contains in terms of usability and performance when compared with the "legacy" %Library.ResultSet.

To be clear, Invalid column name means the column doesn't exist in the result set.

The result set's returned columns aren't always fixed like the example I wrote above. The returned columns are dynamic due to they are left joined through some several tables.

What Kevin Chan and Warlin Garcia said about looping through the metadata's column name to check if the column exist in the result set maybe the only proper method so far.

I think the most efficient way to test if a column exists in the result set is using the columnIndex property of the metadata (instance of %SQL.StatementMetadata):

SAMPLES>Set rs=##class(%SQL.Statement).%ExecDirect(.st,"select name,age from Sample.Person")
SAMPLES>Write rs.%Next()
1
SAMPLES>Set rsmd = rs.%GetMetadata()
SAMPLES>If $data(rsmd.columnIndex("NAME")) Write rs.Name
Xiang,Gertrude X.
SAMPLES>If $data(rsmd.columnIndex("NOTEXISTENT")) Write ""

Enrico

Sorry, last line should be:
SAMPLES>If '$data(rsmd.columnIndex("NOTEXISTENT")) Write ""  

Enrico