Custom queries can also be instantiated without using SQL. Simply call %New and pass in the arguments that are defined by the %OpenCursor method. There is one difference here - the first argument of %New is the SELECTMODE and subsequent arguments correspond to the %OpenCursor arguments. Once instantiated, the interface is like any other %SQL.IResultSet.

USER>set result = ##class(example.custom.Query).%New(,"https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*&DATE_=7","Default")

USER>write result.%Next()
1
USER>write result.name
Alabama
USER>write result.population
4849377
USER>while result.%Next() { write !,result.name,": ",result.population }

Alaska: 736732
Arizona: 6731484
Arkansas: 2966369
California: 38802500

First, keep in mind that all implementations (faithful implementations that is) of %SQL.CustomQuery are also projected as table-valued functions. That means you can include the function in the FROM clause of a SELECT statement.

The process of implementing a custom query is simple. These steps are described in the %SQL.CustomQuery class documentation so I'll just summarize here.

  1. Define a new class that extends %SQL.CustomQuery;
  2. Override the SQLNAME parameter, assign a valid SQL identifier that is to be the name of the TVF;
  3. Define properties, in order, that are the columns of each row returned by this query. Let's call these "result columns". Each result column is defined as a non-private property;
  4. Define properties that you will need to maintain the source data, pointers, etc. that you will use to manage the data used to produce rows. These properties are defined as "private";
  5. Override %OpenCursor. Add parameters to this method override that correspond to the input parameters that will be passed when instantiating the custom query;
  6. Override %FetchCursor. In this method, check for end of data. If not at the end then populate all of the result properties with data and return 1 (true). Otherwise, clear all result properties and return 0;
  7.  Override %CloseCursor. In this override, release any resources acquired during instantiation and perform any necessary cleanup.

I won't post the version of the class that produces this output since the version of %Net.Http in current versions of CE/IRIS do not have a working GetJSON() method. The version of the class I'm posting simply passes in the raw JSON data as an argument.

The query:

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_query('https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*...','Default') 
ORDER BY population DESC

and the results:

stateCode name population
06 California 38802500
48 Texas 26956958
12 Florida 19893297
36 New York 19746227
17 Illinois 12880580

5 row(s) affected

Using this version of a custom query class:

 
Class example.custom.JsonQuery Extends %SQL.CustomQuery
{

Parameter SQLNAME As String = "sample_custom_json_query";

Property data As %Library.DynamicAbstractObject [ Private ];

Property iterator As %Iterator.AbstractIterator [ Private ];

Property atEnd As %Integer [ InitialExpression = 0, Private ];

Property stateCode As %String;

Property name As %String;

Property population As %Integer;

Method %OpenCursor(data As %String(MAXLEN="")) [ Private ]
{
    try {
        if $isobject(data) {
            set ..data = data
        } else {
            set ..data = [].%FromJSON(data)
        }
        set ..iterator = ..data.%GetIterator()
        if '..iterator.%GetNext(.key,.value) {
            set ..atEnd = 0
            set ..iterator = ""
        }
    } catch exception {
            // this is just a place holder, this method reports errors by throwing an exception
            // but a catch can allow the user to log errors or perform some self-healing action
        throw exception
    }
}

Method %FetchCursor() As %Library.Integer
{
    set response = 0
    if ($isObject(..iterator)) && ('..atEnd) {
        if ..iterator.%GetNext(.key,.value) {
            set ..name = value.%Get(0)
            set ..population = value.%Get(1)
            set ..stateCode = value.%Get(3)
            set response = 1
        } else {
            set ..atEnd = 1
            set ..iterator = ""
        }
    } else {
        set ..name = ""
        set ..population = ""
        set ..stateCode = ""
    }
    return response
}

Method %CloseCursor() [ PlaceAfter = %Next, Private ]
{
        // not really necessary as %OnClose will automatically close the cursor during destruction
        // but users can place code here to clean up other resources allocated for this query instance
        // that are external to the query instance. Like a temporary global.
    set ..iterator = ""
	set ..data = ""
}

}

