How to get the native sql error in SQL outbound adapter?
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 querySet tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,SQL,.param)
// Check if there is any errorIf$$$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
Comments
Note: I've done the following code to catch the native error... but it is a bit "smell code"
// run the querySet tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,SQL,.param)
// Check if there is any errorIf$$$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 recordquit$$$OK
}
else
{
// Generic error... thow excepctionquit tSC
}
}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?
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