This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.

Function Prototype:

SQLRETURN SQLEndTran(
  SQLSMALLINT HandleType,         /* 16-bit input */
  SQLINTEGER Handle,              /* 32-bit input */
  SQLSMALLINT CompletionType      /* 16-bit input */
  );

Job:

End a transaction, either with COMMIT or with ROLLBACK. SQLEndTran is not a "statement" function, but we believe that it fits in this chapter because it's used to COMMIT or ROLLBACK the results of SQL statements that have been processed with SQLExecute or SQLExecDirect.

Algorithm:

If (HandleType is not one of: SQL_HANDLE_ENV, SQL_HANDLE_DBC, SQL_HANDLE_STMT)
  return error: CLI-specific condition-invalid handle
If (HandleType == SQL_HANDLE_STMT)
  If (Handle is not a hstmt)
    return error: HY092 CLI-specific condition-invalid attribute identifier
      The COMMIT/ROLLBACK will be done for the dbc associated with this stmt
If (HandleType == SQL_HANDLE_DBC)
  If (Handle is not a hdbc)
    return error: HY092 CLI-specific condition-invalid attribute identifier
  The dbc's diagnostics area is emptied.
      The COMMIT/ROLLBACK will be done for the specified dbc
If (HandleType == SQL_HANDLE_DESC)
  If (Handle is not a hdesc)
    return error: HY092 CLI-specific condition-invalid attribute identifier
      The COMMIT/ROLLBACK will be done for the dbc associated with this desc
If (HandleType == SQL_HANDLE_ENV)
  If (Handle is not a henv, or env is a skeleton env)
    return error: HY092 CLI-specific condition-invalid attribute identifier
      The COMMIT/ROLLBACK will be done for all dbcs associated with this env
If (CompletionType is not either COMMIT (0) or ROLLBACK (1))
  return error: HY012 CLI-specific condition-invalid transaction operation code
If (the current transaction is part of an encompassing transaction)
  If (transaction control is not controlled by this DBMS alone)
    return error: 2D000 Invalid transaction termination -
/* The rest of the algorithm might iterate several times if the
    Handle is a henv, and there are multiple active dbcs */
For (each stmt in the dbc)
  If (a deferred parameter number is associated with the stmt)
    return error: HY010 CLI-specific condition-function sequence error
For each stmt in the dbc:
  If (there is a non-holdable Cursor associated with the stmt)
    Close the Cursor and destroy its copy of the select source
    Remove any associated fetched row
If (CompletionType == SQL_COMMIT (0))
  Commit.
  /* The usual "commit" operations happen, as described in the
      Transactions chapter. For example: if a temporary Table was
      created with the ON COMMIT DELETE [ROWS] option, all its rows
      are deleted. Warning: checking of deferred Constraints might result
      in: 40002 Transaction rollback-integrity constraint violation */
  If (any other error prevents commitment now)
    /* For example, writing to disk causes a "disk full" error */
    return error: 40??? Transaction rollback-implementation-defined subclass value
If (CompletionType == SQL_ROLLBACK)
  Rollback.
  /* The usual "rollback" operations happen, as described in the Transactions chapter. */
The transaction is now terminated.

Notes about the algorithm:

  • See the SQLSTATE error codes HY010, HY092, and 40002 in Chapter 47 "SQL/CLI: Diagnostic Functions". Pay particular attention to 40002, which implies that ROLLBACK occurred when you asked for COMMIT.
  • Because COMMIT and ROLLBACK are non-preparable SQL statements, the correct way to end a transaction is to call SQLEndTran. Some DBMSs accept SQLExecDirect(hstmt,"COMMIT;",SQL_NTS); anyway, but it's not legal according to the SQL Standard and you can't be sure exactly what will happen. In the final version of SQL/CLI, there will be more options for rollback-to-savepoint and release-savepoint.
  • There is no point in passing a hstmt or a hdesc to SQLEndTran you just get an error message. These enhancements have been suggested and may be implemented somewhere: (a) if you pass a hstmt, COMMIT/ROLLBACK will occur for the dbc associated with the stmt; (b) if you pass a hdesc, COMMIT/ROLLBACK will occur for the dbc associated with the desc.
  • When there is only one env and one dbc, the convention is to use the henv for this function:
SQLEndTran(SQL_HANDLE_ENV,henv,SQL_COMMIT)
  • Have a look at our chapter on transactions before you try to use SQLEndTran.

Example:

#include "sqlcli.h"
SQLHDBC hdbc;
SQLHSTMT hstmt;
...
SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
/* Do an SQL statement using a stmt associated with dbc */
SQLExecDirect(hstmt,"INSERT INTO t1 SELECT * FROM t2",SQL_NTS);
/* Commit the statement results using the dbc */
SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);

ODBC: The SQLEndTran function was added in ODBC 3.0, but there was a nearly-equivalent function in ODBC 2.0 (SQLTransact). With ODBC, if the HandleType value is not SQL_HANDLE_ENV or SQL_HANDLE_DBC, the return is HY092 (invalid attribute/option identifier), rather than "invalid handle".

With ODBC, the default behaviour is "autocommit" (i.e.: perform an automatic "commit" after every SQL statement), so SQLEndTran has nothing to do. This is a major difference between ODBC and Standard SQL. The suggested way to resolve it is to call an ODBC function which turns the "autocommit" flag off:

SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF);

And that's it for the statement functions. In the next chapter, we'll take a look at the Cursor functions.

Comments

Comments loading...