Dynamic query execution any error found How to log it
Hi Friends,
In my business process I am updating cache table using dynamic query.
ex:
Set result=##class(%ResultSet).%New("%DynamicQuery:SQL")
Set sc=result.Prepare("SELECT %ID, Name, Salary FROM Sample.Employee WHERE Salary > ?")
If $$$ISERR(sc)
{
// here I want to capture the error details in trace , log , that I can see in production web page
}
could you please provide any suggestions to grab the error message.
I tried
Do DisplayError^%apiOBJ(sc)
Do $SYSTEM.OBJ.DisplayError(tSC)end up with no class found , methods found errors.
I have spent 5 hours today for some clue.
Thanks ,
Prashanth
Product version: IRIS 2022.1
Hi Prasanth,
Can you try this,
Do $System.Status.DisplayError(status)
I created a class for dynamic query:
Class otw.dynq
{
ClassMethod prepareQ() As %Status
{
Set result=##class(%ResultSet).%New("%DynamicQuery:SQL")
Set sc=result.Prepare("SELECT %ID, Name, Salary FROM Sample.Employee WHERE Salary > ?")
If $$$ISERR(sc) {
// here I want to capture the error details in trace , log , that I can see in production web page
Try {
Do ##class(%SYS.System).WriteToConsoleLog("%ZSTART Routine: "_"Import Users",0,0)
} Catch exception {
Do BACK^%ETN // Log error in error log
}
}
Quit sc
}
}
I call the Dynamic Query Prepare from BPL and Trace the Status Text from the Prepare:
///
Class otw.bplJSON2HL7 Extends Ens.BusinessProcessBPL
{
/// BPL Definition
XData BPL [ XMLNamespace = "http://www.intersystems.com/bpl" ]
{
<process language='objectscript' request='Ens.Request' response='Ens.Response' height='2000' width='2000' >
<context>
<property name='dynPrepareStatus' type='%Library.Status' instantiate='0' />
</context>
<sequence xend='200' yend='750' >
<trace name='request.Stream.Read()' value='request.Stream.Read()' xpos='200' ypos='250' />
<assign name="dynPrepareStatus" property="context.dynPrepareStatus" value="##class(otw.dynq).prepareQ()" action="set" xpos='200' ypos='350' />
<trace name='dynPrepareStatus' value='$System.Status.GetOneStatusText(context.dynPrepareStatus)' xpos='200' ypos='450' />
<transform name='otw.dtlJSON2HL7' class='otw.dtlJSON2HL7' source='request' target='response' xpos='200' ypos='550' />
<call name='HL7FileOperation' target='HL7FileOperation' async='1' xpos='200' ypos='650' >
<request type='Ens.Request' >
<assign property="callrequest" value="response" action="set" />
</request>
<response type='Ens.Response' />
</call>
</sequence>
</process>
}
Storage Default
{
<Type>%Storage.Persistent</Type>
}
}
The code is in this GitHub repo:
https://github.com/oliverwilms/HL7
The error from Prepare will write to messages.log with this change:
Class otw.dynq
{
ClassMethod prepareQ() As %Status
{
Set result=##class(%ResultSet).%New("%DynamicQuery:SQL")
Set sc=result.Prepare("SELECT %ID, Name, Salary FROM Sample.Employee WHERE Salary > ?")
If $$$ISERR(sc) {
// here I want to capture the error details in trace , log , that I can see in production web page
Try {
Set x = $System.Status.GetOneStatusText(sc)
Do ##class(%SYS.System).WriteToConsoleLog("otw.dynq prepareQ: "_x)
} Catch exception {
Do BACK^%ETN // Log error in error log
}
}
Quit sc
}
}
The same example but in a different form:
try { set result=##class(%ResultSet).%New("%DynamicQuery:SQL") $$$ThrowOnError(result.Prepare("SELECT ... ")) } catch error { do error.Log() }The error details will be in the Application Error Log
If you want to quit processiong, it's enough to either quit:
quit:$$$ISERR(sc) scOr raise an error (if you're several levels deep for example):
$$$TOE(sc, sc)If you don't want to interrupt processing, use
$$$LOGmacroes, for example:$$$LOGWARNING($System.Status.GetErrorText(sc))This code would create a new Log entry of a warning type.