New post
Question
· Jan 4, 2020

Set SQL Error In ObjectScript SqlProc

#Caché #InterSystems IRIS

I've writing a short classmethod that extracts an HL7 message from EnsLib.HL7.Message by ID and returns it as a stream. It's to be called as a stored procedure via ODBC (or ADO.NET) to fetch messages into an application.

I'm not using an SQL query in the classmethod itself, but it will be called as part of an SQL query via ODBC, but I can't figure out how to set a custom SQLCODE and error text from within the classmethod that propagates back to the external application. I thought %sqlcontext would be useful for this, but it doesn't appear to do anything. Here's the class:

Class User.HL7.Message Extends %RegisteredObject
{

ClassMethod Get(pId As %String) As %String [ SqlName = GetMsg, SqlProc ]
{
    Set tMsg = ##class(%Stream.TmpCharacter).%New()    
    Set tHl7 = ##class(EnsLib.HL7.Message).%OpenId(pId,,.tSC)
    If $$$ISERR(tSC)
    {
        Set %sqlcontext.%SQLCode = -400
        Set %sqlcontext.%Message = "EnsLib.HL7.Message Object with ID "_pId_" Not Found - "_$System.Status.GetErrorText(tSC)
        QUIT ""
    }
    Do tHl7.OutputToLibraryStream(.tMsg)
    Return tMsg.Read(tMsg.Size)
}

}

I've scoured the documentation, but I'm not getting it. Thanks in advance for any help!

Discussion (13)3
Log in or sign up to continue
Eduard Lebedyuk · Jan 4, 2020

You need to throw an exception. Sample proc:

Class Test.Obj
{

/// do ##class(Test.Obj).TestFunc().%Display()
Query Test() As %SQLQuery
{
SELECT Test.Obj_MyProc()
}

ClassMethod MyProc() As %String [ SqlProc ]
{
    Set tSC = $$$ERROR($$$GeneralError, "MyMsg")
    If $$$ISERR(tSC)
    {
        Throw ##class(%Exception.SQL).CreateFromSQLCODE(-400, $System.Status.GetErrorText(tSC))
        Quit ""
    }
    Quit "OK"
}

}

And after I call the procedure, this message is returned:

do ##class(Test.Obj).TestFunc().%Display()
Expression_1
 
 
[SQLCODE: <-149>:<SQL Function encountered an error>]
[%msg: <SQL Function TEST.OBJ_MYPROC failed with error:  SQLCODE=-400,%msg=ERROR #5001: MyMsg>]
0 Rows(s) Affected
1 0
Jeffrey Drumm · Jan 4, 2020

Hi Eduard, and thanks!

 I had already tried that option, but discarded it because the SQL code returned is <-149>:<SQL Function encountered an error>, with my error code and text as a subordinate (child?) error. Is there any way to set the "parent" SQLCODE and associated error text? Or is that effectively the "query" error, with the child error generated by the SqlProc?

0 0
Eduard Lebedyuk · Jan 5, 2020

I don't think there is a way to do that.

If you check generated code for this procedure it  would be something like this:

try {
  new err, result
  set result=##class(Test.Obj).MyProc()
  return result
} catch err {
  set SQLCODE=-149
  set %msg="SQL Function TEST.OBJ_MYPROC failed with error:  SQLCODE="_err.AsSQLCODE()_",%msg="_err.AsSQLMessage()
  ztrap "SQER"
}

Maybe @Dan Pasco knows?

0 0
Dan Pasco · Jan 6, 2020

I believe the correct way to report an error from an SQL-Invokable Routine (procedure or function) is to throw an exception as Eduard demonstrated. Attempting to set local variables or retrieve them using an SQL function/procedure might produce invalid results.

As for reporting a nested error - I don't know.

A side note - it is not necessary to extend %RegisteredObject when a class contains only class methods. Not extending %RegisteredObject will result in smaller runtime footprints.

0 0
Robert Cemper · Jan 5, 2020

Hi Jeff,

After some playing around it was clear that any error in a procedures ends up with <-149>:<SQL Function encountered an error>
as you found out yourself.

To have <-400> the error must happen at the top level of your SQL statement .
Using your initial SqlProc
If you add an argument to your SELECT ....., 1/HL7.Message.Get(pid) as found ....

you get a useless 1/tMsg.Read(tMsg.Size) value

but you get 

[SQLCODE: <-400>:<Fatal error occurred>]
[%msg: <Unexpected error occurred: <DIVIDE>%0AmBuncommitted+4^%sqlcq.USER.cls34.1>]

enforced by a 0 return value.

That's just half of the request and I see no way  to influence %msg variable
So you can enforce a STOP of your query. 

2 0
Joel Solon · Jan 6, 2020

I'm sorry to mislead; ReturnResultSets doesn't make a difference after all. I played around a little more, calling a simple stored procedure from a JDBC client (DBeaver).

Select Sample.Employee_StoredProcTest('jj') returns the result, and ignores anything in %sqlcontext unless you throw an exception, and the %sqlcontext properties are nested.

Call Sample.Employee_StoredProcTest('jj', '') shows the un-nested %SQLCode and %Message properties of %sqlcontext, but doesn't return the result.

So maybe the solution is to return tMsg.Size by reference and use Call?

0 0
Jeffrey Drumm · Jan 6, 2020

So to clarify ... the class method isn't so much a stored procedure as a custom function. It should return an HL7 message as a  string, which will be represented as a column in the result set generated by a SELECT. So technically, the SQLCODE I'm getting back is correct for what I'm doing, which is calling a function against a column value in the query. If the function fails, the query fails, and the reason for the query failure is the function failure. That's how it's coming back to ADO/ODBC when I force a failure, and the reason I asked the question in the first place is that sometimes I'm just not too smart :D

0 0