ch11.htm

Learn SQL in an easy way...

		<TD ALIGN="LEFT">SC</TD>
		<TD ALIGN="LEFT">29652</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Rebecca Little</TD>
		<TD ALIGN="LEFT">7753 Woods Lane</TD>
		<TD ALIGN="LEFT">Houston</TD>
		<TD ALIGN="LEFT">TX</TD>
		<TD ALIGN="LEFT">38764</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Izetta Parsons</TD>
		<TD ALIGN="LEFT">1285 Pineapple Highway</TD>
		<TD ALIGN="LEFT">Greenville</TD>
		<TD ALIGN="LEFT">AL</TD>
		<TD ALIGN="LEFT">32854</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">John MacDowell</TD>
		<TD ALIGN="LEFT">2000 Lake Lunge Road</TD>
		<TD ALIGN="LEFT">Chicago</TD>
		<TD ALIGN="LEFT">IL</TD>
		<TD ALIGN="LEFT">42854</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
</TABLE>
</P>
<P>A Sybase SQL use of the <TT>COMMIT</TT> statement would look like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>begin transaction</B>
2&gt; <B>insert into CUSTOMERS values</B>
   <B>(&quot;John MacDowell&quot;, &quot;2000 Lake Lunge Road&quot;, &quot;Chicago&quot;, &quot;IL&quot;, 42854, 7)</B>
3&gt; <B>commit transaction</B>
4&gt; <B>go</B>
1&gt; <B>select * from CUSTOMERS</B>
2&gt; <B>go</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">The CUSTOMERS table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Name</B></TD>
		<TD ALIGN="LEFT"><B>Address</B></TD>
		<TD ALIGN="LEFT"><B>City</B></TD>
		<TD ALIGN="LEFT"><B>State</B></TD>
		<TD ALIGN="LEFT"><B>Zip</B></TD>
		<TD ALIGN="LEFT"><B>Customer_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Bill Turner</TD>
		<TD ALIGN="LEFT">725 N. Deal Parkway</TD>
		<TD ALIGN="LEFT">Washington</TD>
		<TD ALIGN="LEFT">DC</TD>
		<TD ALIGN="LEFT">20085</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">John Keith</TD>
		<TD ALIGN="LEFT">1220 Via De Luna Dr.</TD>
		<TD ALIGN="LEFT">Jacksonville</TD>
		<TD ALIGN="LEFT">FL</TD>
		<TD ALIGN="LEFT">33581</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Mary Rosenberg</TD>
		<TD ALIGN="LEFT">482 Wannamaker Avenue</TD>
		<TD ALIGN="LEFT">Williamsburg</TD>
		<TD ALIGN="LEFT">VA</TD>
		<TD ALIGN="LEFT">23478</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">David Blanken</TD>
		<TD ALIGN="LEFT">405 N. Davis Highway</TD>
		<TD ALIGN="LEFT">Greenville</TD>
		<TD ALIGN="LEFT">SC</TD>
		<TD ALIGN="LEFT">29652</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Rebecca Little</TD>
		<TD ALIGN="LEFT">7753 Woods Lane</TD>
		<TD ALIGN="LEFT">Houston</TD>
		<TD ALIGN="LEFT">TX</TD>
		<TD ALIGN="LEFT">38764</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Izetta Parsons</TD>
		<TD ALIGN="LEFT">1285 Pineapple Highway</TD>
		<TD ALIGN="LEFT">Greenville</TD>
		<TD ALIGN="LEFT">AL</TD>
		<TD ALIGN="LEFT">32854</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">John MacDowell</TD>
		<TD ALIGN="LEFT">2000 Lake Lunge Road</TD>
		<TD ALIGN="LEFT">Chicago</TD>
		<TD ALIGN="LEFT">IL</TD>
		<TD ALIGN="LEFT">42854</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
