New post
Question
· Apr 29

How to access a stream property in a SQL trigger

#ObjectScript #SQL #InterSystems IRIS

I have an IRIS persistent class with a %Stream property whose value is a JSON object. I'd like to use a SQL trigger to pull some value out of the JSON and persist it in another property, for easy querying and indexing. See below for a minimal example:

Class PAB.DebugStream Extends %Persistent
{

Property Contents As %Stream.GlobalCharacter;
Property msg As %String;
ClassMethod InsertRow()
{
    set stream = ##class(%Stream.GlobalCharacter).%New()
    $$$ThrowOnError(stream.Write({"msg":"hello world!"}))
    &sql(insert into PAB.DebugStream (Contents) values (:stream))
    $$$ThrowSQLIfError(SQLCODE, %msg)
}

Trigger ExtractKeys [ Event = INSERT/UPDATE, Foreach = row/object, Time = AFTER ]
{
    new contentsJSON, id, msg
    if {Contents*C} {
        set contentsJSON = {}.%FromJSON({Contents})
        set id = {ID}
        set msg = contentsJSON.msg
        &sql(update PAB.DebugStream set msg = :msg where Id = :id)
        $$$ThrowSQLIfError(SQLCODE, %msg)
    }
}
}

However, the SQL insert fails in the trigger with a JSON parsing message like this:

<THROW>InsertRow+9^PAB.DebugStream.1 *%Exception.SQL -415 -415 InsertRow+9^PAB.DebugStream.1 Error occurring during INSERT in table 'PAB.DebugStream':  $ZE=<THROW>%FromJSON+22^%Library.DynamicAbstractObject.1 *%Exception.General Parsing error 3 Line 1 Offset 1

If I log the value of {Contents} in the trigger, it's not the stream object as I expect but an integer value.

How can I get the new value of a %Stream property in a SQL trigger?

I'm also open to suggestions about better ways to persist/index specific paths in a JSON property.

Product version: IRIS 2024.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2024.1.1 (Build 347U) Thu Jul 18 2024 17:09:39 EDT
Discussion (2)1
Log in or sign up to continue
Ashok Kumar T · Apr 29

Hi @Pravin Barton

The {Contents} has the id of the stream property. So, We can open the the stream object  by using OID and then convert to JSON like below. and the stream write need to use %ToJSON() for JSON serialization stream.Write({"msg":"hello world!"}.%ToJSON())


Trigger ExtractKeys [ Event = INSERT/UPDATE, Foreach = row/object, Time = AFTER ]
{
    new contentsJSON, id, msg
    
    if {Contents*C} {
        set contentsJSON = {}.%FromJSON(##class(%Stream.GlobalCharacter).%Open($lb({Contents},"%Stream.GlobalCharacter","^PAB.DebugStreamS")))
        set id = {ID}
        set msg = contentsJSON.msg
        &sql(update learn_Smp.NewClass13 set msg = :msg where Id = :id)
        $$$ThrowSQLIfError(SQLCODE, %msg)
    }
}

Thanks!

2 0