It's not a big difference between Studio and VS Code - ObjectScript here. Studio automatically does the #dim for you when you do a %New() or %OpenId(), and VS Code - ObjectScript doesn't. But #dim is still necessary in both IDEs for referenced or returned objects:

set person = ##class(Simple.Person).%New()  // Studio WILL provide code completion for person, VS Code WON'T

#dim address as Simple.Address  // without #dim, neither Studio nor VS Code will provide code completion for address

set address = person.Address 

#dim rs as %SQL.StatementResult

set rs = statement.%Execute(args)  // you'll get code completion for rs thanks to #dim

Thanks Michael.

You could code it the oop way, and use $sortbegin on all the index globals for your multiple tables, which means you'd have to know or look up what the index global names are (since they're not always ^D and ^I anymore), and test 1000000 inserts (main table and referenced tables) with the index build deferred to the end, and time it to see how long it takes. 

And then code it the sql way, inserting the same data into the multiple tables using %NOINDEX, and calling %BuildIndices() on all the classes at the end, and time it to see how long that takes. The sql way is supposed to be faster...

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).

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.

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!

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.