TIP #350: Tcl Database Connectivity - Corrigenda

Abstract

This TIP defines a common database access interface for Tcl scripts. It is an update to TIP #308 to take into account experience gained since that TIP was written. Note that this TIP does not repeat the contents of that one, which is mostly correct apart from the changes described in this document.

Summary of Changes

Implementation experience on Tcl Database Connectivity TIP #308 has exposed several issues with its specification that require editorial corrections. In brief:

1. The error codes returned from TDBC drivers are detailed in such a way as to make them more usable in the try command.

2. The starttransaction method on a database connection is renamed, begintransaction

3. The execute method on a statement, and all of the methods that invoke it (allrows and foreach on database connections) changes its behaviour in the case where a bound variable in its SQL code refers to a Tcl variable that is an array, or a read trace on the associated variable fails.

4. The order of arguments on the foreach methods on database connections, statements and result sets is changed.

5. The statementClass and resultSetClass instance variables, and the init method of connections, statements and result sets, are deprecated; a new initialization API is provided.

6. A Tcl command, tdbc::mapSqlState, and a C function, Tdbc_MapSqlState are provided for the convenience of driver writers.

Introduction

The actual implementation of TDBC and three database drivers for it has revealed a handful of mistakes in the TDBC specification TIP #308. The purpose of this TIP is to correct those errors and promulgate a specification that matches TDBC as implemented.

Specification

Error Codes

Whenever a TDBC driver reports an error in interacting with an underlying database, it SHOULD set the interpreter error code to a list of at least four elements. The first element should be the constant string TDBC. The second should be an 'error class' chosen from the list below. The third should be the (usually five-character) SQL state that the database reported, or the constant string HY000 if the SQL state cannot be determined. (In the latter case, the error class should be GENERAL_ERROR.) The fourth element should be the name of the TDBC driver that reported the error. Any elements beyond the fourth SHOULD give further details (for example an error code returned by a native API), and are driver dependent.

The permissible values for the error class are as follows. Note that each one corresponds to the first two characters of a five-character 'SQL state' that is common to most SQL database API's; the SQL state corresponding to the class is also given.

 SQL State
 Prefix     Error Class
 --------------------------------------------------------
    00      UNQUALIFIED_SUCCESSFUL_COMPLETION
    01      WARNING
    02      NO_DATA
    07      DYNAMIC_SQL_ERROR
    08      CONNECTION_EXCEPTION
    09      TRIGGERED_ACTION_EXCEPTION
    0A      FEATURE_NOT_SUPPORTED
    0B      INVALID_TRANSACTION_INITIATION
    0D      INVALID_TARGET_TYPE_SPECIFICATION
    0F      LOCATOR_EXCEPTION
    0K      INVALID_RESIGNAL_STATEMENT
    0L      INVALID_GRANTOR
    0P      INVALID_ROLE_SPECIFICATION
    0W      INVALID_STATEMENT_UN_TRIGGER
    20      CASE_NOT_FOUND_FOR_CASE_STATEMENT
    21      CARDINALITY_VIOLATION
    22      DATA_EXCEPTION
    23      CONSTRAINT_VIOLATION
    24      INVALID_CURSOR_STATE
    25      INVALID_TRANSACTION_STATE
    26      INVALID_SQL_STATEMENT_IDENTIFIER
    27      TRIGGERED_DATA_CHANGE_VIOLATION
    28      INVALID_AUTHORIZATION_SPECIFICATION
    2B      DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST
    2C      INVALID_CHARACTER_SET_NAME
    2D      INVALID_TRANSACTION_TERMINATION
    2E      INVALID_CONNECTION_NAME
    2F      SQL_ROUTINE_EXCEPTION
    33      INVALID_SQL_DESCRIPTOR_NAME
    34      INVALID_CURSOR_NAME
    35      INVALID_CONDITION_NUMBER
    36      CURSOR_SENSITIVITY_EXCEPTION
    37      SYNTAX_ERROR_OR_ACCESS_VIOLATION
    38      EXTERNAL_ROUTINE_EXCEPTION
    39      EXTERNAL_ROUTINE_INVOCATION_EXCEPTION
    3B      SAVEPOINT_EXCEPTION
    3C      AMBIGUOUS_CURSOR_NAME
    3D      INVALID_CATALOG_NAME
    3F      INVALID_SCHEMA_NAME
    40      TRANSACTION_ROLLBACK
    42      SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION
    44      WITH_CHECK_OPTION_VIOLATION
    45      UNHANDLED_USER_DEFINED_EXCEPTION
    46      JAVA_DDL
    51      INVALID_APPLICATION_STATE
    53      INSUFFICIENT_RESOURCES
    54      PROGRAM_LIMIT_EXCEEDED
    55      OBJECT_NOT_IN_PREREQUISITE_STATE
    56      MISCELLANEOUS_SQL_OR_PRODUCT_ERROR
    57      RESOURCE_NOT_AVAILABLE_OR_OPERATOR_INTERVENTION
    58      SYSTEM_ERROR
    70      INTERRUPTED
    F0      CONFIGURATION_FILE_ERROR
    HY      GENERAL_ERROR
    HZ      REMOTE_DATABASE_ACCESS_ERROR
    IM      DRIVER_ERROR
    P0      PGSQL_PLSQL_ERROR
    S0      ODBC_2_0_DML_ERROR
    S1      ODBC_2_0_GENERAL_ERROR
    XA      TRANSACTION_ERROR
    XX      INTERNAL_ERROR
  anything
   else     UNKNOWN_SQLSTATE

