All
Accepted answers
Robert Cemper · 47 min ago go to post

You may create an SQL PROCEDURE to serve your needs: 

CREATE PROCEDURE %Z.NSPACE() RETURNS VARCHAR
Language ObjectScript
{
 set (%,%r)=""
 for  set %=$o(^|"%SYS"|SYS("CONFIG","CACHE","Namespaces",%)) quit:%=""  set %r=%r_%_";"
 quit %r
}

and it runs like this, assuming you have access to Namespace %SYS

USER>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------

The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
USER>>select %Z.NSPACE()
3.      select %Z.NSPACE()

Expression_1
%ALL;%SYS;DOCBOOK;SAMPLES;USER;

1 Rows(s) Affected
statement prepare time(s)/globals/lines/disk: 0.0006s/5/160/0ms
          execute time(s)/globals/lines/disk: 0.0005s/6/425/0ms
                          cached query class: %sqlcq.USER.cls4
---------------------------------------------------------------------------
USER>>
Robert Cemper · 2 hr ago go to post

Instead of 
var CS = "Server=XXXXX;User=system;Password=sys;";

This may work

var CS = "Server=XXXXX;User=_system;Password=sys;";

_SYSTEM (with leading underscore !!) is the default System Manager User
not sure if sys or SYS  for password  (in Caché)

And YES you  should use namespace %SYS

Robert Cemper · Apr 7 go to post

You should use the system variable $KEY to get input the terminator

doc in $KEY
suggested code:

