This is not documented because we don't want new development using this kind of storage (which we call "SQL Strorage").  We'd much prefer you to use the standard storage strategy that you get for free when creating classes.   If you look at Brendan's 5th (of 3) articles, you'll see a link with the text "Get the Examples here" where you can download some examples (if that works, the link will be here as well). 

Again, if you can avoid doing this, I recommend using standard storage.

If you run into any problems or if you have any questions, I strongly suggest you contact the WRC.  

How does it connect - via ODBC/JDBC or is there another connection mechanism?

Using the macros is a bad idea.  The changes to the JSON, especially in this case, are simple.   Any source control system worth its salt is going to be able to make nearly all of the changes for you automatically.  As far as I know the only change that can't be caught by find-and-replace is the one requiring COS expressions embedded within JSON to have parentheses.  In this case, the new syntax is supported in 2016.1 JSON, so you wouldn't even need to change that.  Because of the ease of change, the fact that the change only needs to happen once, and the code obfuscation that occurs when adding  macros, I do not think using them is a good idea for anyone planning to use Caché's native JSON implementation.


Edit: It occurs to me that the macros would be useful if you have to switch back and forth between 2016.1 and later versions over and over again, but to do the conversion once is too simple to bother with macros.

Are you using a trigger generator or writing your own code?  Can you send the whole trigger definition for review?

There is no such command.  I would recommend moving the index global as well.  However, if this is not an option then you probably want to do something like:

USER>s sql="select Name from %Dictionary.CompiledClass where system=0 and NOT(Name %Startswith '%') AND SUPER [ 'Persistent'"     

USER>s rs=##class(%SQL.Statement).%ExecDirect(,sql)                                                                               

USER>while rs.%Next() { s sc= $classmethod(rs.%GetData(1),"%BuildIndices") w "Built "_rs.%GetData(1)_" with return code = "_sc,! }

Obviously this is kind of hacked together and you'll want to clean it up, but the outline is there.  I had to make some assumptions like you don't have any other classes in your application that have 'Persistent' in them.  If you do then you'll want (Super [ '%Library.Persistent' OR Super [ '%Persistent').  Since classes are case sensitive this should be fine. 

Triggers are fired after data normalization and validation.  Do you think you can/should use your own datatypes, overriding the Normalize or LogicalToDisplay/DisplayToLogical functions.  If that doesn't make sense you could have a field calculated off of the stored data?  There are lots of options here, the best one is going to depend strongly on the goals you have for the project.

These answers are all sufficient.  However, perhaps a more natural solution is to make the field of type %Date.  Then you can use the SelectMode to determine how the date is represented in a query.  For instance, look at the DOB field in Sample.Person (in the SAMPLES) namespace.  The storage looks like this:

^Sample.PersonD(1)=$lb(","Waterman,Brenda Z.","437-96-2023",36088,...)

Here we see the ID (1) the Name ("Waterman,Brenda Z.") the SSN (437-96-2023), and the DOB (36088).  If you want to see the data you can do the following:



Name                                     DOB

Waterman,Brenda Z.      36088


If we wanted to see that in a readable format, we can change the selectmode and get:


Name                                       DOB

Waterman,Brenda Z.   1939-10-22


Moreover, you can use this in the WHERE clause very naturally:


SELECT Name,DOB FROM Sample.Person WHERE DOB='1939-10-22'

Name                                           DOB

Waterman,Brenda Z.     1939-10-22

Underman,Jane V.          1939-10-22

Bush,Susan E.                   1939-10-22


The way to change the selectmode is going to depend on how you are running your query.  All queries that come through xDBC are in ODBC mode, so that's easy.  If you are running a dynamic SQL statement you can do:

s stmt.%SelectMode=1  

Possible values are:

  • 0 for LOGICAL mode.
  • 1 for ODBC mode.
  • 2 for DISPLAY mode.

If you are using embedded SQL, you can use the precompiler directive #SQLCompile select=<value> as outlined here:

Eduard is right, you should use GREATEST in this case.  CASE statements are kind of sloppy, and while they are fine to use, I like to avoid them in order to keep the query as readable as possible.

More to your other points, you can find a list of valid SQL functions here:

Note that all $-functions are not SQL Standard and will only work with Caché (most likely), so if you are interested in database agnosticism then try to use one of the more standard functions.  Good luck! 



DELETE FROM Sample.Person where (ID # 2) = 0   --Deletes all even numbered rows (so you know I'm not cheating and using ID numbers)

select * from (select id,name,ssn from sample.person) where %vid between 11 and 20 

%VID is a special variable in Cache SQL (VIEW ID, I think?) which allows you limit your rows without too much trouble.  And alternative is the following:

select TOP 10 ID,Name,SSN from Sample.Person where ID NOT IN (SELECT TOP 10 ID FROM Sample.Person)

The first TOP gives you your page size, and the subquery's TOP gives you where to start (in this case, we start at 11, because we're ignoring the first 10).  You can dynamically put in the sizes for TOP.  

SIMPLEST?  %String, definitely.  You can even use the ['' syntax to search if you have to.  Using List of %String is a messy way to do anything in the relational world.  

If you do need to index them later, I'm pretty sure you can use iFind to do this without too much trouble (although you would need a new license, potentially).