New post
AllAccepted answers
Joel Solon · Apr 28, 2021

I haven’t used any of the Gateways before, nor have I played with any of the External Language Servers. It looks like the new skill demoed here is that an IRIS object (persons in this example) is able to hold a reference to a Java class outside of IRIS, and you can call methods from that Java class as if they are methods inside IRIS, including making use of Java libraries (AsciiTable in this example). So Java System.out redirects to the IRIS Terminal window? If so, that all seems amazing! Do you have some examples you can share about how we expect developers to use this?

Joel Solon · Apr 8, 2021

For those of you that like GUI tools in an IDE, I just installed the Microsoft Docker extension for VS Code. It shows my Containers and Images just like Docker Desktop Dashboard does, and lets me start/stop/launch CLI/inspect etc.

But the real reason I installed it is because there's also a Registries section with a Connect Registry icon (looks like a plug).

Using that, I chose "Generic Docker Registry" (other choices: Azure, Docker Hub, GitLab). I supplied the registry URL. It prompted me for my username and password (Docker login token as described above) and I can now browse the registry. Beautiful!

Joel Solon · Apr 5, 2021

zen(id) is shorthand for zenPage.getComponentById(id) which as @Vitaliy.Serdtsev said gives you access to the Zen component identified by id. document.getElementById(id) gives you access to the element identified by id in HTML/JavaScript document object model. Since a Zen component could comprise several HTML elements, it's usually better to use zen(id).

Joel Solon · Mar 2, 2021

Point 5-5: Relationship on the object side is bi-directional. Class A has a reference to class B, and class B has a collection of references to class A. On the SQL side, table A has a reference to table B, along with automatic referential integrity.

Point 16: Important: for a class to be persistent, %Persistent must be the first class. So the example should be: Class Person extends (%Persistent, Animal)

Joel Solon · Mar 6, 2020

To answer the original question: What is the difference?

There are 3 ways to use SQL in Caché/IRIS:

Dynamic SQL: The main use case for this approach is that you, the developer, don't know the complete SQL statement at design/compile time. Instead, inside your Method, you build the complete SQL statement at run time (adding columns to the SELECT, conditions to the WHERE clause, whatever you need), prepare the finished SQL, and execute it. This approach uses %SQL.Statement and %SQL.StatementResult. As you loop through the result set, to access the data in each column, you can use rs.columnname or rs.%Get("columnname"). But neither one will work if you don't actually know the names of all the columns, which can obviously happen when the SQL is dynamic. So the only other approach is to use rs.%GetData(columnnumber).

The other two alternatives are for the case where you, the developer, know the complete SQL statement as design/compile time. Note that you can use Dynamic SQL for this case also.

Embedded SQL: In this approach, you embed the complete SELECT statement inside your Method using &sql(). This is similar to embedding SQL in procedural languages provided by other vendors, such as PL/SQL (Oracle) and T-SQL (MS).

Class queries: In this approach, you create a Query in your class that contains the complete SELECT statement, and you use that query in a Method. For this, it's recommended to use the %PrepareClassQuery() method of %SQL.Statement.

Note: the original implementation of Class queries and Dynamic SQL also used %Library.ResultSet.

Joel Solon · Mar 3, 2020

I did a little bit more research.

  • Maybe %STARTSWITH 'abc' was at one time faster than the equivalent predicate LIKE 'abc%'.
  • The quote comes from the FOR SOME %ELEMENT predicate documentation. This predicate can be used with Collections and an old feature called Free Text Search. The quote was actually only meant to apply to the Free Text Search usage.
  • I've tested %STARTSWITH 'abc' and LIKE 'abc%' today using FOR SOME %ELEMENT with Collections and Free Text Search. The code is identical.

Conclusion: the quote will be removed from the documentation since it's no longer true.