USER>read *in set key=$key
;; after arrow back
USER>ZZDUMP in,key
0000: 32 37                         27
0000: 1B 5B 44                      .[D

so you get the full escape sequence in the variable key
or just 08 for backspace, .....

Robert Cemper · Mar 30 go to post

It is possible by using $replace function: some string to a different string
you define your fractional codes and run them in iterative loops 

USER>read txt
¼ test character and ½ test character and ¾ test character and
USER>s a14=$c(188)
USER>s a12=$c(189)
USER>s a34=$c(190) 
USER>s a17=$c(8528)

USER>write $replace($replace(txt,a14,"1/4"),a12,"1/2")
1/4 test character and 1/2 test character and ¾ test character and
USER>

Attention: 
Some codes are wide characters:  this makes the replacement tricky
example. 1/7 >> $c(8528) !!

Robert Cemper · Mar 30 go to post

There's no PPG involved, it was developed long before that.
To move it from ENS to your ONS you may use this merge

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

Just a guess after reading the source of Class %Library.ScrollableResultSet 

AND: you feed the Journal by both globals 

Robert Cemper · Mar 27 go to post

Basically, this query might provide the content you are looking for:

SELECT distinct TableName FROM Ens_Util.LookupTable

or simply $LB formatted output:

SELECT List(distinct TableName) FROM Ens_Util.LookupTable

or as JSON Array

SELECT JSON_Array(tablename) json from (
SELECT List(distinct TableName) tablename FROM Ens_Util.LookupTable)
Robert Cemper · Mar 24 go to post

Embedded SQL  &SQL(.-.-.-)
doesn't allow variables for Table names.
You should use %SQL.Statement instead
 

Set tTable = "%ZZ.resultset"_ tStamp
set sql=1
set sql(1)="DROP TAQBLE "_tTable
set statement=##class(%SQL.Statement).%New()
set result=statement.%ExecDirect(.statement,.sql) 
write  result.%SQLCODE
Robert Cemper · Mar 20 go to post
  • This example is using a TEMPORARY Table based on Process Private Globals.
  • You can access the content of the table just from the process that created the table. 
    Not from any other process.
  • From SMP you see the Table Definition from any Namespace because ot the %ZZ. package.
    so the table is FOUND but EMPTY
  • Though your SELECT from SMP runs in a totally different process with anything but your Process Private Global
  • You have to get your final SELECT and generating your temporary table into the same Process,

  • To achieve this in SMP, declare your ClassMethod As SqlProcedure like this:

    ClassMethod Export(nspace = "", query As %String = "") As %Integer [ SqlName = ZZEXPORT, SqlProc ]
{
	new $namespace
	;; and so on

  • Then you can run it from SMP using this SQLprocedure as Static WHERE condition.

    SELECT * from %ZZ.resultset
WHERE ZZEXPORT('SAMPLES',
'SELECT TOP 5 Description, ErrorStatus, Priority from ENS.MessageHeader')=0
  • For Static WHERE Conditions see my article just 6 years back.

  • Using a TEMPORARY table avoids problems if running in more than 1 instance in parallel. 

    Sorry, 
    The new Editor is not so well-suited for long line replies. I dislike it.

Robert Cemper · Mar 19 go to post

The demo is designed for large screens
icons are almost invisible, small, and text is more guessing than reading
White on black is not everyone's choice
 

Robert Cemper · Mar 18 go to post

I composed a variant that doesn't require any code in ENS.
It can all run from your ONS namespace.
All you need is a valid SQL SELECT statement
First, the utility class:

Class User.ZZ [ Abstract ]
 {

ClassMethod Export(nspace = "", query = "") As %Integer
{
	new $namespace
	zn:$l(nspace) nspace
	&SQL(DROP TABLE %ZZ.resultset)
	; default just for demo
		set:'$l(query) query="SELECT TOP 5 ssn, home_state,name from Sample.employee"

    ;
	set sql=2,
		sql(1)="CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as ",
		sql(2)=query
	set statement=##class(%SQL.Statement).%New()
	set result=statement.%ExecDirect(.statement,.sql)
	quit result.%SQLCODE
} }

a quick demo:

USER>set select="SELECT TOP 3 home_state,name,SSN from Sample.employee"
USER>w ##class(ZZ).Export("SAMPLES",select)
0
USER>:sql
SQL Command Line Shell
----------------------------------------------------
[SQL]USER>>select * from %ZZ.resultset
5.      select * from %ZZ.resultset 
| Home_State | Name | SSN |
| -- | -- | -- |
| NC | Emerson,Xavier O. | 217-28-9642 |
| CO | Malkovich,Bob V. | 226-71-5540 |
| UT | Joyce,Juanita V. | 396-77-7486 |
 
3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0579s/39,198/181,087/0ms
 execute time(s)/globals/cmds/disk: 0.0002s/4/815/0ms
 query class: %sqlcq.USER.cls73
----------------------------------------------------------

[SQL]USER>>cos ZN "SAMPLES"
[SQL]SAMPLES>>select * from %ZZ.resultset
6.      select * from %ZZ.resultset

| Home_State | Name | SSN |
| -- | -- | -- |
| NC | Emerson,Xavier O. | 217-28-9642 |
| CO | Malkovich,Bob V. | 226-71-5540 |
| UT | Joyce,Juanita V. | 396-77-7486 |
 
3 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0552s/39,198/181,087/0ms
 execute time(s)/globals/cmds/disk: 0.0002s/4/815/0ms
 query class: %sqlcq.SAMPLES.cls92
--------------------------------------------------
[SQL]SAMPLES>>q
 
SAMPLES>

hope this helps:
The temporary table is visible from both namespaces
And the utiltiy is not tied to a specific query or table
All just runs and exists in your process.
Also no traces in the Journal 
 

Robert Cemper · Mar 17 go to post

I was just running it without any problem in Windows Docker Desktop
Some preparation:

  • docker pull intersystemsdc/iris-community
    • 28 days old !
  • set DOCKER_BUILDKIT=1
  • docker compose build --no-cache

No problems with build

Start also OK
 

Robert Cemper · Mar 13 go to post

assuming  Document.StreamGC is of type %Stream.Object you may
use method FindAt which combines Read() and Contains()
see: method FindAt() 

method FindAt(position As %Integer, target As %RawString,
   ByRef tmpstr As %RawString = "", caseinsensitive As %Boolean = 0) as %Integer

Find the first occurrence of target in the stream, starting the search at position.
The method returns the position of this match, counting from the beginning of 
the stream, and leaves the stream positioned at an indeterminate location. 
If it does not find the target string, it returns -1.

If position=-1 then it starts searching from the location found in the 
previous search and returns the offset from the last search. 
This is useful for searching through the entire file. 
If you are doing this, you should pass in tmpstr by reference in every call. 
This is used to store the last buffer read, so the next call will start 
where the last one left off. 
If caseinsensitive=1 then the search will be case insensitive, 
rather than the default case-sensitive search.
Robert Cemper · Mar 12 go to post

A very dirty hack:  possible not really a recommendation

IF Source table is not mapped to ONS

  1. copy class definition including <Storage> from ENS to ONS
  2. in <STORAGE> you see
  3. <DataLocation>^Sample.EmployeeD</DataLocation>
<IdLocation>^Sample.EmployeeD</IdLocation>
<IndexLocation>^Sample.EmployeeI</IndexLocation>
<StreamLocation>^Sample.EmployeeS</StreamLocation>
  4. Now replace all  ^  by ^|"ENS"|  looks similar 
    For my example, it's ^|"SAMPLES"|
  5. <DataLocation>^|"SAMPLES"|Sample.EmployeeD</DataLocation>
<IdLocation>^"|SAMPLES"|Sample.EmployeeD</IdLocation>
<IndexLocation>^|"SAMPLES"|Sample.EmployeeI</IndexLocation>
<StreamLocation>^|"SAMPLES"|Sample.EmployeeS</StreamLocation>
  6. Extended Global references allow access to the other namespace. Nothing new, but risky
Robert Cemper · Mar 12 go to post

YES Sir!

  • Process private globals live in IRISTEMP and are strictly bound to the process and its lifetime.
  • The package with %ZZ.* makes sure it is visible from ALL namespaces
  • At process start it is always empty
    • you fill it implicitely by CREATE at definiftion time
    • or if reused by 
      INSERT INTO %ZZ.resultset SELECT .......
    • this SELECT must be the same as in CREATE ...
      to  fit  column names
  • Either CREATE once + INSERT later works
  • Or DROP TABLE %ZZ..... and fresh CREATE ...
  • It's a matter of taste, I tried both

Warning: 
Test from MGMT Portal (SQL) doesn't work as the process 
(and its ID) in background changes. The process stops and the content is gone.

IF your table must survive several processes, you need some 
other tricky solution based on the same basic idea of %ZZ*
combined with a manually tuned Class definition.

Robert Cemper · Mar 12 go to post

Hi @Evgeny Shvarov 
I created a mix from #1 and #2 without global mapping.
Temporary tables are excellent for this challenge 
as they drop at the end of the process. No clean-up needed !
It's kind of a Snapshot
 

Robert Cemper · Mar 12 go to post

A totally different approach, excluding JSON, pure SQL

  1. create temporary table
  2. Insert your result
  3. consume it within the same process
  4. eventually drop the table definition for a different query

I use the same query for my small code example

SELECT TOP 5 ssn, home_state,name from Sample.employee

USER>
USER>ZN "SAMPLES"

SAMPLES>:sql
SQL Command Line Shell
-----------------------------
[SQL]SAMPLES>>  << entering multiline statement
       1>>CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as
       2>>SELECT TOP 5 ssn, home_state,name from Sample.employee
       3>>go
       
17.    CREATE GLOBAL TEMPORARY TABLE %ZZ.resultset as
       SELECT TOP 5 ssn, home_state,name from Sample.employee
5 Rows Affected
[SQL]SAMPLES>>quit

SAMPLES>zn "USER"
USER>:sql
SQL Command Line Shell
-----------------------------
[SQL]USER>>SELECT * from %ZZ.resultset
18.     SELECT * from %ZZ.resultset

| SSN | Home_State | Name |
| -- | -- | -- |
| 217-28-9642 | NC | Emerson,Xavier O. |
| 226-71-5540 | CO | Malkovich,Bob V. |
| 396-77-7486 | UT | Joyce,Juanita V. |
| 558-18-5848 | IA | Ingleman,Josephine Q. |
| 943-90-5082 | TN | Page,Valery R. |

5 Rows(s) Affected

Now it's all in your hands
Eventually run DROP TABLE %ZZ.resultset
before next use with different columns.
Or just use different temp table for repeated use with other SELECT

Robert Cemper · Mar 11 go to post

Once upon a time, there was no JSON support!
But your challenge could be resolved by these steps:

  1. ZN form original namespace (ONS) to execution namespace (ENS)
  2. Execute query in ENS and get ResultSet 
  3. While in ENS, let %Display print the Resultset into SPOOL
  4. ZN to ONS.
  5. Loop over the spooled ResultSet to get your data line by line

A simplified example:

USER>ZN "SAMPLES"
SAMPLES>set sql="SELECT top 5 name, ssn from sample.employee"
SAMPLES>set sta=##class(%SQL.Statement).%New()
SAMPLES>write sta.%Prepare(.sql)
1
SAMPLES>set res=sta.%Execute()
SAMPLES>kill ^SPOOL($j)
SAMPLES>open 2:$JOB
SAMPLES>use 2 do res.%Display()
SAMPLES>close 2
SAMPLES>ZN "USER"

USER>Zwrite ^|"SAMPLES"|SPOOL($JOB)
^|"SAMPLES"|SPOOL(13488,1)="Name"_$c(9)_"SSN"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,2)="Emerson,Xavier O."_$c(9)_"217-28-9642"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,3)="Malkovich,Bob V."_$c(9)_"226-71-5540"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,4)="Joyce,Juanita V."_$c(9)_"396-77-7486"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,5)="Ingleman,Josephine Q."_$c(9)_"558-18-5848"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,6)="Page,Valery R."_$c(9)_"943-90-5082"_$c(13,10)
^|"SAMPLES"|SPOOL(13488,7)=$c(13,10)
^|"SAMPLES"|SPOOL(13488,8)="5 Rows(s) Affected"
^|"SAMPLES"|SPOOL(13488,2147483647)="{67640,77930{9{"
 
USER>

The content here is:

Name    SSN
Emerson,Xavier O.       217-28-9642
Malkovich,Bob V.        226-71-5540
Joyce,Juanita V.        396-77-7486
Ingleman,Josephine Q.   558-18-5848
Page,Valery R.  943-90-5082
5 Rows(s) Affected
Now you can loop over your result as on any other Global
as you need

Robert Cemper · Mar 10 go to post

same situation for Caché and IRIS

  • in the package you install class zrcc.EX.ISOS.cls 
  • class method ##class(zrcc.EX.ISOS).Gset(....) shows up
  • as SQL procedure zrcc_EX.Gset(....)

inside the classmethod it is all standard COS/ISOS
similar;

  • ##class(zrcc.EX.ISOS).Xcmd(....)  >>> zrcc_EX.Xcmd( ...) 

Of course: Working with Globals requires experience in COS/ISOS

Robert Cemper · Mar 10 go to post

ClassMethods can be projected to SQL as Procedures.
That's what I  used in the only class (ISOS.cls) of this package
The package was written for IRIS but worked in Caché as well for ages..

Robert Cemper · Mar 6 go to post

READY 2026 is the place to find your ultimate answers on IRIS.

in variation of Frank Sinatra:

  •   If you don't get it there
  •   You will NOT get it anywhere ! 
  •   It's up to 👉 YOU 👉 just YOU 👉 just YOU  ! 
Robert Cemper · Mar 4 go to post

so this acts in some way similar to #(variable + formula)# syntax
that we know from &html<..> or &js< ..> blocks for CSP

??

Robert Cemper · Mar 2 go to post

first you may try to run your query in your code for a few records 
to see by what format EFFDT is returned internally