Written by

Please note that documentation link is for InterSystems Caché. In IRIS, we've gotten more strict around the deprecation of those older classes and that section of the documentation has been removed, and the associated classes are marked as deprecated and/or internal to further encourage you to take advantage of %SQL.Statement

0 0

Dan Pasco · Jul 1, 2024

%SQL.Statement is part of the IRIS implementation of Dynamic SQL and it is based on the SQL Standard's Call Level Interface (CLI). It allows the user to prepare and execute any SQL statement, including DDL. The result of executing a dynamic statement is an instance of %SQL.StatementResult. That result contains at least %SQLCODE and %Message, indicating success/failure along with some minimal information about the failure.

Part of Dynamic SQL is also a result set interface - %SQL.IResultSet. There is at least one extention of %SQL.IResultSet, %SQL.ISelectResult. When a statement result is a result set or includes one or more result sets then those result sets are likely instances of %SQL.IResultSet.

The other result set classes (some deprecated) are not necessarily instances of %SQL.IResultSet but do implement the most common members of that interface.

There is another class, %SQL.CustomQuery, that can be used to implement custom queries that are more intuitive than class queries. The documentation for %SQL.CustomQuery contains information on how to implement your own custom query along with an example.

0 0

Ben Spead Jul 1, 2024 to Dan Pasco

nice summary Dan - thank you!

0 0

David.Satorres6134 · Jul 3, 2024

Just for fun, or to see if AI matches the experts, I've asked the question to Mixtral. This is what it is explaining:

Yes, I can certainly explain the difference between %ResultSet.SQL and %SQL.Statement in Intersystems Cache ObjectScript.

%ResultSet.SQL is a class that provides a result set interface for executing SQL queries. It allows you to iterate over the rows returned by a SQL query and access the columns as properties of each row. Here's an example of how to use %ResultSet.SQL:
SET rs = ##class(%ResultSet.SQL).%Open("SELECT * FROM mytable")
DO WHILE (rs.%Next()) {
    SET column1 = rs.column1
    SET column2 = rs.column2
    // Do something with the columns
}
rs.%Close()
On the other hand, %SQL.Statement is a class that provides a statement interface for executing SQL queries. It allows you to prepare a SQL statement and then execute it multiple times with different parameter values. Here's an example of how to use %SQL.Statement:
SET stmt = ##class(%SQL.Statement).%New()
DO stmt.%Prepare("SELECT * FROM mytable WHERE column1 = :val1 AND column2 = :val2")
DO stmt.%Execute(val1, val2)
DO WHILE (stmt.%Fetch()) {
    SET column1 = stmt.column1
    SET column2 = stmt.column2
    // Do something with the columns
}
stmt.%Close()
In summary, the main difference between %ResultSet.SQL and %SQL.Statement is that %ResultSet.SQL is used for iterating over the rows returned by a SQL query, while %SQL.Statement is used for preparing and executing a SQL statement multiple times with different parameter values.

0 0

Dan Pasco Jul 3, 2024 to David.Satorres6134

I was tempted to criticize the Mixtral response. I did implement the original versions of both of these items so I know them both well. The Mixtral code example for %SQL.Statement is incorrect in a few ways but I'm surprised that it was as close as it is.

Once that temptation was put aside, I realized that Mixtral can only analyze what it finds. If it can't find the solution then perhaps our class documentation is not going to provide the answer to a user. Hmmm...

0 0