Thanks, @Vitaliy.Serdtsev, for making me realize that I should have been testing with placeholders rather than fixed values to the right of %STARTSWITH or LIKE. I was testing with Embedded SQL; with fixed values, my earlier statements are true. But if the query itself uses placeholders (? or host variables), or the WHERE clause is parameterized automatically (thanks, @Eduard Lebedyuk, for mentioning that) then the generated code differs, and LIKE sometimes does do an extra (slightly slower) comparison, because at runtime, LIKE could get a simple pattern ("abc%") or a complex one ("a_b%g_i") and the code has to cope with those possibilities.

New conclusion: the quote will be clarified so that it mentions placeholders/paramaterization and moved to the %STARTSWITH and LIKE documentation, instead of being buried in FOR SOME %ELEMENT.

And thanks to @Hao Ma for bringing this up!

Joel Solon · Mar 3, 2020

%STARTSWITH is not faster or slower when comparing apples to apples.

LIKE can find a substring wherever it occurs, and has multi-character and single-character wildcards. %STARTSWITH is looking only at the beginning of the string, so it's equivalent to LIKE 'ABC%'.

Updating to match another updated post lower on this page. If the comparison string is parameterized, LIKE sometimes does an extra check, so %STARTSWITH will be slightly faster.

When the comparison string ('ABC%' and 'ABC') is fixed. The code that checks LIKE 'ABC%' is exactly the same as the code that checks %STARTSWITH 'ABC'

Joel Solon · Feb 27, 2020

Then besides this, in the documentation for %STARTSWITH need to add the note DEPRECATED and the recommendation "use LIKE 'XXX%'"

select count(*) from del.t where p like 'test7%'
Row count: 1 Performance: 0.291 seconds 333340 global references 2000537 lines executed

select count(*) from del.t where p %startswith 'test7'
Row count: 1 Performance: 0.215 seconds 333340 global references 1889349 lines executed

I'm not sure what you mean here. %STARTSWITH executed fewer lines so why would we recommend LIKE instead?

Joel Solon · Feb 26, 2020

I have never heard of anyone issuing the blanket statement that InterSystems predicates are faster or slower than the ANSI standard ones. I don't think there are that many predicates that have similar functionality. As I said in a different comment, %STARTWITH 'abc' is 100% equivalent to LIKE 'abc%'. InterSystems also provides %MATCHES and %PATTERN, but they are different.

Joel Solon · Feb 24, 2020

We may need more clarity in order to answer this question. This is what I think you mean. You want to take an ObjectScript array of arbitrary structure, like this:

a(1)="a"
a(3,4)="b"
a(3,6)="c"
a(5,6,7)="d"

...and turn it into JSON. But what should the target JSON for this example look like? Something like this?

{"1":"a", "3":{"4":"b","6":"c"}, "5":{"6":{"7":"d"}}}

...or something different?

In any case, to loop through an ObjectScript array of arbitrary structure, you need to use $Query.

Joel Solon · Feb 24, 2020

Even though Tim's article talks about this, I'll mention it briefly here. Since ObjectScript Try/Catch construct doesn't have a "Finally" block like some other languages, the code following the Try/Catch is often used for "Finally" code. Since Return inside Try/Catch exits the Try or Catch and terminates the method, this would bypass any "Finally" code at the end. Therefore, I'd recommend avoiding using Return inside Try/Catch.

So if $$$ISERR(tSc), convert tSc into an exception and Throw it, and then handle the error in the Catch. After that, any "Finally" code will run.

Joel Solon · Jan 30, 2020

I've changed my recommendation on this a little. Return the result of %ValidateObject() as the final argument, but don't return that status as the return value of the method. That way, if there are any required properties, and the call to %New() doesn't supply them, %OnNew() still works. Here's the updated example:

/// constructor
Method %OnNew(name As %String = "", phone As %String = "", dob as %Date, Output valid As %Status) As %Status [Private]
{
    set valid = $$$OK
    set ..Name = name
    set ..Phone = phone
    set ..DOB = dob
    set valid = ..%ValidateObject() // validate the new object
    return $$$OK
}