Besides the functionality and speed that you mentioned already,
there come other factors to my mind. Just a few from the hip:

• Portability: Moving a Caché/IRIS DB from one system to the other is as easy as a file copy. And this goes across most actual operating platforms on local HW or in the cloud, from Win to *X and back
• Flexibility: Splitting or extending your DB if you run out of disk space has no impact on the applications. Vertical or horizontally scaling is a standard feature
• Connectivity: especially with IRIS you have a huge range of adapters and interfaces available that allow access not only by SQL but also by Objects without any need to separate the data accessed from each other.
• Openness: You need a private SQL Function or SQL Procedure because Standards don't fit?   No problem: Just add your self modeled ClassMethod and you have it done for both SQL and Object access.  

As I mentioned already in a different place: The only limit is your own imagination.  

 

There is my related question Multi Language Sort  and the new extension may cover some very simple cases.

@Vitaliy.Serdtsev provided an excellent solution as reply to the problem
including a detailed example of how to cover solve the issues I found
with basic features already available in Caché since almost ever(?).

with error trapping

/// Set value to %session.Data 
ClassMethod setValue(params...) As %Status
{
 try {
   set var=$name(%session.Data)
   for i=1:1:params-1 set var=$name(@var@(params(i)))
   set @var=$g(params(params))
   set status=$$$OK
  }
  catch { set status=$system.Status.Error(710)
  }
  quit status 
 }

it doesn' trap this cases yet

do ..setValue("key1", "key2", , "key4", "val")
do ..setValue("key1", "key2", "", "key4", "val")

did you mean that ?

/// Set value to %session.Data 
ClassMethod setValue(params...) As %Status
{
quit:params<2 0
set var="%session.Data("
for i=1:1:params-1 set var=var_"params("_i_"),"
set $e(var,*)=")"
set @var=params(params)
quit $$$OK
}

/// Set value to %session.Data 
ClassMethod setValue(params...) As %Status
{
 quit:params<2 0
 set var="%session.Data("
 for i=1:1:params-1 set var=var_""""_params(i)_""","
 set $e(var,*)=")"
 set @var=params(params)
 quit $$$OK
}

Incredible!   I really missed something important!
@Dan Pasco    Thanks for the pointer. I never stop learning.

Another unknown feature that isn't mentioned anywhere else.

Ah! csp.pagename.CLS

THX

No legal that I know of

What do you mean by csp.INT  ?? 

StreamPrefetch looks related but it is totally related to the .NET end (googled SQL prefetch in .NET), 
while <INVALID OREF> is clearly an issue @Ensemble end.
I'm not too deep in the external ODBC  world but I assume that collecting the resultset and
transmitting it to .NET  [ %SYS.SQLStreamSRV ] could happen asynchronously in different processes.
So I doubted %Stream.TempCharacter which is bound to a process private Global  vs. a standard Global visible also to other processes. And only the final %Save nails it down.

I wonder if you experience the same problem if you run your query from
terminal prompt: do $system.SQL.Shell()  
This can signal if the ODBC connection plays a role at all.

Jeff,
if this is still happening I suggest contacting WRC. 
They have access to the sources and more diagnostic tools.

%SYS.SQLStreamSRV.obj  is a deployed routine / class so we can't look into its details.
<INVALID OREF> expects a (stream) Object, but it doesn't exist.
So an additional check might be required.  Or you force some content for testing.
 

ClassMethod GetHL7Msg(pId As %String) As %Stream.TmpCharacter [ SqlName = GetMsg, SqlProc ]
{
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" Not Found.")
    Set tMsg = ##class(%Stream.GlobalCharacter).%New()
    Set tSC = tHl7.OutputToLibraryStream(.tMsg)

     ;;  Throw:$$$ISERR(tSC) ..GetErr(-400, "HL7 Message with ID "_pId_" failed.") 
         IF 'tMsg.Size DO tMsg.Write("*** no message for ID:"-pId_" ***")
         SET tSC=tMsg.%Save()

    Do tHl7.%Close()
    Return tMsg
}

So you should have always a Stream Object