Derby
  1. Derby
  2. DERBY-254

SQLStates for SQLExceptions thrown from the client should not be null and should match embedded where possible

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.1.0, 10.2.1.6
    • Fix Version/s: 10.2.1.6
    • Component/s: Network Client
    • Labels:
      None

      Description

      SQLExceptions generated from the client tend to have a null SQLState. An evaluation of the SQLExceptions thrown by the client needs to be performed and match embedded where possible.

      If this does not happen before the 10.1 release, it would be good to document that SQLStates in the client will change for future releases

        Issue Links

          Activity

          Hide
          Andrew McIntyre added a comment -

          Moving this issue to 10.2 - will add comment about this issue to doc review issue for the reference manual.

          Show
          Andrew McIntyre added a comment - Moving this issue to 10.2 - will add comment about this issue to doc review issue for the reference manual.
          Hide
          David Van Couvering added a comment -

          A discussion around this has started on the Wiki page http://wiki.apache.org/db-derby/JDBC_error_messages_and_SQL_States

          Show
          David Van Couvering added a comment - A discussion around this has started on the Wiki page http://wiki.apache.org/db-derby/JDBC_error_messages_and_SQL_States
          Hide
          Tomohito Nakayama added a comment -

          Comment moved from wiki
          http://wiki.apache.org/db-derby/JDBC_error_messages_and_SQL_States

          Hi, Tomohito. I am dealing with the null SQL states in the client driver as part of my work on internationalizing the messages. I think it makes a lot of sense for the SQL states to match between network client and embedded client. I noticed, for instance, test code that did something like:

          if ( client driver is embedded )
          look for SQL State NNNN
          else
          look for SQL State YYYY
          end if
          This seems silly.

          What I am doing (we'll see if people have comments on this) is defining a new severity for exceptions thrown by the network client. Since the exception severity is mapped to the SQLException error code, the application programmer can check the error code to determine if it's a network client exception.

          The other way they can distinguish is to see what the instance of the class is. It will be an instance of org.apache.derby.impl.jdbc.EmbedSQLException if it came from the embedded driver, and an instance of org.apache.derby.client.am.SqlException if it comes from the network client.

          I think we should keep the SQL states (and the error messages) identical where the error is identical between the network client and the engine.

          Also, I would like to recommend that this discussion be moved to or linked to from the JIRA issue around SQL states – DERBY-254

          David VC

          Show
          Tomohito Nakayama added a comment - Comment moved from wiki http://wiki.apache.org/db-derby/JDBC_error_messages_and_SQL_States Hi, Tomohito. I am dealing with the null SQL states in the client driver as part of my work on internationalizing the messages. I think it makes a lot of sense for the SQL states to match between network client and embedded client. I noticed, for instance, test code that did something like: if ( client driver is embedded ) look for SQL State NNNN else look for SQL State YYYY end if This seems silly. What I am doing (we'll see if people have comments on this) is defining a new severity for exceptions thrown by the network client. Since the exception severity is mapped to the SQLException error code, the application programmer can check the error code to determine if it's a network client exception. The other way they can distinguish is to see what the instance of the class is. It will be an instance of org.apache.derby.impl.jdbc.EmbedSQLException if it came from the embedded driver, and an instance of org.apache.derby.client.am.SqlException if it comes from the network client. I think we should keep the SQL states (and the error messages) identical where the error is identical between the network client and the engine. Also, I would like to recommend that this discussion be moved to or linked to from the JIRA issue around SQL states – DERBY-254 David VC
          Hide
          Kathey Marsden added a comment -

          David said:

          >What I am doing (we'll see if people have comments on this) is defining a new severity for exceptions thrown by the >network client. Since the exception severity is mapped to the SQLException error code, the application programmer >can check the error code to determine if it's a network client exception.

          I feel that the client error codes should stay as they are until changed to match the embedded severity. That in itself will be disruptive enough. Application servers use the current codes to detect stale connections, and will have to make changes when/if the codes are changed. We documented only that client SQLStates and error codes might be changed to match embedded, not that it might be changed to something else entirely.

          Show
          Kathey Marsden added a comment - David said: >What I am doing (we'll see if people have comments on this) is defining a new severity for exceptions thrown by the >network client. Since the exception severity is mapped to the SQLException error code, the application programmer >can check the error code to determine if it's a network client exception. I feel that the client error codes should stay as they are until changed to match the embedded severity. That in itself will be disruptive enough. Application servers use the current codes to detect stale connections, and will have to make changes when/if the codes are changed. We documented only that client SQLStates and error codes might be changed to match embedded, not that it might be changed to something else entirely.
          Hide
          Daniel John Debrunner added a comment -

          David said:

          > What I am doing (we'll see if people have comments on this) is defining a new severity for exceptions thrown by the network client. Since the exception severity is mapped to the SQLException error code, the application programmer can check the error code to determine if it's a network client exception.

          I'm not sure Derby should be recommending checking the error code for an exception, I don't think today it's specified as any part of the documentation that the error code is a severity. Use of the error code is vendor specific and will result in non-portable programs. JDBC 4.0 is addressing this with the sub-classing of SQLException.

          David also said:
          > The other way they can distinguish is to see what the instance of the class is. It will be an instance of org.apache.derby.impl.jdbc.EmbedSQLException if it came from the embedded driver, and an instance of org.apache.derby.client.am.SqlException if it comes from the network client.

          No, this is not part of any documented api., there are no guarantees that any exception thrown by Derby embedded or the network client will be any speciific class. I would like us to use SQLException directly and I think JDBC 4.0 will force us in that direction (using SQLException and the other exception classes declared by JDBC 4.0)

          Show
          Daniel John Debrunner added a comment - David said: > What I am doing (we'll see if people have comments on this) is defining a new severity for exceptions thrown by the network client. Since the exception severity is mapped to the SQLException error code, the application programmer can check the error code to determine if it's a network client exception. I'm not sure Derby should be recommending checking the error code for an exception, I don't think today it's specified as any part of the documentation that the error code is a severity. Use of the error code is vendor specific and will result in non-portable programs. JDBC 4.0 is addressing this with the sub-classing of SQLException. David also said: > The other way they can distinguish is to see what the instance of the class is. It will be an instance of org.apache.derby.impl.jdbc.EmbedSQLException if it came from the embedded driver, and an instance of org.apache.derby.client.am.SqlException if it comes from the network client. No, this is not part of any documented api., there are no guarantees that any exception thrown by Derby embedded or the network client will be any speciific class. I would like us to use SQLException directly and I think JDBC 4.0 will force us in that direction (using SQLException and the other exception classes declared by JDBC 4.0)
          Hide
          David Van Couvering added a comment -

          OK, thanks for your comments, Kathey and Dan.

          If I could summarize: in general SQLStates and their associated severities on the network client should match the embedded SQL States/severities on the embedded driver. If the exception is unique to the network client, it should still use one of the pre-defined severities in ExceptionSeverity class. Do I have that right?

          Thanks, also, Dan, for reminding me that the underlying SQLException classes are private interfaces and should not be documented or depended on. You mention that we should move towards "using SQLException directly". Can you explain a little more what you mean here? EmbedSQLException and org.apache.derby.client.am.SqlException both extend java.sql.SQLException. What does it mean to use SQLException "directly?"

          Also, if the exception are indistinguishable between embedded and network client (which makes a lot of sense so that code is easily portable between embedded and network client), how do we address Tomohito's concerns that (if I understand him correctly) application logic may want to react differently depending upon whether the exception is thrown directly by the client or indirectly from the engine?

          Thanks,

          David

          Show
          David Van Couvering added a comment - OK, thanks for your comments, Kathey and Dan. If I could summarize: in general SQLStates and their associated severities on the network client should match the embedded SQL States/severities on the embedded driver. If the exception is unique to the network client, it should still use one of the pre-defined severities in ExceptionSeverity class. Do I have that right? Thanks, also, Dan, for reminding me that the underlying SQLException classes are private interfaces and should not be documented or depended on. You mention that we should move towards "using SQLException directly". Can you explain a little more what you mean here? EmbedSQLException and org.apache.derby.client.am.SqlException both extend java.sql.SQLException. What does it mean to use SQLException "directly?" Also, if the exception are indistinguishable between embedded and network client (which makes a lot of sense so that code is easily portable between embedded and network client), how do we address Tomohito's concerns that (if I understand him correctly) application logic may want to react differently depending upon whether the exception is thrown directly by the client or indirectly from the engine? Thanks, David
          Hide
          David Van Couvering added a comment -

          The SQL States will be fixed as part of internationalizing the client messages

          Show
          David Van Couvering added a comment - The SQL States will be fixed as part of internationalizing the client messages
          Hide
          David Van Couvering added a comment -

          All client messages have been converted to use SQL States as part of the DERBY-400 work, which is now complete except for some additional testing and documentation

          Show
          David Van Couvering added a comment - All client messages have been converted to use SQL States as part of the DERBY-400 work, which is now complete except for some additional testing and documentation
          Hide
          David Van Couvering added a comment -

          Proposed release note on this:

          In this release, all of the client SQLExceptions now return valid SQL States rather than null. As much as possible, these SQL States are consistent with the SQL and DRDA standards and are consistent with the SQL States returned by the embedded driver for the same error.

          Show
          David Van Couvering added a comment - Proposed release note on this: In this release, all of the client SQLExceptions now return valid SQL States rather than null. As much as possible, these SQL States are consistent with the SQL and DRDA standards and are consistent with the SQL States returned by the embedded driver for the same error.
          Hide
          David Van Couvering added a comment -

          Re-opening pending finalization of the release note text that should go with this change.

          Show
          David Van Couvering added a comment - Re-opening pending finalization of the release note text that should go with this change.
          Hide
          Kathey Marsden added a comment -

          Will there be related updates to:
          http://db.apache.org/derby/docs/dev/ref/rrefexcept71493.html
          It seems like it would be good to reference this page iin the release note if it will be updated with the client SQLStates.

          Show
          Kathey Marsden added a comment - Will there be related updates to: http://db.apache.org/derby/docs/dev/ref/rrefexcept71493.html It seems like it would be good to reference this page iin the release note if it will be updated with the client SQLStates.
          Hide
          David Van Couvering added a comment -

          This issue was kept open because of the need to update the documentation of SQL States. DERBY-1566 has since been opened to track this task, so I am relating these two, and then I'll close this issue.

          Show
          David Van Couvering added a comment - This issue was kept open because of the need to update the documentation of SQL States. DERBY-1566 has since been opened to track this task, so I am relating these two, and then I'll close this issue.
          Hide
          David Van Couvering added a comment -

          Resolving this issue and removing Release Note Needed as DERBY-1566 is tracking this now.

          Show
          David Van Couvering added a comment - Resolving this issue and removing Release Note Needed as DERBY-1566 is tracking this now.

            People

            • Assignee:
              David Van Couvering
              Reporter:
              Kathey Marsden
            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development