Question
· Jul 25

Cache SQL - Cached Query error

#SQL #Caché

Hello to all the Cache Experts out there and Happy Friday!

The company I work for uses Cache 2017.1.3 and we have been seeing intermittent errors in the Cache Error Trap when an SQL query runs.   The error message looks like the error is occurring within the cached query routine that Cache auto-generates.  Unfortunately, when I pulled up the routine in the SMP System Explorer, I only found an obj version, so was unable to look at any code.

Here is the error:    Note:  the .cls375 part will be different based on the different SQL queries that run, since that part of the cached query routine name is auto-generated.  Also, the generated method can be different, again, based on the SQL query that is run - since the methods in the cached query routines are also auto-generated.

<EXTERNAL INTERRUPT>%0AmBk1^%sqlcq.{namespace}.cls375.1

 

So my questions for the Cache Developer Experts out there are:

a) have any of you seen this type of issue before?   If yes, how did you resolve?

b) any idea how to get the *.INT (or *.MAC) code associated with the *.obj version of the auto-generated cached query routine so I can see the code which might also give me some clues as to what is causing the error?

c) Does anyone have any idea about what the <EXTERNAL INTERRUPT> error is?   Could that be happening due to a timeout?

 

Most of the SQL queries are very simple/basic SQL - an example is below.

Set sql="SELECT DISTINCT PrintBy FROM appPkg.appTable WHERE (Status <> '1' AND Status <> '4')"

Set resultSet=##class(%ResultSet).%New()

Set status=resultSet.Prepare(sql)

Set status=resultSet.Execute()

While(resultSet.Next()) {

    // loop and do stuff

}

Much Thanks in Advance for any thoughts, ideas, feedback, suggestions.

Best Regards and Go Team!!!

Product version: Caché 2017.1
Discussion (3)3
Log in or sign up to continue
DC AI Bot · Jul 25

a) The <EXTERNAL INTERRUPT> error might occur due to an operation that has been interrupted by the system, typically caused by a timeout, or a forced user action or process interruption. This happens when the code execution is halted by an external signal like a system-level command. For more complex handling, implement proper timeout handling or consider logging additional details to diagnose such occurrences further [1][2][3].

b) To view the .INT or .MAC code associated with .OBJ, you can utilize debugging commands or tools within the Cache platform. Using the ObjectScript terminal, invoking debugging frameworks like %SYS.Debug could help. However, ensure that debugging permissions are appropriately assigned. Consider methods or tools that provide insights into such .OBJ routines or consult specific debugging best practices documentation applicable to your Cache release version. Without exact tools defined, specific steps can sometimes be ecosystem-managed system [3][4][5].

Further challenges in processing may arise from mismatches of commands - swizzle directions, context pointer handling errors-hands values. Advised debugging tools , e.g invoke"""

Sources:

0 0
Warlin Garcia · Jul 25

a) Haven't seen this issue before so not sure what might be trying to stop/interrupt the process

b) The %sqlcq.{namespace}.cls375.1 is the INT version. If you can't find it then your system is not configured to keep generated source. 

One way to see the code in this situation, assuming you can make code changes in that environment, is to create a routine with the target SQL using embedded SQL and you'll see in most cases the same code that should be in the *.cls### routines. 

c) One way to get it according to documentation https://docs.intersystems.com/iris20252/csp/docbook/Doc.View.cls?KEY=GWO...

1 0
Oliver Thompson · Jul 28

I can't help with the specific error, but you can configure Caché to keep the INT for SQL Queries on this page in System Management Portal:

  System > Configuration > SQL  - (configuration settings)* 
Look for "Retain cached query source"

And the <EXTERNAL INTERRUPT> has the following description:

<EXTERNAL INTERRUPT> Another process has attempted to interrupt this process.


This is in the docs, search for "System Error Messages" should get you there.

 

1 0