To provide machine-readeble SQLIntegrityConstraintViolationException. Required schema name, table name, constraint name.

[DmitriyGod]

Driver version

latest

SQL Server version

mcr.microsoft.com/mssql/server:2022-latest

Client Operating System

any

JAVA/JVM version

any

Table schema

Some constraint, i.e. unique index

Problem description

MSSQL jdbc driver does not provide machine-readeble SQLIntegrityConstraintViolationException.

Expected behavior

Convinient ConstraintViolationException instance with fields: schema name, table name, constraint name like in PG:

if (
    ex instanceof PSQLException pex &&
    ex.getSQLState().startsWith("23")
) {
    return new ConstraintViolationException(
        null,
        **pex.getServerErrorMessage().getSchema(),
        pex.getServerErrorMessage().getTable(),
        pex.getServerErrorMessage().getConstraint()**
    );
}

Actual behavior

The hard way (and fragile solution):

var mssqlUniqueConstraintCode = "23000";
if (
    ex instanceof SQLServerException sex &&
    sex.getSQLState().equals(mssqlUniqueConstraintCode)
) {
    var message = sex.getMessage();
    var databaseName = message.replaceAll(".*database \"(\\S+)\".*", "$1");
    var tableAndSchema = message.replaceAll(".*table \"(\\S+)\".*", "$1").split("\\.");
    var constraintName = message.replaceAll(".*constraint \"(\\S+)\".*", "$1");

    return new ConstraintViolationException(
        databaseName, tableAndSchema[0], tableAndSchema[1], constraintName
    );
}

Error message/stack trace

No

Any other details that can be helpful

Obviously in PG not a perfect solution, but at least this way:
https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PSQLException.html#getServerErrorMessage--

JDBC trace logs

No

[machavan]

Current behavior:

try {

...
} catch (SQLException e) {
			System.out.println("SQL State : " + e.getSQLState());
			System.out.println("SQL Error Code : " + e.getErrorCode());
			System.out.println("Error Message : " + e.getMessage());
			e.printStackTrace();
}

SQL Server :

Exception type : com.microsoft.sqlserver.jdbc.SQLServerException

SQL State : 23000
SQL Error Code : 2627
Error Message : Violation of PRIMARY KEY constraint 'PK__tcons__3BD0198ED76C99CC'. Cannot insert duplicate key in object 'dbo.tcons'. The duplicate key value is (1).

PostgreSQL :

Exception type : org.postgresql.util.PSQLException

SQL State : 23505
SQL Error Code : 0
Error Message : ERROR: duplicate key value violates unique constraint "tcons_pkey"
Detail: Key (a)=(1) already exists.
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "tcons_pkey"

(Note : PG does not have a specialized exception type to represent constraint violation exception)

The ask here is an enhancement to create a new exception type in mssql-jdbc e.g. SQLIntegrityConstraintViloationException with various fields such as database, schema, table, constraint name set in it. Is this a correct interpretation?

[DmitriyGod]

Yeah