Request for Documentation of SQLState to Exception cause mapping

[SriSK97]

Summary

The SQL Server JDBC driver maps various database errors to SQLState S0002 when the databaseState is 2, but there is no official documentation explaining which specific error types or conditions result in databaseState 2. This makes it difficult to add logic based on a specific exception type

Environment

• SQL Server JDBC Driver Versions: 7.x and 12.x
• SQL Server Version: Microsoft Azure SQL Edge (RTM) - 15.0.2000.1574 (ARM64) Jan 25 2023 10:36:08 Copyright (C) 2019 Microsoft Corporation Linux (Ubuntu 18.04.6 LTS aarch64)
• Java Version - 11

Problem Description

• Application Context: Our JDBC client application interacts with SQL Server through the JDBC driver and implements specialized error handling logic that categorizes and processes connection-related exceptions differently from other types of database errors

• Initial Testing Observations: During our testing phase, we identified several SQLStates that consistently indicated connection-related issues and built our error classification logic around these patterns.

• SQLState S0002 Classification: SQLState S0002 was initially categorized as a connection-related error based on our observations during testing.

• Unexpected Behavior Discovery: We recently encountered the same SQLState (S0002) in a completely different context - specifically for an overflow-related exception, which contradicted our initial classification.

• Root Cause Investigation: This unexpected occurrence prompted us to investigate the JDBC driver's error mapping logic by examining the source code

• Technical Finding: Upon debugging the SQL Server JDBC driver source code, we discovered that SQLState S0002 is generated in two distinct scenarios:
  Explicit mapping: Error number 208 (table not found) is explicitly mapped to S0002 (line 389)
  Default mapping: Any database error with error state 2 falls through to the default case and is dynamically mapped to S0002 using the database state value

• Code Reference: The relevant code in SQLServerException.java demonstrates this behavior through the default case logic that constructs SQLStates using the database state value.

default: {
    String dbState = databaseState.toString();
    /*
     * Length allowed for SQL State is 5 characters as per SQLSTATE specifications. Append trailing
     * zeroes as needed based on length of database error State as length of databaseState is between 1
     * to 3 digits.
     */
    StringBuilder trailingZeroes = new StringBuilder("S");
    for (int i = 0; i < 4 - dbState.length(); i++) {
        trailingZeroes.append("0");
    }
    return trailingZeroes.append(dbState).toString();
}

This means that any SQL Server error with database state 2 will result in SQLState S0002, regardless of the actual error type.

Issues This Causes

• This ambiguity makes it difficult for our application to reliably distinguish between connection-related issues and other types of database errors when relying solely on SQLState values for error categorization
• Core Issue: The absence of comprehensive documentation explaining which specific database error conditions result in database state 2 or SQLState of S0002 makes it impossible for us to build reliable error handling logic around SQLState values.

Questions

1. What is a databaseState, which is utilised to construct the SQLState of the SQLException?
2. Is there official Microsoft documentation that maps SQL Server database error states or the SQLStates to their meanings?
3. Are there plans to provide more granular SQLState mappings for common error scenarios?

We understand that maintaining comprehensive documentation for all error mapping scenarios is challenging. Any clarification or documentation updates regarding SQLState mappings would be greatly appreciated and would help developers build more reliable error-handling logic. Thank you very much for considering this request.

[divang]

Thank you for your detailed analysis and questions regarding SQLState mappings in the SQL Server JDBC driver.

1. What is a databaseState, which is utilised to construct the SQLState of the SQLException?
   databaseState: In the context of the JDBC driver, databaseState refers to the SQL Server error state value, which provides additional information about the error beyond the error number. This value is used by the driver to dynamically construct the SQLState code (as you observed in the source code). However, the meaning of specific state values is generally determined by SQL Server itself, and their significance can vary depending on the error number.

2. Is there official Microsoft documentation that maps SQL Server database error states or the SQLStates to their meanings?
   Currently, official Microsoft documentation does not provide a comprehensive mapping of SQL Server error numbers or states to SQLState values as used in the JDBC driver. The driver’s mapping logic can be inferred from its source code, but a list of all possible error scenarios and their resulting SQLState codes is not published.

3. Are there plans to provide more granular SQLState mappings for common error scenarios?
   We acknowledge your concern about the ambiguity caused by generic SQLState mappings. At present, the granularity of SQLState mapping is limited, and many errors are mapped based on the state value rather than the specific error type. We agree that improved documentation and more specific mappings would benefit developers in building robust error handling logic. We will consider documenting the mapping logic or enhancing the SQLState assignment in future releases.