Merge ^|"ONS"|IRIS.TempRSet=^|"ENS"|IRIS.TempRSet

Why do this if ^IRIS.Temp* is accessible from any namespace?

Did you open the documentation links I provided above?

In which namespace is ^IRIS.TempRSet located?

System Use of IRISTEMP

These globals are automatically mapped to IRISTEMP and include:

• ^IRIS.Temp*
• ^CacheTemp*
• ^mtemp*

Am I correct in assuming that this mechanism relies on Process Private Globals (similar to Robert’s option)?

No.

If not, how can I view the global values in the SMP?

Using the Globals Page

Does each “rs” value get stored directly in ^IRIS.TempRSet, or does each "rs" data have its own node within ^IRIS.TempRSet? If it saved in separate node, how a name for such a node can be determine?

USER>zwrite ^IRIS.TempRSet
^IRIS.TempRSet=counter
^IRIS.TempRSet(rsId)=...
^IRIS.TempRSet(rsId,...)=...

Are the stored data/nodes removed automatically, or do they require a manual KILL operation?

%Library.ScrollableResultSet

Note that if you open a %ScrollableResultSet and do not call %Save on it then when you close this object the data stored in the global will be removed and so you will not be able to open this resultset again. So if you open a scrollable resultset and you wish to use this again call %Save on it, but you must always make sure that when you are finished with it you do not call %Save so the temporary global used is cleaned up when you are done. Alterntively you can call %DeleteId passing the id to remove the saved data.

Temporary data is stored in the global ^IRIS.TempRSet, which of course can be viewed in the Portal.

From the description of your task, it will be easier to use the %ScrollableResultSet class.

Here is a small example:

try{
  n $namespace
  s $namespace="ENS"
  s rs=##class(%ScrollableResultSet).%New()
  d rs.Prepare("query in ENS")
  d rs.Execute()
  d rs.%Save()
  s rsId=rs.%Id()
  w "rsId=",rsId,!
  k rs

  s $namespace="ONS"
  
  s rs=##class(%ScrollableResultSet).%OpenId(rsId)
  while rs.Next() {
    w rs.Data("your data"),!
  }
}catch(ex){
}

86

WIJ Block Comparison (MISMATCH.WIJ)

See

• Integer Codes for Data Types
• SQL Metadata
• %Set()

Here is a small example:

