New post
AllAccepted answers
Robert Cemper · Jan 20

If you use a custom class query %Library.Query type you may write your parameters to some
^mtemp.Evgeny($i(^mtermp.Egeny)) = ..... direct from the Execute method
or ^mtemp.Evgeny($h) = ....

For basic class query %SQL.Query () you may take the usual SQL approach

  • Create a SQL method that always returns 1 (TRUE)
  • You pass all your parameters into that method 
    • which does the ^mtemp trick and a QUIT 1
  • add to the WHERE clause  . . .  AND MYTRACE(par1,par2,---)=1

I refer to this a STATIC clause since it is only executed once by query
because of no reference to any column values 

It was my approach to SQL debugging
 

Robert Cemper · Jan 18 
Class MyPackage.MyClass Extends (%Persistent, %JSON.Adaptor)
{
 Property JSONid As %Integer(%JSONFIELDNAME = "id");
 Property strprop As %String;
 Property boolprop As %Boolean;
}

next this worked

set jsn={  "id": 1,  "strprop": "string", "boolprop": true }
set sc=obj.%JSONImport(jsn)
zw obj
+----------------- general information ---------------
|      oref value: 2
|      class name: MyPackage.MyClass
| reference count: 1
+----------------- attribute values ------------------
|       %Concurrency = 1  <Set>
|             JSONid = 1
|           boolprop = 1
|            strprop = "string"
+-----------------------------------------------------
Robert Cemper · Jan 6

$system.SQL.SetIdentityInsert(1) is deprecated and replaced by $system..SQL.Util.SetOption(IdentityInsert ,1) 

from class docs:>>>
https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic.cls?LIBRARY=%25SYS&CLASSNAME=%25SYSTEM.SQL.Util#METHOD_SetOption

IdentityInsert - Set the IDENTITY_INSERT option for this process. IDENTITY_INSERT
controls the ability of the user to specify a value for the IDENTITY property when
saving a new object, a value for the IDENTITY column, or an explicit ROWID value in an SQL INSERT.

Notes

  • Changing this configuration setting takes effect immediately and lasts for the duration of the process or until $SYSTEM.SQL.Util.SetOption("IsolationMode",pValue) is called again.
  • This is a per-process setting.

Sounds good to me 

Robert Cemper · Jan 5

As I expected, it's one of the new storage features that cause the irritation

/// Use hashed global names
Parameter USEEXTENTSET = 1

from docu  https://docs.intersystems.com/iris20253/csp/docbook/DocBook.UI.Page.cls?KEY=GOBJ_storageglobals#GOBJ_storageglobals_hashed

When you set USEEXTENTSET to 1, each index is also assigned to a separate global, instead of using a single index global with different first subscripts. Again, this is done for increased performance.

Not explicitly mentioned - this also affects  IDKEY !

And the example presented shows in detail that 
IdLocation and DataLocation are NOT identical anymore, as it used to be for decades
 

Robert Cemper · Jan 5

You can get the required Global reference like this programmatically:

 ; get compiled class  with your classname
USER>set classname="oex.Dir"
USER>set dic=##class(%Dictionary.CompiledClass).%OpenId(classname)
 ; get relationship to CompiledStorage
USER>set stor=dic.Storages.GetAt(1)
 ; get name of the ID-Global
USER>Write stor.IdLocation
^oex.DirD
USER>
Robert Cemper · Jan 4

I'm surprised that   data is already imported via SQL but ^myclassD has no content.
So take a look into the related generated class:

Storage Default
{
<Data name="DirDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
- - - - - -
after all properties
</Data>
<DataLocation>^oex.DirD</DataLocation>
<DefaultData>DirDefaultData</DefaultData>
<ExtentSize>986</ExtentSize>
<IdLocation>^oex.DirD</IdLocation>
<IndexLocation>^oex.DirI</IndexLocation>

 <IdLocation> is the one to go for
with all the new Storage strategies (columnar, sharded, ...  )
This might be quite an exotic global name and rarely the traditional.

Robert Cemper · Dec 28, 2025

What you describe is a Basic Class Query. Slightly advanced to embedded SQL
BUT inside a Custom Class Query 
https://docs.intersystems.com/supplychain20251/csp/docbook/DocBook.UI.Page.cls?KEY=GOBJ_queries_userqueries

You have Exec, and Fetch methods. 
They can mask whatever you like as long as you feed the formal requirements.
Define the following class methods in the same class:

  • querynameExecute — This method must perform any one-time setup.
  • querynameFetch — This method must return a row of the result set; each subsequent call returns the next row.
  • querynameClose — This method must perform any cleanup operations.

Where queryname is the name of the query.

Each of these methods accepts an argument (qHandle), which is passed by reference. You can use this argument to pass information among these methods.

So you can mask your DELETE (implemented by embedded SQL) or any other way
It's not the standard way, but nothing prevents you as long as the formalism is served.

Robert Cemper · Dec 21, 2025

You may reduce the number of  XDBC connects by 50%
#1)
Create an SQL function for the update

CREATE FUNCTION done_func(integer,varchar) RETURNS integer
    AS 'update my_postgres_table set status=$2 where id=$1'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN 1 ;

#2)
run your SELECT, adding the function in your WHERE clause which is always TRUE
 

select * from my_postgres_table limit 10000 Where 1 = done_Func(id,'S')

You should check the exact syntax for PostgreSQL
I just composed it guided by
https://www.postgresql.org/docs/current/sql-createfunction.html 
 

Robert Cemper · Dec 20, 2025

@Evgeny Shvarov  you  pointed me to the correct place
Did some checks using the most native approach.

 set sc=$system.OBJ.Import(file, . . . . .

Removing the Export Tag from the 2nd line in the 'hidden XML'  names DFI

<Export generator="IRIS" version="26" zv="IRIS for Windows (x86-64) 2024.3 (Build 217U)" ts="2025-09-21 16:53:05">

ends with unknown flle type !!!

After re-insert of that Export Tag all import works perfect


​​​​​​might be necessary to teach VScode to add that tag