People have asked for many things after the release of func_odbc. Func_odbc is a module that permits you to create fill-in-the-blank SQL templates, which become dialplan functions. One of the requests was for automatically using the column names to populate variables, which became the HASH() function. Another request was for multi-row access, and that is now integrated within func_odbc as the parameter "mode=multirow".
The latest request has been for support for transactions within func_odbc. Transactions are a way to ensure that when a group of SQL statements are run, their completion essentially happens at the same time. So the database, when queried from other places, either sees the data as it was before the entire group of statements was run or after, never any step between those two states. This is important for things like accounting, where you'd like a value to be decremented in one table and incremented in another, to keep the system balanced.
The odbc_tx_support branch will permit you to do just this, in the dialplan. There are several new functions and applications, to keep the concept clear.
First, to start a transaction, you'll need to use the new ODBC() dialplan function to specify on which connection you want to create a transaction. All of the statements on a transaction must occur on exactly the same database handle, or else transactions will not work. So database handle failover will not work, for starters. The method of starting a transaction is simply to call: Set(ODBC(transaction,)=). The purpose of naming transactions is to allow multiple transactions (either on different databases or on the same database) to occur at the same time. It should be clear that for each transaction active at the same time, a separate database connection is necessary. Therefore, even if you are using non-pooled connections, a transaction necessitates a completely separate database handle.
When you execute the start of a transaction, three things will happen. First, a separate database handle is reserved to the channel, associated with the transaction name. Second, autocommit mode is turned off for the duration. Normally, statements are committed as they are executed; clearly, this behavior has to be changed so that the set of statements all occur at the end, instead of individually at execution time. And lastly, your transaction will be marked as the currently active transaction. This is important only if you have multiple transactions open at the same time.
At this point, you are free to execute whatever statements you like on the transaction. Note that if you start two different transactions on the same database handle, you may switch back and forth as to which transaction is used, by using the ODBC() dialplan function to specify the current active transaction: Set(ODBC(transaction)=).
Another property that you may wish to alter, also with the ODBC() dialplan function, is the isolation property. The isolation property is a special setting that doesn't affect how your transaction is run, but will affect how others see the data in your uncommitted transaction. Normally, others will not see any data within your transaction at all, until you commit that transaction. This default isolation is known as read_committed. However, they may opt to see that data by setting their isolation to read_uncommitted. Similarly, you may see others' uncommitted transaction data by setting your own isolation level.
The last property that may be altered is the 'forcecommit' option. Normally, if your channel dies before you have a chance to commit your transaction, either because of a hangup or for some other error, the transaction will automatically be rolled back. If you'd prefer that the transaction be committed anyway, you can Set(ODBC(forcecommit[,])=1). Note that if you don't specify a transaction name, the property will be set on whatever the currently active transaction is.
Once you have completed your set of statements and you wish to commit your data, you should use the ODBC_Commit with the transaction name as an argument. The variable COMMIT_RESULT will contain the result from your commit, which should be "OK". If it's not "OK", then it will be a comma-delimited list of the error states that your commit generated. In most cases, you should probably do an ODBC_Rollback in response to an error code generated by ODBC_Commit.
Hopefully, this has provided a good introduction to using the new ODBC Transaction functionality in Asterisk. As of this writing, this functionality is still in a developer branch, but it will be soon merged into trunk and may be a part of Asterisk as soon as version 1.6.2. Until that time, you may check out the code and try it for yourself at http://svn.digium.com/svn/asterisk/team/tilghman/odbc_tx_support/. Testing is certainly appreciated. I suspect this addition will create as much excitement as the initial introduction of both func_odbc and cdr_adaptive_odbc in the past.