and this query

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_json_query('[["STNAME","POP","DATE_","state"],["Alabama","4849377","7","01"],["Alaska","736732","7","02"],["Arizona","6731484","7","04"],["Arkansas","2966369","7","05"],["California","38802500","7","06"],["Colorado","5355866","7","08"],["Connecticut","3596677","7","09"],["Delaware","935614","7","10"],["District of Columbia","658893","7","11"],["Florida","19893297","7","12"],["Georgia","10097343","7","13"],["Hawaii","1419561","7","15"],["Idaho","1634464","7","16"],["Illinois","12880580","7","17"],["Indiana","6596855","7","18"],["Iowa","3107126","7","19"],["Kansas","2904021","7","20"],["Kentucky","4413457","7","21"],["Louisiana","4649676","7","22"],["Maine","1330089","7","23"],["Maryland","5976407","7","24"],["Massachusetts","6745408","7","25"],["Michigan","9909877","7","26"],["Minnesota","5457173","7","27"],["Mississippi","2994079","7","28"],["Missouri","6063589","7","29"],["Montana","1023579","7","30"],["Nebraska","1881503","7","31"],["Nevada","2839099","7","32"],["New Hampshire","1326813","7","33"],["New Jersey","8938175","7","34"],["New Mexico","2085572","7","35"],["New York","19746227","7","36"],["North Carolina","9943964","7","37"],["North Dakota","739482","7","38"],["Ohio","11594163","7","39"],["Oklahoma","3878051","7","40"],["Oregon","3970239","7","41"],["Pennsylvania","12787209","7","42"],["Rhode Island","1055173","7","44"],["South Carolina","4832482","7","45"],["South Dakota","853175","7","46"],["Tennessee","6549352","7","47"],["Texas","26956958","7","48"],["Utah","2942902","7","49"],["Vermont","626562","7","50"],["Virginia","8326289","7","51"],["Washington","7061530","7","53"],["West Virginia","1850326","7","54"],["Wisconsin","5757564","7","55"],["Wyoming","584153","7","56"],["Puerto Rico Commonwealth","3548397","7","72"]]') 
ORDER BY population DESC

produces the same result:

stateCode name population
06 California 38802500
48 Texas 26956958
12 Florida 19893297
36 New York 19746227
17 Illinois 12880580

5 row(s) affected

I am happy to post other examples if you wish.

Dan

Sure, Fab, but let's compare apples to apples. Your test takes the most highly optimized $list traversal ($listnext) and compares its performance to an iterator. An iterator is an instance of a class and you are invoking methods to iterate through a dynamic array which, by its very definition, shouldn't have holes in it. So let's compare direct iteration vs. object iterators. I populated array and list the same way you did in your class. Then a simple command line test. Not valid as a white room benchmark but still it gives us a sense of what can be. Try it. Dynamic array random access is much better than $list random access. As the third test shows. 

%SYS>set start=$zh for i=0:1:array.%Size-1 { set disregard=array.%Get(i) } w !,$zh-start        

.000019

%SYS>set p=0,start=$zh while $listnext(list,p,value) { set disregard=value } w !,$zh-start

.007311
%SYS>set start=$zh for i=1:1:$ll(list) { set disregard=$li(list,i) } w !,$zh-start

8.673268

For 2017.1CE and later as well as all IRIS versions, %SQL.CustomResultSet should not be used. Instead, use %SQL.CustomQuery. There are several good reasons for this. There is good class documentation available. I am happy to post examples if anyone is interested.

You should not write %BuildIndices/%PurgeIndices. Can you provide the SQL map definition and the index definition of one of your indexes - just as an example? Perhaps we can start with a simple index on a table with a smaller number of rows?

That is a good question! I checked the internal change logs and found my changes were in 2020.1. Maybe earlier but I installed 2020.1 build 215 and tested a simple case.

IRIS for UNIX (Apple Mac OS X for x86-64) 2020.1 (Build 215U) Mon Mar 30 2020 20:24:45 EDT