Class dc.test Extends %Persistent
{

Property pClob As %Stream.GlobalCharacter;

Property pBlob As %Stream.GlobalBinary;

ClassMethod RSToDynamicArray(ByRef rs As %SQL.StatementResult) As %DynamicArray
{
  #dim metaData as %SQL.StatementMetadata = rs.%GetMetadata()
  ;d metaData.%Display()
  
  f i=1:1:metaData.columns.Count() {
    #dim column As %SQL.StatementColumn = metaData.columns.GetAt(i)
    s colInfo(i,"label") = column.label,
      colInfo(i,"ODBCType") = $$$GetOdbcTypeName(column.ODBCType)
  }
  s colInfo=i
    
  s arr = []
  while rs.%Next() {
    s row = {}
    f i = 1:1:colInfo {
      s label=colInfo(i,"label"),
        odbcType=colInfo(i,"ODBCType"),
        val = rs.%GetData(i)
        
      i val="" {
        d row.%Set(label,"","null")
      }elseif odbcType="BIT" {
        d row.%Set(label,$num(val),"boolean")
      }elseif odbcType="DATE" {
        d row.%Set(label,$zd(val,3))
      }elseif (odbcType="LONGVARCHAR")||(odbcType="LONGVARBINARY") {
        d row.%Set(label,##class(%Stream.Object).%Open(val),"stream")
      }elseif $IsValidNum(val) {
        d row.%Set(label,$num(val),"number")
      }else {
        d row.%Set(label,val,"string")
      }
    }
    d arr.%Push(row)
  }    
  
  q arr
}

ClassMethod Test()
{
  d ..%KillExtent()
  
  s tmp=..%New()
  d tmp.pClob.Write("Hello")
  d tmp.pBlob.Write($c(0,1,7))
  d tmp.%Save()
  
  s sql="select 'hello' fStr" _
  ",2 fInt" _
  ",current_date fDate" _
  ",cast(1 as BIT) fBool" _
  ",null fNull" _
  ",(select top 1 pClob from dc.test) fClob" _
  ",(select top 1 pBlob from dc.test) fBlob"

  #dim rs As %SQL.StatementResult=##class(%SQL.Statement).%ExecDirect(,sql)
  d ..RSToDynamicArray(.rs).%ToJSON()
}

}

Result:

USER>d ##class(dc.test).Test()
[{"fStr":"hello","fInt":2,"fDate":"2026-03-05","fBool":true,"fNull":null,"fClob":"Hello","fBlob":"\u0000\u0001\u0007"}]

PS: By the way, in version 2016.3, this could be done much more easily:

write rs.$compose("%Array").$toJSON()

Unfortunately, I have not found a way to replicate this code in version 2025.3

See:

• Resultset to JSON
• Creating JSON objects from ObjectScript objects

It looks like you are using Dynamic SQL Using Older Result Set Classes

Could you please specify which class you are using: %Library.ResultSet or %ResultSet.SQL?

It looks like it has something to do with your past issue: https://community.intersystems.com/post/how-can-i-code-if-studio#commen…

In Caché Studio 2014.1, the following code worked for me and produced the result:

w $zutil(67,15,$j)
w $zutil(67,12,$j)

I would like to clarify once again that the InterSystems JDBC driver itself returns its current limits on the maximum size of certain types of data that can be viewed by any JDBC client (DbVisualizer, SQuirreL, etc.).

Let's check together in practice the maximum size of LONGVARCHAR in the InterSystems JDBC driver.

Class dc.test Extends %Persistent  
{  
Property Note As %Stream.GlobalCharacter;  
}

import java.io.*;
import java.sql.*;
class IRISDemo
{
    public static void main(String[] args)
    {
        Connection db = null;
        try
        {
            File file = new File("C:\\big.iso");
            FileInputStream fis = new FileInputStream(file);
            InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
            BufferedReader br = new BufferedReader(isr);
            Class.forName("com.intersystems.jdbc.IRISDriver");
            DriverManager.setLoginTimeout(3);
            db = DriverManager.getConnection("jdbc:IRIS://localhost:1972/USER", "_SYSTEM", "SYS");
            PreparedStatement ps = db.prepareStatement("update dc.test set Note=? where id=1");
            ps.setCharacterStream(1, br, file.length());
            System.out.printf("Result = %s", ps.executeUpdate()).println();
            ps.close();
            db.close();
            System.out.println("OK!");
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

If the file size exceeds 2147483647 characters, the following error occurs:

java.sql.SQLException: Stream too long: 2895740928 at com.intersystems.jdbc.IRISPreparedStatement.setCharacterStream(IRISPreparedStatement.java:889)

Same thing, just different:

set true="1",
    false=0,
    j={}

do j.%Set("a",1,"boolean")
do j.%Set("b",0,"boolean")
set j.c=true
set j.d=false
  
set iter = j.%GetIterator()
while iter.%GetNext(.key, .value, .type ) {
  write "key = "key", value = "value", type = "_type,!
}
Output:
key = a, value = 1, type = boolean
key = b, value = 0, type = boolean
key = c, value = 1, type = string
key = d, value = 0, type = number

Actually, my another proposal is to have true and false as part of the language, where true=1 and false 0 or similar for booleans.

Isn't that the case now?

set true="1",
    false=0,
    j={"a":true,"b":false,"c":(true),"d":(false)}

set iter = j.%GetIterator()
while iter.%GetNext(.key, .value, .type ) {
  write "key = "_key_", value = "_value_", type = "_type,!
}

Output:
key = a, value = 1, type = boolean
key = b, value = 0, type = boolean
key = c, value = 1, type = string
key = d, value = 0, type = number

@Evgeny Shvarov

Unfortunately, I have not yet found a documented and [Not Internal] way out of the box to see not only the text of the request, but also the values of its input parameters.

PS: take a look at the methods of the %SYSTEM.SQL.xDBC class. Here is an example of the log for the query above (from DbVisualizer):

SQL text (raw from client): 1 lines
line 1: SELECT * FROM del . a WHERE id < :%qpar(1)
        Var Types: Parameter 1: Constant of type INTEGER
        INPUT params:
          %qpar(1) = 3

If auditing is enabled, you can see the query without the actual parameter values.
To copy the query to the SQL query tool, the query will also have to be cleaned of garbage.

For example, there is the following query

select * from del.a where id<3

From SMP:

Event: DynamicStatementQuery
Event Data: SELECT * FROM del . a WHERE id < ?

From DbVisualizer:

Event: XDBCStatementQuery
Event Data: SELECT * INTO :i%%col1, :i%%col2, :i%%col3 FROM del . a WHERE id < :%qpar(1)

Rather, the discussion was about out-of-the-box equivalents of some SQL functions, so that programmers wouldn't have to reinvent the wheel. Furthermore, the routine of ^%qarfunc is undocumented.

For example, making an Object Script analog of VECTOR_COSINE for integer vectors yourself is a non-trivial task, given that $VECTOROP doesn't support the integer type.

Sometimes it's easier to use embedded SQL.

I'm curious why LPAD, RPAD and similar string functions are not exposed as $SYSTEM.SQL.Functions class members