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

Question

Question

Kurro Lopez · Oct 18, 2024

#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')
	??????
}ObjectScript
ObjectScript

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

score 0 · Answer 1 · 2024-10-18T04:34:53-04:00

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
	}
}ObjectScript
ObjectScript

score 0 · Answer 2 · 2024-10-18T05:29:54-04:00

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)
  }ObjectScript
ObjectScript

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?

score 0 · Answer 3 · 2024-10-18T07:36:43-04:00

Thanks Julian.

If there is any error in the query, always throw a generic error code (ERROR #6022: Gateway: Execute fail), but I don't know if it is due a a duplicate value (code 2061) or is due other sql error code.

Using this code, err.Code always is 6022