The test is simple - I am running in the 2020R1 instance, namespace USER and there are no classes runnable in this namespace - just a clean, new install. I have several other instances running, all different versions/build numbers. I am testing against my XDBC instance which is currently labeled as 2020.4 (obviously not yet released but the IRIS Native code is essentially the same as in 2020.1). The XDBC instance is listening on port 51780. First, proof this doesn't work locally.

USER>write ##class(Sample.Person).CurrentAge($h-35000)                 

WRITE ##CLASS(Sample.Person).CurrentAge($H-35000)
^
<CLASS DOES NOT EXIST> *Sample.Person

And then, attempt the same function using the IRIS Native connection to the XDBC instance.

USER>set host="localhost",port=51780,namespace="USER",user="_SYSTEM",pwd="SYS" 

USER>set connection = ##class(%Net.DB.DataSource).CreateConnection(host, port, namespace, user, pwd)

USER>set iris = connection.CreateIris()                                                             

USER>write iris.ClassMethodValue("Sample.Person","CurrentAge",$h-35000)                             
95

This is just a sample. Browse the classes in the SMP Explorer, look at the class docs for %Net.DB.Iris.cls.

This functionality might also be present in a 2019 kit but I didn't test it.

Let me focus on the last two items in your list. IRIS Native for Java, Node.js, DotNet, Python - these are all consistent implementations of the IRIS Native API and the communication is over TCP or shared memory. IRIS Native for ObjectScript is just another - consistent - implementation of the IRIS Native API.

To get a connection to an IRIS server, the command is similar across all implementations of IRIS Native API:

set connection = ##class(%Net.DB.DataSource).CreateConnection(host, port, namespace, user, pwd)

Once you have a connection, you can get an IRIS object.

set iris = connection.CreateIris()

and from an iris object, you can invoke class methods, code implemented in routines, set/get globals, and so on.

Can you elaborate? How else can a session running on one IRIS instance invoke a class method on some other IRIS instance?

There are a number of limitations on the size of an SQL statement I think, maximum string length is one of those limitations. It is based on the normalized "signature", including the statement text, as a single string, even when you pass the source statement as an array. I haven't worked in this area for several years so I can't speak to the details.

Timothy Leavitt's excellent response notwithstanding, this is supported. I do fully embrace the option presented by Timothy Leavitt. The structures I demonstrate here actually produce a model very close to his and the index, since it includes both KEYS and ELEMENTS is projected to the child table projected from the addresses array. Of course, reversing KEYS and ELEMENTS in the index key specification would make the index more useful for searching on city name.

This definition:

Property addresses As array Of Sample.Address;
Index xA On (addresses(KEYS), addresses(ELEMENTS).City)

Not only works but the filing code also recognizes the ability to fold both properties in the index into the same iterator:

    If ('pIndexHandle)||($Ascii($Get(pIndexHandle("Sample.Person")),5)=1) {
        set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",""))
        While bsv26N1 '= "" {
            Set bsv0N8=$zu(28,##class(Sample.Address).%Open($select(^Sample.PersonD(id,"addresses",bsv26N1)="":"",1:$listbuild(^Sample.PersonD(id,"addresses",bsv26N1)_""))).City,7,32768)
            Set ^Sample.PersonI("xA",bsv26N1,bsv0N8,id)=$listget(bsv0N2,1)
            set bsv26N1 = $Order(^Sample.PersonD(id,"addresses",bsv26N1))
        }
    }

And a quick test shows this structure is produced:

panther.local:XDBC:USER>d ##class(Sample.Person).Populate(10) 

panther.local:XDBC:USER>zw ^Sample.PersonI("xA")

^Sample.PersonI("xA","A886"," GANSEVOORT",3)=""

^Sample.PersonI("xA","B350"," MIAMI",6)=""

^Sample.PersonI("xA","B748"," NEWTON",3)=""

^Sample.PersonI("xA","C135"," UKIAH",9)=""

^Sample.PersonI("xA","C261"," ALBANY",1)=""

^Sample.PersonI("xA","C883"," DENVER",2)=""

^Sample.PersonI("xA","D162"," ST LOUIS",4)=""

And this has been in the product since maybe 2010. I couldn't find the original release note for this but I did find a change that fixed a problem when consolidating the iterators and that fix is present in 2010.2.