Announcement

Collapse
No announcement yet.

ODBC: Commit Required after SELECT?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ODBC: Commit Required after SELECT?

    Here's one I can't find any documentation for anywhere...can someone please help?

    If I connect to a database via ODBC, then change the commit mode from AUTO_COMMIT (the default for ODBC connections) to manual commit mode; I know "SqlEndTran handleType, handle, SQL_COMMIT | SQL ROLLBACK" is required to "complete" an INSERT or UPDATE operation.

    But what about "completing " SELECT statment?

    I have seen code that COMMITs after a SELECT and other code which doesn't.

    Seems to me COMMIT or ROLLBACK should be "moot" if there is nothing to commit or roll back (i.e, only SELECTs executed on the handle since last commit or rollback), but I just cannot find any definitive and/or authoritative documentation on this.

    FWIW, I am settin the commit mode on the Connection handle (not the Environment handle); and am currently debating with myself if for this application I should hold the connection open whilst I periodically monitor a column value. I.e., the application - essentially a "batch" process, although I do have a "quit waiting" button available - does this:
    Code:
    DO 
       SELECT column from table where condition
       ***COMMIT OR ROLLBACK HERE? ***
       IF column = target value
         EXIT DO
       ELSE
          Wait five (5) or ten (10) seconds
         ' either value perfectly acceptable for this application
       END IF
    LOOP
    ... more cool stuff goes here
    My other option is to just connect in default (autocommit) mode, which is what I will probably end up doing. (This application also does INSERTs and UPDATEs, so I will need to use Manual Commit mode in those parts of the program).

    Any comments?

    Thanks,
    MCM
    Michael Mattias
    Tal Systems Inc. (retired)
    Racine WI USA
    [email protected]
    http://www.talsystems.com

  • #2
    I've an old ODBC textbook (Seligman or something like that) at home (I'm at work now) Michael. I'll check tonight or tomorrow to see if there's anything in there about that.
    Fred
    "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

    Comment


    • #3
      Originally posted by Michael Mattias View Post
      I know "SqlEndTran handleType, handle, SQL_COMMIT | SQL ROLLBACK" is required to "complete" an INSERT or UPDATE operation.

      But what about "completing " SELECT statment?
      I think a commit or rollback is required to complete a transaction, which can be one or more SQL statements. If autocommit is off and you do a SELECT without explicitly starting a transaction before, I guess the SELECT implicitly starts a new transaction, that must then be closed regardless of whether some data was written or not.

      Comment


      • #4
        Good Ole ODBC!

        Did find this in "The ODBC Solution" >> subtitle >> "Open Database Connectivity In Distributed Environment" by Robert Signore, John Creamer, and Michael O. Stegman...

        "In manual-commit mode, a driver automatically starts a transaction when an SQL statement is submitted if one has not been started already. This transaction will not be committed until the application explicitely specifies this action. This is the mode that an application should use for all transaction processing because the driver is not managing transactions itself. The driver does not commit SQL statements itself, so a transaction is active when the application has control. Calling a commit or rollback then will act on this transaction."

        That was from a fairly short chapter in the book on transaction processing. If you would like I can mail you copies of the few pages involved. Its a bit much to type in here.
        Fred
        "fharris"+Chr$(64)+"evenlink"+Chr$(46)+"com"

        Comment


        • #5
          Doesn't directly answer my question, but I think it's fair to infer from the above that NOT committing a SELECT statement can/might cheese up any open cursors and/or result sets from a prior SELECT on that statement handle.

          I have not run into problems, perhaps because I always close my cursor and free the statement handle (which discards any pending result set) as soon as I have my SELECT results.

          Well, since COMMITing a SELECT returns SQL_SUCCESS, I think I'll just 'do it' and not tempt the fates.

          Thank you for the cite.

          MCM
          Michael Mattias
          Tal Systems Inc. (retired)
          Racine WI USA
          [email protected]
          http://www.talsystems.com

          Comment


          • #6
            From first principles, I would expect that the ODBC driver itself would not get involved in trying to track transaction processing. As you are not making changes to the user tables there is no need to commit the implied transaction. The effect of COMMIT is to write those changes to the database itself, rather than your session's view of it. The database should be able to take care of empty or uncommitted transactions.

            But then, I'm not an ODBC expert.

            Comment

            Working...
            X