I would like to start a discussion regarding Caché Objects and Caché SQL.

It is my understanding that the creators of Caché Objects see Caché SQL as the reporting arm of Objects and as such SQL is essential to Caché Objects.

I once met a Caché Objects programmer who was writing code to $Order through the Globals because that person thought that Caché SQL was too slow and inefficient. I attempted to convince the person otherwise.

So, what say you? Is SQL essential to Caché Objects?


0 29
0 738
· Jun 22, 2017
Hash values of columns

I try to find a function, which generates hash values of columns. In MS SQL Server I can use

select hashbytes('sha2_256', my_column) ...

to create hash values of my_column. Is it possible to use such things in Caché?

Thank you

0 1
0 979

Hi all,

I have a non objectscript application connecting to a cache instance via ODBC and one column is a list of serial objects. The output from the query contains a lot of special characters and I'm hoping there's a better way to get this data back so I won't have to perform extensive parsing on the application side.

I've tried using the $ListToString() function, but that didn't help much, probably because the list contains complex objects rather than primitives.

Any help will be greatly appreciated!

1 4
0 703
· Jun 14, 2017
SQL Query for Range


I have a question related to running an SQL query for range of data.

I am running a query like:

Select A,B from table_name where A>=12345 AND A<=12390

- Where A and B are my two of the properties under the mentioned table/class definition

- A is an integer property

Question: The result of the above query does not return me all the values between the mentioned range of integer though I do have all the values in that range. I see that some of the values are missing in result.

0 9
0 416

In addition to its general security, Caché offers SQL security with a granularity of a single row. This is called row-level security. With row-level security, each row holds a list of authorized viewers, which can be either users or roles. By default access is determined at object modification Some time ago I became interested in determining row-level security at runtime. Here's how to implement it.

4 9
0 671

Whenever I try to run a simple query on a production database table, I get a timeout 504 error. I'm using the SQL interface in Ensemble. One of the queries is a simple SELECT TOP 10. Even SELECT COUNT(*) gives a timeout.

I have tried to run the SQL Runtime Statistics, but this gives me a timeout as well.

Does anyone have an idea where I might look to find what is causing this?

Thanks in advance,

Best regards

Glenn van Bavel,

0 3
0 1,387
· Jun 8, 2017
SQL Connect

How to simply going to get the value of the system language from ^%z? Because I got a problem, U2 context isn't setup while connecting the SQL Connect.

0 4
0 287
· Aug 25, 2016
JSON changes in Caché 2016.2

As Bill has mentioned earlier in his post, we have carefully reviewed the JSON capabilities and made some adjustments to ensure they deliver the best benefit to you. In this post, I am going to describe the modifications in more detail and provide guidance for you to understand the implication for your code base.

11 8
1 3,684
· May 27, 2017
Insert SQL

I am inserting rows in a table. This table is appearing in all namespace as I did global mapping.

So once I run insert command from a method, it insert the rows. When I run the same insert command from other namespace, it replace the existing data in table.

Insert command is same in all namespace but the data I m inserting is different.

0 2
0 447
· May 25, 2017 2m read
The Interns are Coming!

The Data Platforms department here at InterSystems is gearing up for this year's crop of interns, and I for one am very excited to meet them all next week!

We've got folks from top technical colleges with diverse specialties from hard core engineers to pure computer scientists to mathematicians to business professionals. They come from countries around the world like Vietnam, China, and Finland and they all come with impressive backgrounds. We're sure they will do very well this summer.

6 0
0 440

How do I write DDL script for collection properties?

For example I want to create the following class:

Class SQLUser.Person {

Property Name As %String;

Property FavoriteColors As list Of %String;


My DDL script looks like this:

CREATE TABLE Person (Name varchar(50), FavoriteColors ???)
0 4
0 300

Atelier Users:

Has anyone found an Eclipse plug-in that provides the capability to connect to a Caché server and give the user a way to write SQL queries using the tables from that server? I'm picturing something like a "WinSQL"-client built as an Eclipse plugin.

I've found and tried the following, but I couldn't get it to connect to my local Caché instance.


2 4
0 793

Hi All -

I was wondering if the below query could be enhanced to automatically look back 24 hours from the current datetime the query is run. As is now I, of course, have to update the date range in the WHERE clause manually.

The query is just getting all MessageBodyClassNames, counting them and then doing an AVG on TimeCreated and TimeProcessed. Nothing too complex.

SELECT MessageBodyClassName, count(ID) as Count_Of_Messages, avg(datediff(ss, TimeCreated, TimeProcessed)) as avg_processing_time_in_seconds

FROM %PARALLEL Ens.MessageHeader

0 1
0 2,515

Hi All,

I use SQL function JSON_OBJECT to get data as a JSON object.

However, sometimes I get error with function JSON_OBJECT when values contain [, ], { or }.


[SQLCODE: <-400>:<Fatal error occurred>]

Unexpected error occurred in JSON_OBJECT() function execution of <JSON_OBJECT>.%FromJSON().Parsing error

For example,

Query: SELECT JSON_OBJECT('idSQL':id, 'content':content) FROM DocBook.block

0 9
0 1,206

It's almost time to get your customers upgraded to new versions - are you worried about showing off your SQL Performance after upgrades? If you want to upgrade without worrying, then I have just the program for you!!! Check out this video from Global Summit 2016 featuring yours truly explaining how to upgrade a system without worrying about pesky SQL queries showing on your waistline!


1 0
0 282

Hello, we have a few hundreds of triggers to port from Oracle to Cachè for a migration project, and many of them have to change (for example, normalize a value, null it, etc) the value which is being inserted.

The documentation says "You cannot set {fieldname*N} in trigger code." , so we're unlucky.

Is there a good workaround for this ?

SqlComputeOnChange doesn't seem the best way, but I'm not totally sure: for example normalization and validation could have a better place somewhere else than a trigger.

1 2
0 318
· Mar 22, 2017

Hi All, In Cache Table i have stored the data value as horlog format ,by query how to retrive the data when i give the data field as date format.

0 4
0 460

I am trying to return the maximum of the value of 2 fields: LastViewed and LastDownloaded AS a local variable -LastAccessed for each row, using a SQL query . These values are stored as $ H format. Is there an existing SQL command that compares two column values ? I could not find one, so I tried using a $Select statement . I got an error that said A term expected beginning with either of: identifier, constant, aggregate, $$,(,:,+....)

Here is the SQL Query I am trying to run:-

0 5
0 647


Encryption of sensitive data becomes more and more important for applications. For example patient names, SSN, address-data or credit card-numbers etc..

Cache supports different flavors of encryption. Block-level database encryption and data-element encryption. The block-level database encryption protects an entire database. The decryption/encryption is done when a block is written/read to or from the database and has very little impact on the performance.

With data-element encryption only certain data-fields are encrypted. Fields that contain sensitive data like patient data or credit-card numbers. Data-element encryption is also useful if a re-encryption is required periodically. With data-element encryption it is the responsibility of the application to encrypt/decrypt the data.

Both encryption methods leverage the managed key encryption infrastructure of Caché.

The following article describes a sample use-case where data-element encryption is used to encrypt person data.

But what if you have hundreds of thousands of records with an encrypted datafield and you have the need to search that field? Decryption of the field-values prior to the search is not an option. What about indices?

This article describes a possible solution and develops step-by-step a small example how you can use SQL and indices to search encrypted fields.

5 9
1 1,526

I have been following the online Zen Quickstart Tutorial using the lastest release documentation. In addition to playing around with the styling and making a few minor functionality tweaks, I wanted to add an additional column that shows a count of the number of phone numbers for that Contact (as shown in the image below).

0 4
0 398

I have a tablePane ZEN Component and I am trying to get a filter running on the Specimen Id / Lab Number. The SQL is fairly complex with 3 UNION ALL statements joining 4 tables and a couple of lookup tables.

How would I get the filter on SpecId to work for my tablePane? It's not automatically applying the filter for me so I think I need code something.

0 1
0 343

I use the HS_IHE_ATNA_Repository.Aggregation table a lot. Someone just referred me to the HS_IHE_ATNA_Repository.Document table, which has an AggregationId column.

I assume that column references the ID column in the .Aggregation table. If so, does this mean that if the same document was requested 1,000 times that there will be 1,000 entries for it in the .Document table? This seems inefficient to me. Why not have one record in the document table and have a DocumentId column in the Aggregation table?

0 7
0 355