ch15.htm

好书《C++ Builder高级编程技术》

database work you will do with BCB. Using transactions is, however, a technique most
frequently associated with client/server databases.</P>
<P>To begin, drop down a 
<TT>TDatabase</TT> component on a <TT>TDataModule</TT>.
Set the <TT>AliasName</TT> property of the <TT>TDataBase</TT> object to a valid alias
such as <TT>IBLOCAL</TT>. Create your own string, such as <TT>TransactionDemo</TT>,
to fill in the 
<TT>DatabaseName</TT> property of the <TT>TDatabase</TT> object. In
other words, when you're using a <TT>TDatabase</TT> component, you make up the <TT>DatabaseName</TT>
rather than pick it from a list of available aliases.</P>
<P>Drop down a 
<TT>TQuery</TT> object, and hook it up to the <TT>EMPLOYEE.GDB</TT>
file that ships with BCB. In particular, set the <TT>DatabaseName</TT> property of
the <TT>TQuery</TT> object to <TT>TransactionDemo</TT>, not to <TT>IBLOCAL</TT>.
In other words, set 
the <TT>DatabaseName</TT> property to the string you made up
when filling in the <TT>DatabaseName</TT> property of the <TT>TDatabase</TT> component.
You will find that <TT>TransactionDemo</TT>, or whatever string you chose, has been
added to the list 
of aliases you can view from the <TT>Query1.DatabaseName</TT> Property
Editor. Now rename <TT>Query1</TT> to <TT>EmployeeQuery</TT> and attach a <TT>TDataSource</TT>
object called <TT>EmployeeSource</TT> to it.</P>
<P>Finally, set the 
<TT>EmployeeQuery-&gt;SQL</TT> property to the following string:</P>
<PRE><FONT COLOR="#0066FF">select * from employee

</FONT></PRE>
<P>Then set the <TT>Active</TT> property to <TT>True</TT> and set <TT>RequestLive</TT>
to <TT>True</TT>.</P>
<P>Add a 
<TT>TTable</TT> object to the project, hook it up to the <TT>SALARY_HISTORY</TT>
table, and call it <TT>SalaryHistoryTable</TT>. Relate the <TT>SalaryHistoryTable</TT>
to the <TT>EmployeeQueryTable</TT> via the <TT>EMP_NO</TT> fields of both tables.

In particular, you should set the <TT>MasterSource</TT> property for the <TT>SalaryHistoryTable</TT>
to <TT>EmployeeSource</TT>. Then click the <TT>MasterFields</TT> property of the
<TT>TTable</TT> object, and relate the <TT>EMP_NO</TT> fields of both 
tables. This
way, you can establish a one-to-many relationship between the <TT>EmployeeQueryTable</TT>
and the <TT>SalaryHistoryTable</TT>.</P>
<P>After you're connected to the database, you can add two grids to your main form
so that you can view the 
data. Remember that you should use the File | Include Unit
Header option to link the <TT>TDataModule</TT> to the main form.</P>
<P>On the surface of the main form, add four buttons, and give them the following
captions:</P>
<PRE><FONT 
COLOR="#0066FF">Start Transaction

Rollback

Commit

Refresh

</FONT></PRE>
<P>The code associated with these buttons should look like this:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TForm1::StartTransactionBtnClick(TObject *Sender)

{

  
DMod-&gt;TransDemo-&gt;StartTransaction();

}

void __fastcall TForm1::RollbackBtnClick(TObject *Sender)

{

  DMod-&gt;TransDemo-&gt;Rollback();

}

void __fastcall TForm1::CommitBtnClick(TObject *Sender)

{

  DMod-&gt;TransDemo-&gt;Commit();

}


void __fastcall TForm1::RefreshBtnClick(TObject *Sender)

{

  DMod-&gt;SalaryHistoryTable-&gt;Refresh();

}

</FONT></PRE>
<P>Run the program, click Start Transaction and edit a record of the <TT>SalaryHistoryTable</TT>.
When you do so, be sure to 
fill in all the fields of the table except for the first
and last, which are called <TT>EMP_NO</TT> and <TT>NEW_SALARY</TT>. Be sure not to
touch either of those fields, as they will be filled in for you automatically. In
particular, you might enter 
the following values:</P>
<PRE><FONT COLOR="#0066FF">CHANGE_DATE: 12/12/12

UPDATER_ID: admin2

OLD_SALARY: 105900

