Interesting! I didn't see which version of InterSystems IRIS you are using but you might try a couple of things:

First, don't return a status value from your method. Instead return a %Stream.GlobalBinary instance. If you still want a status, get it some other way. I recommend just throwing an exception - IRIS Native should handle that okay. The object value you get back will be a proxy object that should allow you to read the stream. In our documentation you might search for "reverse proxy objects".

If get some time, I will try to build a sample of doing this.

Not quite - I just defined that alias this morning to test something out. I think that a pre-defined (shipped with Iris) sql alias should launch the shell - I didn't know anything about pre-installed/shipped with Iris aliases.

That's because I defined that alias myself - on Linux it is the .iris_init file in my home directory. I don't know where that is on Windows...

I like :sql as a way to launch the shell - so that means this alias should be renamed to something else!!

USER>:sql "select top 2 * from person"
do $system.SQL.Execute("select top 2 * from person").%Display()
id    name    ssn    dob    home_street    home_city    home_state    home_zip
1    Willeke,Thelma K.    934-46-2099    18948    308 Madison Blvd    Bensonhurst    HI    71501
2    Nathanson,Will P.    771-91-1008    37359    9664 Second Place    Jackson    VA    15837

2 Rows(s) Affected

I suspect that is the case but I cannot verify it without seeing more of your code. Someone from our HealthShare team probably knows this but I don't work on that team. Sorry.

I didn't count the number of question marks but I assume you've verified that it matches the number of columns.

Also, there are limitations on the number of command line arguments you can pass but that number is rather high. I don't recall exactly what it is but it is certainly limited. At one time we established 16 as the maximum number of arguments. As I recall, we did nothing to impose an arbitrary limit but ObjectScript itself does have a limit.

My next questions are meant to establish where the error is reported. You can prepare a dynamic SQL statement and then execute it separately and multiple times. Is the error reported during prepare or during execute?

PS: (edited):

Sorry, I see now your code. You are using a HS Adapter to execute this statement. That leads me to a different question - can you prepare and execute this statement as a Dynamic SQL Statement?

set statement = ##class(%SQL.Statement).%New()

set status = statement.%Prepare(.tSQL)

If status is okay (1) then execute it - set result = statement.%Execute( <your parameter values go here>)

Then check result.%SQLCODE.

In the meantime, I will take a look at the Adapter (not my area but I can still look) to see if there is some limitation.

I agree with Herman - to a point. Leave the IDKEY index alone and just accept the system assigned idkey. In most cases this is the best policy. But - there are potentially several "candidate keys", all can be defined - or not, but one can be chosen to be the "primary key". In that case, define the key not as unique but as "primarykey". Some tooling works better with a defined primary key. It does not interfere with the IDKEY unless it is also defined as the "idkey".

Hi Jonathan,

I am very interested in this problem (I wrote the early version of dynamic statement parameter binding). My first question is simple - can you execute this statement using embedded SQL? If you can then we need to dig further to discover the correct error (some errors get masked as syntax when there is a different problem).

Thanks,

Dan

PS:

Of course, you will have to supply values for ? to test compile this as an embedded statement (:hv1, :hv2... can work) - or - can you try this using an SQL utility or as a ODBC/JDBC statement?

Just to clarify - inheritance considers the primary super class hierarchy. %Persistent must be the first class some where in the primary super class hierarchy. Sample.Employee extends Sample.Person which extends %Library.Persistent.

If there is no storage definition then a storage definition whose type is %Storage.Persistent is created when compiling the class. This is done before the storage definition is compiled (every class member is compiled by the class compiler). Then, when the storage definition is compiled and if the type is %Storage.Persistent then the %Storage.Persistent.STORAGECOMPILERCLASS will generate a full storage definition for the current class definition.

What does that mean? Well - if this is the first time this storage definition has been compiled then it is most likely empty, other than the type class. The STORAGECOMPILERCLASS will generate a complete storage definition. But even if the storage definition is already defined, the STORAGECOMPILERCLASS still reviews that definition. If there are any changes detected to the class definition then the storage definition is updated to reflect those changes.

That means that the user can manually edit the storage definition at any time and the storage compiler will simply make sure that the definition is "complete" - no missing properties, no missing indexes, and so on.

Bottom line is that the user is free to make any desired changes, including deleting the storage definition completely.

Keep in mind that some changes can make the compiled class incompatible with existing data.

I agree with Tim but I'll take it one step further. Foreign keys are much more useful than relationships. After all, a relationship is simply a foreign key that maintains references to instances of the related class in memory. The projection of a relationship to SQL is simply as a foreign key. The set of related objects is simply populated using an SQL query. The problem with relationships is that they are extremely sticky and that can cause large numbers of objects to be inadvertently swizzled into memory. With foreign keys you have no in-memory model. That means with a foreign key you have to manage desired swizzling. Some view that as a problem, I view it as an advantage.

Another advantage of using foreign keys is that you can define multiple foreign keys using the same key component properties. No need to define a direct reference.

Creative minds might come up with a calculated property whose value is derived from the foreign key components, adding property methods to manipulate the related object/objects. This calculated property could be the direct reference. Perhaps transient would be better as a transient property also has instance memory allocated for it.

I am a fan Robert...but...

https://community.intersystems.com/post/new-video-sql-%E2%80%93-things-…

Somewhere around the 36 minute mark of this video. Buried perhaps. Still, %SQL.CustomQuery has a lot of interesting capabilities.

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:…;
ORDER BY population DESC

and the results:

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:

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.

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.