The reason for structuring the error codes in this way is to make errors more accessible to the try command TIP #329. For instance, a Tcl script that wishes to detect and handle division by zero in a SQL statement might look like:

  try {
      $statement foreach row {
          # ... process the row
      }
 } trap {TDBC DATA_EXCEPTION 22012} {
      puts "Division by zero!"
 }

Since the previous specification TIP #308 left the error code unspecified, this change is not expected to impact any client code.

Transaction Control

The begintransaction method was inadvertently called, starttransaction in the TDBC specification. Therefore, the word starttransaction should be replaced with begintransaction wherever it appears.

This change will break no existing code; no starttransaction method has been defined for any TDBC driver.

The '''execute''' Method of a Statement - Variable Substitution

The rule that an array variable provided as a bound value to a substituent in a SQL statement MUST result in an error has proven to be awkward to implement in practice. Moreover, the original specification TIP #308 fails to indicate what happens if a read trace on one of a statement's bound variables throws an error.

The sentence,

An array variable provided to a substituent MUST result in an error.

is therefore to be replaced with:

An array variable provided to a substituent, or a variable in which substitution results in an error being reported by a read trace, MUST result in a NULL value being provided.

This change is expected to have minimal impact on existing code; the behaviour being described is simply providing a NULL value for a case that was an error before (an array where a scalar is expected) and a case that was unspecified before (an error within a variable trace).

The ''foreach'' Methods

The syntax of the foreach method of connections, statements, and result sets in the original specification contains editorial errors. The correct syntax is:

dbHandle foreach ?-as lists|dicts? ?-columnsvariable varName? ?--? varName sql ?dictionary? script

statement foreach ?-as lists|dicts? ?-columnsvariable varName? ?--? varName ?dictionary? script

resultset foreach ?-as lists|dicts? ?-columnsvariable varName? ?--? varName script

This change represents an editorial correction; the reference implementation functioned in this way even prior to the acceptance of the original specification TIP #308.

The Constructor Patterns

The statementClass variable, and the init method, are no longer recommended for use in the constructors of connection classes. Instead, the recommended pattern is that a connection class SHOULD implement a statementCreate method that accepts the fully qualified name of the command that is to represent the statement, the connection handle and the SQL statement, and returns a handle to the statement object. The usual way to do so is with a forwarded method:

  forward statementCreate ::driver::statement create

If the statementCreate method is not present, the default one looks for a variable named statementClass in the connection object, and invokes its create command.

In this way, drivers that are written to the original specification continue to operate.

Similarly, the resultSetClass variable, and the init method, are no longer recommended for use in the constructors of statement classes. Instead, the statement class SHOULD implement a resultSetCreate method that accepts the fully qualified name of the command that will represent the result set, the statement handle, and the parameters to the prepare method. Once again, this method will usually simply be forwarded to the appropriate constructor:

  forward resultSetCreate ::driver::resultSet create

Once again, backward compatibility is provided by a resultSetCreate method in the base class. This method looks for a resultSetClass variable in the statement instance, and interprets it as a class name, invoking the create method in that class.

Rationale: These changes eliminate several jumps among methods with uplevel calls, and yield both simpler code and improved performance.

SQL State Mapping

For the convenience of drivers that deal with database APIs that provide a standard SQL dtate in the event of errors, a Tcl command, tdbc::mapSqlState is provided. This command accepts a (usually five character) SQL state, and returns the error class that should go in the second element of the error code. The mapping is described in the table in the Error Codes section above.

Similarly, A C function is provided:

const char * Tdbc_MapSqlState(const char *sqlstate);

This call looks up the given sqlstate and returns its error class according to the table.

License

This file is explicitly released to the public domain and the author explicitly disclaims all rights under copyright law.

[Index] [History] [HTML Format] [Source Format] [LaTeX Format] [Text Format] [XML Format] [*roff Format (experimental)] [RTF Format (experimental)]