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

This should be checked on your environment, depending on the versions of Caché, Windows, and Microsoft SQL Server used. I have already provided the code for this above.

I would advise you to first make sure that you are using the latest version of Caché (and the ODBC/JDBC driver), which has fixed many bugs, including various memory leaks: Caché® and Ensemble® Change Notes (2018.1.11)

In addition, you may find the New Method $SYSTEM.Util.CleanDeadJobs() useful.