To unfreeze:

  • Do $SYSTEM.SQL.FreezePlans(0,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace
  • Do $SYSTEM.SQL.FreezePlans(2,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace that were marked Frozen/Upgrade.

Then execute $system.SQL.Purge()

That should unfreeze everything and delete all CQs.

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)


USER>set stmt = ##class(%SQL.Statement).%New()
USER>set query = 1, query(1) = "SELECT * FROM testTable"
USER>write stmt.%Prepare(.query)
USER>set rs = stmt.%Execute()
USER>write rs.%Next()
USER>write rs.%Get("Column B")
USER>write rs.%Next()
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 believe this page explains why,  and I will also try my best to condense information:

  • Stored Procedures that return a single value are classified as Stored Functions. These can be SELECTed but not Stored Procedures.
  • Class Queries are SQL-Invokable-Routines (SIRs), but they have additional ClassMethods defined that allow it to be SELECTed. This is namely the <<name>>EXECUTE() and <<name>>FETCH() functions. This is reflected in the CREATE QUERY documentation that warns if EXECUTE() and FETCH() are not defined, SQLCODE -46 is thrown. (

If you have a java.sql.PreparedStatement and what seems to be a subclass (since you can call read() on it), why not utilize java.sql.PreparedStatement.setBinaryStream(int parameterIndex, InputStream x)? I am not sure which version of com.intersys.jdbc.CacheDriver you are using, but it is likely that your driver version has that API supported.

try (
    java.sql.Connection        cnxn  = DriverMasnager.getConnection(url, cnxnProps);
    java.sql.PreparedStatement pstmt = cnxn.prepareStatement(
            "INSERT INTO tableWithBlobField VALUES(?)");
    pstmt.setBinaryStream(1, binaryStreamFromRestAPI);

Hello Magnus,

I recommend  that you use a SQL CALL instead of IRIS Native for .Net.

The following CALL query should work: CALL SYS.Mirror_MemberStatusList('NAMEOFTHEMIRROR') and this is no different than using a stored procedure (because MemberStatusList is a class query).

If you do not have a connection to %SYS where SYS.Mirror exists , but the user for your existing connection does have privileges to access %SYS, you can write a wrapper stored procedure something similar to:

:#Include %occResultSet
    new $NAMESPACE
    set $NAMESPACE="%SYS"
    $$$ResultSet("CALL Sys.Mirror.MemberStatusList('" _ MirrorName _ ')")

Hello Elize,

Based on the error that you are receiving, it seems that you might be executing a direct SQL statement instead of a parameterized statement. This means that your DateTime object (new DateTime(1900, 1, 1, 12, 03, 30) is being converted to a String and this, in effect, bypasses our ODBC driver's handling of specific nuances regarding temporal types.  

%Library.Time expects either a proper time string (denoted by the regex /(\d{2}:){2}\d{2}(.\d*)?/) or an appropriate $HOROLOG value and for this reason, you get SQL error -147.

Nonetheless, there are several solutions you can go about this:

  • If you cannot change to a parameterized statement nor edit the code, but can change the contents of the direct SQL statement
    • INSERT INTO tablename VALUES(CAST({ ts '1900-01-01 12:03:30' } AS TIME))
  • If you cannot change to a parameterized statement but can edit code
    • Change the DateTime.toString() method to DateTime.toString("T") such that it returns the time string only
  • If you can change to a parameterized statement, be advised that DateTime is not a valid object to bind to a %Time column. Per Microsoft's document, you should be using a TimeSpan object to store time.​​ This is easily achieved by reference DateTime's TimeOfDay property.

According to the link above, you can set up an automatic task that periodically purges these orphaned messages. 

If you set the value of "NumberOfDaysToKeep" to a low value (0 for all messages at the time of purge) and set up a Task to purge messages on a periodic basis, this should meet your needs.