DbTransaction executes a number of DbStmt Objects using a single a database Connection. If the database supports commit and rollback, a group of DbStmt tags can be joined together into an atomic transaction. If one statement throws an Exception the entire group can be rolled back.

For databases that don't support commit, rollback there still might be an advantage to putting all the stmts in one group. The connection is reserved and not returned to the pool till all the stmts are done. This could be a problem if stmts take a long time to execute.

Setting the autocommit attribute to false should only be done when your databases and JDBC driver supports transactions, commit and rollback. A verion of the JDBC driver that we used for MySQL threw and Exception when autocommit was false. This will probably be fixed in a future release and may be done by the time you read this.

Besides the dbcon attribute there is another one called dbkey. The value of dbkey is used to store the Connection for children of the DbTransaction. Each DbStmt tag used inside the DbTransaction parent will reference the dbkey to get the Connection that is shared for the transaction. When DtStmt is used outside a DbTransaction it must use the dbcon attribute to get a Connection.

Here is an example...

<dxp:DbTransaction dbcon="mydb" dbkey="sharedconn" autocommit="false">
  
   <dxp:DbStmt action="getPosValue" dbkey="sharedconn" store="ID">
      <dxp:SqlStmt>
         <dxp:Cp name="sql">select ID from users where firstname='?' and lastname='?'</dxp:SqlStmt>
         <dxp:SqlParam type="CHAR">firstname</dxp:SqlParam>
         <dxp:SqlParam type="CHAR">lastname</dxp:SqlParam>
      </dxp:SqlStmt>
   </dxp:DbStmt>

   <dxp:If test="ID gt 0">
      <dxp:DbStmt action="update" dbkey="sharedconn">
         <dxp:SqlStmt>
            <dxp:Cp name="sql">insert into consumer (beverage, userID) values (?',?)</dxp:Cp>
            <dxp:SqlParam type="CHAR">beverage</dxp:SqlParam>
            <dxp:SqlParam type="NUMBER">ID</dxp:SqlParam>
         </dxp:SqlStmt>
      </dxp:DbStmt>
      <dxp:DbStmt action="update" dbkey="sharedconn" store="count">
         <dxp:SqlStmt>
            <dxp:Cp name="sql">update cooler set count=count-1 where beverage='?'</dxp:Cp>
            <dxp:SqlParam type="CHAR">beverage</dxp:SqlParam>
         </dxp:SqlStmt>
      </dxp:DbStmt>
   </dxp:If> 

 </dxp:DbTransaction>
 

The action attribute specifies the type of stmt to execute. Currently support types are:

• query - Query result formatted as StringRecordSet for html output
• getValue - Query for col value formatted as String
• getPosValue - Query for pos int formatted as String. Returns neg if not found
• getFirstRow - Query for the first record formatted as StringRecordSet
• getMultValues - Query for the first record and store as numbered values store.n
• update - Update and return the the count as a String
• autoid - Insert into a table that has an auto_increment field, then select LAST_INSERT_ID() This is supported by MySQL but other DBs may not support it.
• queryObjects - Query result formatted as ObjectRecordSet to feed into other tags or methods
• getObject - Query for col Object

The dbcon attribute specifies the name of a DbPool to get a connection from. DbPools are managed by the DbManager which is stored in the ServletContext.

Return to Index

Note: We have tried to test all of the code examples but they may contain errors. Any questions, comments, suggestions, or problems should be sent to info@plands.com.