ch16.htm

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

<HR>


</BLOCKQUOTE>

<P>If you find yourself in a situation where you can't use a trigger, there is no
great need for 
alarm. The absence of trigger support is not a big concern under most
circumstances. Instead of using a trigger, you can use a stored procedure to retrieve
the next number from a generator. In my opinion, it is simpler and easier to use
a stored 
procedure rather than a trigger if you want to fill in the primary key of
a table.
<H3><FONT COLOR="#000077">Working with Stored Procedures</FONT></H3>
<P>In this section, you will see a discussion of the stored procedures used by the
Music program to 
enforce or support referential integrity. These simple stored procedures
use a generator to fill in the value of a primary key. Near the end of the chapter,
I discuss a series of more complicated stored procedures used to query the data in
the 
database.</P>
<P>A stored procedure is simply a routine that is stored on the server side rather
than listed in your Object Pascal source code. Like the language for writing triggers,
there is a unique language for writing stored procedures that has 
nothing to do with
Object Pascal or SQL. In fact, you need to keep in mind that there is no particular
relationship between BCB and InterBase. They are made by two different teams, using
two different languages, with two different goals in mind. The 
stored procedure language
was made up long before anyone thought of creating BCB, and in general, the two languages
have absolutely nothing to do with one another.</P>
<P>One key difference between BCB code and InterBase code is that the language of

stored procedures is completely platform independent. If you want to move your database
back and forth between Windows and UNIX, then you might find it helpful to create
many stored procedures that handle the majority of work for your databases. Then

you can write very thin clients that simply ask the stored procedures to do all the
work.</P>
<P>Stored procedures are not difficult to create. Here, for instance, is a stored
procedure that returns the next number generated by the <TT>Music_Gen</TT> 
generator:</P>
<PRE><FONT COLOR="#0066FF">CREATE PROCEDURE GETALBUMGEN

RETURNS (NUM INTEGER)

AS

BEGIN  NUM = GEN_ID(ALBUM_GEN, 1);

END

</FONT></PRE>
<P>The first line tells WISQL that you are going to create a procedure called 
<TT>GetMusicGen</TT>.
The next line states that it is going to return a value called <TT>Num</TT>, which
is an integer. The <TT>AS</TT> statement tells InterBase that you are now ready to
define the body of the procedure. The procedure itself appears 
between a <TT>BEGIN..END</TT>
pair and consists of a call to the <TT>GEN_ID</TT> function, which returns the next
number from the <TT>MUSIC_GEN</TT> generator. When it retrieves the number, it asks
InterBase to increment its value by one.



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE: </B></FONT>Stored procedures are handled on the BCB
	end with either a <TT>TStoredProc</TT> component or by returning an answer set by
	way of a SQL statement. In general, if the stored procedure 
returns several rows
	of data, you access it by way of a SQL statement in a <TT>TQuery</TT> component.
	The SQL statement to use in such a case is <TT>Select * from GetAlbumGen</TT>, where
	<TT>GetAlbumGen</TT> is the name of the procedure that 
returns one or more rows of
	data. If the stored procedure returns only a single item of data, you can call it
	with a <TT>TStoredProc</TT> component. Examples of both methods for calling stored
	procedures from BCB appear in the next section, in the 
form of excerpts from the
	Music program. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">Stored Procedures from BCB's End</FONT></H2>
<P>The Album and Book tables of the Music program use stored procedures to fill in
their primary index. Because 
both procedures are identical, I describe only the one
on the Album page.</P>
<P>To get started, you need to be sure the stored procedure is set up on the server
side. Here is the code you should enter into WISQL to create the procedure:</P>

<PRE><FONT COLOR="#0066FF">CREATE PROCEDURE GETALBUMGEN RETURNS (NUM INTEGER)

AS

BEGIN

  NUM = GEN_ID(ALBUM_GEN, 1);

END

</FONT></PRE>
<P>As you can see, this is a simple stored procedure that does nothing more than
return a single value.



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE: </B></FONT>If you are having trouble setting up the
	procedure, the entire data definition for the database is supplied later in this
	chapter, or you can just use the version of the database that 
