New post
Question
· Oct 18

How to get the native sql error in SQL outbound adapter?

#SQL #HealthShare #InterSystems IRIS for Health #Health Connect #InterSystems IRIS

Hi all,

We have an restriction in a SQL database with a unique index.

We want to catch that exception when it tries to insert or update a value that violates the unique index condition.

// run the query
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,SQL,.param)

// Check if there is any error
If $$$ISERR(tSC)
{
	Set msgError = $System.Status.GetErrorText(tSC) 
	// Check here if the native error code is 2601 (Cannot insert duplicate key row into object 'MYPATIENTS' with unique index 'UQ_UNIQUE_INDEX')
	??????
}

I've tried to get the ..Adapter.%SQLCODE, but it is empty

Is this code hidden into the tSC variable? I mean, the same way I can get the Error Text using the $System.Status class, is there any method to get the native error?

Best regards

Product version: IRIS 2021.1
$ZV: IRIS for Windows (x86-64) 2021.1.3 (Build 389U) Wed Feb 15 2023 14:50:06 EST
Discussion (3)1
Log in or sign up to continue
Kurro Lopez · Oct 18

Note: I've done the following code to catch the native error... but it is a bit "smell code"

// run the query
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,SQL,.param)

// Check if there is any error
If $$$ISERR(tSC)
{
	Set msgError = $System.Status.GetErrorText(tSC) 
	// Check here if the native error code is 2601 (Cannot insert duplicate key row into object 'MYPATIENTS' with unique index 'UQ_UNIQUE_INDEX')
	if $FIND(msgError, "[2601]") > 0
	{
	    // This is a insert/update that violates the unique code
	    // remove duplicate record
	    quit $$$OK
	}
	else
	{
	    // Generic error... thow excepction
	    quit tSC
	}
}
0 0
Julian Matthews · Oct 18

It's a wild shot in the dark, but looking here: https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=ESQL_adapter_methods_creating#ESQL_transactions

has a try/catch where the catch has the following:

catch err{
    if (err.%ClassName(1)="common.err.exception") && ($$$ISERR(err.status)) {
      set tSC = err.status
    }
    else {
      set tSC = $system.Status.Error(err.Code,err.Name,err.Location,err.InnerException)
  }

If you try to recreate this, does the code you're looking for appear in either err.Code,err.Name,err.Location, or err.InnerException?

0 0