go to post Dan Pasco · Dec 6, 2023 Can you please zwrite sqlResult? It should be an instance of %Library.ProcedureContext. It has properties for sqlcode, message, result set sequence, etc. Knowing what is there will be very helpful.
go to post Dan Pasco · Dec 4, 2023 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) AffectedThe 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
go to post Dan Pasco · Nov 20, 2023 We are working on a project to post to the InterSystems Corporation Github. The project will include Java, ObjectScript, and DotNet. Stay tuned!
go to post Dan Pasco · Nov 16, 2023 Along with a co-worker, I worked on this for three days. The good news is that I have something to show. The not-so-good news is that we discovered a bug and a deficiency. The better news is that the next version of JDBC to be released will include these fixes and enhancements. Also good news is that most of the old %Net.Remote.Java.Test class works with minor refactoring. Mo Cheng will be adding information on how to leverage a more backward compatible Java Gateway. The legacy Java Gateway used generated classes and the new Dynamic Gateway ($system.external) does not. I'll leave that description for Mo to provide. For now - pending review - I am ready to share a new class (actually a full project). I am just waiting for a place for this to be posted. In the meantime, I could post code snips here. Let me know.
go to post Dan Pasco · Nov 15, 2023 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
go to post Dan Pasco · Nov 13, 2023 Enrico, For some reason I didn't get an email alert that you had replied. I'm sure it is buried in my "group" mail somehwere - that's my fault and I'll try to get that sorted. %Net.Remote.DotNet.Test - I can't help much with that but I certainly can with %Net.Remote.Java.Test. I'll take a look at that now. $system.external.* is just a helper to get a gateway connection to an external language server such as the Java Server. I refer to the "gateway" as the connection. Maybe that doesn't work for every one. Sorry :( Once you have a gateway connection you can interact with the external Server using methods supported by that connection. Some of those methods are still unknown to me so I'll have to do some research. Stay tuned! Dan
go to post Dan Pasco · Nov 6, 2023 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
go to post Dan Pasco · Oct 24, 2023 @RobertCemper - our paths have crossed a time or two. I hope they will again.
go to post Dan Pasco · Oct 23, 2023 Hi, Using the new(ish) External Java Server and Gateway connection, this worked for me. I copied your class, refactored it for the Apache PDFBox version 3.0.0 and then called it from the command line. I did receive some warnings that have nothing to do with PDFBox, they are related to the larger project that I placed this code into. The call worked just fine. I don't know exactly when the new external language server/gateway support was added but it is documented in 2021.1: https://docs.intersystems.com/iris20211/csp/docbook/DocBook.UI.Page.cls?KEY=BEXTSERV_intro. I'm pasting the IRIS Session commands I used: USER>set java = $system.external.getJavaGateway() USER>do java.addToPath("<path to my project/target/intersystems-demo-1.0-SNAPSHOT.jar") USER>set text = java.invoke("demo.intersystems.utils.PdfToText","getText","/home/danp/Downloads/DM32.2-2017-00157-ambiguity-in-JSON-array-constructor.pdf") USER>zw text text="ISO/IEC JTC1/SC32 WG3:CMH-023"_$c(10)_"ANSI INCITS DM32.2-2017-00157"_$c(10)_"- 1 of 7 -"_$c(10)_"Title: Ambiguity in <JSON array constructor>"_$c(10)_"Author: <<snipped>> I even have a nice way to return the text as an instance of %Stream.Object if that is interesting to you. HTH, Dan
go to post Dan Pasco · Sep 13, 2023 I don't recommend using the "embedded extent" model. It might be possible to achieve this model but it isn't simple to do and it does produce some odd behavior. I could, reluctantly, describe how this is done. It may not apply to this case anyway. The default serialization of a LIST collection is as an embedded list and we do not support - currently - a serialized list as a child table. This is an example of a MANY to MANY relationship. IRIS does not support this relationship type. Rather, we recommend using the Associative Entity model where there is a third class/table that maintains the relationship. The structure of the AE class/table is quite simple, consisting of two properties/columns, one referencing of the related classes and the other referencing the other related classes. If restructuring the user class is not possible then the %OnDelete recommendation is good. Perhaps it would be better to use a FOREACH=ROW/OBJECT, ONAFTER=DELETE trigger to iterate over the collection and delete each referenced item. Triggers have the added advantage of working with both Object and SQL filing operations. Keep in mind that collections use OID format when crafting the delete.
go to post Dan Pasco · Sep 13, 2023 Interesting question. There is no good option to manipulate the ObjectRegistry but you do have options to access stored data. My recommended option you've already taken off the table. The FOREACH=ROW/OBJECT trigger is by far the best and easiest solution if you require access to the version of an object that is currently stored. There are several reasons why this is the best option, perhaps the most important being that ROW/OBJECT triggers are consistently applied between Objects and SQL. Of course, the same restrictions exist for triggers that exist for %OnBeforeSave - we don't recommend modifying objects/rows. Another option is to use <property>GetStored. This may not work for every situation but it does allow code to retrieve the value of a property directly from storage. I believe this is restricted to what we call "default storage". That isn't an entirely true statement but it is for common storage types (SQL Mapped storage being the other common type). USER>zw ^demo.person.1(1) ^demo.person.1(1)=$lb("Doe, John","123 Main Street","Cambridge","MA","02142","[{""type"":""mobile"",""country_code"":""1"",""number"":""999-999-9999""},{""type"":""work"",""country_code"":""1"",""number"":""888-888-8888""}]") USER>set person = ##class(demo.intersystems.Person).%OpenId(1) USER>write person.name Doe, John USER>set person.name = "Richard, Maurice" USER>write person.nameGetStored(person.%Id()) Doe, John USER>write person.name Richard, Maurice Of course, you can always use SQL to retrieve stored values. For the intrepid explorer, there is another option. This code isn't supported as the %Load* api's aren't supposed to be public. I'm sure most people have examined generated code and have discovered these methods so I'm divulging any big secrets here. First, the results and then the code. USER>set person = ##class(demo.intersystems.Person).%OpenId(1) USER>set person.name = "Enono, William" USER>set sperson = ##class(demo.intersystems.Person).GetStored(person.%Id()) USER>write sperson.name Leavitt, Timothy USER>write person.name Enono, William Fair warning - this code isn't supported, it is considered to be user-implemented code. I also didn't rigorously test this code. I added this class method to my demo.intersystems.Person class: ClassMethod GetStored(id As %Integer) As demo.intersystems.Person { try { set obj = ..%New() set cur = obj.%Concurrency set obj.%Concurrency = 0 $$$THROWONERROR(sc,obj.%LoadInit(,,1)) $$$THROWONERROR(sc, obj.%LoadData(id)) set obj.%Concurrency = cur do obj.%SetModified(0) } catch e { set obj = $$$NULLOREF } return obj } The object referenced by the oref returned by this code is not assigned an id. If you were to save this object it would create a new stored object and it would be assigned its own ID. Exercise caution.
go to post Dan Pasco · Aug 28, 2023 $$$QN is a macro that we use in code generators to delimit identifiers. It returns either the parameter value unaltered or the quoted parameter. For example, if you have a property named select then it has to be delimited for use in SQL since it is a reserved word. $$$QN("case") returns "case".
go to post Dan Pasco · Aug 26, 2023 Property methods, or any member methods in general, originate from three places: the property's memberType class, the declared type class (sometimes call the "datatype" class) and the containing class definition. The memberType is simply the type of class member - property, index, query, method, parameter - all are types of members that can be defined in a class. What isn't well known is that there is a memberType class for several memberTypes. (in the past, this was referred to as the property class) We'll focus on the property memberType here but keep in mind that the concepts extend to indexes, queries, triggers, etc. There are two categories of property methods. Methods inherited from the datatype class collectively provide the datatype class runtime for the datatype generated specifically for a property. These methods are classmethods but they behave as if they are operating on an instance of the datatype class. That instance is passed as an argument when invoking the method or returned by the method. We think of these methods as running in a provided context. The second category of property methods is those inherited from the memberType classes. Yes, I wrote classes - plural. That is because in addition to the memberType classes that are automatically inherited by each property, the class definition can specify additional classes as the value of the PropertyClass class keyword. To specify multiple classes simply enclose the list of classes in parentheses. Class User.Special Extends %RegisteredObject [ PropertyClass = User.MyProperty ] { Property MyProperty As %String; } In this example, each property defined in User.Special will inherit methods implemented by User.MyProperty. Before we look at User.MyProperty we need to understand another class keyword - NoContext. There is also a NoContext method keyword. When used as a class keyword it applies to all methods that do not also specify NoContext. The documentation for NoContext describes the code generation implications of using NoContext but does not explain what it means. Perhaps that is because the keyword name implies something other than its compile-time behavior. NoContext simply means that the method has access to the current instance. In other words, the member method does not provide its own context. What member methods do provide their own context? Datatype methods. Why do we care about context? Simply put, datatype methods should not have the ability to mutate the object since they are intended to implement only the datatype behavior and can access only the provided value. Class User.MyProperty [ NoContext ] { Method IsSpecial() As %Boolean [ CodeMode = generator ] { set ivar = "i%"_$$$QN(%property) $$$GENERATE(" return $select(+"_ivar_"#2>0:1,1:0)") } } This is a simple little example of a property method generator. When the Special class is compiled it will as an IsSpecial property method to each property defined by the class. MyPropertyIsSpecial() methodimpl { return $select(+i%MyProperty#2>0:1,1:0) } And when you instantiate Special you can invoke this method. USER>set special = ##class(Special).%New() USER>set special.MyProperty = "200" USER>write special.MyPropertyIsSpecial() 0 USER>set special.MyProperty = 201 USER>write special.MyPropertyIsSpecial() 1
go to post Dan Pasco · Jul 12, 2023 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
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);
