Jeffrey Drumm · Jan 4, 2020

Set SQL Error In ObjectScript SqlProc

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!

0 255
Discussion (13)3
Log in or sign up to continue

Did you try to use

ClassMethod Get(pId As %String) As %String [ SqlName = GetMsg, SqlProc , ProcedureBlock = 0  ]

to get SQLCODE and related variables out fo your Class Context ?

Sorry, I had no chance to try it self.

Thanks Robert, but setting ProcedureBlock = 0 had no effect. I'm not getting any errors when setting the properties for %sqlcontext, but its properties don't seem to be exposed to the caller in a way that either the SQL shell or the ODBC invocation see as an error. I've tried setting SQLCODE as well with no effect.

I had in mind to set SQLCODE = -400  , %msg, . .. or similar Embedded SQL variables directly

if it is just for SQLCODE instead of ProcedureBlock = 0 you may use

ClassMethod Get(pId As %String) As %String [ SqlName = GetMsg, SqlProc , PublicList = SQLCODE   ]

% variables are public anyhow
details on PublicList

I had tried PublicList as well. See my comment on Eduard's answer ... I think it may not be possible to set the "top level" error code returned from the SELECT (which is what I was hoping to do).

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()
[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

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?

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?

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.

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. 

Hi Robert,

Thanks. I understand what's happening now ... and very much appreciate your research and input!

Would you please try adding ReturnResultSets to your original method and let us know? This works for me.

ClassMethod Get(pId As %String) As %String [ SqlName = GetMsg, SqlProc,  ReturnResultSets]

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?

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