I was tempted to criticize the Mixtral response. I did implement the original versions of both of these items so I know them both well. The Mixtral code example for %SQL.Statement is incorrect in a few ways but I'm surprised that it was as close as it is.

Once that temptation was put aside, I realized that Mixtral can only analyze what it finds. If it can't find the solution then perhaps our class documentation is not going to provide the answer to a user. Hmmm...

%SQL.Statement is part of the IRIS implementation of Dynamic SQL and it is based on the SQL Standard's Call Level Interface (CLI). It allows the user to prepare and execute any SQL statement, including DDL. The result of executing a dynamic statement is an instance of %SQL.StatementResult. That result contains at least %SQLCODE and %Message, indicating success/failure along with some minimal information about the failure.

Part of Dynamic SQL is also a result set interface - %SQL.IResultSet. There is at least one extention of %SQL.IResultSet, %SQL.ISelectResult. When a statement result is a result set or includes one or more result sets then those result sets are likely instances of %SQL.IResultSet.

The other result set classes (some deprecated) are not necessarily instances of %SQL.IResultSet but do implement the most common members of that interface.

There is another class, %SQL.CustomQuery, that can be used to implement custom queries that are more intuitive than class queries. The documentation for %SQL.CustomQuery contains information on how to implement your own custom query along with an example.

Actually, the problem is that %Exception.AbstractException already has five parameters. The error message you received is because the fifth argument has a type of %Exception.AbstractException and your fifth argument has a type of %String.

It is entirely possible to override the implementation of a member inherited from a super class so long as the inherited member is not defined as final and the signature of the local override is compatible with the inherited signature. The class compiler does perform a signature check to ensure the class does not violate the prime directive: Every instance of a class is implicitly a valid instance of its primary super class. Rewording that slightly - the interface of a class must be compatible with the interface of its primary super class. You can add new parameters to an inherited method but the existing parameters, those inherited, must declare a compatible type. A compatible type can be more specific (a subclass of the inherited type) but it cannot be a competely different type.

I tried adding a sixth parameter and also corrected the type of the fifth:

Class User.MyException Extends %Exception.AbstractException
Method %OnNew(pName As %String = "", pCode As %String = "", pLocation As %String = "", pData As %String = "", pInnerException As %Exception.AbstractException = {$$$NULLOREF}, myArg As %String) As %Status [ Private ]

This class compiled cleanly.

ObjectScript does not allow method overloading which is a different thing.

I like @Norman W. Freeman 's answer. That is the factory pattern and I use it regularly. %OnNew is a single method that is implemented in each class. There is a little known feature of %OnNew - it can return an oref whose type class is different from the class that implements %OnNew. In other words, %OnNew can be that factory method. Norman's Create() solution is cleaner IMO. The sole advantage (again, IMO) of a polymorphic %OnNew() is that it works with %New. I can provide a demo is anyone is interested.

I am a huge fan of try/catch. The try is virtually cost-free with overhead only encountered when an exception is caught. I do agree with Jon Willeke - when I am in older code I normally do not refactor $ZTRAP unless the changes I have to make are more than a simple edit. I also use RETURN when exiting a function/method instead of QUIT.

It is possible to simluate a finally but it isn't as nice as having a real finally block would be.

I can provide a solution for this but it comes with warnings - SQL doesn't do well with polymorphic embedded objects. If that isn't an issue then consider using a factory pattern. You must define a base class that extends %SerialObject and it must be instantiable. Then, override %OnNew() to dispatch to the concrete class that you wish to instantiate. Perhaps pass in a type argument. That %OnNew() can return an OREF (or a %Status if an error occurs).

If you are interested in this model then I can provide a small demo.


Yes, of course. JSON_TABLE in the FROM clause is just another table and can be used as such. If you have wish to use indexes on JSON values stored in columns then you can always define indexes on computed values. That has been possible even before JSON_TABLE. Keep in mind that dynamic data does not always follow expectations as a field in a dynamic object can be a literal, another object or an array of values.

I previously posted about ASQ. You can use ASQ to derive a computed value in your class definition.

I don't know if this works on Windows systems but for macOS and Linux (I use Ubuntu), I defined a file in my home folder named .iris_init with these contents:

