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.

-Dan

Hi,

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.

-Dan

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.

https://github.com/intersystems-community/samples-dynamicgateway-java

https://github.com/intersystems-community/samples-dynamicgateway-dotnet

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.

DROP TABLE IF EXISTS demo_person;
DROP PROCEDURE IF EXISTS multi_results;

CREATE TABLE demo_person (name VARCHAR(20), age INT, home_city VARCHAR(40));
CREATE PROCEDURE multi_results() RESULT SETS
LANGUAGE OBJECTSCRIPT
{
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:

In general, a <LIST> error indicates a malformed $list value.

USER>set bad = "something wrong"   

USER>write $li(bad,1)

WRITE $LI(bad,1)
^
<LIST>
USER>set good = $listbuild("something right")

USER>write $li(good,1)
something right

In %SerializeObject, this typically is caused by an expectation that a value is a valid list but it is not. In a more complex class that extends %SerialObject, one that contains at least one property whose type class is a swizzlable (persistent, serial, stream), $list(value, n) is used to extract the object value (id or serial value) from a value that is expected to be an OID. While OID's an be more complex, the typical OID format is $list(<serialized object value>, <most specific type class>). <serialized object value> can be a simple ID and <most specific type class> (MSTC) is the class of which the object is an instance but not an instance of any subclass of MSTC.

That isn't the only possibility but it is the most likely. I can't be more specific without seeing the full class definition.

-Dan

Hi Enrico,

I am listening! $system.external does not replace the old Java Gateway but the old Java and JDBC Gateways should still be available. I am not intimately familiar with either of those although I did some of the refactoring work with them both. I am not aware of any user code changes that are necessary to keep using them.

If you want to use the new External Java Server then you will necessarily need to write new code. I am happy to provide demos showing how this can be done. I can also assist you with implementing your own project to get you started. Just let me know how you wish to proceed.

As for leveraging existing Java libraries, you should be able to do many things without writing new Java code but not everything. If you have some examples of libraries you have attempted to use but were not successful, please provide information so that I can attempt to replicate your issues.

As always, I am here, listening, and happy to help!!

-Dan

@RobertCemper - our paths have crossed a time or two. I hope they will again.

Warning: what I describe here is not part of any released IRIS version. It is still in development and details might change.

We are working on an implementation of something we call "ASQ". This is intended to be a superset of the ISO Standard "JSON Path Language" (JPL). I suspect our current implementation will not meet all of your requirements but perhaps you will find this interesting:

USER>set obj = {"meta":{"versionId":"2","security":[{"system":"http://terminology.hl7.org/CodeSystem/v3-Confidentiality","code":"U","display":"unrestricted"}]}}

USER>zw obj.apply("$[*].meta.security[*].system")
["http://terminology.hl7.org/CodeSystem/v3-Confidentiality"]  ; <DYNAMIC ARRAY>

We can repeat this same pattern but remove the security field value's enclosing array and we see the same result:

USER>set obj = {"meta":{"versionId":"2","security":{"system":"http://terminology.hl7.org/CodeSystem/v3-Confidentiality","code":"U","display":"unrestricted"}}}

USER>zw obj.apply("$[*].meta.security[*].system")
["http://terminology.hl7.org/CodeSystem/v3-Confidentiality"]  ; <DYNAMIC ARRAY>

Of course there is more, much more. I have more complex examples if you are interested.

-Dan

I have to throw in my opinions and possibly a few facts regarding nulls and unique constraints.

IRIS Unique index - this is primarily a syntactical shortcut as it defines not only an index but also a unique constraint on the index key. Most pure SQL implementations don't merge the two concepts and the SQL standard doesn't define indexes. The SQL Standard does define unique constraints. Keep in mind that both IDKEY and PRIMARYKEY are modifiers of a unique constraint (and, in our world, the index defined as IDKEY is also special). There can be at most one index flagged as IDKEY and one that is flagged as PRIMARYKEY. An index can be both PRIMARYKEY and IDKEY.

There was once an SQL implementation that defined syntax for both "unique index" and "unique constraint" with different rules. The difference between them was simple - if an index is not fully populated (not all rows in the table appear in the index - we call this a conditional index) then the unique index only checked for uniqueness in the rows represented in the index. A unique constraint applies to all rows.

Also keep in mind that an index exists for a singular purpose - to improve the performance of a subset of queries. Any SQL constraint can be expressed as a query.

The SQL Standard is a bit inconsistent when it comes to null behavior. In the Framework document there is this definition:

A unique constraint specifies one or more columns of the table as unique columns. A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.

In the Foundation document, there exists two optional features, F291 and F292. These features define a unique predicate (291) and unique null treatment (292). These features appear to provide syntax where the user can define the "distinct-ness" of nulls. Both are optional features, both are relatively recent (2003? 2008?). The rule when these features are not supported is left to the implementor.

IRIS is consistent with the Framework document statement - all constraints are enforced on non-null keys only. A "null" key is defined as a key in which any key column value is null.

You can try using "ck-d" for the flags/qualifiers.

Very nicely done!