PERCENT_CHANGE: 3

</FONT></PRE>
<P>These values are not randomly chosen. For example, you have to enter <TT>admin2</TT>,
or some other 
valid <TT>UPDATE_ID</TT>, in the <TT>UPDATER_ID</TT> field. You can,
of course, enter whatever values you want for the date, old salary, and percent change
fields. Still, you need to be careful when working with the Employee tables. This
database has 
referential integrity with a vengeance!</P>
<P>After entering the preceding values, you can post the record by moving off it.
When you do, the <TT>NEW_SALARY</TT> field will be filled in automatically by something
called a trigger. Go ahead and 
experiment with these tables some if you want. For
example, you might leave some of the fields blank, or enter invalid data in the <TT>UPDATER_ID</TT>
field, just to see how complex the rules that govern this database are. This data
is locked up 
tighter than Fort Knox, and you can't change it unless you are very
careful about what you're doing. (It's worth noting, however, that the developers
of this database probably never planned to have anyone use these two tables exactly
as I do here. 
Defining rules that limit how you work with a database is easy, but
finding ways to break them is easier still. For all of its rigor, database programming
is still not an exact science.)</P>
<P>If you started your session by clicking the Start 
Transaction button, you can
now click RollBack and then Refresh. You will find that all your work is undone,
as if none of the editing occurred. If you edit three or four records and then click
Commit, you will find that your work is preserved.

<DL>
	
<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Though you are safe in this particular
	case, in some instances like this you can't call <TT>Refresh</TT> directly because
	the table you're using is not 
uniquely indexed. In lieu of this call, you can close
	the table and then reopen it. You could use bookmarks to preserve your location in
	the table during this operation, or if you're working with a relatively small dataset,
	as in this example, you 
can just let the user fend for himself or herself. 
<HR>


</BLOCKQUOTE>

<P>Note that when you run the TRANSACT program included on the CD, you don't have
to specify a password because the <TT>LoginPrompt</TT> property of the <TT>TDatabase</TT>

object is set to <TT>False</TT>, and the <TT>Params</TT> property contains the following
string:</P>
<PRE><FONT COLOR="#0066FF">password=masterkey

</FONT></PRE>
<P>Now that you have seen transactions in action, you probably want a brief explanation

of what they are all about. Here are some reasons for using transactions:

<DL>
	<DD><B>1.</B> To ensure the integrity of your data. Sometimes you need to perform
	a transaction that effects several different interrelated tables. In these cases,
	it 
might not be a good idea to alter two tables and then find the session is interrupted
	for some reason before you can alter the next two tables. For example, you might
	find that a data entry clerk posts data to two records, but the system crashes 
before
	he can finish updating two more records in a different table. As a result, the data
	in your database may be out of sync. To avoid this situation, you can start a transaction,
	edit all the rows and tables that need to be edited, and then 
commit the work in
	one swift movement. This way, an error is far less likely to occur because of a system
	crash or power failure.<BR>
	<BR>
	<B>2. </B>To handle concurrency issues in which two or more people are accessing
	the same data at the same 
time. You can use a transactions feature called <TT>TransIsolation</TT>
	levels to fine-tune exactly how and when updates are made. This way, you can decide
	how you will react if another user is updating records exactly on or near the record
	you're 
currently editing.
</DL>

<P>Now that you have read something about the theory behind transactions, you might
want to think for a moment about the <TT>TransIsolation</TT> property of the <TT>TDatabase</TT>
object, which affects the way transactions 
are handled. Here are some quotes from
the very important online help entry called &quot;Transaction Isolation Levels.&quot;
<BR>
<BR>

<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>tiDirtyRead</TT></TD>
		<TD 
ALIGN="LEFT">Permits reading of uncommitted changes made to the database by other simultaneous
			transactions. Uncommitted changes are not permanent, and might be rolled back (undone)
			at any time. At this level a transaction is least isolated from 
the effects of other
			transactions.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>tiReadCommitted</TT></TD>
		<TD ALIGN="LEFT">Permits reading of committed (permanent) changes made to the database by other 
simultaneous
			transactions. This is the default <TT>TransIsolation</TT> property value.</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT" VALIGN="TOP"><TT>tiRepeatableRead</TT></TD>
		<TD ALIGN="LEFT">Permits a single, one-time reading 
of the database. The transaction cannot see any
			subsequent changes made by other simultaneous transactions. This isolation level
			guarantees that after a transaction reads a record, its view of that record does
			not change unless it makes a 