</TABLE>
</P>
<P>The preceding statements accomplish the same thing as they do using the Oracle7
syntax. However, by putting the <TT>COMMIT</TT> command soon after the transaction
begins, you ensure that the new transaction will execute correctly.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The <TT>COMMIT WORK</TT> command
	performs the same operation as the <TT>COMMIT</TT> command (or Sybase's <TT>COMMIT
	TRANSACTION</TT> command). It is provided simply to comply with ANSI SQL syntax.
	
<HR>


</BLOCKQUOTE>

<P>Remember that every <TT>COMMIT</TT> command must correspond with a previously
executed <TT>SET TRANSACTION</TT> or <TT>BEGIN TRANSACTION</TT> command. Note the
errors you receive with the following statements:</P>
<P>Oracle SQL:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO BALANCES values (18765.42, 19073.06, 8);</B>
SQL&gt; <B>COMMIT WORK;</B>
</FONT></PRE>
<P>Sybase SQL:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>insert into BALANCES values (18765.42, 19073.06, 8)</B>
2&gt; <B>commit work</B>
</FONT></PRE>
<H2><FONT COLOR="#000077">Canceling the Transaction</FONT></H2>
<P>While a transaction is in progress, some type of error checking is usually performed
to determine whether it is executing successfully. You can undo your transaction
even after successful completion by issuing the <TT>ROLLBACK </TT>statement, but
it must be issued before a <TT>COMMIT</TT>. The <TT>ROLLBACK</TT> statement must
be executed from within a transaction. The <TT>ROLLBACK</TT> statement rolls the
transaction back to its beginning; in other words, the state of the database is returned
to what it was at the transaction's beginning. The syntax for this command using
Oracle7 is the following:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">ROLLBACK [WORK]
[ TO [SAVEPOINT] savepoint
| FORCE 'text' ]
</FONT></PRE>
<P>As you can see, this command makes use of a transaction savepoint. We discuss
this technique later today.</P>
<P>Sybase Transact-SQL's <TT>ROLLBACK</TT> statement looks very similar to the <TT>COMMIT</TT>
command:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">rollback {transaction | tran | work}
  [transaction_name | savepoint_name]
</FONT></PRE>
<P>An Oracle SQL sequence of commands might look like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;</B>
SQL&gt;<B> INSERT INTO CUSTOMERS VALUES</B>
    <B> (&quot;Bubba MacDowell&quot;, &quot;2222 Blue Lake Way&quot;, &quot;Austin&quot;, &quot;TX&quot;, 39874, 8);</B>
SQL&gt; <B>ROLLBACK;</B>
SQL&gt;<B> SELECT * FROM CUSTOMERS;</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">The CUSTOMERS table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Name</B></TD>
		<TD ALIGN="LEFT"><B>Address</B></TD>
		<TD ALIGN="LEFT"><B>City</B></TD>
		<TD ALIGN="LEFT"><B>State</B></TD>
		<TD ALIGN="LEFT"><B>Zip</B></TD>
		<TD ALIGN="LEFT"><B>Customer_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Bill Turner</TD>
		<TD ALIGN="LEFT">725 N. Deal Parkway</TD>
		<TD ALIGN="LEFT">Washington</TD>
		<TD ALIGN="LEFT">DC</TD>
		<TD ALIGN="LEFT">20085</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">John Keith</TD>
		<TD ALIGN="LEFT">1220 Via De Luna Dr.</TD>
		<TD ALIGN="LEFT">Jacksonville</TD>
		<TD ALIGN="LEFT">FL</TD>
		<TD ALIGN="LEFT">33581</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Mary Rosenberg</TD>
		<TD ALIGN="LEFT">482 Wannamaker Avenue</TD>
		<TD ALIGN="LEFT">Williamsburg</TD>
		<TD ALIGN="LEFT">VA</TD>
		<TD ALIGN="LEFT">23478</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">David Blanken</TD>
		<TD ALIGN="LEFT">405 N. Davis Highway</TD>
		<TD ALIGN="LEFT">Greenville</TD>
		<TD ALIGN="LEFT">SC</TD>
		<TD ALIGN="LEFT">29652</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Rebecca Little</TD>
		<TD ALIGN="LEFT">7753 Woods Lane</TD>
		<TD ALIGN="LEFT">Houston</TD>
		<TD ALIGN="LEFT">TX</TD>
		<TD ALIGN="LEFT">38764</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Izetta Parsons</TD>
		<TD ALIGN="LEFT">1285 Pineapple Highway</TD>
		<TD ALIGN="LEFT">Greenville</TD>
		<TD ALIGN="LEFT">AL</TD>
		<TD ALIGN="LEFT">32854</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">John MacDowell</TD>
		<TD ALIGN="LEFT">2000 Lake Lunge Road</TD>
		<TD ALIGN="LEFT">Chicago</TD>
		<TD ALIGN="LEFT">IL</TD>
		<TD ALIGN="LEFT">42854</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
