[TCLCORE] TIP #350: Tcl Database Connectivity - Corrigenda

[Kevin B. K. <ke...@ac...>]

 TIP #350: TCL DATABASE CONNECTIVITY - CORRIGENDA 
==================================================
 Version:      $Revision: 1.1 $
 Author:       Kevin B. Kenny <kennykb_at_acm.org>
 State:        Draft
 Type:         Informative
 Vote:         Pending
 Created:      Saturday, 18 April 2009
 URL:          http://purl.org/tcl/tip/350.html
 WebEdit:      http://purl.org/tcl/tip/edit/350
 Post-History: 
 Obsoletes:    TIP #308

-------------------------------------------------------------------------

 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. 

-------------------------------------------------------------------------

 TIP AutoGenerator - written by Donal K. Fellows