modification to the record itself. At this level, a
			transaction is most isolated from other transactions.</TD>
	</TR>
</TABLE>
<BR>
<BR>
Most of the time, you can simply leave this field set to <TT>tiReadCommitted</TT>.
However, it is important to 
understand that you have several options regarding how
the data in your database is affected by a transaction. The whole subject of how
one user of a database might alter records in a table while they are being used by
another user is quite 
complicated, and it poses several paradoxes for which no simple
solution exists. The preceding <TT>TransIsolation</TT> levels allow you to choose
your poison when dealing with this nasty subject.</P>
<P>You must consider other issues when you're 
working with transactions, but I have
tried to cover some of the most important here. In general, I find that transactions
are extremely easy to use. However, they become more complex when you consider the
delicate subject of concurrency problems, 
which are frequently addressed through
setting the <TT>TransIsolation</TT> levels of your transactions.
<H3><A NAME="Heading21"></A><FONT COLOR="#000077">Cached Updates</FONT></H3>
<P>Cached updates are like the transactions just described, except 
that they enable
you to edit a series of records without causing any network traffic. When you are
ready to commit your work, cached updates enable you to do so on a record-by-record
basis, where any records that violate system integrity can be 
repaired or rolled
back on a case-by-case basis.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some users have reported remarkable
	increases in performance on some operations when they use cached 
updates. 
<HR>


</BLOCKQUOTE>

<P>The key feature of cached updates is that they let you work with data without
allowing any network traffic to occur until you are ready for it to begin. This relatively
complex mechanism also enables you to keep 
track of the status of each record on
a field-by-field basis. In particular, when cached updates are turned on, you can
query your records one at a time and ask them whether they have been updated. Furthermore,
if they have been updated, you can ask 
the current value of each field in the updated
record, and you can also retrieve the old, or original, value of the field.</P>
<P>You can do three things with the records in a dataset after the <TT>CachedUpdates</TT>
property for the dataset has been 
set to <TT>True</TT>:

<DL>
	<DD><B>1. </B>You can call <TT>ApplyUpdates</TT> on the dataset, which means that
	you will try to commit all the other records updated since <TT>CachedUpdates</TT>
	was set to <TT>True</TT> or since the last attempt to 
update the records. This is
	analogous to committing a transaction.<BR>
	<BR>
	<B>2.</B> You can call <TT>CancelUpdates</TT>, which means that all the updates made
	so far will be canceled. This is analogous to rolling back a transaction.<BR>
	<BR>
	
<B>3. </B>You can call <TT>RevertRecord</TT>, which will roll back the current record,
	but not any of the other records in the dataset.
</DL>

<P>An excellent sample program in the BCB <TT>DEMOS</TT> subdirectory shows how to
use cached updates. This 
program is a bit complex in its particulars, however, and
therefore can be hard to understand. So, instead of trying to go it one better, I
will create a sample program that takes the basic elements of cached updates and
presents them in the simplest 
possible terms.</P>
<P>The <TT>CacheUp</TT> program, shown in Figure 15.5, has one form. On the form
is a copy of the <TT>OrdersTable</TT>. The <TT>OrdersTable</TT>, as you recall, is
related to both the <TT>Customer</TT> table and the <TT>Items</TT> 
table. As a result,
changing either the <TT>OrderNo</TT> or <TT>CustNo</TT> fields without violating
system integrity in one way or another is difficult. When working with this program,
you should change these fields to values like <TT>1</TT> or 
<TT>2</TT>, which will
almost surely be invalid. You can then watch what happens when you try to commit
the records you have changed.</P>
<P>The code for the CachedUpdates program is shown in Listing 15.1. Go ahead and
get this program up and running, 
and then come back for a discussion of how it works.
When you're implementing the code shown here, the key point to remember is that none
of it will work unless the <TT>CachedUpdates</TT> property of the <TT>OrdersTable</TT>
is set to 
<TT>True</TT>.<BR>
<BR>
<A NAME="Heading23"></A><FONT COLOR="#000077"><B>Listing 15.1. The form for the CachedUpdates
program.</B></FONT></P>
<PRE><FONT COLOR="#0066FF">///////////////////////////////////////

// File: Main.cpp

// Project: 
CachedUpdates

// Copyright (c) 1997 Charlie Calvert

#include &lt;vcl\vcl.h&gt;