</TABLE>
</P>
<P>A Sybase SQL sequence of commands might look like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>begin transaction</B>
2&gt; <B>insert into CUSTOMERS values</B>
  <B> (&quot;Bubba MacDowell&quot;, &quot;2222 Blue Lake Way&quot;, &quot;Austin&quot;, &quot;TX&quot;, 39874, 8)</B>
3&gt; <B>rollback transaction</B>
4&gt; <B>go</B>
1&gt; <B>SELECT * FROM CUSTOMERS</B>
2&gt; <B>go</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">The CUSTOMERS table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Name</B></TD>
		<TD ALIGN="LEFT"><B>Address</B></TD>
		<TD ALIGN="LEFT"><B>City</B></TD>
		<TD ALIGN="LEFT"><B>State</B></TD>
		<TD ALIGN="LEFT"><B>Zip</B></TD>
		<TD ALIGN="LEFT"><B>Customer_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Bill Turner</TD>
		<TD ALIGN="LEFT">725 N. Deal Parkway</TD>
		<TD ALIGN="LEFT">Washington</TD>
		<TD ALIGN="LEFT">DC</TD>
		<TD ALIGN="LEFT">20085</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">John Keith</TD>
		<TD ALIGN="LEFT">1220 Via De Luna Dr.</TD>
		<TD ALIGN="LEFT">Jacksonville</TD>
		<TD ALIGN="LEFT">FL</TD>
		<TD ALIGN="LEFT">33581</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Mary Rosenberg</TD>
		<TD ALIGN="LEFT">482 Wannamaker Avenue</TD>
		<TD ALIGN="LEFT">Williamsburg</TD>
		<TD ALIGN="LEFT">VA</TD>
		<TD ALIGN="LEFT">23478</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">David Blanken</TD>
		<TD ALIGN="LEFT">405 N. Davis Highway</TD>
		<TD ALIGN="LEFT">Greenville</TD>
		<TD ALIGN="LEFT">SC</TD>
		<TD ALIGN="LEFT">29652</TD>
		<TD ALIGN="LEFT">4</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Rebecca Little</TD>
		<TD ALIGN="LEFT">7753 Woods Lane</TD>
		<TD ALIGN="LEFT">Houston</TD>
		<TD ALIGN="LEFT">TX</TD>
		<TD ALIGN="LEFT">38764</TD>
		<TD ALIGN="LEFT">5</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Izetta Parsons</TD>
		<TD ALIGN="LEFT">1285 Pineapple Highway</TD>
		<TD ALIGN="LEFT">Greenville</TD>
		<TD ALIGN="LEFT">AL</TD>
		<TD ALIGN="LEFT">32854</TD>
		<TD ALIGN="LEFT">6</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">John MacDowell</TD>
		<TD ALIGN="LEFT">2000 Lake Lunge Road</TD>
		<TD ALIGN="LEFT">Chicago</TD>
		<TD ALIGN="LEFT">IL</TD>
		<TD ALIGN="LEFT">42854</TD>
		<TD ALIGN="LEFT">7</TD>
	</TR>
