New post
Question
· Jan 30, 2019

Unexpected SQL error returns <UNDEFINED> in InterSystems Caché

#ODBC #OpenVMS #SQL #Caché

Problem

I'm working on exporting data from an Intersystems Cache database through the Cache ODBC Driver. There is a particular table that is giving me an error message. The ODBC Driver crashes and reports an error from the Cache system. I think I was able to trace down where the error is coming from, but I do not know how to debug or fix the error.

The table I am trying to extract is called SEDMIHP.

Here's the Error:

[Cache Error: <<UNDEFINED>%0AmBd16^%sqlcq.PRD.3284 ^SEDMIHP(4,77)>]
[Location: <ServerLoop - Query Fetch>]

Research/Trial & Error

I was able to open up Cache Management Studio and find the class that matched up with the table name. I should mention that this is my very first time working with Intersystems Cache, so I apologize if I'm sounding dumb or inexperienced here.

Within the SQLMap, I found this code:

<Data name="DESCRIP_2">
    <RetrievalCode> S {DESCRIP_2}=$P($G(^PHPROP({L1},"DESC_CODES")),"\",2) S {DESCRIP_2}=$S($L({DESCRIP_2}):^SEDMIHP($P({DESCRIP_2},","),$P({DESCRIP_2},",",2)),1:{DESCRIP_2})
     S {DESCRIP_2}=$E({DESCRIP_2},1,80)
    </RetrievalCode>
</Data>

I'm thinking that the code in here is causing an issue. With my very limited understanding of ObjectScript, I think this code is manipulating the text/string, and maybe if there's an undefined or bad value in the data, its causing those functions to throw an error?

I have limited access to the Cache Management Portal, and I am able to find the table in the SQL Schema and run a query on it. About 300 rows of data are loaded before the same Error as above shows up, and it stops loading any more rows. This is why I'm thinking there is bad data.

I tried using ISNULL() and IFNULL() in the SELECT statement to try and skip any bad data, but had the same error in the same spot every time.

Questions

  1. Is there an easy solution from the SQL side that can avoid this error?
  2. Is there anything I can do with the class code in Studio to debug or get more info about this error?

Any and all help is greatly appreciated!

Additional Info

Cache Version: Cache for OpenVMS/IA64 V8.4 (Itanium) 2012.1.5 (Build 956 + Adhoc 12486) 17-APR-2013 19:49:58.07

Discussion (2)0
Log in or sign up to continue
Dmitry Maslennikov · Jan 30, 2019

If you can edit this code, you can try change to this.

<Data name="DESCRIP_2"> <RetrievalCode> S {DESCRIP_2}=$P($G(^PHPROP({L1},"DESC_CODES")),"\",2) S {DESCRIP_2}=$S($L({DESCRIP_2}):$Get(^SEDMIHP($P({DESCRIP_2},","),$P({DESCRIP_2},",",2))),1:{DESCRIP_2}) S {DESCRIP_2}=$E({DESCRIP_2},1,80) </RetrievalCode> </Data>

But not sure, if this correct.

What I did there, is, wrapped retrieving data from global ^SEDMIHP with the function $Get()

Or this way, with the default value

<Data name="DESCRIP_2"> <RetrievalCode> S {DESCRIP_2}=$P($G(^PHPROP({L1},"DESC_CODES")),"\",2) S {DESCRIP_2}=$S($L({DESCRIP_2}):$Get(^SEDMIHP($P({DESCRIP_2},","),$P({DESCRIP_2},",",2)),{DESCRIP_2}),1:{DESCRIP_2}) S {DESCRIP_2}=$E({DESCRIP_2},1,80) </RetrievalCode> </Data>
1 0