TIP #350: TCL DATABASE CONNECTIVITY - CORRIGENDA ================================================== Version: $Revision: 1.1 $ Author: Kevin B. Kenny State: Draft Type: Informative Vote: Pending Created: Saturday, 18 April 2009 URL: https://tip.tcl-lang.org350.html 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