There is much to be discovered regarding Object Persistence. The ability to specify an expression in place of a literal global name in any of the various LOCATION keywords is just one. For example, you can add a public variable to a LOCATION keyword and its value at object filing time will be used to form the global location where the filer will update the data. There is risk involved and these "features" are most likely not documented. I do not recommend using these in production systems unless you fully understand the ramifications of doing so.

That said, there are a number of features that may or may not be documented but are certainly not private. In the context of this message, two come to mind. First is the Object Journal. Override the parameter OBJJOURNAL in a persistent class and all other classes referenced by this class and filing events are journaled. The Object Journal records each filing event for classes with the OBJJOURNAL parameter set and another class, %ObjectJournalTransaction, can be used to view the versions of those objects that were filed. All this would be wonderful but for a bug that I just discovered while coming up with an example for this post. I did a bit of research and it seems this bug has been present for a very long time and never reported. That indicates nobody is aware that this feature exists. I fixed the bug temporarily for the example.

SAMPLES>set person = ##class(Sample.Person).%OpenId(10)

SAMPLES>write oldperson.Name
Uhles,Ralph W.
SAMPLES>set person.Home.State = "NY"

SAMPLES>w person.%Save()
1
SAMPLES>set person.Office.State = "FL"

SAMPLES>set person.Name = "Book, John J"

SAMPLES>write person.%Save()
1
SAMPLES>write person.Name
Book, John J
SAMPLES>set journal = ##class(%ObjectJournalRecord).IDKeyOpen(3,4)

SAMPLES>set oldperson = journal.OpenObjectVersion(.s)

SAMPLES>write oldperson.Name
Uhles,Ralph W.

The second item of interest is something that has been around for a while as well and is definitely a fully documented and supported feature. This feature, Triggers, was previously an SQL only feature but it is now (and has been for several versions) available for Object filing as well. Using a save trigger for Objects and SQL allows access to the old and new values as well as a way to detect which values have been modified. I am happy to post an example if anyone is interested.

While the ability to specify an expression as a LOCATION keyword value and the use of Object Journal are not well known or mainstream features, Triggers are very much mainstream and can be quite useful.

The difference between runs can be caused simply by normal variations in system load. The difference between Concurrency = 0 and Concurrency = 1 is only present when the object is stored in multiple global nodes. There are five possible values that are described in the %Library.Persistent class documentation (viewable through the SMP). Concurrency = 1 is referred to as "atomic read". For single node objects there is no extra work required for atomic read. 

The name of the <index>Open method is based on the name of the index. I suspect that in one case the name of the IDKEY index is "IDKEY" and in another it is "idkey". No <index>Open method is generated if the index is not unique, primary or id.

What I don't know is if you need SQL mapped storage because default storage doesn't work for your case. Inheritance of storage is not the same as for other class members. I wrote storage inheritance and I am intrigued by the case where there are common serializations shared by multiple classes. Steve Canzano's idea is really close to what I think is a good solution but introducing %SerialObject brings with it some complications. Interestingly enough, the idea of SQL Map inheritance was probably brought up first by Steve several years ago.

What is clear is that no storage is inherited from anything other than the primary super class. I would like to understand your need to see if that restriction is preventing you from doing as you wish. Relaxing that restriction is not a simple task so I would also like to know if there is an existing pattern that can address your need.

-Dan

I admit that when I first read this post I wasn't interested in commenting. I don't normally get involved in SQL mapping questions. @Brendan Bannon is the expert on that topic. As I read through this more, I became interested in what you didn't ask and I need to make sure that I'm on the right track.

Are you most interested in sharing the definition of a data node or do you have specific requirements that make you want to use SQL-mapped storage?

--Dan

That is a problem. SQL requires metadata to be provided at statement prepare time. Any SQL-Invokable-Routine (function - including Table-Valued Function - and procedure as invoked by CALL) must provide that metadata and that metadata is generated by the compiler. There is no dynamic prepare-time hook unfortunately.

There is syntax in standard SQL that allows you to provide "value to column binding" within the statement (late-schema binding) but we do not support that. 

