User bio
404 bio not found
Member since Nov 7, 2015
Replies:

I submitted some feedback to documentation regarding the content you cited.

The line in question originated from something I wrote while implementing the %On*Finally set of methods. The "calling method" in this context refers to the method that invokes the %On*Finally() method. In this case, %Save() is the calling method.

%Save may or may not initiate a transaction, depending on a few factors. If %Save does initiate a transaction then that transaction is complete prior to the %OnSaveFinally() call.

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.

%Library.ResultSet remains in the product for backward compatibility reasons but there are better ways to execute class queries. Any class query can be projected as a table valued function (TVF). TVF's can be executed if the class query also declares SQLPROC. A TVF can be included in the FROM item list, it can be joined with other FROM items, it can be ordered, restricted and a subset of available columns made available. Here is a simple example from the Sample.Person class:

select id,name,dob from sample.SP_Sample_By_Name('Ad') order by dob desc

I populated Sample.Person with some generated data and ran the above statement:

 
ID Name DOB
855 Adams,Elvira X. 03/16/2021
1378 Adams,Ed L. 01/15/2018
477 Adams,Debra S. 10/01/2015
1341 Adam,Chad U. 10/20/2013
32 Adam,Dmitry N. 10/28/2010
1099 Adams,Pam Z. 10/20/1993
897 Adam,Joe Y. 02/23/1984
1469 Adam,Phyllis N. 04/20/1982
358 Adam,Liza H. 12/13/1980
1096 Adam,Belinda Z. 08/02/1975
1269 Adam,Charlotte P. 03/03/1974
1396 Adams,Robert E. 03/14/1973
1109 Adams,Quigley H. 01/01/1968
454 Adam,Amanda A. 01/22/1964
856 Adams,Lawrence A. 03/23/1961
1104 Adam,Stavros O. 02/24/1948
1179 Adam,Pam A. 05/16/1941
426 Adams,Brian M. 01/15/1928

18 row(s) affected

And you can also execute this using a dynamic statement:

USER>set result = $system.SQL.Execute("select id,name,dob from sample.SP_Sample_By_Name('Ad') order by dob desc")

USER>write result.%Next()
1
USER>write result.Name
Adams,Elvira X.
Followers:
Following:
Global Masters badges: