Find

Article
· Apr 23 3m read
Open Exchange

Testing Metadata Inconsistencies in InterSystems IRIS Using the DATATYPE_SAMPLE Database (Part II) - Silent Corruption

#InterSystems IRIS

The first part of this article provides all the background information. It also includes links to the DATATYPE_SAMPLE database, which you can use to follow along with the examples.

In that section, we explored an error type ("Access Failure") that is easy to detect, as it immediately triggers a clear error message when attempting to read the data via the database driver.

The errors discussed in this section are more subtle and harder to detect. I’ve referred to them as “Silent Corruption” and “Undetected Mutation”

Let’s start with “Silent Corruption”:
In the Employee table of the DATATYPE_SAMPLE database, there is a deliberately manipulated record that demonstrates this behavior – it’s the row with ID = 110. At first glance – and even at second glance – no issues are apparent. Neither the database driver nor the query tool indicates a problem if read this row.

Only upon closer inspection does it become clear that the value in the red-marked cell does not match the transmitted (and defined) metadata.

The column "Name" is defined as VARCHAR(50), but the actual value is 60 characters long!

 

There are scenarios where this behavior doesn’t cause any issues — for example, when the driver handles such inconsistencies leniently.
However, problems can arise when downstream systems rely on the provided metadata. If further processing is based on these metadata definitions, errors may occur when the actual content doesn’t conform to the agreed interface.

A typical example involves ETL tools, which often generate target tables or define transformations based on metadata.

The following SQL query can be used to identify records where the content deviates from the defined metadata:

SELECT
 Name,
 CASE WHEN LENGTH(Name) > 50 THEN 1 ELSE 0 END AS Name_LENGTH_CHECK
,SSN,
 CASE WHEN LENGTH(SSN) > 50 THEN 1 ELSE 0 END AS SSN_LENGTH_CHECK
FROM SQLUser.Employee
WHERE 
      LENGTH(Name) > 50
OR    LENGTH(SSN) > 50

If you execute this query, it will return only the rows that contain errors. In each row, the faulty cell will be marked with a 1 if the value exceeds the length defined by the metadata.
 

Now let’s take a look at the next error type: “Undetected Mutation.”
To demonstrate this issue, the DATATYPE_SAMPLE database includes a faulty record specifically changed to illustrate this behavior.

The record in question is the row with ID = 120

Again, neither the database driver nor the query tool will indicate a problem when reading this row.

 

In this case, the value even appears to match the metadata! The column is defined as INTEGER, and the row returns an integer value (in this example: 0) in that cell. 

However, this value is not actually stored in the database! A direct look at the underlying Global reveals the true content. Through manipulation, a string value was injected into this field.

 

SELECT
 CAST(Age AS VARCHAR(255)) AS Age,
 ISNUMERIC(CAST(Age AS VARCHAR(255))) AS Age_ISNUMERIC
FROM SQLUser.Employee
WHERE
    ISNUMERIC(CAST(Age AS VARCHAR(255))) = 0


If you execute this query, it will return only those rows that contain metadata inconsistencies. In each result row, the problematic cell is flagged with a 0 if the value cannot be interpreted as numeric by the driver

 

Final Thoughts

These scenarios highlight how seemingly well-formed data can conceal subtle inconsistencies—especially in legacy systems that bypass standard safeguards. While "Access Failures" are easy to spot, issues like "Silent Corruption" and "Undetected Mutation" often go unnoticed but can cause serious problems downstream, particularly in systems that rely on strict metadata compliance.

The DATATYPE_SAMPLE database and the diagnostic queries shared here provide a foundation for identifying such issues manually. But let’s face it—writing these checks by hand is tedious and error-prone.

Fortunately, SQL DATA LENS (min. version 3.22) makes this process much easier. 😉With just a click, it can generate comprehensive integrity checks for Tables, Views, and Stored Procedures—saving time and helping you stay ahead of hidden data quality issues.

1 Comment
Discussion (1)1
Log in or sign up to continue
Question
· Apr 23

Using CCR with Angular Applications

#Angular #Angular2 #CCR #InterSystems IRIS

Hi,

What is the best way to develop Angular applications while working with CCR?

