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. 
 

00
5 0 6 365

Replies

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.

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

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

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.

To answer the original question: What is the difference?

There are 3 ways to use SQL in Caché/IRIS:

Dynamic SQL: The main use case for this approach is that you, the developer, don't know the complete SQL statement at design/compile time. Instead, inside your Method, you build the complete SQL statement at run time (adding columns to the SELECT, conditions to the WHERE clause, whatever you need), prepare the finished SQL, and execute it. This approach uses %SQL.Statement and %SQL.StatementResult. As you loop through the result set, to access the data in each column, you can use rs.columnname or rs.%Get("columnname"). But neither one will work if you don't actually know the names of all the columns, which can obviously happen when the SQL is dynamic. So the only other approach is to use rs.%GetData(columnnumber).

The other two alternatives are for the case where you, the developer, know the complete SQL statement as design/compile time. Note that you can use Dynamic SQL for this case also.

Embedded SQL: In this approach, you embed the complete SELECT statement inside your Method using &sql(). This is similar to embedding SQL in procedural languages provided by other vendors, such as PL/SQL (Oracle) and T-SQL (MS).

Class queries: In this approach, you create a Query in your class that contains the complete SELECT statement, and you use that query in a Method. For this, it's recommended to use the %PrepareClassQuery() method of %SQL.Statement.

Note: the original implementation of Class queries and Dynamic SQL also used %Library.ResultSet.