is found on disk. You
	can add the code shown in this section to your current version of the music program.
	Or, if you want, you can just start a new project, create a data module, drop a <TT>TDatabase</TT>
	object on it, and connect it to the Music 
database that ships on the CD that accompanies
	this book. Setting up the alias for the database is described in depth in the readme
	file from the CD. Once you have the <TT>TDatabase</TT> object connected to <TT>Music.gdb</TT>,
	you can drop down the 
<TT>TStoredProc</TT> and start working with it, as described
	in the rest of this section. 
<HR>


</BLOCKQUOTE>

<P>To get started using a <TT>TStoredProc</TT>, drop it onto the Album page or onto
a data module. Set the <TT>StoredProcName</TT> alias 
to the <TT>GetAlbumGen</TT>
stored procedure.</P>
<P>After selecting the procedure to use with the <TT>TStoredProc</TT>, you can pop
up the <TT>Params</TT> field to see the parameters passed to or returned by the function.
In this case, only one 
parameter returned as the result of the function.</P>
<P>Whenever the user wants to insert a record into the Album table, the following
procedure is called:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TAlbumForm::sbInsertClick(TObject *Sender)

{

  
AnsiString S;

  if (!InputQuery(&quot;Insert New Album Dialog&quot;, &quot;Enter album name&quot;, S))

    return;

  DMod-&gt;AlbumTable-&gt;Insert();

  DMod-&gt;AlbumTable-&gt;FieldByName(&quot;Album&quot;)-&gt;AsString = S;

  
DMod-&gt;AlbumTable-&gt;FieldByName(&quot;Types&quot;)-&gt;AsString = &quot;&quot;;

  DMod-&gt;AlbumTable-&gt;FieldByName(&quot;Loudness&quot;)-&gt;AsString = &quot;&quot;;

  DMod-&gt;AlbumTable-&gt;FieldByName(&quot;Medium&quot;)-&gt;AsString = 
&quot;&quot;;

  DMod-&gt;AlbumTable-&gt;Post();

  TypeCombo-&gt;SetFocus();

}

</FONT></PRE>
<P>As you can see, there is no reference to a stored procedure in these lines of
code. Instead, I reference the stored procedure in the <TT>BeforePost</TT> 
event
for the <TT>AlbumTable</TT>:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TDMod::AlbumTableBeforePost(TDataSet *DataSet)

{

  if (AlbumSource-&gt;State == dsInsert)

  {

    GetAlbumGen-&gt;Prepare();

    GetAlbumGen-&gt;ExecProc();

    
AlbumTableCODE-&gt;AsInteger = GetAlbumGen-&gt;ParamByName(&quot;Num&quot;)-&gt;AsInteger;

  }

}

</FONT></PRE>
<P>The key lines of this procedure are the ones involving the stored procedure:</P>
<PRE><FONT COLOR="#0066FF">GetAlbumGen-&gt;Prepare();


GetAlbumGen-&gt;ExecProc();

AlbumTableCODE-&gt;AsInteger = GetAlbumGen-&gt;ParamByName(&quot;Num&quot;)-&gt;AsInteger;

</FONT></PRE>
<P>This code first executes the stored procedure and then snags its return value
from the <TT>Params</TT> field of 
the <TT>TStoredProc</TT>. The <TT>Params</TT> field
for stored procedures works the same way as the <TT>Params</TT> field for <TT>TQuery</TT>
objects.</P>
<P>As you can see, BCB makes it easy for you to use stored procedures in your programs.
In this 
particular case, you could have used a stored procedure rather than a trigger.
However, it is not a great crisis if you have to use a stored procedure rather than
a trigger. The one advantage triggers have over stored procedures is that they are

called automatically, thereby helping you to ensure data integrity.</P>
<P>Near the end of this chapter, I return to the subject of stored procedures when
I discuss techniques for querying the data in a database in the section &quot;Asking
the 
Database a Question.&quot;
<H3><FONT COLOR="#000077">Deciding When to Use Triggers</FONT></H3>
<P>When working with triggers, you should keep the following ideas in mind:

<UL>
	<LI>BCB has no support for InterBase events. As a result, it does not 
know when a
	trigger occurs. This means you should never use a trigger to alter a field that your
	program is indexed on. It's okay to use a trigger to alter a field that is indexed;
	just don't let it fire while your program is actively using that 
index.
	<P>
	<LI>C++Builder's lack of support for InterBase events usually is not a problem. Trouble
	only occurs if you index on the field that is updated by a trigger or if you rely
	on a grid to perform certain operations. If you index on a field 
that is updated
	by a trigger, the record disappears out from under the user. It doesn't matter if
	some index you are not currently using is updated; it must be a live index currently
	in use by your program to cause this kind of trouble. For 
instance, if there is a
	<TT>CODE</TT> or <TT>CUSTNO</TT> field on a table, and you currently index on that
	field, you will have trouble after an insert because the record seems to disappear
	beneath you. In other words, Builder won't know about the 
new <TT>CUSTNO</TT> or
	<TT>CODE</TT> number and will not be able to find the record after the insert.
	<P>
	<LI>The best solution to the problem outlined previously is to never have the current
	index be the same field that is updated by a trigger. 
If you use this solution, be
	sure not to show the user the field that is updated by the trigger because it might
	not be updated automatically by Builder when displayed in a visual control. In other
	words, Builder doesn't know the trigger was fired, 
so it doesn't know to update a
	<TT>TDBGrid</TT> or <TT>TDBEdit</TT> control. However, it won't matter if the data
	is out of date, as long as you do not index on it and the user can't see the field
	in your grid.
	<P>
	<LI>If you must index on a 
field that is updated, then the solution is to delete
	the trigger and insert a stored procedure instead. The stored procedure returns the
	new number created by a generator. You can then call this stored procedure inside
	a <TT>BeforePost</TT> event 
and use the number returned to fill in the <TT>CUSTNO</TT>
	or <TT>CODE</TT> field. You should do all this inside a <TT>TDataModule</TT> and
	then insist that all members of your team use that <TT>TDataModule</TT> whenever
	they access the table in 
question. This is the solution used throughout the Music
	program and the one that I prefer to use in most cases.
	<P>
	<LI>A third alternative is to use triggers in combination with the <TT>IBEventAlerter</TT>
	component from the samples page in the 
<TT>Examples</TT> directory that ships with
	BCB. There is no description of the <TT>IBEventAlerter</TT> in this book, and you
	will have to explicitly add the Samples page to the Component Palette before you
	can use it. A description of how to add 
it to the IDE is found in the <TT>Examples</TT>
	directory set up by BCB during the default installation.
</UL>

<P>Following is the code for generating both the trigger and the stored procedure
described previously. You do not ever want to include 
both the trigger and the stored
procedure in the same database. You have to choose between the two techniques:</P>
<PRE><FONT COLOR="#0066FF">CREATE GENERATOR IMAGECODE;

CREATE PROCEDURE GETIMAGECODE RETURNS (NUM INTEGER)

AS

BEGIN  NUM = 
GEN_ID(IMAGECODE, 1);END

CREATE TRIGGER GENERATE_IMAGECODE FOR IMAGES

ACTIVE BEFORE INSERT POSITION 0

AS BEGIN  New.Code = Gen_ID(ImageCode, 1);END

</FONT></PRE>
<P>When checking for referential integrity, your program may raise an exception 
because
you failed to fill in a field specified as <TT>NOT NULL</TT> before the <TT>BeforePost</TT>
event is fired. In other words, you might need to fill in the field even before the
code reaches the <TT>BeforePost</TT> event. One way to solve this 
problem is to simply
put in a dummy number at the time the user first decides to do an <TT>Insert</TT>:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TDMod::AlbumTableAfterInsert(TDataSet *DataSet)

{

  AlbumTableCODE-&gt;AsInteger = 0; // Temp 
value, real value in BeforePost

}

</FONT></PRE>
<P>The value <TT>0</TT> shown in this code serves as a placeholder. It is replaced
during the <TT>BeforePost</TT> event or during the execution of a trigger. All the