You didn't specify a version so I'll use IRIS 2020.1. This should also work in 2019 versions. My example uses a Table-Valued Function. Any query can potentially be used as a table valued function. %SQL.CustomQuery simply generates all the infrastructure for you. Custom query works quite simply and is very similar to an embedded SQL cursor - you implement Open, Fetch and Close yourself (%OpenCursor, %FetchCursor, %CloseCursor). You define properties that correspond to columns in your row. You define private properties to hold the cursor state. Open typically binds the source data to the instance of your custom query and close releases any resources. Fetch simply advances the cursor to the next row, populating the properties that represent columns in your table. Look at the class doc for %SQL.CustomQuery for more details.

select * from TVF_TEST('[["one",2,"three"],["first",0,"third"]]')
col1
col2
col3
one
2
three
first
0
third

The class is simple:

Class User.TVF Extends %SQL.CustomQuery [ Language = objectscript ]
{

Parameter SQLNAME As String = "TVF_TEST";

Property data As %Library.DynamicArray [ Private ];

Property iterator As %Iterator.Array [ Private ];

Property col1 As %String;

Property col2 As %Integer;

Property col3 As %String;

Method %OpenCursor(data As %Library.DynamicArray) [ Private ]
{
    if $isobject(data) {
        set ..data = data
    } else {
        set ..data = [].%FromJSON(data)
    }
    set ..iterator = ..data.%GetIterator()
}

Method %FetchCursor() As %Library.Integer
{
    if ..iterator.%GetNext(.key,.value) {
        set ..col1 = value.%Get(0)
        set ..col2 = value.%Get(1)
        set ..col3 = value.%Get(2)
        return 1
    } else {
        set ..col1 = ""
        set ..col2 = ""
        set ..col3 = ""
    }
    return 0
}

}

I believe the correct way to report an error from an SQL-Invokable Routine (procedure or function) is to throw an exception as Eduard demonstrated. Attempting to set local variables or retrieve them using an SQL function/procedure might produce invalid results.

As for reporting a nested error - I don't know.

A side note - it is not necessary to extend %RegisteredObject when a class contains only class methods. Not extending %RegisteredObject will result in smaller runtime footprints.

Perhaps this link will help - https://cedocs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_from.

Look specifically for the paragraph on table-valued functions.

What isn't especially clear in this document is how to implement a custom query. The simplest and most portable way to implement a custom query is to extend %SQL.CustomQuery. Another way is to define a class query whose type is %Query, define a ROWSPEC, and implement the various functions. It is much simpler to extend %SQL.CustomQuery. There should be good documentation in that class.

There was a bug in 2017.1 that caused some problems with the seamless projection of a %SQL.CustomQuery subclass as an SQL function. That bug has since been fixed but I do not know if it is fixed in 2017.2.2.

If you need more information, please let me know.

-Dan

Hi,

Yes, SIR :) SQL Invokable routine. A procedure is invoked by CALL and a function is invoked using the function syntax. The primary difference between the two is in which statements you use to invoke the SIR. CALL invokes procedures, other DML statements can be used to invoke functions. The standard states that for every SQL Function there is an implicit procedure. That means that every SQL Function can also be invoked by CALL. The reverse is not necessarily true. IRIS SQL (and Caché SQL) does not enforce that restriction as even a procedure can be invoked using function syntax, as long as it returns a value. The issue with us doing that is that we do not restrict output-directed arguments and we do not restrict a Result Set Sequence (RSS) - strict SQL does not allow either in an SQL Function.  Just be careful - invoking such a procedure (with output directed parameters and/or returnresultsets) as a function will ignore all but the return value.

That said, where the SQL Function is located in a DML statement matters. In order for an SQL Function to be referenced in the FROM clause of a SELECT statement it must implement the Table-Valued Function (TVF) interface. Queries do that (I don't recall in which version we added that capability) and %SQL.CustomQuery does. Refer to the class documentation for %SQL.CustomQuery for more information. If you still have questions then let me know!

Defining a class method as a stored procedure will not work as it does not implement the TVF interface.

-Dan