</TABLE>
</P>
<P>As you can see, the new record was not added because the <TT>ROLLBACK</TT> statement
rolled the insert back.</P>
<P>Suppose you are writing an application for a graphical user interface, such as
Microsoft Windows. You have a dialog box that queries a database and allows the user
to change values. If the user chooses OK, the database saves the changes. If the
user chooses Cancel, the changes are canceled. Obviously, this situation gives you
an opportunity to use a transaction.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The following code listing uses
	Oracle SQL syntax; notice the SQL&gt; prompt and line numbers. The subsequent listing
	uses Sybase SQL syntax, which lacks the SQL&gt; prompt. 
<HR>


</BLOCKQUOTE>

<P>When the dialog box is loaded, these SQL statements are executed:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;</B>
SQL&gt; <B>SELECT CUSTOMERS.NAME, BALANCES.CURR_BAL, BALANCES.ACCOUNT_ID</B>
  2  <B>FROM CUSTOMERS, BALANCES</B>
  3  <B>WHERE CUSTOMERS.NAME = &quot;Rebecca Little&quot;</B>
  4 <B> AND CUSTOMERS.CUSTOMER_ID = BALANCES.ACCOUNT_ID;</B>
</FONT></PRE>
<P>The dialog box allows the user to change the current account balance, so you need
to store this value back to the database.</P>
<P>When the user selects OK, the update will run.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE BALANCES SET CURR_BAL = 'new-value' WHERE ACCOUNT_ID = 6;</B>
SQL&gt;<B> COMMIT;</B>
</FONT></PRE>
<P>When the user selects Cancel, the <TT>ROLLBACK </TT>statement is issued.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> ROLLBACK;</B>
</FONT></PRE>
<P>When the dialog box is loaded using Sybase SQL, these SQL statements are executed:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>begin transaction</B>
2&gt; <B>select CUSTOMERS.Name, BALANCES.Curr_Bal, BALANCES.Account_ID</B>
3&gt;<B> from CUSTOMERS, BALANCES</B>
4&gt; <B>where CUSTOMERS.Name = &quot;Rebecca Little&quot;</B>
5&gt;<B> and CUSTOMERS.Customer_ID = BALANCES.Account_ID</B>
6&gt; <B>go</B>
</FONT></PRE>
<P>The dialog box allows the user to change the current account balance, so you can
store this value back to the database.</P>
<P>Here again, when the OK button is selected, the update will run.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>update BALANCES set Curr_BAL = 'new-value' WHERE Account_ID = 6</B>
2&gt; <B>commit transaction</B>
3&gt; <B>go</B>
</FONT></PRE>
<P>When the user selects Cancel, the <TT>ROLLBACK </TT>statement is issued.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">1&gt; <B>rollback transaction</B>
2&gt; <B>go</B>
</FONT></PRE>
<P>The <TT>ROLLBACK</TT> statement cancels the entire transaction. When you are nesting
transactions, the <TT>ROLLBACK</TT> statement completely cancels all the transactions,
rolling them back to the beginning of the outermost transaction.</P>
<P>If no transaction is currently active, issuing the <TT>ROLLBACK </TT>statement
or the <TT>COMMIT</TT> command has no effect on the database system. (Think of them
as dead commands with no purpose.)</P>
<P>After the <TT>COMMIT</TT> statement has been executed, all actions with the transaction
are executed. At this point it is too late to roll back the transaction.
<H2><FONT COLOR="#000077">Using Transaction Savepoints</FONT></H2>
<P>Rolling back a transaction cancels the entire transaction. But suppose you want
to &quot;semicommit&quot; your transaction midway through its statements. Both Sybase
and Oracle SQL allow you to save the transaction with a savepoint. From that point
on, if a <TT>ROLLBACK</TT> is issued, the transaction is rolled back to the savepoint.
All statements that were executed up to the point of the savepoint are saved. The