ODBC: Commit Required after SELECT?

[Michael Mattias]

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:

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

[Fred Harris]

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.

[Davide Vecchi]

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.

[Fred Harris]

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.

[Michael Mattias]

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

[Chris Holbrook]

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.