CCR peer reviewers require submitting Angular source files, so that a peer reviewer has something readable to review. However, CCR environments require that you deploy compiled Angular applications to them.

 

I'm thinking one approach is to compile locally and manually include both the source files and compiled code in an itemset, but that introduces potential errors where the CCR user forgets to include the correct compiled files with the source code (or vice versa). Another approach is to set up something elaborate using CCR event handlers to compile files as they pass peer review, but setting this up is more challenging and error prone.

2 Comments
Discussion (2)3
Log in or sign up to continue
Discussion (0)1
Log in or sign up to continue
Question
· Apr 23

Debugging %Net.HttpRequest

#ObjectScript #InterSystems IRIS

I think I found my solution but I'm trying to understand better why it works. Forgive me as my descriptions here may be scattered but I'm trying to piece the puzzle together.

Scenario: I've found there are times when I make a request using a %Net.HttpRequest object and as I'm debugging and stepping through via the debugger in VSCode, if I try to access an object in the 'variables' menu, sometimes when I click that little expand button it will just spin and never load what's in the object to the viewer. This recently manifested itself in a process where we are calling a bunch of APIs and it got stuck.  In the CLI if we run it and see it's stuck we just CTRL+C or X and it DOESN'T break the process rather it's almost like it was a GO command on a break point and the process continues.