:alias pp do ##class(%ASQ.SetUtils).pp($*)
:alias parse try { do ##class(%ASQ.SetUtils).pp(##class(%ASQ.Parser).parse("$*").toDao()) } catch e { w !,e.AsSQLMessage() }
:alias asq try { do ##class(%ASQ.SetUtils).pp($1.apply("$2")) } catch e { w !,e.AsSQLMessage() }
:alias find do find^%z($1,$2,$3,$4)

This file is automatically loaded each time I establish an IRIS session.


When you say "purging" do you mean that you are deleting the class definition? Or do you simply mean that you are deleting the data - perhaps by invoking %DeleteExtent or %KillExtent?

IRIS has three types of Classes - datatypes (not instantiable), registered (instantiable), and dynamic (not registered but instantiable). Your question seems to involve only registered types. While any data can be serialized/deserialized, we define an interface for doing so - the so-called Storage Interface. Storage Definition is a class member that can be included in just about any class but we only recognize and process it when the class is either a serial or persistent class. A serial class is a class that extends %SerialObject (meaning that %SerialObject is in its primary superclass hierarchy) and a persistent class is a class that extends %Persistent (again, %Persistent is in its primary superclass hierarchy). For these classes, the class compiler will ensure there is a fully defined Storage Definition. As with all class members, the Storage Definition has a type class. This type class is often referred to as the Storage Class - it is a class that implements the Storage Interface. The Storage Definition defines the format of the serialized object. For serial classes, this serialized object value is not directly stored anywhere. Rather, the serialized value is expected to be stored as a value embedded inside of another object. For persistent classes, an object is serialized, perhaps into multiple nodes, and stored in a global at a location defined by the IDKEY index. Persistent classes can also use globals for storing other things such as indexes, counters, metadata and so on. The set of all globals used by a persistent class is referred to as the Extent Set which is, by default, registered with the Extent Manager.

When you remove the Storage Definition from a serial or persistent class, then it is possible that any existing data serialized using the removed Storage Definition will not be able to be deserialized as it might not be compatible with a newly defined Storage Definition. We only recommend removing a Storage Definition from a class if there are no serialized/stored instances of the class present.

When you delete a persistent class, you can also delete the data and the Extent Set by adding the /deleteextent qualifier (or the 'e' flag) to the command. You can also delete the data by invoking %DeleteExtent (logical delete) or %KillExtent (physical delete - no constraints are enforced). That only deletes the data and leaves the Extent Set registered with the Extent Manager. You can delete the Extent Set from the Extent Registry by using utilities we provide. Start here: https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic....

I am happy to help if you wish (I wrote much of the core serialization/deserialization/storage code) but I will need to know more about the types of classes you are using and the issues you have.


We posted a sample project to the community github account. There are java and dotnet versions of the project. Take a look and we would appreciate any feedback you may want to offer.



Please note that there are some deficiencies when working with parameterized Java types when using the new External Java Server. Some of these were discovered during the refactoring of the %Net.Remote.Java.Test code. Some have already been addressed but not yet included in a release.

I came up with this quick and dirty example. I did discover a problem when I executed this using the Database Console in IntelliJ - evidently the IRIS Server requires statement results to be only ResultSets! (I've reported that problem). If the result of executing the insert statements is not added to the %sqlcontext then this works through client connections, otherwise an error is reported. I'll include the result of executing this from the command line and then displaying the results. Also, the results from executing this from a client with the insert results suppressed.


CREATE TABLE demo_person (name VARCHAR(20), age INT, home_city VARCHAR(40));

    set ins = $system.SQL.Prepare("insert into demo_person(name, age, home_city) VALUES(?,?,?)")
    do $system.SQL.Execute("TRUNCATE TABLE demo_person")
    do %sqlcontext.AddResultSet(ins.execute("Dan", 25, "Miami"))
    do %sqlcontext.AddResultSet(ins.execute("Jorge", 32, "Tampa"))
    do %sqlcontext.AddResultSet(ins.execute("Enrico", 29, "Turin"))
    do %sqlcontext.AddResultSet(ins.execute("Alexy", 21, "London"))
    do %sqlcontext.AddResultSet($system.SQL.Execute("SELECT name, age, home_city FROM demo_person ORDER BY age DESC"))

CALL multi_results();
USER>set result = $system.SQL.Execute("call multi_results()")

USER>do result.%Display()

Dumping result #1
1 Row Affected

Dumping result #2
1 Row Affected

Dumping result #3
1 Row Affected

Dumping result #4
1 Row Affected

Dumping result #5
name    age    home_city
Jorge    32    Tampa
Enrico    29    Turin
Dan    25    Miami
Alexy    21    London

4 Rows(s) Affected

The results when not adding the insert results as displayed by a database console:

name age home_city
Jorge 32 Tampa
Enrico 29 Turin
Dan 25 Miami
Alexy 21 London