go to post Dan Pasco · Jul 27, 2022 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.
go to post Dan Pasco · Jul 6, 2022 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.
go to post Dan Pasco · Jun 1, 2022 %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.
go to post Dan Pasco · Mar 24, 2022 This project will be available as intersystems-utils, version 4.0.0. It is in the pipeline now.
go to post Dan Pasco · Mar 24, 2022 There is a project that will be available soon (don't ask me to define "soon") that will allow the Java programmer to load and compile sources from the local system into an IRIS Server. The IRIS Server does not need to be running on the same system where the files are located. This example is slightly old as the current implementation of load() returns a list of items loaded and compile() returns a list of items compiled. There are load implementations that accept directories, individual files, any Java streamable, and also JAR files. SourceLoader sourceLoader = new SourceLoader(connection); Path path = Paths.get("path/to/samples/cls/Sample"); sourceLoader.load(path, null); sourceLoader.compile(true);
go to post Dan Pasco · Feb 22, 2022 When not using EXTENTSET mapping, all indexes are stored by default in a single global. To keep the index structures separate the first subscript of the index global, by default, is the index name. This creates two conditions that impact performance negatively. Firstly, the index name subscript creates a longer reference for each index key. That leaves less room for index key subscripts and longer references consume resources. Secondly, the size of the index global is increased and the number of index key values per block is reduced. Fewer key values per block means more blocks read during query execution and a less efficient global cache. The index name subscript is essentially "noise". Using EXTENTSET mapping removes the need for an index identifying subscript (the global is the index).
go to post Dan Pasco · Feb 22, 2022 First of all, the global names are not random but are based on a hash algorithm to reduce the length of the global name and to reduce the probability of global name collisions. This is the default global name assignment when USEEXTENTSET is true. The benefits of using EXTENTSET mapping are many but primarily, the size of indexes is reduced substantially making index filing is faster. Queries using indexes are also likely faster with USEEXTENTSET mapping. With EXTENTSET, the storage default global is used as a base value for the set of globals used by the extent. Each index, including the master map/master data index (MDI) - also known as the "DATALOCATION", is the base value (the EXTENTLOCATION) plus ".n" where "n" is a number computed when mapping globals to indexes. The master map/MDI always is ".1". In the original post, the DEFAULTGLOBAL setting overrides the hash computation of the EXTENTLOCATION. Compare the index reference for a simple name index on Sample.Person between USEEXTENTSET = 1 and USEEXTENTSET = 0. ^Sample.PersonI("PersonNameIndex","DOE, JOHN Q", 100) vs ^Sample.Person.2("DOE, JOHN Q", 100). The developer has to choose whether to use conveniently named globals or better performance. Note that not all class definitions/tables will benefit from USEEXTENTSET mapping. But many will.
go to post Dan Pasco · Jan 12, 2022 Yes, of course "inverse" - sorry. Persistent vs RegisteredObject - not a problem but you are calling a simple class method so we don't need any super class. I used this implementation for the IRIS Class: Class Utils.CSW1JavaFunctions { ClassMethod IrisReturn(user = "user", pass = "pass") As %Stream.GlobalBinary { try { set cswStream=##class(%Stream.GlobalBinary).%New() set cswReturn = {"user":(user), "pass":(pass) } do cswReturn.%ToJSON(cswStream) return cswStream } catch exc { write !,"Caught Exception on server: ", exc.AsSQLMessage() } } } And this is a crude hack at the Java code - the anonymous InputStream class could use more work but it does run for this simple example. I'll leave the rest of the InputStream coding to you. package utils; import com.fasterxml.jackson.databind.JsonNode; import com.fasterxml.jackson.databind.ObjectMapper; import com.intersystems.jdbc.*; import java.io.*; import java.sql.SQLException; public class Reader { public static final String CACHE_CLASS_NAME = "Utils.CSW1JavaFunctions"; public IRISConnection connection; public IRIS iris; public Reader(IRISConnection connection) throws SQLException { this.connection = connection; this.iris = IRIS.createIRIS(connection); } public static void main(String[] args) throws SQLException { IRISDataSource dataSource = new IRISDataSource(); dataSource.setServerName("localhost"); dataSource.setPortNumber(51776); dataSource.setDatabaseName("USER"); dataSource.setUser("_SYSTEM"); dataSource.setPassword("SYS"); IRISConnection connection = (IRISConnection) dataSource.getConnection(); Reader reader = new Reader(connection); try { JsonNode jsonNode = reader.execute("IrisReturn", "java", "jpass"); System.out.println(jsonNode.toString()); } catch (Exception exc) { exc.printStackTrace(); } } public JsonNode execute(String method, Object... args) throws Exception { ObjectMapper mapper = new ObjectMapper(); JsonNode jsonNode = null; try { IRISObject data = (IRISObject) iris.classMethodObject(CACHE_CLASS_NAME, method, args[0], args[1]); InputStream is = new InputStream() { byte[] buffer; int pos = 0; int len = -1; @Override public int read() throws IOException { if (pos >= len) { getBuffer(); } if (len == -1) { return -1; } return buffer[pos++]; } void getBuffer() { pos = 0; IRISReference readLen = new IRISReference(3200); String string = (String) data.invoke("Read", readLen); if (readLen.getLong() == -1) { buffer = null; len = -1; } else { buffer = string.getBytes(); len = buffer.length; } } }; jsonNode = (JsonNode) mapper.readTree(is); return jsonNode; } catch (Exception ex) { ex.printStackTrace(); } return null; } } Running this produces this output: /usr/lib/jvm/adoptopenjdk-11-hotspot-amd64/bin/java -javaagent:/home/...{"user":"java","pass":"jpass"} Process finished with exit code 0