Now that I think of it, this behavior sometimes shows up when I'm trying to access a dynamic object.  E.g. let's say I'm expected to set personId=dynArray.%Get(0).personId, but that there's no object with a personId at index 0, I will get this kind of error. I find often the safter thing to do is if $ISOBJECT(dynArray.%Get(0),dnyArray%Get(0).%Exists("personId"), then set personId= . . . 

I THINK the solution in my %Net.HttpRequest is to check the status before trying to process the response. E.g. s status=httpReqObj.Get() if $$$ISERR(status) throw . . . Before I was simply doing a d httpReqObj.Get() and then trying to process the response in the object without fist checking if it was an error. 

This is ALL in a try/catch block, so what is happening that trying to access these object and properties that may be errors or bad data that causes the process to spin rather than throw the exception to the catch block?

Please, throw some questions at me if you need more info . . . I know I haven't provided the clearest steps to reproduce and my hope is that people have come across this kind of behavior.  

To summarize though, it seems I can avoid by checking the status after calling the HTTP request method when dealing with %Net.HttpRequest objects. When it come to dynamic objects and accessing properties there, it seems tedious to call $ISOBJECT and %Exists() on every object and property when trying to access objects but it does seem to save me lots of failed error catches.

What are your thoughts?

Discussion (0)1
Log in or sign up to continue
Article
· Apr 23 7m read

Generating an ObjectScript Class from JSON Response Using ChatGPT

#ChatGPT #ObjectScript #InterSystems IRIS

I have a new project to store information from REST responses into an IRIS database. I’ll need to sync information from at least two dozen separate REST endpoints, which means creating nearly that many ObjectScript classes to store the results from those endpoints.

Could I use ChatGPT to get a headstart on creating these classes? The answer is “Yes”, which is great since this is my first attempt at using generative AI for something useful. Generating pictures of giraffes eating soup was getting kind of old….

Here’s what I did:

  • Get some sample JSON REST call output using “curl” to query the endpoint.
  • Visit ChatGPT (InterSystems provides a company-wide license for employees)
  • Have the following dialog with ChatGPT.

I put the following request in the “Message ChatGPT” prompt:

Me: Generate an ObjectScript class that will hold data from the following JSON:

I hit “Return” before entering the JSON so ChatGPT directed me as follows:

ChatGPT: Please provide the JSON structure you'd like me to generate the ObjectScript class for. If you have an example JSON object, paste it here, and I'll create the corresponding InterSystems ObjectScript class for you.

I entered the following in the prompt:

{
  "id": 335818632,
  "appId": 113,
  "appName": "Batch",
  "contentId": 182455380233,
  "subject": "Test Subject",
  "name": "Test email campaign name",
  "counters": {
    "processed": 3910,
    "deferred": 86,
    "unsubscribed": 17,
    "statuschange": 17,
    "bounce": 153,
    "mta_dropped": 8,
    "dropped": 299,
    "delivered": 3749,
    "sent": 3903,
    "click": 16,
    "open": 323
  },
  "lastProcessingFinishedAt": 1733232499000,
  "lastProcessingStartedAt": 1733232303000,
  "lastProcessingStateChangeAt": 1733232499000,
  "numIncluded": 4201,
  "processingState": "DONE",
  "scheduledAt": 1733232300000,
  "type": "BATCH_EMAIL"
}

ChatGPT’s response:

Here is the InterSystems ObjectScript class that maps to the provided JSON structure:

Class MyNamespace.MyJSONData Extends (%Persistent, %JSON.Adaptor)
{
Property id As %Integer;
Property appId As %Integer;
Property appName As %String;
Property contentId As %Integer;
Property subject As %String;
Property name As %String;
Property counters As MyNamespace.Counters;
Property lastProcessingFinishedAt As %BigInt;
Property lastProcessingStartedAt As %BigInt;
Property lastProcessingStateChangeAt As %BigInt;
Property numIncluded As %Integer;
Property processingState As %String;
Property scheduledAt As %BigInt;
Property type As %String;
ClassMethod %OnJSONImport(jsonObject As %Object, instance As MyNamespace.MyJSONData) As %Status
{
    Quit ..%JSONImport(jsonObject, .instance)
}
}

Class MyNamespace.Counters Extends (%SerialObject, %JSON.Adaptor)
{
Property processed As %Integer;
Property deferred As %Integer;
Property unsubscribed As %Integer;
Property statuschange As %Integer;
Property bounce As %Integer;
Property mta_dropped As %Integer;
Property dropped As %Integer;
Property delivered As %Integer;
Property sent As %Integer;
Property click As %Integer;
Property open As %Integer;
}

Along with an Explanation:

This code is a great start, but I would like it to fit into my package named “MyPkg” and give the name “EmaiCampaign” to the class. Also, using a bit of NI, I know the values which generated the “%BigInt” properties are actually Linux times and the properties should be %Library.PosixTime”.

I make the following request (not sure why I'm saying “please” to a machine; I guess my parents did a good job):

Me: Can we recreate the class again please, renaming the package " MyPkg" and the Class as "EmailCampaign"? Also, please change types of Properties from %BigInt to %Library.PosixTime.

ChatGPT’s response:

Here's the updated ObjectScript class with the specified package name and class name, and the timestamp properties updated to use %Library.PosixTime:

Class MyPkg.EmailCampaign Extends (%Persistent, %JSON.Adaptor)
{
Property id As %Integer;
Property appId As %Integer;
Property appName As %String;
Property contentId As %Integer;
Property subject As %String;
Property name As %String;
Property counters As MyPkg.Counters;
Property lastProcessingFinishedAt As %Library.PosixTime;
Property lastProcessingStartedAt As %Library.PosixTime;
Property lastProcessingStateChangeAt As %Library.PosixTime;
Property numIncluded As %Integer;
Property processingState As %String;
Property scheduledAt As %Library.PosixTime;
Property type As %String;
ClassMethod %OnJSONImport(jsonObject As %Object, instance As MyPkg.EmailCampaign) As %Status
{
    Quit ..%JSONImport(jsonObject, .instance)
}
}

Class MyPkg.Counters Extends (%SerialObject, %JSON.Adaptor)
{
Property processed As %Integer;
Property deferred As %Integer;
Property unsubscribed As %Integer;
Property statuschange As %Integer;
Property bounce As %Integer;
Property mta_dropped As %Integer;
Property dropped As %Integer;
Property delivered As %Integer;
Property sent As %Integer;
Property click As %Integer;
Property open As %Integer;
}

Along with this explanation:

After copying the code “as is” into VSCode, syntax issues with the curly braces were highlighted. ObjectScript classes don’t enable internal class definitions.

Asking ChatGPT to fix the “curly brackets” issue by making the MyPkg.Counters internal class a separate class is a “no-op”; a human will need to move that class into another file. ChatGPT did rename that class after the following request:

Me: The class as it is written does not compile. Can we move the internal class named MyPkg.Counters out of the main class into its own class and rename it to MyPkg.CampaignCounters?

ChatGPT’s response:

Here's the updated ObjectScript code with the MyPkg.CampaignCounters class moved out as its own class:

Class MyPkg.EmailCampaign Extends (%Persistent, %JSON.Adaptor)
{
Property id As %Integer;
Property appId As %Integer;
Property appName As %String;
Property contentId As %Integer;
Property subject As %String;
Property name As %String;
Property counters As MyPkg.CampaignCounters;
Property lastProcessingFinishedAt As %Library.PosixTime;
Property lastProcessingStartedAt As %Library.PosixTime;
Property lastProcessingStateChangeAt As %Library.PosixTime;
Property numIncluded As %Integer;
Property processingState As %String;
Property scheduledAt As %Library.PosixTime;
Property type As %String;
ClassMethod %OnJSONImport(jsonObject As %Object, instance As MyPkg.EmailCampaign) As %Status
{
    Quit ..%JSONImport(jsonObject, .instance)
}
}

Class MyPkg.CampaignCounters Extends (%SerialObject, %JSON.Adaptor)
{
Property processed As %Integer;
Property deferred As %Integer;
Property unsubscribed As %Integer;
Property statuschange As %Integer;
Property bounce As %Integer;
Property mta_dropped As %Integer;
Property dropped As %Integer;
Property delivered As %Integer;
Property sent As %Integer;
Property click As %Integer;
Property open As %Integer;
}

After pasting the above into VSCode and compiling it, the compiler reported that the type named “%Object” does not exist in ObjectScript. According to the IRIS documentation, the property for the first parameter of %JSON.Adaptor’s %JSONImport() method is flexible:

“The input argument is either JSON as a string or stream, or a subclass of %DynamicAbstractObject.”

After removing the “As %Object” property for %OnJSONImport()’s first parameter in the first class and separating the two classes into separate files, the MyPkg.EmailCampaign class compiled successfully. However, the MyPkg.CampaignCounters did not compile; ChatGPT doesn’t (yet?) know that the underscore is a special character in ObjectScript (the string concatenation operator).  

Let’s try to teach it.

Me: The underscore character "_" is a special character in ObjectScript. Please change properties with an underscore in their names to camel case names and remove the underscore.

ChatGPT’s response:

Here is the updated code with properties that had underscores in their names converted to camel case:

Class MyPkg.EmailCampaign Extends (%Persistent, %JSON.Adaptor)
{
Property id As %Integer;
Property appId As %Integer;
Property appName As %String;
Property contentId As %Integer;
Property subject As %String;
Property name As %String;
Property counters As MyPkg.CampaignCounters;
Property lastProcessingFinishedAt As %Library.PosixTime;
Property lastProcessingStartedAt As %Library.PosixTime;
Property lastProcessingStateChangeAt As %Library.PosixTime;
Property numIncluded As %Integer;
Property processingState As %String;
Property scheduledAt As %Library.PosixTime;
Property type As %String;
ClassMethod %OnJSONImport(jsonObject As %Object, instance As MyPkg.EmailCampaign) As %Status
{
    Quit ..%JSONImport(jsonObject, .instance)
}
}

Class MyPkg.CampaignCounters Extends (%SerialObject, %JSON.Adaptor)
{
Property processed As %Integer;
Property deferred As %Integer;
Property unsubscribed As %Integer;
Property statusChange As %Integer;
Property bounce As %Integer;
Property mtaDropped As %Integer;
Property dropped As %Integer;
Property delivered As %Integer;
Property sent As %Integer;
Property click As %Integer;
Property open As %Integer;
}

Along with this explanation:

ChatGPT went above and beyond and changed another property, one named with two separate words, into camel case as well (statuschange); I’m keeping that change since other similarly-named properties in the main class are all camel case.

With the latest generated code in two separate files (and removing the “As %Object” definition in the %OnJSONImport() method definition), all of the code compiles successfully.

These generated classes are an excellent starting point for what I need and I will repeat the process for the other REST data sources for this project, saving me from a lot of unnecessary typing.

2 Comments
Discussion (2)1
Log in or sign up to continue