There are several hurdles to consider:
Quite a can of worms.
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>>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
THANK YOU @Dmitry Maslennikov for this excellent insight into the probably
most important internal structure element of IRIS and its data type variations.
As you mentioned, InterProcessCommunication, it might be worth taking
a look at my Article + Demo on OEX
Using Interjob Communication (IJC) + InterJob-communication
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 .[Dso you get the full escape sequence in the variable key
or just 08 for backspace, .....
Thanks for the enlightenment.
That has never passed my ways
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) !!
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.TempRSetJust a guess after reading the source of
AND: you feed the Journal by both globals
Basically, this query might provide the content you are looking for:
SELECT distinct TableName FROM Ens_Util.LookupTableor simply $LB formatted output:
SELECT List(distinct TableName) FROM Ens_Util.LookupTableor as JSON Array
SELECT JSON_Array(tablename) json from (
SELECT List(distinct TableName) tablename FROM Ens_Util.LookupTable)
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
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 onThen 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')=0Using 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.
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
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
I was just running it without any problem in Windows Docker Desktop
Some preparation:
No problems with build
Start also OK
I fail to understand your question.
For the %ZZ*case it is all SQL. no Objectscript involved
as you see in this reply
The dirty hack is based on editing a copy of the class you want to query
as described in my other reply.
For a description of extended global reference syntax, see Extended Global References.
THANKS for your feedback !
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.A very dirty hack: possible not really a recommendation
IF Source table is not mapped to ONS
<DataLocation>^Sample.EmployeeD</DataLocation>
<IdLocation>^Sample.EmployeeD</IdLocation>
<IndexLocation>^Sample.EmployeeI</IndexLocation>
<StreamLocation>^Sample.EmployeeS</StreamLocation> <DataLocation>^|"SAMPLES"|Sample.EmployeeD</DataLocation>
<IdLocation>^"|SAMPLES"|Sample.EmployeeD</IdLocation>
<IndexLocation>^|"SAMPLES"|Sample.EmployeeI</IndexLocation>
<StreamLocation>^|"SAMPLES"|Sample.EmployeeS</StreamLocation> YES Sir!
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.
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
A totally different approach, excluding JSON, pure SQL
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) AffectedNow it's all in your handsEventually run DROP TABLE %ZZ.resultsetbefore next use with different columns.Or just use different temp table for repeated use with other SELECT
Once upon a time, there was no JSON support!
But your challenge could be resolved by these steps:
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
From your initial question, I understood that you have SQL access over ODBC
so this article might show a workaround: Get your code over ODBC/JDBC
You are free to formulate whatever you want to achieve.
same situation for Caché and IRIS
inside the classmethod it is all standard COS/ISOS
similar;
Of course: Working with Globals requires experience in COS/ISOS
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..
Way back in 2020 I wrote an example of how to run COS over ODBC/JDBC
Works of course for any ^Global manipulations
ObjectScript-Over-ODBC-
In OEX there are 2 examples to create a persistent (or temporary) class from JSON
Launching a related query to get a ResultSet is just a standard exercise then.
READY 2026 is the place to find your ultimate answers on IRIS.
in variation of Frank Sinatra:
so this acts in some way similar to #(variable + formula)# syntax
that we know from &html<..> or &js< ..> blocks for CSP
??