Docstoc

Delphi Database Developers' Guide The Interesting Parts

Document Sample
Delphi Database Developers' Guide The Interesting Parts Powered By Docstoc
					  Included in ahis Chapter:
  w The Dataset Objects n The Table Component (TTable)
  n The Query Component (TQuery) n The Datasource
  Component (TDataSource) H Putting the DataSource,
  Table, and Query Components to Work H The Stored
  Procedure Component (TStoredProc) n The Database
  Component (TDatabase) n The Session Component
  (TSession) n The BatchMove Component (TBatchMoye)
  n The UpdateSQL Component (TUpdateSQL)    H The
  NestedTable Component (TNestedTable)



F inally, we arrive at the Delphi promise of RDAD, Rapid Database
Application Development. Dash out a form, slap some components on ._ 3
that surface, point the components towards your database, and voil$-“ii
a database application is born. Building database applications in Delp&j$$
can be that easy but only after spending the requisite time exploring ;
the power of the tools. Like everything else in Delphi, the database
components are rich with functionality and adaptability, and the effort: ji
expended to build your knowledge on these classes will result in         2-t?
greater user satisfaction and measurably higher quality in your
applications.
The data access components, available to the Delphi developer from $
the so-named tab of the component palette, are non-visual componenti.
that expose the BDE database capabilities to the Delphi development :f’
environment. All database access and manipulation is performed
through these components. This chapter wil 1 cover all of these base
components, their properties, and events.
IO6 n Part II---The Delphi Database Tools
                                                                      ^.i

The Dataset Objects
             A dataset is the base building block of any database application. The&&
             are the objects that contain or access the data; the facts that we am,$
             attempting to manage. The most immediately familiar dataset objet+
             the table, a structure that we have discussed at length in the precec
             pages. There are four components on the data access palette that a
             derived from the TDataSet base class: Table, Query, Stored Proc
             and NestedTable. Before examining the individual classes, the
             ing class from which they inherit the majority of their functiona
             be laid open.                                                            “. 1;
                                                                                   , $ .-~$

The DataSet Object (TDataSet)                                                            *: _(i
             The TDataSet object (Latin name, TDataSet) is the core unit that .:*
             enables access to tables through the BDE or other database access !$
             points. A TDataSet object is the ancestor class from which all oth&#
             dataset objects are derived and models such real-life items as a ta
             a database or a query that presents a subset of this data. The clas
             defines a set of fields, properties, methods, and events that are in
             ited by all of the descendants. You cannot utilize the TDataSet ob
             directly because many of the properties and methods are virtual ~$2
             abstract. A virtual method is one that is defined in the base clas
             can be overridden in descendant classes. An abstract method in
             class provides a function prototype but no implementation; descen-        ,i
             dants are required to provide the implementation. This object modei &
             perfect for implementing these database components: Each is built F4$$
             the cask of accessing data; they just use different methods of accom-l’*”
             plishing the job.
             There are three classes that are direct descendants from TDataSet.
             TBDEDataSet enables the TDataSet class to work with the BDE to
             enable database access (TDataSet itself is database engine indepen
             ent). The TDBDataSet component is a descendant of the BDE-eni
             TDataSet that adds additional session and database features. I~Jx&~
             were to develop your own data access components, you would m
             likely create a descendant from the TDBDataSet class. The last cl&$
             also a direct descendant of TDataSet, TClientDataSet. This class
             designed lo implement multi-tier database applications using di
             uted databases.
                                                  Chapter S--Data Access with Delphi


Dataset   St   ates
               Before we talk about what can be done with a dataset, it is
               to understand under what condition we are addressing the table. The‘
               state of the dataset is a read-only flag that determines what actions u
               and cannot occur at any specific moment. The table in Figure 5.1 SU$$
               marizes the DossibIe states that are communicated bv the tables.       ;?4


                dslnactive          Inactive        This state indicates that the data in the   tab&
                                                    not available. The dataset is closed.
                dsBrowse            Browse          This is the default state for a dataset; indicates   ::


                &Edit               Edit

                Dslnsert            insert


                DsSetKey            SetKey




                DsCalcFields        Calcfields                                                  5 are
                                                           recessed and prevents changes to


                DsCurValue          CurValue

                DsNewValue          NewValue

                DsOIdValue          OldValue

                Dsfilter            Filter




               Figure 5. I   Possible   states of a TDataSet   descendant

               Each of the state values shown breaks down into one of two basic          p*
                                                                                          -i
               conditions-the table is either open or closed. A closed dataset object   $5
               does not expose any of the data contained within to either browsing or :f
               modification. In order to perform these operations, the dataset must be $$
               opened. Delphi has two methods of performing this function. You can :i:k$
               set the Active property of the dataset object to True, either at design
               time or programmatically at run time:
               Vendors.Active      :=    true;

               You can also call the Open method at run time:
               Vendors.    Open;
           Many operations, such as setting the current index, also require that
           the dataset be closed and reopened for the effects to be seen. Delphi
           provides two methods for performing this task as well. The Active prop-
           erty of the dataset can be set to False at design or run time:
           Vendors.Active   :=   False;

           or, you can utilize the Close method of the dataset:
           Vendors.Close;

           When the dataset state falls in the open category, the developer must
           be aware of two factors: what actions can be performed against the
           database and how the dataset arrived at the current state. For example,
           a dataset object linked to a data-aware control can be displaying the
           data but the user may find that he is unable to edit the current record.
           The developer should be aware that the default state of a dataset,
           Browse, will allow browsing of the data values but no modifications.
           The state of the dataset will have to be changed to Edit before any
           changes can be applied. Depending on the dataset component used,
           certain combinations of properties can be set to automatically set the
           state to Edit upon accessing the table. Awareness of these conditions is
           important in your design considerations and in testing your application
           prior to rollout.


The Table Component Fable)
           The Table component is generally the first data access control that you
           will pair with a DataSource control to construct a database application.
           It has been designed to provide uniform access and navigability to each
           row in the table types supported by the BDE regardless of the underly-
           ing rules of the dataset source. Despite the wide range of properties, it
           can be quite simple to use. Place a table component on a form, set the
           Database and Table properties to point to the relation you want to
           access, and the data flows immediately to your application.


Key Properties
           The Object Inspector displays the wide range of properties published
           for the Table component and the chart in Figure 5.2 provides a brief
           definition of each of them. Many of these will be utilized when we
           arrive at the first sample application, but there are a number of proper-
           ties whose values are not self-explanatory or immediately put to use.
                            Chapter S--Data Access with Delphi I




Active            False
AutoCalcFields    True


CachedUpdates     False       Enables or disables cached

Constraints                   This contains the record-level constraints     for
                              this table.

DatabaseName                  The name of the database to which this table
                              belongs.

Defaultlndex      True        When True, the data will be ordered by the              .
                              primary key or index of the table.

Exclusive         False       Setting this property to True gives the    current
                              application exclusive access to Paradox or
                              dBASE tables, preventing other users from


FieldDefs                     This property contains the field definitions    for
                              the table. This should not be modified     unless ..:
                              you are creating a new table.
                                                                                      i
Filter                        The text string defining the current filtering       ,jl:
                                                                                     4.
                              condition.                                           .“3

Filtered          False       This activates or deactivates filtering   for the
                                                                                     .;
                              table.

FilterOptions                 The set of options affecting the filter process Q
                                                                              :-;j
                              the table.

IndexDefs                     This set contains information about the
                              available indexes for this table.

IndexFieldNames               This property lists the column names      fo be
                              used in building an index for the table.

IndexFiles                    dBASE only. This property specifies index files


IndexName                     The property contains the name of a       secondary              .
                              index for the table.                                            I.

MasterFields                  The column names from a master table         that       .’ 2
                              links to this child table are contained in this         j
                                                                                     *F: z
                              property.

MasterSource                  The name of the DataSource       component           -:t
                              linked to the master table in a parent-child                ,
                              relationship.

Name              Table I    IThe Internal name of this comoonent.

ObjectView        False       Determines the method of storing the column 3
                              names. Included for compatibility reasons.  ‘::
      ReadOnly          1 False         This determines the read-only status   of the


      SessionName
                       I                table.

                                        Associates a session name with this    table.
      StoreDefs          False          This property determines whether or not         the
                                        Field and Index definitions are stored


      TableName
                       I 1              persistently with the form or data module.
                                        The name of the table associated with    this              ..
                                        component.                                                  (ri
                                                                                                    5
      TableType          ttDefault      This determines the database structure of       the      2
                                                                                                 _; 0
                                        table.

      Tag                0              This property is for the developer’s use.

      UpdateMode         UpWhere        This BDE setting determines the method used
                         All            to locate updated rows.

      UpdateObject                      Specifies the update object used to update a
                                        read-only   dataset.

     Figure 5.2 The TTable     component properties


Aura Cc&Fields
     This table property controls the firing of the OnCalcFields event that .!$
     updates the calculated fields in a row. If the property is set to True, cat- %j
     culated fields will be updated under the following conditions:
                                                                                   ‘;
     n The table is opened.
     n The state of the table is set to dsEdit.
     n ii column is modified through a grid or other data-aware control
        and the focus changes.
     n A row is retrieved from the database.

     Depending on the complexity of the calculation and the number of col-                         $
                                                                                               ,. :g
                                                                                                  ! sQa
     umns with which it works, auto-calculation may slow data entry or                              .-
     retrieval considerably. For this reason, it might be desirable to set this                 (4
     property to False during periods of intense data entry When set to                       _,::
     False, the event will not be fired and the columns will not be updated.
                                                                                   .,-*-s
                                                                                      r’,iys

                                            Chapter S-Data Acces
   ~m*b^*s.cI_^*- x1 _-~*_d_j_ij_*s_lyl-_t~*~-~~~~-_*s.__*_‘*__


       Tip      Be sure to fire the OnCalcFields   event prior to displaying               .~‘;.i
             updated data to the user. Without firing the event, the contents
             of the columns will not contain current data. The event does not
             need to be fired explicitly; you can also cause one of the triggers
             listed above to occur after setting the property of AutoCalcFields
             back to True.

                                                                                                      .:
CachedUpdates                                                                                       ..f
      Another table feature available for improving performance is the ability ‘3
      to utilize an internal cache to temporarily store updates to a dataset    J
      and then write them to the underlying dataset as a single transaction.
      The CachedUpdates property value determines whether or not the            -i,,
      cache is used. When the value is set to True, the internally cached rows ,,‘I’
      will be updated rather than sending the updates to the remote dataset. -I
      When the default value of False is selected or remains, all modifications
      are written directly to the dataset when they are entered. Note that in
      all situations, client data sets are always cached.
      The advantage of caching is in the minimization of traffic to the
      dataset, increasing the apparent speed of database updates. Using the .’
      cache is not without its drawbacks, however. Without the records or
      table being locked, other users can modify the underlying row data
      prior to the updates being committed, causing possible anomalies when ,~i
      the current session’s data is written to the table.                      -9
                                                                               -.
      Enabling CachedUpdates at run time is a matter of setting the Boolean
      variable:
      Vendors.CachedUpdates      :=   True;

      When caching is turned on, the user will work with a local copy of the
      data held in memory All updates will be applied to the in-memory data
      and written to the source table in a single transaction. Disabling cached                           ‘2
      updates without writing the data back to the table will result in a loss                            ,ii
      of data as the updates will be discarded without notification.                                   ‘1 .;
                                                                                                          .$
                                                                                                          i
Constraints                                                                                           .‘7*’
                                                                                                    _ ?&
      Record-level constraints can be set to control the data that can be                               .I4;I
                                                                                                    “:‘,s
      entered in the fields of a table. Constraints at this level of control gen-                    ‘,+j
      erally relate one field to another to control the data.                                          ,.-+
    Part /l-The Delphi Database Tools
.,..m-.  ,(,I i ____, i                    __   i   ^...   I   /_   .._   i_.*   _.s~u.pI~-~~~~
                                                                                              ‘.:
Filter                                                                                              --
         Filtering is the method by which some data from the table is exclude “-
         from view by displaying only those rows that meet specific criteria. :‘:
         These criteria can be set through the Filter property of the Table
         ponent. The property contains a string that defines the filter criteria.
         For example, if we want to limit the displayed data to those custome __
         who owe more than $10,000, a filter such as the following will only-g4.  ,. <
         display records that meet the condition:                                     I
         TotalDue > 10,000
         This condition can be set either at design or run time. In addition to---
         this string, the values in the Filtered and FilterOptions properties ati ,_r
         how the filtering occurs. The Filtered property is a Boolean value th@$
         determines if the filter string is ignored or acted upon. When set to -.::
         False, the filtering is ignored and the complete dataset is displayed.4
         ting the property value to True will enable the filter string in the Fi&
         property. The FilterOptions property is a set of two separate prop&j
         that further control the filtering process. The first, foCaseInsensit
         makes the comparison between the literal in the Filter property
         column value case-insensitive. When foNoPartialCompare is set.tu
         False, strings in the Filter property that end in an asterisk signify a
         tial match with the asterisk acting as a wild card. If the value of thk,,> _~
         item is set to True, the asterisk is taken literally.

lndexFie/dNames
         This orooertv allows vou an alternate method of defining an index for r:;j
              I   1   J


         the I:able. The value entered in this property is the column (
         that you want to use to index a table. Entering a value in this propeM
         overrides the value set in IndexName and vice versa.

MasterFields
         The parent-child relationship is fundamental to the proper functionh
         of a relational database system, and this property is one part of ma&
         that a reality in your Delphi application. The value in the MasterFi&
         property specifies the field or fields in the master table on wh
         ate a link with corresponding fields in this table. The property                       :.$
         MasterSource works in conjunction with this field to define the                         -.?
         DataSource component that is linked to the master table. Let’s exam
         the links for a moment.                                                                 tj;
                                                                                                  /*
                                                        er 5-Data Access with Delphi II 1 13
                                                   *,x_xH -. “> <*”  / _

                    Field Link Designer
                    Delphi has a tool for use at design time to aid in setting your relation-
                    ships correctly. It’s called the Field Link Designer and the main window
                    is shown in Figure 5.3. The designer is an easy-to-use visual tool that
,                   shows all of the participants in the relationship. It will be invoked
                    through the MasterFields property of the child [detail) table. The
                    MasterSource property is set to point to the DataSource component of
                    the master table. Click on the MasterFields property, selecting it, then
                    click on the ellipsis button to invoke the link designer.




    Figure 5.3
    The main win-
    dow ofthe
    Field Link      / *
    Designer

                    The Field Link Designer has done some quick background work and has         ,
                    already determined the salient data for you when the program starts.
                    The indexed columns from the detail table have been placed in the
                    drop-down box; you simply choose the linking field in the detail table
                    by selecting it from your list of choices. In the example, the column
                    CustNo has been selected; it then appears in the Detail Fields box.
                    Select this field and move your attention to the Master Fields edit box.
                    To create the linkage, the value in the detail field must match the value
                    in the master field. Select the CustNo field in the Master Fields box and
                    click on the Add button. The join will now be displayed in the Joined
                    Fields box. Clicking on OK finishes the process.

             SessionName
                    The Borland Database Engine uses a Session object to wrap all of the
                    database connection, drivers, cursor, queries, etc., under a single name
                    and separate them from other applications’ use of the engine. By
                    defaulr, every database application creates an object called Session and
                    the BDE maintains a list of all of these objects called Sessions. The
                    property SessionName allows you to associate a new session name with
n
-

         this table. You will create new session objects only under exceptional
         circumstances, such as concurrent queries against the same database,
         for two reasons. First, the default Session object can manage a wide
         range of conditions and needs and rarely needs to be overridden on
         low-tier applications. Secondly, the complexity of managing multiple
         sessions can quickly overwhelm an application and its developer.

    Table Type
         The TableType property specifies the underlying data-table structure for ,.’
         the table associated with this component. The default value, ttDefault,
         allows the BDE to determine the table type based on the file extension         .:,7:
         that it finds. The associations that will occur with this component are:     3
                                                                                            :$
         H DE or no extension-Paradox table                                           ,,i$
         w DBF-dBASE table                                                          :
         n   TX-T-ASCII table
         This default association can be overridden by explicitly selecting the
         table type for your file. The drop-down list offers the following-choices:
         H ttDefault-the      file extension determines the driver type
         w ttparadox-uses        the Paradox driver
         n ttDBase-uses        the dBASE driver
         H ttFoxPro-uses      the FoxPro driver
         n ttASCII--uses     the ASCII driver. The file is comma delimited, with
            quoted strings   for each field.

    b/e Events
         The events for dataset objects are somewhat different from those for
         other visual components. The events are triggered by movement and
         modification within the underlying data rather than the changes to
         control itself. The Table component events are:
         H AfterCancel
         n AfterClose
         n AfterDelete
         n AfterEdit
         W AfterInsert
         n AfterOpen
         W AfterPost
         W AfterScroll
                                 Chupter 5-b                   De    @hi
                                         -_“l*-__l             -_I


     n  BeforeCancel
     n BeforeClose
     W BeforeDelete
     H BeforeEdit
     H BeforeInsert
     n BeforeOpen
     H BeforePost
     w BeforeScroll
     w OnCalcFields
     w OnDeleteError
     n OnEditError
     n OnFilterRecord
     w OnNewRecord
     n OnPostError
     w OnUpdateError
     w On UpdateRecord
     The “before” and “after” events for TDataSet descendants surround a
     corresponding interaction with the dataset. For example, we have          .g4-
     BeforeCancel and AfterCancel events. The event that will trigger both       ( A :”
     of these events is a request to cancel changes to a row. The
     BeforeCancel event is fired after the cancellation message is sent but          -i
     before it is executed. The AfterCancel event is fired after the request         ,;*
                                                                                     “;.;
     has been executed. By utilizing the before and after events, you can             i ii
     wrap and control nearly every transaction against your database.

CIose Events
     These events fire when a message to close the dataset has been issued ‘,.X:’
     and immediately after the dataset has been closed.                       cfj
                                                                           .,$.$
                                                                                   ,;c;
Delete Events                                                                      ,/
     The delete events fire when the message to delete a record is issued      ?
     and immediately after the record is deleted.

Edit Events
     This set of events fires immediately before the application enters edit         3
     mode for the current record. The AfterEdit event fires after the
                                                                                     .*
     of the record begins.
   Part I/--The Delphi Database Tools
.,~~oI _-_- bilk ,- _ )/

Insert Events
      These events fire before the application enters Insert mode and after a
      new record is inserted.

Open Events
      The BeforeOpen event fires when a request to open a dataset is
      received and before the dataset is opened. The AfterOpen event occurs
      after the dataset has been opened to the application and prior to any
      data access.

Post Events
      After changes have been made to a record, the modifications must be
      posted to the dataset. The BeforePost event is fired before the update
      request is executed. AfterPost is triggered after the changes have been
      posted to the dataset but before the dataset is returned to the Browse
      state.

Scroll Events
      These events fire before the application scrolls from one record to
      another and after the scroll action has occurred.

OnCalcFields
      The OnCalcFields event fires when an application recalculates calcu-
      lated fields within a dataset. This action occurs, in the default mode,
      when a dataset is opened, the dataset is put into Edit mode, the cursor
      moves from one column or field to another, and when a record is
      retrieved from a database.

OnDeleteError
      This event fires when your application fails at an attempt to delete a
      record. Your event handler can issue corrective instructions and then a
      retry attempt for the delete action.

OnEciitError

      Similar to the previous event, OnEditError will be triggered when an
      attempt to modify or insert a record raises an exception.
                                           Chapter 5-Data Access with Delphi         n
                                          -__         .i   .__*-   -._   _n_((-~qs

        OnFilterRecord                                                                    ?sj

              The OnFilterRecord event fires each time a new record becomes the            1-j
              current record and filtering is enabled. This event handler is included -’
              so that you can set a filter condition that cannot be set in the Filter  ..$
              property due to its complexity. Each record is tested against this code %&
              determine if the data is visible to the application.                       q
                                                                                            4
        OnNewRecord
              This event is fired when your application adds a new record to the
              dataset.

        OnPostError
              Another of the error handling events, OnPostError is triggered when an ,,;$
              exception is raised by an insert or modification action.                 L..,.-*

        OnUpdateError                                                                             ‘”
                                                                                                ‘?.I

              The OnUpdateError event is triggered by an exception that is generated:
              by cached updates being applied to the database. The event handler
              can take corrective action based on the error and then request that the :$
              updates be reapplied.                                                     :

           .
        OnUPdateRecord
              This event is fired when cached updates are successfully applied to a
              dataset. The event handler is used to perform more complex updates                ~‘7
              than those that can be handled by a simple update action.


The Query Component (TQuery)
              The Query component is similar in nature to the Table component; it
              provides a method of accessing table data from a wide variety of
              sources and making it available to the application. What makes it dif-      <
              ferent from the table is that it uses SQL SELECT statements to build tk..~$
              dataset that it displays.                                                   _.
                                                                                          .*




Key Properties
           The property list for the Query component is detailed in Figure 5.4 and
           as you will notice, it looks remarkably similar to the property set of the
           Table. This, of course, is because both the Table and Query components ‘.!
           descend from the TDataSet class and inherit its properties, methods,       1
rrt l/-The Delphi Databose        Tools
                                                                                                       .i *
                                                                                                     ,b.


    and events. The changes to tllis property list reflect the differences ia
    the component’s approach to da ta access.


     Active                False              Determines if the database table is OP
    AutoCalcFields         True               This setting determines when the
                                              OnCalcFields event is fired.
     CachedUpdates         False              Enables or disables cached updates.
     Constrained           False

                                              statement that
                                              This contains the     record-level



                                          I   The name of the database to which this
                                              table belonns.
                                                                                                   II
                                                                                                   :
                                                                                                  -I
                                                                                                      .y:j




                                              This activates or deactivates filtering for’ ‘f,.,
                                              the mhlc                                             .?j

     FilterOptions                            The set of options affecting the filter
                       I                      process of the table.
     Name              IQ uery I              The   internal name of this comoonent.
     ObjectView                               Determines the method of storing the
                                              column names Included for compatibility
                                              reasons.
                                              This value determines whether or not tb
                                              Params list for the SQL statement is
                                              regenerated during run-time changes.
                                              The parameter    values   to be used in the



     RequestLive           False


     SessionName                              Associates a session name with this tab&.:
     SQL                                      This property contains the SQL string to:-’
                                              be executed.                                -1
     Tag                   0                  This property is for the developer’s use.
     UniDirectional        False              Determines the navigational          capabilities
                                              the query’s result set.
                                                             mines the metha-




     Figure 5.4 The TQuery component properties

Constrained




     Constrained property value determines if, when using a live result set,
     rows can be inserted or modified that would violate the parameters of
     the WHERE statement that built the result set. For example, if the origi- .?
     nal result set was created using the following SQL statement:
     SELECT * FROM Vendors WHERE State = 'CO'




     "CO".


DataSource
     Parameterized SQL statements build enormous flexibility into your          -.$
     application by allowing users to define the specific boundaries of their
     dataset. Many times the parameters are filled by an interactive session z3
     with the user or by internal values within the application itself. Another
     method of defining the parameter values is through retrieval of the
     data from another dataset. This is the domain of the DataSource prop-
     erty. The value in this property names the DataSource component from
     which the values to fill the parameters are drawn. If the parameters
     have the same name as columns in the DataSource dataset, the values

     ate parameter.

ParamCheck
     The ParamCheck property determines if the parameter list for the SQL
     statement is cleared and rebuilt when the SQL property is modified
     during execution of the application. The SQL property is easily modi-
     fied during execution to run different SQL statements. If the statement
n     fort II-The     Delphi Database Tools
                .^,   ,_.   _         ,,

          is modified, it is important that the corresponding parameter list ma&&:{
          the new query. To ensure that this occurs, the parameter list is cleared ,$4
          and rebuilt based on the current contents of the Params property.         .$!


          The Params property is an array that contains the parameter values
          needed for the SQL statement. At design time, the collection editor wiiI
          aid in setting the name and data type of each of the parameters.

    RequestLive
        There are two types of result sets that can be obtained through the use z.~’
        of a Query component: read-only and live. A read-only result set con- ‘G:-.i
        tains the results of the SQL statement but cannot be modified by the            --.A
        application. If the value of the RequestLive property is True, a live         i
        result set open to modification is returned when possible. The user CSQ+~
        modify the contents of the result set and have those modifications -:
        propagated to the underlying dataset.
                                                                                 I.
                                                                                  .,g!
    SQL                                                                              +a
                                                                                    c$;
                                                                                    .:;:
        The SQL property is the heart of the Query component. This string IiW:$
        contains the text of the SQL statement that is executed to provide the ’ “!:
        result set to the application. This property can be set at design time or
        built dynamically at run time. The BDE and the underlying capabilities
        of the target database determine the syntax supported.

    UniDirectional
          The BDE supports bidirectional cursors, meaning that both forward and .i
          backward navigation are allowed regardless of the rules of the undetly-‘$2
          ina database. If the value of the UniDirectional oropertv is False,          . __e
                                                                                           ;.
          bidirectional cursors are returned in the result set. Setting this value to ;:i
          True will result in a unidirectional cursor that allows only forward         :;i ”.‘8,
          navigation.                                                                       .z ,


I DataSource Componel It (TDataSource)
          The DataSource component is the pipeline between a dataset and the
          data-aware components. This intermediary component is required anyL’ti
          time that data-aware components and dataset components are to be
          utilized on a form. The DataSource component requires a matching         f:
          DataSet component in every case. The DataSource control also enabMS’:$
          the linking of datasets in master-detail (parent-child) relationships.     ‘r;
                                                                                 I, -,k
                                                                                    ‘;
                                                  Chapter 5-Data              Access with Delphi       n
                 ,.‘    _   _   I,i   _       _   .-   -^- ;I-.   ^.      j       -   .s..   _.*%‘a*



      The DataSource component surfaces a limited number of published
      properties for use at design or run time; these are summarized in Fig-                                      :-
      ure 5.5. Since the number of published properties is so small, the                                           -”
         _I
      maioritv+ of them are kev to extracting the best performance from yet
      application.




     rtoEdir rue /Edit mode.
      DataSet
                                                  Determines if the data-aware components
                                                  connected to this control automatically enter 6~

                                                  Specifies the dataset        from which the compone&..l?
                                                                                                             \i
                                                  gets its data.
     1 Enabled         1 True                     (Determines if the data-aware controls associate&‘l:
                                                   with the DataSource component display data.         4
      Name              DataSource        I        The internal name of the component.
      Tag              10                         This property is for the developer’s use.

     Figure 5.5 TDataSource               component properties


Auto Edit
     When the value of this property is set to True, the associated dataset
     automatically enters the Edit state when a connected data-aware con- :!j
     trol is updated. When it is set to False, the application must explicitly ‘2
     change the state of the dataset to Edit through a call to its Edit metk#
     before the user can update the data.                                     -;

     The convenience of defaulting to the Edit state is a good choice for i-3
     most programs but should be considered carefully for more critical
     applications. You may want to exercise more control over exactly wlI&
     updates can be performed by either having the user explicitly turn on :;$
     the edit functionality or having the program review the conditions      2’
                                                                            -‘**
     under which the update is being performed.                                :
                                                                              $>
                                                                             ...i
                                                                            _
DataSet

     This property defines the dataset to which the DataSource compone
     is connecting the interface components. The value of the property is
     selected either through use of a drop-down list of available dataset .,.J
     components or through manual entry of the name. The DataSet comg
     nent must be added to the form prior to attempting to enter this val
     attempts to enter a nonexistent dataset name will result in an error
     message.
122
#.,%. _   _

              Enabled

                     This property is the switch that controls the visibility of the data con-
                     tained in the dataset. When this property value is True, the DataSourc$f
                     is connected to the dataset and any controls that reference the             “‘:’
                     DataSource will display current data. (This is provided that the Table;:“:
                     etc., is in an active state). A value of False will serve to disconnect the “2
                     data-aware controls from the dataset.                                         I:
                                                                                                  <:
                     The value of the property can be modified at run time in order to tern-%
                     porarily disconnect the controls from the dataset, although this is not --?
                     recommended. The preferred method of performing this operation is ..jf!: .t’
                     through the DisableControls method of the dataset.                                c;
                                                                                                     . :;

                 7   Tip     The component name seems to be the simplest of all of the
                           properties, needing no explanation. Why not take advantage of
                           this property to make your own work easier? Name the
                           DataSource component something similar to the name of the
                           DataSet component that it is connected to. Grouping controls
                           like this in a complex application will make your life a lot easier.

                                                                                                    ,;g
TDU tdource Events                                                                                  _, .A
                     There are three event handlers exposed by the TDataSource class:                  ., .-(
                                                                                                           A‘<
                     n OnDataChange
                     H OnUpdateData                                                                            .,
                     n OnStateChange                                                                           ‘7
                     Each of the event handlers fires on a change in the dataset to which it -2
                     is connected. It does not reflect any change in the DataSource; the con--{ :
                     trol remains a simple intermediary

              OnDataChange                                                                             X.’
                     The OnDataChange event is triggered by the cursor moving to the N+:$
                     or previous record; any method that results in a change in the cursor *l!?
                     position will trigger this event. This event is especially useful for ma
                     ally synchronizing components.
                                                                                                   ,
                                                                                              . .*.t
              OnStateChange                                                                            . .i‘
                                                                                                          .
                                                                                                       --;:
                     Whenever the state of the dataset associated with the DataSource         : .:i
                     changes, the OnStateChange event is triggered. Since a state change 9:
                     the dataset can be a significant event, this event is useful for managin@
                     these changes.                                                              :,
                                                                                                 I.
      OnUpdateData
            Whenever the data content in the current record of the dataset is about?!!
            to be updated, the OnUpdateData event is fired. This handler will pro- :;I
            vide your application with the opportunity to update non-data-aware .,--TZ  -$+
            components and keep them synchronized. For example, when data ina
            field is modified and the user tries to move on to the next record, the ‘1.
            Post method of the dataset is called. Prior to actually changing the
            record, the OnUpdateData event is called.


putting the DataSource, Table, and Query Components 1
Work                                                                                         ,.
            The best way to understand the interaction between the database corn--$
            ponents is to put them to work. A simple project that uses the
            minimum number of components will allow us to explore the
            DataSource and DataSet components without the interference of inter-
            face issues. The first project will zero in on the DataSource componen&,:j:
            On its own, it is singularly unspectacular and cannot function in a vat- .k
            uum without the support of other components. Remember that its             .,;sj?;
            purpose is to provide a conduit between the interface components and ‘:
            the database. The Datasrc project will examine the control that it pro-
            vides to your application. All of the projects described in these pages
            are on the CD-ROM in the Chapter5 subdirectory.

The Dutasrc Project
        I. Start a new application and caption the form Datasrc Project. Save ‘-
            the project, naming the unit Dsrcu.pas and the project Dsrc.dpr.         .*t
        2. On the form, place a DataSource and a Table component. It doesn’t       ’
            matter where you place these components. They are non-visual and
            will not appear on the form at run time.
        3. Click on the Table component and set the properties as follows:
            H DatabaseName = DBDEMOS
                                                                                   . 3.. 21
            W TableName = CUSTOMER.DB                                                ,-’I,
        4. Select the DataSource component and set the value of the DataSet : .;s
            property to Table 1.
        5. From the Data Controls tab, select the DBGrid component and drop it
            onto the form. The DBGrid control provides a table-like display of the
            contents of a dataset. Select the DataSource property and change it to
            DataSourcel. You will notice that all of the database components
     I/-The Delphi Database Tools
     pi      a_.-

      are aware of what other components are a part of the project and their dg;
      names will appear in the drop-down lists for properties such as this.
6.    Select the Table component again. We are going to turn on the spigot
      and start the data flowing to the application. Change the Active prop-     ,jj!
      erty to True. When you do, data from the CUSTOMER table will be             _ f$
      displayed in the DBGrid control.
7.    One last step for neatness’ sake. Drop a BitBtn control on the form and      .i~~3
      modify its Kind property to bkclose.                                         _ A.?$
                                                                                    ihs,$
                                                                                       A
      You can run the application at this point. It does not do anything spec-     d:
      tacular but you should be able to navigate the dataset with the arrow             4
      keys and browse all of the data. Click on Close to continue.                        i
                                                                                       ; ;-.* #
      Recall that the state of the dataset determines what actions can occur        ::$
      against or with the dataset. Using the simple database application that     ..&j
      we have constructed there is an excellent opportunity to learn more      -.::
      about the complexity of the state sequence. The Datasrc application
      will be modified in the following steps to pop up a message box each
      time the state changes in the database.
I.    Select the Events tab for the DataSource component and add the code
      shown in Listing 5.1 to the OnStateChange event handler.
      procedure TForml.DataSourcelStateChange(Sender:      TObject);
      var
          tempstr       : string;
      begin
        tempstr := 'I;
        case Tablel.State of
           dsInactive    : tempstr := 'Inactive';
           dsBrowse         : tempstr := 'Browse';
           dsEdit           : tempstr := 'Edit';
           dsInsert         : tempstr := 'Insert';
           dsSetKey         : tempstr := 'SetKey';
        end;
        messagedlg('Current     state changed to '+tempstr,
                      mtInformation,  [mbOk], 0);

      End;

      Listing 5. I Modifying the OnStateChange event handler

2.    Compile and execute the project.
                                                          r S-Data Access with Delphi II 125
                                                           . , , _.,_ _) _. ,*. _ _,,. __. “,. , * ,. _ _,. _I, ..j,_ _..

             As shown in Figure 5.6, your application should notify you of every sin-
             gle state change.       - * *




                        1354 Cayman Dwers World Unlmted   Pfl Box 54




             Notice that the dataset is originally in Browse mode and will automati-
             tally switch to Edit mode when a change is made to the data. You may
             discover that the state changes more often than you ever suspected.

Wall Street Watch
             Next, we will put the Query component through some simple paces so
             that you can get a good idea of what its job is. Putting this component
             to work will also require the use of possibly nascent SQL skills. An
             advantage of this small program is that you can also use it to experi-
             ment with SQL statements while building some experience.
        I. Begin with a new form, captioning it with the string Wall Street
           Watch. Save the application, naming the unit wswU.pas and the
           project file wsw.dpr.
       2. Add a DataSource, Query, and DBGrid component to the form. Set the
           properties as follows:
           Query:
           DatabaseName:          DBDEMQS
           DataSource:
           DataSet:               Query1
           DBGrid
           DataSource:            DataSource
       3. For good measure, add a BitBtn component and set the Kind property
           to bkclose.
126 w fart II-The        Delphi Database Tools
              .-1~~1^^121p~~~-“.^~~i~._w.1” ~.. ,~“.xs    _     i _.,_ .~ *a _Y -slr_*_.*lil-__/,__llI_~

         4. Unlike the Table component that starts the data flowing to the app,,,
            tion as soon as it is made active, Query sends data to the applicatiu@$$
            only as a result of a successful query. The SQL statement must be        ’
                                                                                     :.:;>
            entered into the SQL property prior to activating the component. Th&,;$
            easiest way to enter code at design time is to invoke the String List :?‘$
            Editor by double-clicking on the SQL property value column. The fi
            statement that will be used is a simple SELECT statement that will
            retrieve all of the rows from the MASTER.DBF table. Enter the follovjx;,, i
            ing code into the String List Editor:                                      ‘.>B
                                                                                         ,<$
                                                                                                            :
               SELECT * FROM         MASTER.DBF                                                            :y

         5.    Set the Active property of the Query component to True. If the SQL         i
               statement was correctly formatted, the grid should fill with data. If a a!
               problem is found with the statement, an exception will be raised and :$
               the Query component will not be activated. Save the project after c
               piling it.                                                            >q
               A simple query but useful in learning to utilize the components. The. L&
               muscle behind the Query component is the complexity that is allows
               in the SQL statement. Local SQL supports a wide variety of construe
               for building static statement but it also has another advantage-it SI
               ports the placement of replaceable parameters into the clauses of a i., ‘J-
               statement. This allows you to build a statement into the Query co
               nent into which your users can place values to manipulate the
               statement at run time.
                                                                                                                     :.p
               MASTER.DBF has a column containing the risk assessment of each of                                      -8
               the stocks that we are tracking. Each of the instruments is rated as low,                         , .,
               medium, or high risk. To add functionality to the Wall Street Watch
               application, we will use the Query component to allow the user to cate-                     ,I
               gorize the stocks displayed by risk.                                         i.*,.Aq
               A SQL statement can be parameterized so that the values in the param- fi:.
               eters can be modified at either design time or run time. The parameters ‘::
               replace persistent values in a SQL statement. One very useful place to ‘ii
               utilize parameterization is in the WHERE clause in which the selection,. “3:s2
               criteria is based on the values found in specific columns. Parameters ‘I f?J
               are represented in a SQL statement by their label and are always pre- :$j
                                                                                       2 I>,2
               ceded by a colon.                                                          -.d
                                                                                           53
               The SQL statement in the Query component will be modified with a ‘:T$
               parameter to represent one of the operands in the WHERE clause. The-:$
               new statement will read as follows:                                       -I 2
                                                                                                           '-3
               SELECT * FROM       master.dbf     WHERE       risk   =    :RISKCODE
RISKCODE will contain a value that will be compared against the col-
umn Risk when the SQL statement is executed.
When the SQL statement is modified, it will share the parameter values 8 ’
with the Params property of the Query component. Modifying this
property at run time is another dynamic method of adding new param-
eters to a SQL statement that enables you to build the statement on the 8
fly.
Listing 5.2 shows the code used to modify the RISKCODE parameter
value at run time.                                                           8

procedure TForml.ButtonlClick(Sender:       TObject);
begin
  with Query1 do
  begin
    Close;
    Unprepare;

    Params[O].AsString     := Radiogroupl.Items[Radiogroupl.ItemIndex];
                                                                             1
     Prepare;
     Open;
  end;
End;

Listing 5.2 Modifying the RISKCODE   parameter

When using parameters in a SQL statement, there are a number of pre-
paratory and cleanup steps that must be executed. Notice that the first
thing that occurs is that the Query control’s Close method is used to   8
close the dataset. Modifications to the SQL or Param properties must be
executed on a closed dataset. Next is the statement Unprepare. This
method works in conjunction with the Prepare method to set up the       8
environment and bind the values to the parameters prior to executing
the SQL statement. The Unprepare method cleans up after the SQL
statement and releases resources back to the application.               8

The value that we want to use in the WHERE clause of the SQL state-
ment is set in the line that reads:
                                                                             1
Params[O].AsString    :=      Radiogroupl.Items[Radiogroupl.ItemIndex];

Remember that the Params property is an array with each of the param-
eters in the statement indexed to their position. Therefore, the first and
only parameter in our statement resides at subscript 0 in the Params
list.
                                                                             I
128 q     Part II---The Delphi Databose Tools
                          -.      I”/   -.-.  ”           _,._                       ,. il. _I.IIN,lihL ,j_

                    Finally, the statement is executed by calling the Open method. The
                    Onen method is used to execute SQL statements when the SOL will
                    result in a dataset being returned such as our SELECT statement.
                    Another method, ExecSQL, is utilized when no result set is returned
                    such as in an INSERT, DELETE, or CREATE TABLE statement.
                    Modify rhe Wall Street Watch application by performing the following
                    steps:
               I. Modify the SQL statement as shown above, if you have not already
                  done so.
               2. Add a Radiogroup control to the form. Caption it Risk and add three
                  items to the Items property: LOW, MED, and HIGH. The strings that
                  are used should match the expected values in the Risk column so that
                  a match can be made.
               3. Add a button captioned Check Risk to the form. The code in Listing
                  5.2 is the OnClick handler for this component.
               4. Compile and execute the application. It should appear somewhat simi-
                  lar to Figure 5.7.




                                                                       HIGH    *




Figure 5.7
The Wall
Street Wutch
project

                    Try selecting the different risk values. For each one, a query will be exe-
                    cuted and a new dataset displayed on the grid. (The Risk column is on
                    the far right-hand side of the table and may not be immediately visible
                    for you to verify your results.)
                                               iota Access with Delfhi q       I 29
                                                                               ,. ,.



                                                 simple components that we
                                   _**-_ - -__  cation that uses a par-
     ent-child/master-detail relationship between two or more tables. To
     explore this topic, we will use the OrderForm project.
I.   Start a project and create a form that is similar to that shown in Figure
     5.8. You are going to use two Tables, two DataSources, and two
     DBCrids. Save the project.




                                                                           .




2. Prior to building your application you will have decided which table
   will be the master, or controlling, table in a relationship. Navigation in
   this table determines the contents of rhe detail table when linked in
   this manner. The Table1 and DataSource components will link to the
   master dataset in this relationship. Their properties are the same as
   those used up to now. Point the DataSource control to Table1 as the
   DataSet property. Table1 will use DBDEMOS as the Database prop-
   erty and fill TableName with CUSTOMER.DB.         Activate the dataset.
3. Select DataSource and set the DataSet value to Table2. Select the
   Table2 component and again, set the DataBase property to
   DBDEMOS. The TableName property for the detail table will point to
     ORDERSDB.
          I Fart /I-The Delphi Database looh

              4. The detail component is used to set up the relationship at the applica-
                 tion level. Select the MasterSource property and select the DataSource
                 control linked to the master table, DataSourcel. Select the
                 MasterFields property and click on the ellipsis button to invoke the
                 Field Link Designer. (This was referenced earlier in this chapter. Please
                 refer back to those paragraphs for usage information.) The primary
                 key for the ORDERS table is the default index displayed in the
                 designer but this will not work as a linking field. Click on the Available
                 Indexes drop-down box and select a secondary index, CustNo. Click
                 on the CustNo field in both the Detail Fields and Master Fields lists
                 and click Add. Select OK to finalize the relationship.
                 Activate the Table2 component. Review the orders shown in the detail
                 grid. They are now limited to those orders matching the customer
                 number of the currently selected record in the master table. Compile
                 and execute the application. As you navigate the master table, notice
                 how the contents of the detail dataset change. The miracle of the rela-
                 tional database is revealed!
                    Filtering is a dataset-based method of limiting the rows that are dis-
                    played. Afilter is a constructed statement that compares a value to a
                    column value. Only those rows that match these criteria become avail-
                    able to the application. The OrderForm application can be modified to
                    utilize the filtering properties of a table to improve the usability. A filter
                    will be built to utilize the contents of the State column to limit the size
                    of the dataset geographically
               1.   Add an Edit and Button component to the form as shown in Figure
                    5.9. We will use the Button OnClick handler to manipulate the
                    filtering.



Figure 5.9
Addrng an edit
box and button
to the
OrderForm
project

              2. Add the following code to the OnClick handler procedure for Buttonl,
                    Carefully examine the number of quotes:
                    Tablel.Filtered   := True;
                    Tablel.Filter   := 'State = "I + Editl.Text      + I"';
                                       Chapter S--Data Access with Delphi
                          .., i __; _ _ _ l.-_ 114 u-.P.l?^len _.-___, ” ,<ial*lsbp

     Execute the OrderForm application and scroll through the CUSTOM,
     table to see the range of values that have been entered in the State&,
     umn. Enter a value in the Edit box and click the Filter button. The .*i
     Customer List grid will display only those items that match the Stati%
     value. None of the other functionality is affected; the relationship &$
     still intact.
     Another limiting tool is the Range. Similar in function to filtering, a
     range allows you to bracket a range of values that are available to t
     application. The filter offers more flexibility than the range, as the
     range requires an indexed Paradox or dBASE column to work. SQL 2
     umns listed in the IndexFieldNames property can also support a ran,-
     Consider both options when deciding how to present data to your .:,$.
     application’s users.                                                    ,..t,t?

   7 Tip     One last thought prior to moving on to the remaining compo-
           nents: If your application plans include a requirement for
           scalability, you may want to use Query components for all table
           access rather than simple Table components. This will make the
           task of scaling up much easier to accomplish with fewer changes
           to the application.


Stored Procedure Component PStoredProc)
    Stored procedures are mainly the domain of the remote server world. d
    stored procedure is a set of SQL statements or other subroutines sup-     ,:
    ported by the server environment that reside and are executed on the “1
    remote server. They serve two purposes: The independent code packa
    can be used on multiple databases to ensure consistent modification t”
    and retrieval actions and they relieve network traffic by not requiring ?
    long SQL statements to be sent to the remote server.
    Stored procedures come in two types. The first class manipulates the ‘5:
    data contents of a remote dataset and the other returns a result set     d
    from a SQL SELECT statement. The type of the stored procedure and i$
    the remote database determine the type of component that you will UZ@
    to execute the stored procedure. In some cases, when a stored proce- ..:i
    dure returns a result set, the Query component is used to execute the %
    procedure rather than the StoredProc control. Stored procedures sup- :
    port both input and output parameters, often utilizing the input          z,-
    parameter as a parameter to the SELECT statement. The results of a ?
    statement are returned to the calling application through the output       ‘i
    parameters. A thorough understanding of the stored procedure that YE&I,
    want to utilize is important so that you can make an accurate decision
              as to what type of object is needed to receive any results returned from
              the execution of the procedure. In Delphi, the StoredProc component is
              the interface to this functionality.

Key Properties
           The StoredProc component surfaces a familiar set of properties, all of
           which are detailed in Figure 5.10. Since the functionality is based on
           the remote server, there is little for the component to do except point to
           it and pass the appropriately formatted parameters.


               Active              False            Determines if the stored procedure is
                                                    to be executed.

               AutoCalcFields      True             This setting determines when the
                                                    OnCalcFields       event is fired.

               CachedUpdates       False            Enables or disables cached updates.

               DatabaseName                         The name of the database to which this
                                                    stored      procedure     belongs.

               Filter                               The text string defining the current
                                                    filtering    condition.

               Filtered            False            This activates or deactivates filtering for
                                                    the results of the stored procedure.

               FilterOptions                        The set of options affecting the filter
                                                    process of the stored procedure.

               Name                StoredProc   I   The internal name of this component.

               ObjectView          False            Determines       the method of storing the
                                                    column names. Included for
                                                    comoatibility     reasons.
               Overload                             This property represents the Oracle
                                                    overloaded stored procedure to be
                                                    executed.
               ParamBindMode       PbByName         Determines the order that the
                                                    parameters are assigned to the
                                                    parameter       fist of the procedure.

               Params                               The list of input and output parameters
                                                    for the stored procedure.

               SessionName                          Associates a session name with this
                                                    stored      procedure.
               StoredProcName                       The name of the remote procedure
                                                    represented by this component.
                                            Chapter S-Data Access with Delphi n              13




                                                      This property is for the developer’s


              UpdateObject                            Specifies the update object used to
                                                      update a read-only dataset.

             Figure 5. IO The TStoredProc   component properties


       Overload
                                                                                                     ‘kq
                                                                                                     ‘: v$
             The Oracle database supports overloaded procedures, which are stored
             procedures that share a name but differ in function based on the con-
             text. Oracle differentiates between them by assigning each a numeric                     r
             identifier, and this property passes that identifier to the BDE. When
             selecting from among the overloaded procedures, it is crucial to
             remember that the parameter lists for the overloaded procedures can
             be different and that the appropriate parameters must be passed and
             received.
             When addressing other server types, this property should be left at its              1::
                                                                                                    -l
                                                                                                   rilr
             default value, 0.

       Param BindMode

             The ParamBindMode property determines the method that will be used
             to match values from the Params parameter list to the parameter list of (,
             the stored procedure. Two assignment strategies are supported: By          1
             Name and By Number. The preferred method is to utilize By Name,
             which explicitly matches the identifiers of the parameters in both the
             parameter list and the procedure. The By Number method assigns the
             parameter values in a sequential method, i.e., the first parameter from
             the Params list is assigned to the first parameter slot at the procedure.

       Par ams

             Stored procedures support the return of values through the procedure’s ‘-3
             parameters, similar to the Pascal variable parameter. The Params list for ,_::
             a stored procedure is used to set and examine the values of both input :.
             and output parameters.                                                     ~?

TStoredProc Events
           All of the events surfaced through the StoredProc component are
           derived from the TDataSet and TBDEDataSet. Because they have been
           discussed in previous paragraphs, you are referred back to those
           descriptions for more information.
134 II Port Ii-7%e Delphi Database TOO/S


Putting Stored Procedures to Work
            We will make use of the Local InterBase Server that ships with some
            versions of Delphi to explore the functioning of the StoredProc compo-
            nent and stored procedures in general. The project that we will build is
            called the Project Manager, and it works with the sample InterBase files
            in the IBLocal darabase. If you have not installed the LIBS, this data-
            base will not appear in the Database Explorer. Remember that the
            stored procedures are a part of the metadata of the database, so all of
            the information pertaining to the structure of the procedures can be
            viewed through the Database Explorer.
                The running application is shown in Figure 5.11.




                                                     MAPCJB   MapBrowser porl
                                                              Translaior blipgrade




Figure 5,
Figure 5. I I
The Project
Manager moin
Manager mum
screen

                Two different operations are being handled by stored procedures run-
                ning on the InterBase server. When the Programmers dataset is scrolled
                from row to row, a procedure is used to extract the programmer’s cur-
                rent project assignments and display them in the Assignments grid.
                Secondly, a procedure is used to insert new rows into EMPLOYEE-
                PROJECT To begin building this project, start with a new application.
           I. Caption the form Project Manager and save the project. Name the
              unit file stprocuZpas and the project stproc2,dpr.
           2. Add three DBCrid components to the form: DBGridl in the top left,
              DBGrid2 in the top right, and DBGrid3 in the lower left-hand cor-
              ner. So that the following instructions will have some measure of
              clarity, add the labeling as shown. We will refer to the grids by their
              closest label in the instructions.
                                                     Chapter    S-Data Access with Delphi q   135


               3. On the Programmers grid place a DataSource and a Query component.
                  Set the DBGrid DataSource property to DataSourcel. On
                  DataSourcel, set the DataSet property to Queryl. Select the Query
                  component and set the DatabaseName property to IBLocal. (Remem-
                  ber, this will only show if LIBS has been installed.) Double-click on the
                  SQL property to open the String List Editor and enter the following
                  SQL statement:
                   SELECT   emp-no,    first-name,   last - name   FROM   employee

                   Click OK to save the statement to the control.
               4. Be sure that the LIBS is running and double-click the Active property
                   on the Query1 component to execute the SOL statement. Enter the
                   password &asterkey. If it is correctly form&ted, the statement will
                   be processed and the grid will fill with the result set. Your projecr
                   should be similar to Figure 5.12 at this point.




Figure 5. I2
Modifyrng the
Project Man-        .,, ~.. . . .    .‘
agerproject
                      .
                   ., . . ..,. >.
                   :.*--:a>        > . .. . _.
                                  ._ ._.I. -,.



               5. To the Current Projects grid, add both a DataSource and Query com-
                  ponent. The grid’s DataSource property should be set to
                  DataSource2. The DataSet property of DataSource should be
                                                   component’s DatabaseName property
                  pointed to Querya. The Query component’s DatabaseName property
                  will be IBLocal and the following SQL statement will be entered for
                  the SQL property:
                  SELECT    proj-id,     proj-name   FROM   project

               6. Activate this query to fill the dataset with the results from the SQL
                  statement.
I36 n   Part II-The Delphi Database Tools
                         _,
                        _. ,.-                                                . *%;--

         7.   Finally, add the DataSource and Query components to the Assignments
              grid. Set the appropriate DataSource and DataSet properties to link
              these three components together. In the SQL property, the SQL state-
              ment that we are going to enter will execute a stored procedure. We
              are using a Query component because the GET-EMP-PROJ stored pro-
              cedure returns a result set. This procedure has an input parameter
              through which we will pass the employee ID number for which to dis-
              play the current projects. Enter the SQL statement as follows:
              SELECT * FROM get-emp-proj(:Emp-Num)

         8. This query will not be activated at design time. The application is
              going to execute this query each time a new employee is selected. To
              do this we will utilize the OnDataChange event of the DataSource
              control. Add the following lines to this event handler procedure:
              procedure    TForml.DataSourcel.DataChange(Sender:   TObject;   Field:
              TField);
              begin
                Query3.CLose;
                Query3.Prepare;
                Query3.ParamByName('Emp-Num').AsInteger       :=
              Queryl.FieldByName('EMP_NO').AsInteger;
                Query3.0pen;
              end;

         9. Add a BitBtn control and set the Kind property to bkclose. Compile
            and save the application. Execute the application and scroll through
            the Programmers list. As the employee ID changes in the current row,
            the GET- EMP PROJ procedure is fired and returns all of the projects
                           -
            assigned to that specific ID number.
        IO. Add a Button component and caption it Add Project. The user will
            click on this button to assign new projects to a programmer. For this
            we are going to use another stored procedure from the IBLocal data-
            base, ADD-EMP-PROJ. This procedure does not return a value, so
            rather than using a Query control we are going to use the StoredProc
            component. Place a StoredProc control on the form and set the
            DatabaseName property to IBLocal. Since the procedures are a part
            of the metadata of the database, clicking on the StoredProcName
            property will display a list of all of the registered procedures. Select
            GET-EMP-PROJ. Double-click on the Params property. The parame-
            ters for the selected procedure are displayed and their properties
            surfaced for editing. By clicking on each parameter you can determine
            what you are working with. Don’t make any changes. Close the editor
            to return to the Object Inspector for StoredProcl.
                                        Chapter S--Data Access with Delphi n        1%

                                                                                  $2
      I I. The code for adding a project record is going to be placed in the but- y$
           ton’s OnClick event handler. GET-EMP-PROJ     has two parameters thag 1s
           are going to be gathered from the EMPLOYEE and PROJECT tables. “ -
           Enter the following code for the event handler:                         >.?I
                                                                                   r;
           procedure     TForml.ButtonlClick(Sender:   TObject);
           begin
              StoredProcl.Prepare;
              StoredProcI.ParamByName('PROJ~ID').AsString       :=
           QueryZ.FieldByName('PROJ-ID').AsString;
             StoreProcl.ParamByName('EMP-NO').AsInteger        :=
           Queryl.FieldByName('EMP-NO').AsInteger;
             StoredProcl.ExecProc;
             StoredProcl.Unprepare;
           end;

     12.   Compile the program and save it. Execute the application and select a 2,
           programmer and a project. Click the Add Project button and the ,:;
           ADD-EMP-PROJ     procedure will take care of inserting the new row. :’
           When you select the employee record again, the new project will have.
           been added to the Assignments list.                                      $2
                                                                                       fj.
                                                                                        .

The Database Component (TDatabase)
           Every Delphi database connection is encapsulated by a Database corn- 1
           ponent, whether or not you actually place one in your project. The     :
           Database component is responsible for providing all of the connection -;
           information from the database to your application. An automatically    i;
           generated database component is instantiated for each BDE application-;
           to encapsulate the connection.                                          ‘5
                                                                                 .;A,.
           The most important task of the Database component is to provide your 3
           application with information about the type and location of the data       1
           files of your database. Recall that in setting up the alias in the BDE, yog’f
           were required to specify the data location, the driver type, and some -:,g$
           basic connection parameters. When a temporary database component &$
           instantiated, these values are read from the BDE and passed to your 1’3
           application. This method of obtaining information about the database .;-
           connection detracts from the scalability and installation flexibility of 7
           the application. For example, to simply specify a new location for the
           storage of data files, your users would be required to edit the BDE con--’
           figuration files and modify the parameters of the alias used at the time’ !
           of installation. This is certainly not the type of task that many users or i7
138 n    Part II-The Delphi Database Tools
                                   ,-   . _s. .a.

               administrators would be comfortable performing, especially in a corn.
               plex client/server environment.
               A persistent Database component, instantiated when a Database corn-
               ponent is explicitly present in an application, brings the task of
               specifying the connection information into your application. Database
               connection parameters can then be stored with your application or in
               your application’s keys in the Registry, allowing the user enormous flex.
               ibility in scaling or simply moving an application’s data stores. In
               addition, much finer control over transactions and logins is possible
               when an explicit Database component is added to the application.

Key Prroper rties
              Since we have left the comfortable confines of the dataset components, . *db  *a
              there are a number of new properties that require discussion. The corn- $i
              plete set is detailed in Figure 5.13.                                   ;gj
                                                                                       :- y


                AliasName                           This determines the BDE alias    that djT


                Connected          False


                DatabaseName


                DriverName                          The name of the BDE driver for the


                Excluwe            False            Setting this to True enables exclusive
                                                    database access, preventing others    from          .;I
                                                    modifying the data.                                       .z,
                                                    Determines whether or not a database               . sz‘-
                                                                                                          4-r
                                                                                                      j.. :;:
                                                    handle is shared.                                  _’

                                                    Creates a persistent connection    even if       ‘- ‘!,
                                                    the dataset      are closed.                           . ..
                                                                                                    , ~ _(_I
                                                    This property controls the display of the      ’ -.:.lt:
                                                                                                    .L; p
                                                                                                        :y3
                                                    standard login prompt when      connecting
                                                                                                         <?j
                                                                                                       ,-A
                                                    to a database.




                                                    Determines if the database     connection is
      SessionName         Default             The name
                                              the database   connection.
      Tag                 0                   This property is for the developer’s use. -
      Translsolation      tiReadCommitted     Specifies the transaction isolation level. :

     Figure 5.13 The TDatabase      component properties
                                                                                             i
AliasName
     The AliasName property is used when you want to utilize an already
     existing BDE alias in the application. This property is filled from a
     drop-down list of all of the known aliases. When an alias is specified
     through this property, it overrides other settings in the Database corn-
     ponent such as the driver type and items stored in the Params properr$?,
     This occurs because the information has already been defined through .i
     the BDE configuration.                                                   /’
                                                                               ai-*-4
                                                                                s-
DatabaseName
     The name of the database is the identifier by which the application
     gains access to the settings required for a connection; this name is con- :’
     tained in the DatabaseName property. If you enter a name that matches:
     an existing alias, the driver and location information for that alias is
     captured and need not be set. When a new application-specific name is
     entered, you must also follow the requirements of the alias and provide,2iz
     location and database type information through the Params and              ‘- : $‘4i
     DriverName properties respectively

KeepConnection
     Unlike the temporary connections created by the default instantiations :$
     of the Database component, the explicit placement of a Database corn- ,4
     ponent allows you to manage the connection status with the database. ,j
     The KeepConnection property determines whether or not a database . ,;*g        _
     connection stays open even after all dataset objects have been closed. -. $
     By creating a persistent connection, the application is not required to : ,;;i..-
     log in each time the dataset needs to be accessed again.                 :-
                                                                                    ‘f
                                                                               1$,3;__A-1:
Loginfrompt
                                                                                             .t.,
     The LoginPrompt property, a Boolean value, determines whether or not $
     the user of the application is required to provide username and pass-
     word information when a connection is created to a database. Though .‘f&’
     little used on a local desktop application, most remote database servers .‘,’
    I40 n         Part /l-The Delphi Dutabase Tools
/   _,,,“i   _.                                 ..

                        will require a login at the time of connection. The login can be       : .:;: ,
                        bypassed by setting this property value to False and providing the net- ‘?$
                        essary information in the Params property.                               t:-:<&a
                                                                                                    -~*P
                                                                                                      ;*;

                        Tip    The data in the Params property is not secure. If security con-
                              siderations are high for the application and the database, it is not
                              recommended that the login prompt be bypassed in this manner.
                              An alternative to this method is to utilize the OnLogin handler to
                              provide the login information from a more secure source.


                  Params
                        This property is similar to the Params property in other components we !*Q
                        have examined. It is a collection of string values that specify certain -.;.$,: ~‘ i
                        characteristics of the database connection. The Params property can
                        used to override the values contained in the BDE alias definition. The
                        parameters that can be set are determined by the alias and driver type:
                        specified.

                  Trans/so/ution
                        Transaction isolation is a specification of the interaction characteristics
                        between simultaneous transactions on the same tables. When transac-
                        tions occur against the same table at the same time, the value in the
                        TransIsolation property determines how much of the other transactio
                        changes are visible to your application’s transaction. There are three
                        settings for this property:
                        tiDirtyRead-Permits      the reading of uncommitted changes made to
                       the database by other transactions. Uncommitted transactions are sub-                !
                       ject to rollback at any time.
                                                                                                         .: /_/
                        tiReadCommitted-Permits          the reading of committed, or perma-
                        nent, changes to the database. At this point the other transaction has       --tj
                        physically changed the data. This is the default value for this propew          3
                                                                                                    :&
                        tiRepeatableRead-This          level of isolation ensures that once your  ,:-?’
                        application’s transaction has read the row, its view of that row will not I‘.
                        change. Subsequent changes to the row by other transactions are not         $
                                                                                                      , _:4i
                        visible.                                                                          .’
                        Isolation levels are supported to different degrees by the variety of
                        database servers. If the target server does not support the requested
                        isolation level, the BDE will use the next highest level of isolation.
                                                               ea Access with Delphi I   14 1


                   Somewhat of a letdown after examining all of those exciting properties,
                   there is only one event for the Database component. The OnLogin
                   event is fired when a login is required by a database. The handler that
                   you write provides an alternative method for passing login information
                   to the server using a more secure source for the username and pass-
                   word information.

 Putting ,the Database Comlt>onent to Work
                   A demonstration of the usefulness of the Database component requires
                   that you follow this project with an idea towards improvements that
                   can be made in your applications. We will start by creating a local alias
                   that is controlled from within the application rather than through the
                   BDE configuration files.
               I. Start a new application and save it. Name the unit file dbaseu.pas
                   and the project file dbasep.dpr.
               2. Place a Database component on the form. You may enter all of the
                  property values through the Object Inspector but Delphi, as it often
                  does, offers a better solution. Double-click on the Database component
                   [or right-click and select Database Editor) to invoke the Database Edi-
                  tor. This tool, shown in Figure 5.14, conveniently gathers all of the
                  necessary property editors for building the connection.




Figure 5. I#
The Datobose
Editor


               3. In this dialog, everything necessary for a BDE alias is going to be set.
                  Enter ProjectData in the Name field. This is the alias by which this
                  connection will be known. Because the settings for the database
142 n   Pa        ‘I-The   Delphi   Database   Tools


                  connection are going to be established through the component, skip
                  over the Alias Name field to Driver Name. Select STANDARD from
                  the list
                  The login parameters for each type of database can be set through the
                  Parameter Overrides field. To see the available parameters and their                 $
                  default values, click on the Defaults button. For simplicity purposes,              ‘i:
                  we are going to point this connection to the Demos directory by plac-            /_ f?f
                  ing the full path in the Path parameter. Set this value (for Delphi 4) to:      ‘:-‘*2
                                                                                                       I1
                  C:\Program          Files\Common Files\Borland Shared\Data
                  and click OK to establish the properties.
             5.   This local alias is now self contained within the application which now
                  has complete control over the settings. To test the connection, place a        _1
                  DBGrid, DataSource, and Table component on the form. Set the
                  DataSource property of the grid to DataSource and the DataSet
                  property of the DataSource to Tablel. In the Table1 DatabaseName        Z”‘. :
                  property, drop-down the list of aliases. You will find the ProjectData
                  alias is now a recognized alias through the BDE. Select this item.
                  Select the CLIENTS.DBF table for the TableName property.
         6.       Activate the Table component and the contents of the CLIENTS table ,:’.:
                  will be displayed in the grid.
                  As mentioned earlier, all of the alias control function is encapsulated       ‘:~ ~,~ j~3
                  within the Database component and controlled by the application.             ’ ,“<..<:>A &:z
                  Because all of the parameters and seie&ions were hard coded, we are                      .;j
                  not much further ahead in our quest for scalability by using the Data-
                  base component. The next steps will enhance the project to
                  demonstrate how the application can modify the values at run time.
                  This opens up the possibility of modifying the database connection
                  parameters in light of changing circumstances or modification of the
                  data store location by the user.
            Double-click on the Params property of the Database component to
             I.
            open up the String list editor. Delete the “PATH” line so that only two
            parameters remain. Click on OK to save the modifications.
         2. The database connection must be established as one of the initial
            startup steps of the application, so we will use the form’s OnCreate
            event to trigger our modifications. Create an OnCreate handler for
            Form1 and add the following code:
                  procedure    TForml.FormCreate(Sender:   TOBject);
                  begin
                    Databasel.Params.Add(PATH=C:\Program       Files\Common   Files\Borland
                  Shared\Data');
                                                  Chapter 5--Data Access with Delphi n                    t&&?$
     _         _*m----Lem.        ,           n,ll,..   .I      ”   .~,
                 Tablel.Open;
               end;

          3.   Execute the application again to see the results of this modification. In
               a distributed application, this method would allow your application to
               determine the path to the data source at run time, making the applica-
               tion infinitelv, more flexible.


The Session Component (TSession)
               The Session component, like the Database, is created by default to
               manage the database connections in any Delphi database application. A
               session represents all of the database connections, drivers, cursors, que-
               ries, etc., that are activated as part of an application. All database and                       I:i
               dataset components are associated with the automatically generated                                 i
               default session component, originally named Session. All sessions                                .,tq
               within an application are in turn automatically managed within a list
               called Sessions.                                                                                 .t~-s
               The default Session component effectively manages all of the tempo-                                .{
               rary and persistent database components and it is only in the most    ,                           ”:
               demanding circumstances that you will need to create supplementary                                 .;
               session components. Applications that would require supplemental ses-                               :
               sion components include those that create concurrent queries against
               the same dataset and multithreaded database applications.

Key \Voperties
            The default properties of the automatically generated Session compo-                                    .&
            nent are rarely, if ever, changed. The properties detailed below and
            listed in Figure 5.15 are required when you create a new session at
            design time or dynamically at run time.


                Active                False             This property specifies whether or not            the
                                                        session is active.

                AutoSessionName       False             Controls whether or not a unique         session
                                                        name is generated when additional -
                                                        sessions are instantiated.

                KeepConnection        True              Creates a persistent connection    even if the
                                                        dataset is closed.

                Name                  Session I         The internal name of this component.
                NetFileDir                              The directory that contains the BDE file
                                                        PDOXUSRS.NET         network   control    file.
144 n   Part II-The Delphi Database Tools




                PrivateDir                                The directory in which temporary files
                                                          associated with the session will be   stored.
                SessionName                               A unique identifier for this session as   it is
                                                          known to the application.

                SQLHourGlass           True               This determines whether or not the
                                                          mouse pointer changes to the SQL
                                                          hourglass during operations.

               1 Tae                                      This DroDertv   is for the develoDer’s use.

                                                                                                            (
               Fieure 5. I5 T h e TSession    combonent    DroDerties                                       ”


        Auto
               The Boolean property AutoSessionName determines whether or not a              :$
               unique session name is automatically generated. This property is          .“.
                                                                                       ..* .$
                                                                                       .ii
               designed to ensure that multithreaded applications always have unique 3;
               session names regardless of r-he number of sessions created. When this .:!
                                                                                         $
               property is True, SessionName cannot be set. Also, a Session compo-     ..j.
               nent cannot be added to a form or data module that already contains a
               Session component that has the AutoSessionName property set to True::.+!!

        NetFileDir

               The NetFileDir property specifies the location of the Paradox network
               control file PDOXUSRS.NET. Applications that work with Paradox files
               use this file to manage file sharing. All of the clients that share the
               same database must use the same network control file.

        PrivateDir

               Similar to the previous property PrivateDir specifies the directory in
               which the BDE will create its temporary files.


               Tip    The performance of an application can often be measurably
                     improved if installed on a network by specifying a local subdirec-
    aif
                     tory as the location for the PrivateDir value. This allows the
                     temporary files to be created on the fast local driver rather than
                     having to travel the network to the server.


TSession Events
               The Session component exposes two events, OnPassword and
               OnStartup. The OnPassword event is triggered when an attempt is
               made by the application to open a Paradox table and the BDE reports
                                    Chapter S-Data Access with Delphi
-       .1,,       _

        insufficient access rights. The event handler for this event must
        to this anomaly. OnStartup is triggered when a session is first a
        Any actions that need to occur during this process can be executed
        through the handler for this event.


    BatchMove Component (TBatchMove)
        Moving quantities of data records from one table to another is a corn-
        mon operation and Delphi offers two ways of performing this task. The -:,”
        first is to use the Table component’s BatchMove method. This method h-3
        an import process in which the calling table seeks to add records from “,:i:
                                                                                  &@A
                                                                                   ,
                                                                                 ..“;:
        another source. The method takes two parameters: the name of the
        source table from which the rows will be extracted and the mode. The --:z
        use of the BatchMove method is limited, however, in comparison to the ‘$
        functionality offered by the component that encapsulates all of Delphi’s .!?
        data movement capabilities, the BatchMove component.
        The BatchMove component expands on that method’s capabilities.
        Shared operations include:
        H Copying rows from one table to another
        w Updating rows in a target that match those in a source table.
        w Appending rows to a target table from the source table.
        n Deleting rows in the target that match those in the source.

        In addition, the BatchMove component offers the ability to dynamically                    ,$
        create a target table for the rows to be moved to. Data types can be                     f?hj
        explicitly mapped from the source to target, and exception handling is                  ,‘i
                                                                                                  9”
        improved with the ability to define key violation and problem tables                      4
        and the return of operational counts.

+operties
        There are a number of new properties introduced with this component.
        The comolete list of oublished properties is shown in Figure 5.16.   ..;


         AbortOnKeyViol      True            When True, the batch move         operation is
                                             cancelled immediately when a key
                                             violation or integrity error is                    :
                                             encountered. If False, the exception             ,: 2
                                                                                                . ;:
                                             records are written to an alternate     table.       .?
I46 H Part II-The De/phi Database Tools



             AbortOnProblem       True           When True, the batch move           operation is
                                                 cancelled immediately when data
                                                 requires truncation to fit into      the target
                                                 table. If False, the data is truncated and
                                                 the original problem rows are          written
                                                 to an alternate      table.

             ChangedTableName                    This specifies the name of the table to
                                                 which copies of all changed rows are
                                                 written during the batch move
                                                 ooeration.

             CommitCount                         The count of rows that are moved
                                                 before a     commit     ooeration occurs.

              Destination                        T h e name of the destination table.

             KeyViolTableName                    T h e name of the table to which key
                                                 violation rows will be written.

              Mappings                           This property     contains    the
                                                 column-to-column mapping              instructions
                                                 between the source and target tables.

              Mode                batAppend      Determines the operation that occurs
                                                 when a batch operation is executed.

              Name                Batchplove I   The internal name of this component.

              ProblemTableName                   The name of the table to which
                                                 problem      rows will be written.

             RecordCount                         Determines     the     maximum      number   of
                                                 records that are applied to the
                                                 destination table when the batch
                                                 operation     is executed.

             Source                              The name of the source table for the
                                                 batch     operation.
                                                 This property     IS   for the developer’s use.
             Tag
             Transliterate        True           Determines if character translation is            to
                                                 occur during the batch operation.

            Figure 5. I6 The TBatchMove component properties


       AbortOnKeyViol

            This Boolean property determines what actions are taken when a key
            violation or integrity exception occurs. A key violation occurs when a
            row is inserted or appended or so modified that an integrity error is
            generated against the primary key. Recall that the highest order rule of
            the primary key is that it cannot be duplicated. An attempt to break this
            rule generates a key violation.
                                          Chapter            S-Dota      Access   with Delphi n 147
_j .~.                    ___   .i_   ;-ricrii-ir-.-i-,(-.     .--   _                 “I . ft./amP

         Referential integrity exceptions are similar in nature. If an attempt to
         delete or modify a key value that is integral to a relationship between                          .,l
         two tables such that it destroys the relationship, an integrity exception                            ”
         will be raised. If the AbortOnKeyViol property value is set to True,                           .8:;“(
                                                                                                       _,.,
         immediate cancellation of the batch operation will result.                                      .,.,<t-;s
         The property can be set to False, giving the application an opportunity                      +*
         to gracefully handle such problems. The rows that were the cause of                             ‘$
                                                                                                          ,;
                                                                                                      i-g ”
         the exception are written to an alternate table, defined in the
         KeyViolTableName property. This is a table that will be dynamically cre-
         ated to contain all of the exception records. The application can expose F$
         these records to the user for correction or deletion. The property        .:
         KeyViolCount contains the count of records in this table.

AbortOnProblem

         The Boolean value in this property determines whether or not the batch
         operation is cancelled immediately when a data type problem is
         encountered. A type exception is a condition in which data from the
         source table must be truncated in order to successfully fit in the target
         column it is mapped to. When the AbortOnProblem property is True,
         the batch operation is cancelled immediately. Set the property to False
         to have the problem fields truncated to fit into the target columns.
         The ProblemTableName property contains the name of a table to which
         copies of the original, non-truncated records are copied when a prob-
         lem is encountered.                                                                                 ;j
                                                                                                             .‘:
                                                                                                           ;1
ChangedTableName                                                                                            2-
                                                                                                             %I
         The ChangedTableName property contains the name of a local Paradox                                  ’
         table used to hold copies of modified records. This dynamically created                              ‘j
         table saves the original data rows from a table that has been modified.                             ‘t
         The table will hold records that were either updated or deleted, giving                        :<
         rudimentary rollback ability to local tables that do not support the                             .;
         function.                                                                                         ;$
                                                                                                       * -‘i
                                                                                                           d
                                                                                                      .‘: f,
                                                                                                           ~-;
Moppings                                                                                                __$
                                                                                                         fb
                                                                                                           ‘3
         Implicit through its name, the Mappings property contains the column                            .?f
         mapping instructions from the source table to the target table. The
         BatchMove default action is to match columns based upon their posi-
         tion, column zero going to column zero, etc. The Mappings property
         overrides this default.
148   H P a r t II-The Delphi Database Tools

                                                                                                    .I
                 The string list used to define the mapping cannot be partially done or             ,.u
                 unexpected results may occur. To define a mapping from a column to a           .::$
                 same-named column in the target table, use the column name alone. To            ,i.i
                 map from two dissimilar named columns, use a complete equation in               :.J
                 the form Target-Column-Name = Source-Column-Name. Col-                               .;
                                                                                                     ‘,;
                 umns that are not defined in the mapping will be Null filled,                       li
                                                                                                     ::
                 necessitating the complete mapping definition in most cases.

        Mod ‘e
                 The BatchMove operations consist of five defined modes. The Mode                         ’
                 property contains the selected mode of operation. The modes are as                       :;J
                                                                                                     .‘I ‘..+*-2
                                                                                                           :“3z$
                 follows:                                                                              z*(. .rg
                 batAppend-This is the default mode. Rows from the source table                       -.i
                                                                                                 -~, :;
                 will be appended to an existing target table.                                       “-,:,
                                                                                                _‘:
                 batupdate-Rows in the existing target table will be replaced by                ;I
                 matching records from the source table. An index must exist that aliow~c;~xq
                 the matching to occur.
                 batAppendUpdate-Rows            in the existing target table will be
                 replaced by matching records in the source table. If no match is found _’
                 in the target table for a source record, the source record is appended w _
                 the target table.
                 batcopy-When this mode is selected, a new target is created based
                 on the structure of the source table. If a similarly named target exists, it
                 will be deleted and replaced by the new table.
                 batDelete-This mode deletes rows in the target table that match
                 those in the source table.
              The mode selected also affects the actions determined by the
              AbortOnKeyViol and AbortOnProblem properties and may be a consid-                       .- :g
                                                                                                          _
              eration in your choice of modes.


Putting   the BatchMove         Component        to   Work
              The BatchMove method and component are enormously useful addi-
              t-ions to your programming repertoire. For all of the additional
              functionality that the component offers, it is relatively simple to imple- : $
              ment. The Copy Machine project that is built in the following steps will ‘:?
              experiment with the functions in two segments. The first will simply          :
              demonstrate the component and its copy mode. After this has been              .’
              done, we will work with some of the error handling abilities of                  3
                                                                                            <$j$
              BatchMove.                                                                   . 3. 63
                                                                                               ;iii
                                          Chupter S-Date Access with Delphi H 149
            .j   ,_    .

            I. Start a new project and place two DataSource, Table, and Grid compo-
               nents on the form similar to the form shown in Figure 5.17. Add a
               BatchMove component, a BitBtn, and a Button to round out the form.




    g4, .
“T, k :




            2. Set the BitBtn component’s Kind property to bkclose.
            3. Point DBGridl to the DataSource control and point the DataSource
               DataSet property to Tablel, completing the troika. For the Table com-
               ponent, set the DatabaseName property to DBDEMOS and the
               TableName property to COUNTRY.DB,
            4. Point DBGrid2 to DataSource and set the DataSource’s DataSet prop-
               erty to Table2. The Table2 component’s DatabaseName property will
               also be set to DBDEMOS. Since we are going to creare a new table by
               the batch operation, the name that we will provide for the TableName
               property will not be selected from the list. Add the name COPIES.DB
               to the property. Remember that this table will be a persistent struc-
               ture, remaining on disk after the project is closed.
            5. Selecr the BatchMove component. Set the Source property to
               Tablel. This will be the table from which the rows are going to be
               copied. The destination for records involved in the barch operation
               will be defined in the Destination property. In this example, we’ll set
                this value to Table2. Set the Mode property to batCopy
            6. The BatchMove component is not activated. Instead, it is executed
               using the Execute method of the component. Since the user of the
                application usually initiates this process, we will duplicate this
                through the button’s OnClick event handler. Enter the following code
                for the handler to perform the two-part process. First, it executes the
150 n Pm-t    IL-The Delphi Database Tools


               BatchMove      process and then opens the new table to display it in the                   -.:
               grid.                                                                                       ,.$I
                                                                                                           ..’
               procedure    TForml.ButtonClick(Sender:          TObject);
               begin
                 BatchMovel.Execute;
                 Table2.Active   := True;
               end;

         7.   Compile and execute the program. Click once on the Copy button to                           -2
              copy all of the rows from COUNTRY.DB to COPIES.DB.                                             ‘3
                                                                                                           ‘,:
              The BatchMove component has error handling capabilities built in that ”
              the BatchMove method does not, making it much more attractive for                        ;‘\-z;
              use. In the next set of steps we are going to modify the copier project t-6:$
              raise an exception and let the application deal with it. Using the                     L;.-G
              COPIES.DB table just built, we are going to force key violations to                ‘I’
              occur by appending the same set of records to the table. Since the            .
              Name column is the primary key for the COUNTRY table, each time a
              similar value is tried, a key violation error will occur. A table is de
              to hold them, KEYVIOLS.DB, and this table is displayed for the use
              review. In a production application, you could allow the user to mo&
              these records and you could repeat the batch process to cleanly hand
                                                                                        ,>- ‘.’
              the exception situation.                                                        ‘.
         I. Add a Label component to the right of the Copy button.
        2.    Select the BatchMove component. Change the value of the
              AbortOnKeyViol property to False. Enter the name KEYVIOLS.DB to
              the KeyViolTableName   property. Finally, change the Mode property to                               ?9
              batAppend.                                                                                           .k

        3.    Modify the Copy button’s OnClick event handler as follows:
              procedure    TForml.ButtonlClick(Sender:  TObject);
              begin
                TableZ.Close;
                Table2.AddIndex('NameIdx',     'Name', [ixprimary]);

                BatchMovel.Execute;
                Table2.Active   := True;

                if BatchMovel.KeyViolCount <> 0 then '
                begin
                  MessageDlg('There    were Key violat ions in the move.',
                                mtError, LmbOkl , 0) ;
                  Labell.Caption := Concat(
                  InstToStr(BatchMovel.KeyViolCount    ),
                                    Chapter S--Data Access with Delphi n           1 sf


           'Key violations');
          Table2.Close;                                                                            5
          TableZ.TableName   := BatchMovel.KeyViolTableName;
          TableZ.Open;
          DBGrid2.Color := clAqua;
                                                                                                .
                                                                                            I .,."
       end;
     end;                                                                                         -. 'c
                                                                                                  "i
4.   Compile and execute the application. When the key violations occur,                       +,
     you will be notified by a message dialog. The key violations table will                   $J
     be displayed in the second grid, changing its background color to                       $$y
     announce the change.                                                                     3
                                                                                              $3


IdaLteSQL Component (TUpdateSQL)
     We discussed cached updates earlier in the context of the                                -“a
     CachedUpdates property published by the dataset components. When                         .:;
                                                                                              &
     cached updates are enabled, data is retrieved from a local or remote
     table and stored in local memory All operations that occur against the                :
     data work against this local dataset. When the modifications are writ-               1
     ten to the remote dataset, they are written as a large block rather than                :a
     individually. This strategy serves to reduce transaction times and mini-             Jj
     mize network traffic through the proximity of the local data.
     There are also caveats involved in the use of cached data that the
     developer needs to be aware of when making the design decision to
     implement this strategy First, it must be remembered that since the      li’ ,;$,G
     user is working on a dataset that is local and viewed only by them,
     changes that they are making to their dataset are not seen by other           .I ”
     concurrent users until they are committed. This issue also appears in              .’
     reverse; the local dataset is not refreshed when other users update the              $
     remote source dataset, thereby rendering the local dataset obsolete.
     Additional consideration must be given to the committing of modifica- ::i
                                                                                  11
                                                                                  .a*
     tions in parent-child relationships due to referential integrity            q?$$
     constraints.                                                                    ‘$j
                                                                                     “‘:-
                                                                                      ‘:
     Cached updates are enabled through the aforementioned property                  ,‘,
     being set to True. When cached updates are enabled, a read-only query ,_,.
     gathers enough data as needed to display and places these records into
     local memory. The local dataset is updated by periodic “fetches” from              -:~
     the remote dataset as needed by the user scrolling through the data. All ,$
     updates are posted to the cached data until complete and then the                    -:
     entire cache of modifications is written back to the remote dataset in a              .:
     single transaction.                                                                 $*
                                                                                          ‘i
in a read-only dataset that can be modified on a local level but cannot
be easily written back to the original relations. To perform this opera-
tion you must use an update object, the UpdateSQL component. As you
can see from reviewing the property list in Figure 5.18, this is a simple
component that belies its power.




Figure 5. I8 The TUpdateSQL component properties

UpdateSQL uses SQL statements to update the remote datasets. Its
usage requires one instance of the control for each table involved in the
original  transaction.
The UpdateSQL component encapsulates three Query controls, one
each for the purposes of insert, update, and delete operations. Each
associated property contains the SQL statement used to process the
appropriate query against the remote dataset. The SQL statements
allowed through the UpdateSQL control support extended parameter
binding and syntax. These properties are string lists and can be modi-
fied at design time or run time. To determine which of the three
properties to use in the update process, the control relies on the
UpdateKind parameter that is automatically generated for each record’s
update.
UpdateSQL and the process of updating cached data are very particular.
Only the type of SQL statement intimated by the property name should
be entered into that property, i.e., only a DELETE statement should be
entered for the DeleteSQL property. Also, all of the statements in the
properties for an instance of UpdateSQL must reference the same table,
as the control is associated on a one-to-one basis with a DataSet object.
Building the statements for each of the properties is simplified through
the tools that Delphi offers. As an example of this process, start a new
application in Delphi and add a Query and an UpdateSQL component
to the form. Select the Query component and set the DatabaseName to
                                                 w S-Data Access with Delphi II ! 53
                                                                           _

              IBLocal   (be sure that the LIBS is running) and the CachedUpdates
              value to True. Set the SQL property to:
              SELECT * FROM EMPLOYEE

              ullll 1_11+ .,Yu-.-vbject property to UpdateSQLl. This last property
              links the dataset object to the specific update object.
              Right-click on the UpdateSQL control to invoke the UpdateSQL Editor.
              This tool builds the SQL statements for the properties encapsulated by
              the control. The editor, as shown in Figure 5.19, will have filled in the
              default values as determined by the associations made earlier.




Figure 5. I
The
UpdateSQI
Editor

              The options default on the side of including all fields and indexes tvhen
              invoked but you can pare them down to the desired selections. Click on
              the Generate SQL button to automate the creation of the SQL that will
              be used for the update process. This statement may not be complete
              but provides a good framework to start from. Clicking on OK saves the
              SQL statement to the property rhat was selected, the default sratement
              going to the ModifySQL property This process is repeated for each of
              the types of operations that will be handled. The ensuing code is exe-
              cuted during the OnRecordUpdate event.


The NestedTable Component (TNestedTable)
              The records in a nested detail set are represented in Delphi by nested
              datasets accessed by the TNestedTable component. This type of data is
              found in Oracle 8 relations only and cannot be created through Delphi.
              The field’s NestedDataSet property contains a reference to the nested
              dataset. BDE functionality is extended to the access and manipulation
 154 W Port l/-The Delphi Database Tools
.;*. ““11/            . il ‘_,                                         )I   Ila..m*~*\

             of the nested table data. The nested table then has most of the func-
                                                                                            ;$fJ
             tionality of a single table component recognizing the nested nature of
             the data.                                                                      a3
                                                                                              ;,-
             Figure 5.20 details the properties of this control.


                                                   etermines   w




             Figure 5.20 The TNestedToble   component properties
                                                                                                3
                                                                                         ,‘ ( -**
                                                                                             “$3
                                                                                            .’
             Putting this control to work requires an in-depth knowledge of the Ora-
             cle data structure that you are addressing. The DataSetField property of     ‘2
             the component is the determinant of the field that contains the nested            ‘Y
             data. A DataSource component is directed at the NestedTable control to            -?$
             pass the result set to the display controls of the application.                     .q
                                                                                                  25

Summary
             This chapter has covered all of the components that are available to the
             developer on the Data Access tab of the VCL. These controls serve the
             purpose of connecting to the datasets in various ways, from straight
             table display with the Table component to complex multiple join result
             sets from the Query control. A minimum of one of these controls, and
             more likely two or more, is necessary in every database application
             developed using Delphi and the Borland Database Engine.


Looking Forward
             The next chapter is going to provide the same coverage to the data con-
             trols. These components provide the visual interface components that
             the user sees and interacts with in using the database application.
      Included in This Chapter:
      n   Common Properties n The DBGrid Component
      n   The DBNavigator Component n The DBImage Com-
      ponent n The DBMemo Component n The DBText
      Component n The DBEdit Component n The
      DBCheckBox    Component n The DBRichEdit Component
      n The DBListBox Component n The DBComboBox Com-
      ponent n The DBLookupListBox and DBLookupCombo-
      Box Components n The DBRadioGroup Component
      n The DBCtrlGrid Component




         elphi’s data-aware components are the other half of the RDAD pic-
    ture introduced in Chapter 5 where we put the data access components
    to work. The data-aware controls are TControl descendants that have
    been modified to display and manipulate the contents of associated        -’
    dataset fields. Delphi includes a wide variety of tools to display every- !
    thing from a single row of data that represents an individual record to ”

    DBGrid control.
    Whatever your need, Delphi includes a control on the Data Controls tab
    to support every kind of data accessible through the Borland Database
    Engine. Different from the components on the Data Access tab which
    perform their tasks in the background never seen by the application’s
    users, the data-aware components are the interface building blocks for
    the program. Each of the controls is a visual component, and the range
    of properties, events, and methods is vastly expanded because of this. .I


k
I56       W Part II-The Delphi Database Tools
.F_ ..‘                                                                                            *      A.__

                  You should find working with the data-aware components to build a
                  functional database program a relatively familiar exercise. The controls
                  essentially mirror their non-database counterparts in functionality, dif-
                  fering only in the source of their values. As we saw in the previous
                  chapter, each control will be associated with a dataset object; this asso-
                  ciation defines its source of data through the interface of a DataSource
                  control.


Common Properties
                  Before diving into an examination of the individual components, the
                  common elements of all of the data-aware controls will receive our
                  attention. The table in Figure 6.1 summarizes the common published
                  properties that carry the same contextual meaning between all of the
                  components. The majority of these are familiar to experienced Delphi
                  developers because they have the same usage and purpose when used
                  with the non-data interface controls. Additionall!; there is a small sub-
                  set of properties that is unique to the data-aware versions of these
                  controls.


                                                      This TControl setting determines how a
                                                      control lines up to Its container or parent
                                                      control.
                  Anchors      __t                    Determnes        how a control   IS   anchored to Its
                                                      parent      control Four- options      Top, Bottom.
                                                      Left. Rreht
                                 -4
                  BiDrMode            bdLeftToRrgh:   This property speclfles     the BiDlrectional
                                                      mode for the contr-ol, whrch          determines the
                                                      r-eadrng    order for text and the placement of
                                                      the scroll bar
                                 -t
                  Ctl3D               True            If True, the assoclared    controt WIII have a
                                                      beveled, three-drmenslonal          appearance
                                                      When False, the controt will have a fiat
                                                      appearance See ParentCtl3D
                                                             __---
                  Cursor              crDefault       Determines wtllch cursor Image          IS   drsplayed
                                                      when It passes over this control.
                  DataFIeld                           This property assocrates     the control with a
                                                      single     field fron the dataset connected to
                                                      the      DataSource   component.
                  DataSource                          This connects the data-aware controls to a
                                                      specified dataset object.
                 :hap lter 6-l    bhi’s Data-Aware Components I


                 m
DragCursor       crDrag           Determines which cursor image is displa) .~~~
                                  when the control is being dragged.
DragKind         DkDrag           This specifies the reason for the control
                                                   docking or normal draggim.2.:
DragMode         DmManual         Determines the drag and drop behavior OF


Enabled          True


Font                              This set of properties controls the attribut&.j
                                  of text displayed by the control.
Height                           1The disolav heipht of the comoonent.
HelpContext                       This value is utilized to determine what
                                  page in online help is displayed when the
                                  application’s user presses the F I key.
Hint                              The text of the fly-over hint message that 6
                                  displayed for this control.                  c$
ImeMode          ImDontCare       This property determines the behavior of ::
                                  the input Method Editor, a conversion tool


ImeName

                                  language   characters.
Name                              The default internal name for this
                                  component.
ParentBiDiMode   True             Determines whether or not this control
                                  uses its parent’s BiDiMode setting.
ParentColor      False            Determines whether or not the component-’
                                  will use the same color properties as its
                                  parent.
ParentCtl3D      True             This property determines whether or not
                                  the control will use its parent’s Ctl3D
                                  property setting.
ParentFont       True             Determines where a control will look for its -.:z
                                                                                  ;
                                  font information.                            I Jf
ParentShowHint   True             When True, the control will use the
                                  ShowHint property setting of its parent. If
                                  False, the control’s behavior is determined
                                  by its own setting.
PopupMenu                         Specifies a context menu to associate with        ‘. .!
                                  this control.
               ReadOnly           False        This property determines whether or no-“*%     I:,~~
                                               the user can modify the data contents of the       5:p
                                               control.
                                                                                            _ -,,a;
              ShowHint            False        Determines whether or not flv-over helo is         %

              TabOrder                         A numeric value specifying the control’s tab
                                               position in its parent’s TabOrder list.
              Tag                              This property is for the developer’s use.
              Visible
                              I   True         This property determines
                                               visibilitv at run time.
                                                                           the   component’s
                                                                                               1
                                                                                                    .:‘;
                                                                                                   “,.j

              Width                            This specifies the component’s width for
                                               display purposes.

             Figure 6. I Common Properties among the data-aware components


Key Properties
           The key properties shared among the data-aware controls are those
           that define the database interface and manipulative abilities of the
           component. Without these properties, the components would have abil- :.J$
           ities on the same level as the controls from which thev are subclassed.


             The value in the DataField property specifies the field in the dataset
             from which the control derives and to which it sends its data. The
             TField objects associated with the dataset determined through the
             DataSource property determine the possible values accepted by the
             component. The property may refer to a field from the table referenced
             or to a persistent lookup or calculated field defined through the Fields
             Editor.

       DataSource

             The DataSource property binds the data-aware control to the specified
             dataset object. The DataSource component handles all of the naviga-
             tion and data access requirements of the database, leaving the interface
             components free to concentrate on displaying and manipulating the



             This property enables or blocks the user’s ability to modify the underly-
             ing dataset. When ReadOnly is set to True, the data-aware controls will
                                 Chapter 6-Delphi’s            Data-Aware Components        n   I!$@
                                                         i,.               ^I e-*__ls_18/           ‘.q
                                                                                                          ‘2
                                                                                                          -*
           display the data from the dataset but will not allow it to enter the Edit ‘z
           state.


The DBGrid Component
                                                                                        4
           The DBGrid component is a workhorse among the data-aware controls. ‘;
           Using a DBGrid, your application can display or manipulate a dataset in
           a familiar table-like form. The data is displayed and can be edited in an 1:
           easily discernable rows-and-columns format. The columns represent the
           field definitions from the dataset with each row representing an indi-     :
           vidual record.
           Without any modification to its properties, the appearance of data in                      .
           the DBGrid component is largely defined by the properties of the fields
           in the underlying dataset. By default, the headings for the columns and                        I,
           the order of their presentation matches those of the table. Changes to
           the properties in the dataset are immediately propagated to the grid.          -:
           The grid does not display Field objects in the dataset whose Visible        ‘4
           property is set to False.                                                     4
                                                                                       :‘”i
           The columns in the grid are dynamic and can be added, deleted, and             1
           rearranged at design or run time. The fact that must always be remem- j
           bered is that the controlling properties for these columns do not belong 1-i
           to the grid itself but rather to the field objects contained in the dataset    *’ iQ
           that the grid is composed of. Dynamic grid columns, for example, exist --::?J
           only so long as the field upon which they are based exists. When the              I.$:/
           dataset is closed, the column is destroyed.                                         i
           Because the field objects determine the makeup of the DBGrid, the con- . .,$
           struction of the grid enjoys a great measure of flexibility. The same
           display grid in an application can display a local database table at one              :;
           moment, then be completely redrawn seconds later to display the                     g
           results of a query. The new dataset and its properties will immediately             ,.j-
           replace all of the properties of the former table. This change is as sim-
           ple as pointing the DBGrid to a different DataSource control.                      .:*,“-.:.($.

Key Properties
                                                                                                                  :i
           There are a number of properties that control the display elements of                                ‘r
           the DBGrid component, some of which will make their only appearance                                 -:>
           in this context.
The Columns property is an indexed collection of TColumn objects,           .1X9
This property is used to view or set the display attributes or field bind- ,“j
ings that make up the column collection. The columns can be modifid.             5i
at design time by using the Columns editor or through the application .:3
at run time. This object is of type TDBGridColumns, a container to h& :$
the collection of TColumns. DBGridColumns can return the number of ‘.j$
columns, or index numbers, for the collection. Each column has its ow$‘,;i$ 1
set of properties, listed in the table in Figure 6.2.                         <a‘$
                                                                                &

                     m
 Alignment           taLeftJustify


 ButtonStyle         cbsAuto            e value of this property det
                                     and how a value can be selected        if



 Color               clWlndow

 DropDownRows                        This property determines   the number of ,
                                     rows displayed in a drop-down list    if the
                                     column is connected to a lookup table or
                                     has values in its PickList property. Action


                                     property.
 Expanded            False


 FieldName


 Font


 ImeMode             imDontCare      This property specifies the Input      Method


 ImeName                             Specifies the name of the input     Method
                                     Editor.

 PickList                            A string list that determines the   values that
                                     can be selected for the column.

 PopupMenu                           This property specifies the   popup me
                                     associated with this column.

 ReadOnly            False           Determines whether or not the data          in the
                                     column can be edited.

 Title                               Specifies a TColumnTitle object        that
                                     contains the attributes of the title    for the
                                     column.
                                 Chapter 6--Delphi’s       Data-Aware Components W 16
.._. I. “_jI   ,,. _ _      -i   _‘   .*,,                                 (weeL_(_ - - -



           Visible                True               Determines whether or not the          column is!-;‘
                                                     visible in the grid.
         1W i d t h                                  Specifies the display width of the      column.

         Figure 6.2 TColumn properties accessible through the Columns editor


Options

      The Options property is a set of values that specify a wide range of
       behavioral and display attributes for the DBGrid control. The set can bc
       made up of different combinations of the elements shown in Figure 6.2
       Each of the options is a Boolean value that turns on or off the various
       grid options.


           dgEditing                     The grid can be used for editing.
           dgAlwaysShowEditor            The grid is always prepared for editing. Must        be used
                                         in coniunction   with the deEditine settine.
           dgTitles                      If this option is included, titles are displayed    at the top
                                         of the column.
           dglndicator                   This option causes a pointer to appear in the first
                                         column to indicate the current record.
           dgColumnResize                This allows columns to be resized or moved.

           dgColLins                     This option causes lines to be drawn between the
                                         columns of the grid.

           dgRowLines                    This option causes lines to be drawn between the
                                         rows of the grid.
           dgTabs                        This enables the Tab and Shift+Tab keys for
                                         navigation in the nrid
           dgRowSelect                   This allows the entire row to be selected in addition
                                         to individual cells. If this option is included, dgEditing
                                         and deAlwavsShowEditor       are @ored.

          dgAlwaysShowSelection          A focus rectangle is displayed on the current cell even
                                         w h e n the focus has transferred to another        control.
          dgConfirmDelete                This option causes confirmation of a deletion in the
                                         et-id.
          dgCancelOnExit                 This causes inserted records to which no
                                         modifications were made to be discarded when the
                                         user exits the et-id.
          dgMultiSelect                  This option allows multiple rows to be selected at
                                         one time.

        Figure 6.3 Options values for the DBGrid component
I62 n    Part II-The   Delphi Database Tools


DBGrid Events
                                                                                ”
           The events exposed by the DBGrid focus on the movement of the cursof -:
           within the grid and the presentation of the grid by the application. -$
                                                                                  : .s‘$,;
        OnCeKlick                                                                  ~‘>

              This event fires when the mouse click is released in a cell of the grid.         -$
                                                                                                  .-i
                                                                                               ..
        OnColEnter                                                                                T>

              The OnColEnter event fires when the focus shifts into a new cell in the ’ .:.I
              grid including navigation by keyboard or mouse click. OnColExit events .*:;
              fire when the focus is in the process of leaving the cell.

        OnColumnMoved

              This event fires when dragging with the mouse moves a column.
              OnColumnMoved occurs just after the column has been moved.                   ,

        OnDrawColumnCell
              This event is provided to give the application an opportunity to repaint
              a cell.

        OnEdit6uttonC/ick

              The OnEditButtonClick   event fires when the ellipses button on a
              drop-down or pick list column is pressed.

        OnTitleClick
              This event is fired when the user clicks on the title of a column.
              We will put the DBGrid component to work in a moment after we dis-
              cuss another control that is often found in close proximity, the
              DBNavigator. We have seen simple usage examples in the previous
              chapters, so we will attempt something a little more ambitious with
              DBGrid in this chapter.


The DBNavigator Component
              The DBNavigator control gives the users of your application a simple              :h
              and friendly tool for navigating through the datasets used by your
              application. In addition to its navigational abilities, the DBNavigator            ,,i
              provides a wealth of tools for manipulating the data by performing               ‘- 2
              insertions, deletions, confirmation of the changes, or cancellation of the        .‘+
                              Chapter    6--Delphi’s   Data-Aware   Components m
                                                                        --- .~*s.nm,I*~

         transaction. The toolbar takes the familiar shape of a VCR button set,
         giving the user visual clues as to the actions of the navigation buttons.
         Using the button set the user is able to skip to the first or last record iti”
         a dataset, move to the next or previous record, insert new rows, de&&j
         existing rows, post changes, or cancel changes. The buttons that are *:$$
         visible to the user can be controlled through the manipulation of the “‘“4
         property settings. The DBNavigator component is especially useful                 ‘j
         when used in conjunction with a DBGrid control to make navigation I
         and editing fast and simple.
                                                                                        ,‘3
                                                                                          <
                                                                                          :*
+operties
      The majority of the properties for the DBNavigator control are listed &$
      among the common properties. There are two specifically that are of ‘:{
      interest to the developer.

  ConfirmDelete
         The ConfirmDelete property is provided to the application developer to-2
         help prevent the careless deletion of rows of data. When ConfirmDeleM
         is True, a message box is displayed to the user asking for confirmation
         of the pending deletion. If the user chooses the OK button, the deletion
         action commences; otherwise it is cancelled. If ConfirmDelete is set to .Jpj   .-
         False, no message box is displayed and the deletion is immediately          .i,:s
                                                                                      ”1
         allowed.                                                                ‘a-2 .?
                                                                                    ,
                                                                                           ;i
     ’   Tip    Leave the ConfirmDelete property set to True for all of your               .:
               applications to prevent data loss through careless actions. The
               only time that users will generally resent the message box is
               when they are deleting large bodies of data; for this task you can
               easily provide an alternate solution.


  VisibleButtons

         This property controls which buttons are visible to the user on the            -t
         DBNavigator and can be set at design time or run time. VisibleButtons      -;$
         is a set of TNavigateBtn objects and removing one from the set has the ..:,.:I
         effect of hiding the button. The buttons available for the VisibleButtons /,:y
         set and their associated actions are:
         First          Go to the first record.
         Prior          Go to the previous record.
         Next           Go to the next record.
I64 n Pm-t II-The Delphi Database Tools

                Last         Go to the last record.
                Insert       Insert a blank record.                                 :;$g
                Delete       Delete the current record.                                -,I,;:$<-
                                                                                         : .x
                Edit         Place the current record in Edit mode.                        ::;
                                                                                           1;G.
                Post         Post the updates to the current data.                           .e
                                                                                     !&
                Cancel       Cancel the transaction.                                 .qj
                                                                                     ::*$
                Refresh      Refresh the data displayed.                            IQ
                                                                                    ;:+q
                                                                                       pj
DBNavigator Events
                                                                                     . ~,1:xJ
           Two events are native to the DBNavigator class, both allowing you to ;J
           trap the push of one of its buttons and initiate any special processing      $)
           that you want to implement.                                               -y
                                                                                    .‘2
                                                                                   24
         BeforeAction
                The BeforeAction fires when a button on the navigator is clicked but
                immediately before the default action for that particular button is
                executed.


                                                                                       4
               OnClick is fired when any type of click event occurs. This includes a .‘-j
               mouse click, the spacebar being pressed when the navigator has focus, ‘ij
               or the BtnClick method being called. The default action for the associ- : ;‘~I.;
               ated button will be executed unless overridden by the event handler. ‘ii”“
               One important item to remember when designing your application           .;.
               around the DBNavigator is that the dataset object. not the DBNavigator,i$
               encapsulates the dataset’s navigation capabilities. The navigation but- :z
               tons only initiate the execution of the dataset’s methods. If the         ,%
               application needs to interrupt or manipulate these abilities, it should .-j
               point directly to the dataset.                                          _&‘_ \.b

Putting DBGrid and DBNavigator to Work
               The project that we are going to build will be expanded throughout th
               chapter, adding capabilities as appropriate to the components being dli;-.
               cussed. The program is a tool for the Portland Office of Theaters and j;
               Arenas which is going to be managing the Blue Sky Sporting Festival. .:.
               We will utilize two tables provided by Borland in the DBDEMOS data- I :r,
               base in the development of the application. We are going to begin          :x
               simply, placing the tables into grids and associating the navigation
               buttons.
                                      hi’s Duta-Awure       Components I 165
                                                        ,       ,,

   The two tables used throughout the program are going to be linl red in =
                                                                       L1. -
   manner that was not in Borland’s original design. We want to set up a
   master-detail relationship with the VENUESDB table being the master.
   In order to accomplish this, a new index needs to be added to the
   EVENTS.DB table.
I. Start the Database Desktop application and select Tools 1 Utilities (
   Restructure from the menu. At the bottom of the Select File dialog,
   set the Alias to DBDEMOS. Select the EVENTS table from the files
   listed by double-clicking on the filename.
2. The Restructure Paradox dialog will display the schema for the
   EVENTS table. In the Table Properties drop-down box, select Second-
   ary Indexes, You will find that one index, Date-Time, has already
   been created for the table. Click on the Define button.
3. The Define Secondary Index dialog will be displayed as shown in Fig-
   ure 6.4. Select the VenueNo field and click on the Add arrow button.




   The field name will be displayed in the Indexed Fields list and none of
   the options at the bottom of the dialog will be modified. It is very
   important that the Maintained check box remain checked. Maintained
   indexes are kept up to date automatically by the BDE and should be
   your standard choice unless the overhead requirements are too severe.
   Click on OK to define the index. You will be asked for a name for the
   new index; name it VenueIdx.
4. Click Save on the Restructure dialog to save your changes to the file.
   With the new index defined, the tables have been prepared for use in
   the sample application. Thinking about the design of the program and
Pa   II-The Delphi Database Tools


      other typical Delphi database applications leads to the need to discuss Se?4
      an important tool that falls outside of either of the VCL component              ‘I:$
     tabs, the Data Module. The Data Module is a non-visual container fom :.g
     that serves one very specific purpose: It provides a common access                         i:
     point to the dataset objects from all of the forms in a project. The alter- si
     native to using the Data Module approach would be to place multiple ,~;,lIh,r.Sti
     sets of dataset objects on each of the forms of an application. All of the A:, *
     objects would required individual configuration settings, and the appli- .c$
     cation would be required to provide the code necessary to maintain the ~~5
     relationships, record pointers, etc., in the transference between forms. .-:,;
                                                                                       >:,t -:
     The Data Module has uses beyond simply sharing database tables. Any ‘Gi
     Delphi object, with the exception of those that descend from TControl, ::l           C.g,‘. ;
     can be placed on a data module and become available to all of the                      $3
     forms in the program. This carte blanche should be used wisely with               :$
     consideration for the actions of the components themselves prior to .) :
     placing such things as your main menu in the module.
                                                                                  / -ji
     Adding a data module to an application is done through menu selec-
     tions. Select File 1New and pick the Data Module from the New Q&                    tab




     the repository A small container form is added to the application with :
     three properties:
     n Name
     H OldCreateOrder                                                                          ,“.
     n Tag
                                                                                             >
     OldCreateOrder is the only unfamiliar property surfaced. Its purpose is ,-,
     to control the firing of the module’s OnCreate and OnDestroy events for .$:i
     backward compatibility.                                                                i
                                                                                         3%
     For all of its benefits, there are also times in which it is not appropriate, .j
     to use the data module. The first consideration is a situation that does ,‘z$
     not require the sharing of components between forms. The overhead ot,:$
     adding the module is wasted in this situation; place the dataset objects Y.7
     directly on the form that needs them. Also place the datasets directly                 1:
     when a form has a completely unique view of the data not used in any
     other place in the application such as report generation tables and spe-
     cial views.
     The Venue Manager will use a data module so that the datasets that
     are going to work with can be set up once and shared among all oft
     forms used in the demonstration. Clear naming rules for the dataset ‘.
     objects become much more important in this type of situation because-:
     of the new distance between the datasets and their interface controls, ‘2::
                                                                             ,.“s
                                                                                     x

                       Chapter    6-Delphi’s   Data-Aware   Components        n I&%
      ‘                                                                  __    ..v.-&
                                                                                     :.
                                                                            -i-
I.   Start a new Delphi application and add a Data Module to the project. i :$$
     Place a Table and DataSource component on the data module form. A!_. . 1
     Name the Table control VENUE-TBL and set the DatabaseName prop- :i$
                                                                               .-p$
     erty to DBDEMOS. The TableName property should be set to                   yqd
     VENUES.DB. Activate the table by setting the Active property to            i “4
     True. Save the project; the form unit will be named VenMgrU.pas,        .~f’
     the data module unit will be dModU.pas, and the project will be          .t
     VenMgr.dpr.
2. Select the DataSource component and set the DataSet property to 1.
   VENUE-TBL. Name the DataSource VENUE-SRC. To make the con- ‘”
    tents of the data module visible to the other forms in an application        I_ $4gs
    you must create an association between the objects. Select Form1             -‘I!#
    and then File 1Use Unit from the menu. Choose the dModU unit and “g$
    click OK. Alternately, you could simply type the Uses declaration in the
    Implementation section of your form’s unit.
3. Add a DBNavigator component to the form. If you want to position
    this control so that the resizing of the form will not affect it, use the         .
    Align property to lock the position. Select alTop as the value for the
    property; the navigator will move to the top of the form and size itself
    to match the client area. To associate the control with a dataset object,
    set the DataSource property to DataModulel.VENUE-SRC. To
    familiarize your users with each button’s function, set the ShowHint
    property to True. This will provide fly-over hints to the user as they       _’ ’
    move their mouse across the navigator bar.
4. Now add a DBGrid control to the form. This component also offers an ,>..g2     -,,j
    alignment property; we will set the Align property for the grid to          .$j
    alTop as well. It will fit itself in just below the navigator and will    :
    receive the same benefits of using Align as discussed for the navigator.:
    Point the DataSource property to DataModulel.VENUE-SRC                to
    make the table connection.
     Execute the application and use the navigator to move around in the
     table. Note that the movements controlled by the navigator are row
     and table centered. Movement between the fields and columns is
     driven by the cursor control keys.
     Before adding the detail end of the relationship there are a couple of
     items that need attention on the Venues grid. The last two columns in                 ‘;:i
     the VENUES.DB table are of type BLOB and MEMO, data types that do                        i.
     not lend themselves to display in a DBGrid. For the time being, we will                 ;
     hide these columns to give the table a neater appearance. Additionally,              ;‘:
     we will unlink the table from the top of the form and adjust its width to              -1
     match the displayed columns.                                                            ,4
                                                                                            .wa
   II-The    De/phi Database Tools


     Select DBGridl and set the Align property to alNone. This will let
    you move the table down a bit from the DBNavigator bar.
    Right-click on the DBGrid and invoke the Columns Editor from the                 .:j
    context menu. The Columns Editor is similar to the Fields editor that               :
    we have worked with previously. The first action that needs to occur is .i.lj
    to add the columns that we want to manipulate to the list. Click on the -i
    Add All Fields button to perform this transfer. You will notice that the
    underlying properties of the columns are displayed in the Object
    Inspector. Select the Remarks column and ser its Visible property to
    False. The column will disappear from the DBGrid but remain unaf-
    fected within the dataset. Do the same thing for the Venue-Map
    column.
3. Before making the modifications suggested in the next step, the cre-               ‘;
    ation order of the forms should be reviewed. Select Project 1Optiom
    from the menu. As it stands, Form1 will be created before
    DataModulel. Since we are going to make use of the form’s OnCreate: ‘:
    handler and it requires knowledge of the internals of the datasets, w&j
    need to have the data module created first. To move DataModule up i L
    in the order, select it and drag the list entry above Form1 and release:?
    it. Select OK to save the changes.                                       ygj
4 . After hiding the columns, the table is left in a rather odd-looking grid
    with excessive white space on the right-hand side. If we add a little          *‘:
                                                                               :+.cs;j_
    code to the project, the DBGrid component can size itself to accommW.i.I,. : 1..
    date the columns that are displayed, regardless of changes to that                  :$I
    number. Add the following code to the form’s OnCreate handler:                        .$
                                                                                          .:j
    procedure TForml.FormCreate(Sender:  TObject);
    var
      tablewidth : integer;
                    : integer;
    begin
      tablewidth := 0;
      with OBGridl do
      begin
         for x := 0 to (Columns.Count - 1) do
         begin
           tablewidth := tablewidth + Columns[x].Width;
         end;
         (Add column widths + scroll bar width}
         ClientWidth := tablewidth + 18;
      end;
    end;
                        Chapter 6--Delphi’s     Data-Aware Components I 169
                                                                         . . _/

     One note of caution before adding this code to a production applica-
     tion; this code would have to be highly modified if there were a risk
     that the ClientWidth of the DBGrid component would be greater than
     the ClientWidth property of the form.
     The next step in the project is to add the detail table to the form. Set-
     ting the relationship between the two tables will be done in the same
     fashion that has been used in previous projects. When the new table
     has been added, some additional programming will enable the
     DBNavigator to be shared between them.
I. Add a second Table and DataSource component to the data module.
   Name the table EVENT-TBL and set the DatabaseName to
   DBDEMOS and the TableName to EVENTS.DB. Select the
   DataSource control and point the Dataset property to EVENT-TBL.
   Name the DataSource EVENT-SRC.
2. Add another DBGrid to the form and set the DataSource property to
   DataModulel.EVENT-SRC. Similar to the Venues table, this grid
   contains two rows that will not display well. Using the Columns Edi-
   tor, change the Visible property for the Event-Description and
   Event-Photo columns to False. You may also want to apply the grid
   sizing procedure to this table as well if appearances are important.
3. Establish the relationship between the two tables with VENUE-TBL
   being the master table in the connection. We are seeking to display al .l
   of the events assigned to each venue. The new index that was estab-
   lished a few pages back is the one that will be used to establish the
   relationship.
4. The last modification we will make to this project will share the navi-
   gator bar between the two tables, rather than adding another
   DBNavigator to the form. Rather than coding separate event handler
   procedures to implement this, we are going to write a single proce-
   dure and then call it when the OnEnter event of the DBGrids is fired.
   Add the following procedure to the VenMgrU unit:
     procedure      TForml.ChangeDataSource(Sender:    TObject);
     beg  in
        i f Sender = DBGridl then
           DBNavigatorl.DataSource     :=    DBGridl.DataSource
        e lse
           DBNavigatorl.DataSource     := DBGridZ.DataSource;
     end

     Don’t neglect to add the procedure declaration to TForml’s type
     declarations.
        I   70   II Part II-7-k? Delphi Database 7-00/s
                                                                                                 _   )
                        ‘,., _.                 ,,.  I ._

                       5, Select either of the Grid components and click on the OnEnter event in
                          the Object Inspector. Select the ChangeDataSource procedure for this
    I                     event. Be sure to add this to the other grid as well.
    i
    1                     The running application should appear similar to that shown in Figure
                          6.5.




                                       Y a:? ,iud:lcrum                       *:’ __     :\
                                                                              ‘__\,\‘,     ,:,
                                                                1400     i
                                     E Gutlltrma Fald




        Figure   6.5
i       The Venue
        Manager

                          For each venue selected, the Events grid should only display the events
                          scheduled for that venue. Selecting either of the grids will transfer the
                          focus of the navigator to that table. You can use rhose control buttons
                          to move around freely and manipulate the data regardless of the table
                          chosen.

                          Tip     The Data Module by default will be an auto-created form. If the
    L                           dataset objects are also automatically opened when the data
    /                           module is created, you should do a performance assessment to
                                judge whether or not this is the best approach. Opening all of the
                                datasets in the data module could severely slow the startup pro-
                                cess of your application, leaving your users with negative
                                impressions.


        The DBImage Component
                          It’s a graphical world out there, and Delphi’s darabase supporr for a
i                         graphics object is second to none. Nearly all database products sup-
t                         ported by the BDE support the inclusion of bitmapped graphics in BLOB
                          {Binary Large OBject) data fields within the table. (BLOBS are not
                                  Chapter B-Delphi’s Data-Aware Components I 4.9


              limited to being containers for graphics but this is one of their most
              common uses.) If a field type is supported by the BDE you can count _, _,,
              Delphi offering a component to work with this type of data. For graph-.:?;a
              its, that control is the DBImage component, a data-aware version of t&&3$
              TImage class.
                                                                                             “&’z
              When the DBImage control is enabled, the BLOB graphics images are :t+%        ;!+j
              captured from the dataset and then stored internally in the Windows             :$
              DIB format. Device-independent bitmaps are an internal data format ‘-.”
              that is sent to the computer’s display adapter driver for translation into. :
              a screen display. Because of the size of images and the time needed to -g
              process them to and from memory, BLOB fields are cached locally wheti.’
              rows are retrieved from a dataset. This improves the application’s per--.-,
              formance when a user scrolls through the rows of a table and the              .‘%!
              images are retrieved from memory rather than a server.                      *;$$
                                                                                                *.

Key Pr
              The properties that will receive our attention here are those that are
              unique requirements of the image orientation of the control.

         AutoDisplay

              The value of this property determines whether or not the contents of               :
              the graphic BLOB will be automatically displayed. When set to True,                 a
              each time the contents of the BLOB field change, such as when the user            .-i
              browses from row to row in a dataset, it is automatically redrawn.                .?
              Depending on the size of the image, this continual redisplay of the                 !
              graphics could become a serious impediment to the performance of the                I.
              system.
              If the performance of the application suffers when displaying the
              images, the value of AutoDisplay can be set to False. With this setting in                 :
              place, the image in the DBImage is not automatically redrawn; the                         =
              name of the field is displayed in its place instead. To view the image,                  Ti
              the user can simply double-click on the DBImage space and the image                         :
              will be loaded.

         Center
              The amount of the image that is displayed by the DBImage control is
              constrained in its default state by the physical measurements of the
              control itself, and the portion of the image that is displayed may not    .i$
              satisfy the user’s needs. In addition, an image that is much smaller than 1;
              the measurements of the container may be displayed in an awkward          “,,$
                                                                                           IZ
                                                                                          ^
Part I/-The    Delphi Database Tools
                                                                        (“_ .-..“- q
                                                                                      D
                                                                                   ::#j
       manner. The Center property controls whether or not the image is              ‘<j
                                                                                      &
                                                                                  ”;&&j
       automatically centered within the boundaries of the control.                ._ s;j
     Setting this property to True will center the image within the DBImage ,.z
     control. When this property is False, the upper-left corner of the image /.i?
                                                                                      ‘;-2“i
     is displayed in the upper-left corner of the DBImage container.
                                                                                      -i::
                                                                                     ..hai
                                                                                       i .-
                                                                                       ‘f$Z
QuickDraw                                                                                 -4;
                                                                                     f -1‘j
     When QuickDraw is False, a custom color palette is used when drawing ..$
     the image into the control. This gives a very high quality picture at the ‘: ‘;$
     expense of processing speed. If the property is set to True, no palette is .;%
     used and the image is displayed much faster. The tradeoff for process- -2%
     ing speed is lower image quality
                                                                                            i
Stretch                                                                                  “&3,.
                                                                                     .- :
       The name of this property is misleading. If the Stretch property is True,,:<2
       the BLOB image stretches or shrinks to fit within the boundaries of the .,gi
       DBImage component. The DBImage component does not change size
       fit to the image. This property’s actions can cause distortion of the
       image because the width and height of the image are independent of 32      ;:, +j
       one another.                                                                 ‘$
       Our sample application will take advantage of the DBImage compo-         .’
       nent’s capabilities later on this chapter but now would be a good
       opportunity to discuss the way that the Windows clipboard can be used,.,~$$
       with the component. Images can be assigned to BLOB fields directly in ‘!<I:;
       the same manner other values are assigned to other field types, but a
       nice feature of the DBImage component is that it can also receive input .I
                                                                                   8
       from the clipboard.                                                          ,)
       Figure 6.6 shows the Image Copier demo program that can be located                     *g :
                                                                                               ; “s
       on the CD-ROM as Imgcop.dpr.                                                             :~
       This application is designed to demonstrate how easily the clipboard
       keyboard shortcuts work with the control. Ctrl-X, Ctrl-C, and Ctrl-V cut,
       copy, and paste images to and from the clipboard. You may select the
       image displayed and copy it to the clipboard. Use the Ctrl-V shortcut to
       paste this data into the other table’s Picture field. The Clipboard class
       has methods that emulate these keystrokes so that you can write cus-
       tom event handlers if your application needs to pursue this capability.


The DBMemo Component
       The DBMemo component is the brother to the DBImage data-aware
       control; it displays BLOB data from the associated database field. The
       BLOB data supported by this control is large blocks of text in Memo
       fields. Memo data is free-form text in which the user has the benefit of
       a variable length field. With no constraint placed on the maximum size
       of the field, the user is able to maintain lengthy collections of text,
       updating it as necessary.
       The text contained in the field can be subject to a number of formatting
       abilities. Alignment, font, and word wrapping are just some of the con-
       trol that the application can exercise over the appearance of the text in
       this control. All of the capabilities of the Windows clipboard are also
       supported for cutting and pasting the contents of the component.
                                                                                                          1
        Pan //--The Delphi Database Tools                                                             .-j 4
       _p_--mG*^-                                                                -i-*.3*.   ‘----~         ‘:

                                                                                                         ::
Key Pr ‘operties                                                                                       :.$
                                                                                                        I,
             There are a number of properties of interest published by the DBMemo                       ?
             component. Mostly centered on the display characteristics of the text
             exhibited by the component, properties are also available to help with                      ”
             the application’s performance.

       Alignment
             The Alignment property determines how the displayed text is displayed                      1.
             within the controls frame. The text can be Left-justified, Right-justified,
             or Centered.                                                                                -.i

       AutoDisplay
             AutoDisplay, similar to the function it performs for the DBImage con-                 <
             trol, determines whether or not the contents of the graphic BLOB are
             automatically displayed. If the property value is True, when a dataset &’ :
             browsed from row to row, the contents of the BLOB field are automati-
             tally displayed. Depending on the amount of text contained in the
             dataset’s field, the process of displaying it could be slow and lengthy.
                                                                                      -’ ‘f~$z$+,:
                                                                                          ‘?$j
             If the performance of the application suffers when displaying the bloch ‘1<~*;$
             of text, the value of AutoDisplay can be set to False. Rather than dis-         -Tg
             playing the text block, the field name is displayed in the control. The      ‘.‘-?
                                                                                           k, . .
             user may view the underlying text by double-clicking on the DBMemo “1
             control.
             The developer must be cautious in utilizing this property. The value dis-
             played by the DBMemo control is the value of the component’s Text
             property. When the text block is not displayed, the value of the Text              .I
             property is a string representing the field name. If the value is queried     ,:
             for a particular string that may exist in the BLOB object, a false nega-      : .L
             tive response may be returned.                                              2’.“.-;5 ”
                                                                                         ‘ -“$
                                                                                       ;.;g
       MaxLength                                                                                     ‘\?:j
             You will use the MaxLength property to limit the number of characters ,$      22
             that can be entered through the DBMemo control. When the value of              I
             the property is set to 0, the default value, there are no limits in place. if
             a maximum length is entered, the underlying memo field is safe; no               :
             text is truncated or lost. The limitation is meaningful only within the
             scope of the control itself.
_ .,,= “__il‘ ; c_. - .,_”

 ScrollSars
           The text displayed in the DBMemo control is not constrained by the
           size of the component. Because of this it can easily be hidden from
           view off to the side or past the bottom of the editing region of the con-
           trol. The ScrollBars property determines what, or if, any scroll bars are
           displayed along with the text. The developer has the option of adding
           single scroll bars to either the bottom or right side of the control or
           adding both simultaneously. The scroll bars are not automatic and are
           displayed at all times, even if they are not needed to display the text.

 Wantlabs
           The WantTabs property is an interesting by-product of the Windows
           world. The Tab key is commonly used for navigation purposes in an
           application and especially for moving among the fields of a dataset. A              ‘.?
           user attempting to place a tab character into the text of a DBMemo
           would find that he had suddenly exited the field with the keystroke.
           The WantTabs property can be set to True to allow the application to
           accept the tab characters literally in the DBMemo text. In this state, the      ?
           user will be able to tab into the control but not out of it. If this property   <
           is used, it will be necessary to explain this behavior and the                      :
           workaround to your users so that they do not become confused and                    -
           frustrated.

 Word Wrap
           The formatting property WordWrap allows the control to wrap text at
           its right margin when displayed. Setting this property to True places
           soft returns into the displayed text so that it does not run off the edge
           of the control. The returns are for display purposes only and do not
           affect the stored text. When this property is True, no horizontal scroll
           bar is needed.

mo Events
           An important event for the DBMemo control as well as the other edit
           controls is the OnChange event. This event fires whenever the Text
           value of an edit control changes. The event is triggered after the Text
           property of the control has been updated and gives the application its
           first opportunity to respond to changes in the text. Before responding
           through the event handler, it is suggested that you query the Modified
           property to determine if a change truly occurred.
I 76   II Part I/-TIE De/phi ~otubose TOO/S


   :ting DBMetno and DBlmage to Work
               The Venue Manager application is going to undergo some serious re no-
               vation after having served us well for several pages. We are going t( )
               modify the Venue Manager to put these two controls through their
               paces. Each of the tables that are used by the application has Memo
               and Image fields within them that lend a lot to the program. Figure 6.7
               shows the modified Venue Manager as it will appear after the follovving
               modifications have been applied.




            I. Adjust the width of DBGrid 1 to approximately the size of the three dis-
               played columns and the scroll bar and move it to the left as shown in
               the picture. Add a DBImage component to the form, placing it to the
               right of DBGridl.
            2. Adjust the size of the conrrol so that its height measurement is the
               same as that of the Venue grid. Set the width so that it fills out the
               space comfortably Depending on the size of your form, the image
               should be roughly rectangular. Set the DataSource property to
               DataModulel,VJZNUE-SRC and the DataField property to
               Venue-Map. Use the Stretch property to have the different-sized
               images display in the DBImage control by setting the property to
               True. We are not going to worry about the distortion of the image in
               this application, though it should not be discounted for others.
                                Chapter 6-Delphi’s   Data-Aware Components n
     *.n=1    ,*. *   *.   .”                                        -‘-a--
                                                                   . -

3 . Add a DBMemo           control to Form1 just below the DBImage compel
         Set the DataSource property to DataModulel.VENUE-SRC              and d .]
         DataField property to Remarks. Set the width to the same size as the
         image control and the height to display about six lines of text. We are ,!.!+
         going to leave the WordWrap property set to True so the only scroll ., ,*d*i
         bar we will need is a vertical one. Set the ScrollBars property to           4
                                                                                *-:q
         ssvertical to add this attribute.
         Execute the modified application and scroll through several rows of &+
         database. The images are small so they display quickly in this applica-Y :
         tion but you should always take into consideration that this is not
         always the case. If the images are large and of high quality, con
         setting the AutoDisplay property to False to improve the user’s br
         ing speed. Speaking of the image, we see that the map is not of
         use to us due to its limited size. Giving the user the ability to displ
         small graphic image at a larger size is an excellent addition to an a
         cation such as this. We’ll add this capability in the next steps.
I.       Add a new form to the project. Name this form EnlargedF and add?%
         DBImage control. Set the Align property to alclient so that it fills a$
                                                                               -:
         of the available area inside of the form. Save the new unit as           :
         EnlargU.pas.
2. The application is going to take advantage of the sharing ability of the<$
    data module so we need to add that unit to the new form. Select the ,:$j
    EnlargedF form and then select File 1Use Unit and the dModU unit $8
    to include the code into the form’s unit.
3. Select the DBImage control and set the DataSource property to            .
    DataModulel.VENUE-SRC.           This value is now available since the
    data module’s unit was added to the Uses clause. Set the DataField     .-“jCL,‘
    property to Venue-Map and the Stretch property to True. The               ,:$
    graphics that we are using are not of the highest resolution but they      “a
                                                                               .-;
    give us a good opportunity to experiment.
4 . One last thing must be done on the new form. When the image form is A<
    closed, the resources should be released to the system. Add the follow- ” ;.
    ing code to the form’s OnClose handler:                                       :>
                                                                                _’ .:P>*
                                                                                       .',' .(
         procedure TEnlargedF.FormClose(Sender:      TObject; var Action:              $
                                                                                      _^"X41
         TCloseAction);                                                                   >a*;
         begin                                                                           % .?
                                                                                         ".9.<
           EnlargedF.Release;                                                          =c,;
                                                                                            .8
                                                                                             1
         end;                                                                              5f;
                                                                                           L
5 . This form should not be displayed as a part of the application until it               1
         is called. For this reason, it should not be auto-created when the
178 n Part   l/-The Delphi Database Tools
                                 ..

            application starts. Select Project) Options from the menu and move                   ,$
           the EnlargedF form from the Auto-create list to the Available list.                  72
                                                                                                ‘f
        6. Select the VenMgrU unit and modify the Uses line under Implementa,                    I-
            tion to read:
              uses   dmodu,   enlargeu;                                                          -.

        7. The application is going to allow the user to click on the small image L.:
           and receive an enlarged view of the same picture that would let them ;:
           view the seating sections seating better. Press F12 to switch from the ,‘z
           unit editor to the form. Double-click on the DBImage control so that      C2
           you can modify the OnClick event handler and enter the following           ‘2
                                                                                   :rX’
                                                                                  .a ’ i
           code:                                                                    ,d
                                                                                       ;:
              procedure TForml.DBImagelClick(Sender:     TObject);
              begin
                EnlargedF := TEnlargedF.,Create(Application);
                EnlargedF.Show;

                                                                                   .-+!
        8. Compile and execute your application. Click on the image displayed .w+:d
           from the database and you will be treated to the sight of a much        ~4
           larger version, Though the image quality causes these to pixelate      ,,a
                                                                              ;:zjj .<
           when displayed, you are able to see the idea in action.                ;;
             The use of the data module is what enables us to create this multiple :!
             form application so easily. Both forms are accessing a single dataset ’ .%$
             object so the record pointer is always synchronized without us having Ji.<
             to write the complex code needed to manage independent table
             accesses.


The DIBText Component
             The DBText component is a data-aware version of the standard Label             :‘$B# 3
             control and is used to display the contents of a dataset field when they ^i
                                                                                            : .i
             will not be edited. By its nature, the representation of the data is       .$ -4,
             read-only, making this control an excellent choice for displaying the        ,-“;
             data with a high degree of security, A common use for DBText is to dis- $$
             play a key value, such as a customer account number or ID, items that 1%
                                                                                         ,-.,;‘$
             cannot be modified without disrupting the relational integrity of the           ‘.i-,42
             database.                                                                     9 2.
                                                                                            )‘”
             The control is simple to implement; set the DataSource and DataField +j
             properties and it is activated. The text of the control is derived from the:!?
             specified field but unlike the Label or StaticText components, it cannot ,$p
             be set at design or run time.                                                     ‘I
                                                                                              ::
                          Chapter 6--Delphi’s Data-Aware Components n           179
                            :,_.Psi/-,/   .iA _. I_;   ‘   / _ . , .   . . --

                                                                                             $ .;
l   operties                                                                                 .;.,
       DBText is a simple control to implement and use with just a few proper-                    ?
                                                                                                    +c
       ties that need attention when placed on your form.                                    ”,>YJ%”
                                                                                                7%:;
Alignment

       The Alignment property specifies the text alignment within the bound-             ;
       aries of the control, the choices being Left-justified, Right-justified, and
       Centered.
                                                                                         .;
                                                                                         . ,. .
Autosize                                                                                    .i
                                                                                            :;
      The amount of text contained in a database field can vary in size, and             .I
      the AutoSize property determines how the DBText component supports              ,$$;
      this attribute. When the property is set to False, the size of the control       :.:Yt-
      is fixed in both height and width. If the text to be displayed exceeds              ,i,$
      these measurements either through length or typeface, the display is                    ‘-
      truncated at the boundaries. Setting AutoSize to True allows the con-
      trol to stretch horizontally to accommodate the data.
      When used in conjunction with the WordWrap property, the control can’*.
      also expand vertically to fit the text. Some thought at design time is ’
      necessary to ensure that this wildly expanding and contracting compo-
      nent does not interfere with other controls in the vicinity.

Transparent
      The Transparent property of the DBText component determines
      whether or not the form or control behind the label shows through.
      When False, the text appears on a background of the color specified in
      the Color property. Setting the property to True allows whatever con-
      trol or form that is behind the label to show through.

Word Wrap
      WordWrap serves the same purpose for this control as it did with the
      DBMemo component. Setting this property to True will insert soft
      returns into the displayed text so that there is no truncation of lines
      longer than the width of the control. To be effective, this property
      needs to be used in conjunction with the AutoSize property.
      There are no events of note for the DBText control. We will put the     .“i
      component through its paces in a few pages, after we have a look at the
      DBEdit control.
180 n   Part l/-The Delphi Database Tools


The DBEdit Component
               DBEdit is the basic building block of any type of data entry application,
               This component is limited to a single line of text and both displays and
               opens the dataset field to modification. DBEdit offers some degree of
               control over the formatting of the data being input and displayed using
               a number of property settings. Similar to the previous component,
               DBEdit is a data-aware version of the TEdit class. Review the data type
               that needs to be supported to determine if this is the appropriate con-
               trol to be used or if the data can be better represented using a DBMemo
               control.

Key Properties
               The properties to be discussed in the context of the DBEdit control are
               focused mainly on the formatting of the data for input or display
               purposes.

        AutoSelect
               To aid the user in quickly replacing the text displayed in a DBEdit con-
               trol, the AutoSelect property can be set to automatically select all text
               when the control receives focus. If the property is set to the default
               value of True, all of the text is automatically selected when the user
               clicks on the field or tabs into the field. The contents of the field are
               then replaced as soon as the first editing keystrokes are received by the
               control. When the property is False, the user must double-click on the
               text in order to select it.

        AutoSize
               The AutoSize property for the DBEdit component differs slightly from
               similarly named properties seen previously. When the property is True,
               the height of the control can resize automatically 10 accommodate
               changes in the text size. The changes can be driven by items such as th
               font or border style of the control. When the value of the property is
               False, the height is fixed and changes in conditions are ignored.               $3
                                                                                              7 47$
                                                                                             -3 -54
        C h a rrCase

               The case of the text in the DBEdit component can be controlled             A-G2:;
               through the CharCase property. The default value of ecNorma1 accepts       5:
               and displays the text input as the user provides it. When the property is ‘- I 3
               set to ecUpperCase or ecLowerCase, text that is input is automatically
               converted to the appropriate case and permanently modified. The text
                              Chapter   6-Delphi’s   Data-Aware   Components    I   .f
_.“-   =          .   -.

           is stored in the dataset field as it is formatted by CharCase and cann@.$52
           be reversed by selecting the opposite or normal case.                     .: g

  MaxLength
                                                                                         .t
           MaxLength determines the maximum number of characters that can b!$i
                                                                           ,. +‘; .g
           entered into this control when no EditMask is in use.                   ::
  P asswordchar                                                                          _.
           The PasswordChar property is unique to the DBEdit component. It               ?
           replaces the actual characters typed with the character specified in th$$
           property, an asterisk for example. When the user types into the field, -i
           the string is hidden behind the string of asterisks, making it ideal for :.‘[$
                                                                                        21
           use in gathering password information or other data that must be          --.+;
           guarded.


DBCheckBox Component
           The DBCheckBox component is a data-aware check box control used ;.i         $j
           for input and representation of Boolean or other logical fields in a       -4
           dataset. Unique to this control is that it does not strictly require that   “”
           the values in the database be True or False. Using the ValueChecked         $
           and ValueUnchecked properties, your application can select any pair of’.$
           characters or strings to represent the two states of the control. Using      7
           the attribute based on the values that best represent the data you are
           modeling will build a great deal of flexibility into your Delphi solutions. : .*
           The check box is a highly intuitive interface element that allows the
           user to quickly represent one of two states. Since the value written to           ‘.i
           the dataset does not have to be strictly True or False, the application                  ,’
           can use this component anyplace that a coded response is necessary.                ,“:
           This saves the user from having to remember codes for the dataset or               :i
           translate visual cues on the form into the necessary string.                       -&;:
                                                                                             ‘3
                                                                                             :;p
                                                                                             ;.,y:;
Voperties                                                                                   ‘E*:
                                                                                             :a.$
       The properties listed here are unique to the DBCheckBox component                  j -$
       and directly influence the action and outcome of the control.
                                                                                                /+l 7’
  AllowGrayed
                                                                                              :Tj
           The property AllowGrayed determines whether or not the DBCheckBox I
                                                                                      :,g
           can be in a “grayed” state. If this property is True, three states for the @.:
                                                                                       -a
182

           component are allowed: Checked, Unchecked, and Grayed. The gra
           state is a non-valued state, applying no data to the database.

      Caption

           The DBCheckBox control identifies itself through the Caption p
           The string in this property appears as a label bound to the chec
           Using the Alignment property, the label can appear to the right or tQ . . -!
           the left of the check box.

      ValueChecked
           ValueChecked works in coordination with ValueUnchecked to specify ‘z!
           the field values generated when the DBCheckBox control is modified,, r
           The control works two ways based on the value in this property. If th&,
           value in the field of the dataset matches the value in this property, &
           DBCheckBox appears checked. On the other hand, if the DBChe&&#
           checked by the user, the field’s value is set to the string contained i~:~
           ValueChecked.
           The ValueChecked property can represent several values in a semic&
           lon-limited list of items. If the field value matches any of the items 7’R
           the list, the check box is checked. As an example, the vaiue of            ‘7
           ValueChecked can be set to “Yes;Si;Oui”. If the value of the assign&$&!
           dataset field matches any of these three items in a case-insensitive,&; 4
           parison, the DBCheckBox will be checked. If the user clicks on an             :<
           unchecked check box, the first element of the list will be assigned to -‘.g
           the dataset field.                                                             23-.1

           The same set of conditions exists for the ValueUnchecked property. If, ;:
           on comparison, the value in the assigned dataset field does not match
           either of the two property’s values, the check box is grayed out. This . ’*;q
           action is dependent on the value of AllowGrayed of course.                 ‘;a


The DBRichEdit Component
           The DBRichEdit component is a data-aware control that can display           t
           formatted text stored in BLOB fields. Rich Text describes text fields tk@
           support the formatting of individual characters, words, and paragraph:
           Text searching and printing functions are supported by Rich Text con- :#
           trols and by default they support the following types of formatting: z
                                                                                    $2
           w Font attributes such as typeface, size, color, etc.
           H Text alignment, tabs, indentation, and list numbering                  .
                               Chapter 6-Delphi’s Data-Aware Components W
         **.. =, _

              n  Drag and drop capabilities
              H The conversion of Rich Text to plain text
              DBRichEdit supports these formatting capabilities but does not provide
              any interface elements to implement them. Your application will have
              to support these functions separate from the control.                             .>,
                                                                                               , :4
             One other aspect of this control is important to realize when imple-
             menting it in an application. DBRichEdit will automatically place the
             dataset object into the Edit state when the text is changed within the
             control. If only the formatting of the text displayed by the control is
             modified, the application must programmatically put the dataset into
             Edit for the changes to be saved; this is an important distinction
             between the text and the formatting that should be remembered.

Key   Properties
            DBRichEdit surfaces a number of properties, some that are very famil-
            iar and others focused on the unique tasks of this component.
                                                                                          :
                                                                                          ..i’9
      Alignment                                                                               :$
                                                                                               “i
             The Alignment property affects the justification of the text contained           1:
             with the control. The standard choices are offered: Left-iustified.
             Right-justified, and Centered.

      AutoDisplay

             AutoDisplay serves the same purpose in this control as it does in the
             other BLOB-related components. By default, the contents of the BLOB
             field will be automatically displayed as the user scrolls through the
             database. If the size of the BLOB fields becomes an impediment to good
             performance, turn off the automatic display through this property

      HideScrollBars

             The HideScrollBars property is used to control the automatic display of
             scroll bars in the DBRichEdit component. If the property is set to True
             and the entire contents of the text fit within the edit window, the scroll
             bars are unnecessary and disappear from the control. They will return
             again, if modifications to the text make it exceed the size of the edit
             window.
             When a user is editing a large block of text and the contents are prone
             to condense or enlarge below or beyond the size of the edit window,
I 84      W fart //--The Delphi Database Tools
Ll-I1ma

                  they may become annoyed at the flashing of the scroll bars. If this is
                  the case, you might want to consider setting this property to False.

           HideSelection
                  The DBRichEdit component allows selection of blocks of text either
                  with the mouse or through standard Windows keyboard methods and :I
                  marks the text in the standard fashion with a colored block highlight
                  it. Depending on the setting in the HideSelection property this select
                  marking may remain or disappear when the focus shifts to another co&?;
                  trol. Setting this property to True will cause the selection marking to
                  visible only when the focus remains on this component. When the                     ,;.
                  visual cue of the selected text is needed in the application, set this
                  property to False to retain the marking even when the focus moves to
                  another control.
                                                                                                      *
                                                                                           ..:g
                                                                                                      i
           Plain Text
                                                                                           a.*; :sL.
                                                                                                  q
                  The PlainText property setting determines how the DBRichEdit in&&:$!
                  prets the text when reading or writing from a file. If the setting is Tn
                  the data is treated as plain text and all formatting is removed. Whe&
                  is False, the default value, all formatting information is retained and-
                  interpreted when displaying the text.                                            ,j

           WantReturns
                  If it is necessary for the users to have the ability of entering Returns in             .:!!$!
                  the DBRichEdit control, set the value of WantReturns to True. When                    - : I<,$
                  property is set to False, the user pressing the Enter key will trigger the            :,,::j
                  default button or some other Windows-specific action and the text will                     ,3
                  not be modified.                                                                              :k
                                                                                                                .~C
                                                                                                                .,:;
           WantTabs                                                                                           _,..?r
                                                                                                            “‘I
                  As discussed earlier, the Tab key is a standard navigation tool in Win-                         ?
                  dows. If your application requires that the DBRichEdit control accept                  : ;:.$
                  tabs for formatting purposes, set the WantTabs property to True.

D5Ric :hEdit Events
                  The majority of events for this control are inherited and are standard EO:-~,~
                  the Delphi interface components but there are a number that are par&$ 1
                  ular to Rich Text controls.
                                     Chapter 6-Delphi’s Data-Aware Components                 l 1
                                                                                           -. c-

           OnProtectChange

                The OnProtectChange event fires when a user attempts to modify ternt; ‘:
                that is protected. Text is protected through the setting of the Protect&:
                property found in SelAttributes.                                        . .-+‘.I
                                                                                           .5 .a;<$++$
                                                                                        _’ -i-”. .7ss$;I
           OnResizeRequest                                                                          _. ;‘A$:*
                                                                                                       :,.$
                This event fires when the edit control attempts to resize to accommo- et>
                date expanding or contracting text. The application then has the      ::r’
                opportunity to adjust or retain the edit window size and manipulate-a
                necessary user interface elements to handle the text.

           OnSaveClipboard

                The OnSaveClipboard event is fired when Windows is in the middle c&<.
                transferring data to the clipboard and the user is attempting to destrov:Ig,
                the edit window that is the source of the data. The event handler can ::&
                be written to delay the window closing until the transfer is complete (,%
                to cancel the clipboard transfer.                                        i:.

           OnSelectionChange

                When the user changes the text that is selected, the OnSelectionChange”
                event is fired. The application is able to effect the modification or inter-i!
                rupt it altogether if necessary.                                             -’.c
                                                                                             _;
                                                                                               A
           OnChange
                We have seen OnChange before but it is also an important facet of
                                                                                       ‘p!
                managing the Rich Edit component. When the event is fired, the text
                property of the control may have changed and the event handler is in a
                position to manage the change. The application should verify that       f.:
                actual change occurred by querying the Modified property               $$

Puttiti   tg DBText, DBEdit, and DBRichEdit to Work
                Being able to simply view items in a database will not satisfy many cli-
                ents. They keep wondering if there isn’t some way that the application
                could allow them to modify the records or even add new data. Dig out
                the Venue Manager application-this next exercise is going to add the&~>
                capabilities to the program. Whenever designing major changes into an ‘5
                application, the business rules that control the transactions must be
                                                                                         ; “_ ‘l%&
                reviewed to ensure that all will be met by the architecture.             *‘:*
    I86 H P a r t II--The D e l p h i D a t a b a s e T o o l s
             ,,,                                     __.\         ,.

                       When a new event is scheduled, it must be for a venue that exists on
                       the list. The referential integrity of the application depends on this
                       requirement, as this is the common field creating the relationship
                       between the two tables. To maintain the integrity of the EVENTS table,
                       the event number must also be unique; the tables selected for use in
                       this program will handle that requirement on their own. The data type
                       of the EventNo field is set to AutoIncrement, making it automatically
                       read-only and guaranteed unique. Finally, any event added to the data-
                       base cannot conflict with another scheduled event for the selected
                       venue.
                       What rules apply to the modification of events that already exist in the
                       database? First, with the exception of fully deleting the record, the
                       event number should not be subject to modification. Allotting the nun]-
                       ber to be modified would subject the application to the risk of integrit!
                       problems. The venue number should also remain read-onI:< ro avoid
                       referential integrity problems but it does reveal an opport\lnit>- for a
                       new feature, the possibility of triggering an addition to the Venues list
                       LIThen an unknown number is added. The last concern for editing exist-
i                      ing records mirrors that of the addition process; changes to a record
                       cannot allow it to conflict with an existing event. Managing these con-
                       straints is easy when the triggering operations are on the surface, such
                       as when we add new forms and controls, and not so eas! lifter the iorr-il
                       has been integrated into the program.




    Figure 6.8
    The completed
    Event Sched-
    uling form of
    the Venue
    Manager
    project
                                         phi’s Dota-Aware Components II 18’7
                             . , ., .‘

Associate rhe data module with the new form so that it has access to
the shared data tables. Under the Implementation section of the
EditEventU unit, enter the following line:
Uses   dmodu:

 Switch your attention to the main form for the application, Forml.
Add the EditEventU unit to the Uses statement for this application,
 either by typing the code in by hand or selecting File 1Use Unit from
 the menu.
.Add a Button control to the form somewhere in the vicinity of the
 Events grid. Caption the button Edit Events and then double-click on
 it to access the OnClick event handler. The application uses this button
to pop up the event editing form, so add the following code to the
procedure:
procedure TForml.ButtonlClick(Sender:     TObject);
begin
  EditEventF :=      TEditEventF.Create(Application);
  EditEventF.Show;
end;

Test the button to ensure that your form will pop up when needed. If
everything is working as planned, switch your attention back to the
edit form and add the following line to the form’s OnClose event
handler:
procedure TEditEventF.FormClose(Sender:        TObject;   var      Action:
TCloseActlon);
begin
  EditEventF.Release;
End;

The first components we will add are shown in Fig.
ure 6.9.                                              : : Memorial Siadiilml I
                                                      .,..       .._.........
A DBText component is going to be used to display ; ; ~y3t-l~ lyyt$er. ; ; jI i :
the currently selected venue because there is no      ,,. ..‘,      # _.....r\,. ,
                                                      .        4    *
need to edit the field, nor do we want the user to               . .
                                                                a .
                                                                .:.
                                                                           l
                                                                             ‘\
                                                                                 . . . . . .._.

                                                                .~__~.,..~,,.~~_.~~~

have that kind of access. Position the component in             _,,,..._,,.,.,....I~
                                                                .          ._       .             j


the upper- left corner as shown and set the          Figure 6.9 Adding
DataSource to DataModule 1 .VENUE-SRC and            a cornponen t
the DataField to Venue. Set the AutoSize property
to True so that it will accommodate the variously
sized labels that will be encountered and the Font
Size property to 12 to make the label more prominent.
                                                                                                ‘1

/
    I88 q Purt //--The Delphi Datobose Tools


             7, Add a Label control just below the venue name and set the Caption
                 nronertv to Event Number. Increase its font size to 10. Immediateh,,
                 beliw this label the Event Number field is going to be added. To meTi
                 the business rules for this field, we want the field to remain under the
                 control of the program and not the user. Again we can use a DBText to
                 display the field in a read-only format that will prevent tampering. Add
                 the component and set the DaraSource property to
                 DataModulel.EVENT-SRC and DataField to EventNo. For the sake
                 of appearance, change the Alignment property to taGenter.
             8. Add four more labels distributed as shown in Figure 6.8 for the
                 remaining edit fields in the table. Caption them as Event Name,
                 Event Date, Event Time, and Ticket Price. Set the font size to 10
                 KO match the other label.
             9. Add four DBEdit components to the form, distributing them below the
                 labels that were just added. Set the DataSource on all of them to
                 DataModulel.EVENT-SRC.              The properties of the controls from left
                 to right are:
                 II DBEditI
                     q DataField = Event-Name
                     0 Width = 150
                 II DBEdit2
                     n DataField = Event-Date
                 II DBEdit3
                     q DataField = Event-Time
                 II DBEdit4
                     II DataField = Ticket-price
            IO. Add another label, setting the font attributes to match the others and
                 the caption to Brochure Description. Below this label add a
                 DBRichEdit control. Set the DataSource property to DataMod-
                 ule l.EVENT-SRC and the DaraField property ‘10 Event-Descrip-
                 tion. Size the component as appropriate for the data. The
                 application’s values are set to a Height of 135 and a Width of 240.
            I I. To the right of this pair of controls, place another label with a font size
                 of 10 and set the caption to read Publicity Photo. Below this we
                 want to display a picture so add a DBImage control, setting the
                 DataSource to DataModulel.EVENT-SRC and the DataField prop-
                 erty to Event-Photo. A good size for the images contained in the
                 database is to set the Height at 135 and the Width at 200.
            12. The last component to add to this form for now is a DBNavigator. Set
                 the Align property to alBottom so that the control will hug the bottom
                       Chapter 6-Delphi’s Dota-Aware        Components W I89
                                                                        .w-

   of the form. Compile and execute the application to ensure that every-
   thing is working as planned. Notice that because we have the
   relationship between the Venues and Events set up in the data module,
   when you switch to the edit form you are only able to access those
   events for the currently selected venue.
   The event handlers and methods of the components are used to imple-
   ment the integrity rules in this application. It would be common for the
   developer to set many business rule items at the table level, close to the
   data. The integrity constraints such as date or time conflicts discussed
   earlier would be best handled through code additions to the event han-
   dlers of the specific field. The date, for example, could be tested against
   the other elements in the same column to determine if a match occurs.
   If so, an error message would be displayed explaining the conflict and
   the focus returned to the date field. A good place to put this code
   would be in the OnExit handler. After a date is entered and the user
   tabs over to the next field, the event would be triggered.
   To explore some of the capabilities of the DBRichEdit control, we’ll set
   up a way in which the contents of the field can be modified. This
   means adding a couple of additional components to the Event Sched-
   uling form.
I. Add a FontDialog and a PopupMenu component to the form. Both of
   the controls are non-visual so their placement is not important. Select
   the DBRichEdit control and set the PopupMenu property to
   PopupMenul.
2. Double-click on the popup control to start the Menu Designer and add
   a single entry that is captioned Font. Double-click on the Font entry
   so that its OnClick handler can be modified. Add the following code to
   the procedure:
   procedure      TEditEventF.FontlClick(Sender:    TObject);
   begin
     if FontDialogl.Execute        then
     begin
         DBRichEditl.SelAttributes.Color       :=
                       FontDialogl.Font.Color;
         DBR ichEditl.SelAt tributes.Size :=
                        FontD ialogl.Font.Size;
         DBR ichEditl.SelAt tributes.Style :=
                        FontD ialogl.Font.Style;
     end;
   End ;
 190 n          Part II-The Delphi Database Tools
^- ..cx.s;_,    1_‘ “. _j                                                                *- ” % ‘ir*ma‘CI-i         __

                    The SelAttributes property refers to the text that is selected by the user, ‘,,
                     If you want to affect all of the text in the control you should address   -:.x.$<1Y
                     the Font properties of the DBRichEditl control directly.                    - $_ I
                 3. Compile and execute the modified program. Select some of the memo ,I’[!
                     text by using the keyboard or the mouse and then right-click on the              iz
                    DBRichEdit control. This will pop up the menu, which can certainly be 2
                                                                                                     ’ :sj
                    a good deal more detailed, from which you can start the Font dialog.            ‘.;j
                    Set the properties the way that you want them to appear and click                   ,9
                    OK. If you press the Cancel button, the code will bypass implementing ‘%         i --“i
                    the property changes.                                                            ._ 2,;

The DBListBox Component
                     A list box control presents the application’s user with a list of elements
                     through which they can scroll and select one or more items. The
                     DBListBox component is a data-aware version of the list box. The item                    ‘:
                     that is selected from the list is entered into the database field that is                 i,
                     associated with the control. A list box is used in interface situations in               ;I
                     which you want to provide the user with a fixed number of selections                     ‘t
                     from which to choose.
                    The list can be built at design time using the Strings Editor or the col-
                    lection of strings can be dynamically constructed at run time, giving the l.:,
                    application the opportunity for the list to always contain an up-to-date       .,!-$
                                                                                                          d
                    set of choices. The DBListBox will highlight an element of the list if             <52
                                                                                                        $a
                    there is a match with the contents of the associated data field. If the
                    value contained in the field does not match any element of the list,
                    nothing is highlighted.

Key Properties
                    The properties of the DBListBox are mainly composed of the commonly
                    inherited set of values. There is a common pair of properties, however,
                    that often is misunderstood in the context of this control. The
                    DataSource and DataField properties do not refer to a field that sup-
                    plies the items for the item list, but instead the DataField property
                    determines the receiver for a choice from the list of strings. The string
                    list is defined at design time or modified from some other external
                    source at run time.

               lntegrdfeight
                    The IntegralHeight property determines whether or not a partial view
                    of any of the items in the list will occur. Since the height of the control
                                Chapter 6- Delphi’s Data-Aware Components n


              can be set at design time, there is a risk that it will cut off a string at
              half or more of its height, rendering it unreadable. When True, the
              property will cause the height of the component to be a multiple of the             -4
              ItemHeight property value.                                                           :.
              The ItemHeight property contains an integer value representing the               i’
              height, measured in pixels, of each item in the list. Both of these prop-       “%
              erties will only have an effect on the control when the Style property is
              set to IsOwnerDrawFixed.

      Items
              The elements contained in the list box are defined through the Items             -;
              property Items is a collection of strings that can be defined at design       I:
              time through the Strings Editor. This tool allows the developer to add,         :’
              rearrange, or remove strings from the list without having to manage                ‘!
              the string list through Delphi code. At run time, if you choose to take
              that route, the application can make use of the Add, Delete, Insert,
              Exchange, and Move methods to perform the same operations. Man-               -1.
              aging the list takes a bit more work within the application, requiring        .>!
              that the program track index values. A closely related property,
              Itemindex, returns the number of the currently selected string.

      Style
              The Style property can be used to specify whether the DBListBox con-
              tains strings or graphical images. Owner-draw list boxes are used to
              display items other than the standard string collection. Owner-draw
              boxes require that the application supply the code to draw the list.

DBListBox Events
              Two events differ from the user-action oriented events of the other
              interface controls and both are connected to owner-draw style
              DBListBox controls. OnDrawItem is fired when a new item needs to be
              drawn in the list. OnMeasureItem supports this capability for variable-
              sized owner-draw lists by measuring and returning the dimensions of
              the object to be drawn.


The DBComboBox Component
              The DBComboBox is very similar to the DBListBox control with one
              exception: If the value that the user wants to enter into the field is not
              listed in the list box, it can be added through the integrated edit box.
              The DBComboBox also allows much greater flexibility in configuring
192 n   Put-t II-The Delphi Database Tools


                the control. Also different from the simpler DBListBox is the action @.-.
                the control when used to display the current contents of a data fie&@#
                the field value is displayed regardless of its membership in the strin&d
                list.

Key PIl     operties

        DropDownCount

             DropDownCount is an integer value that determines the maximum
             number of items displayed in the drop-down portion of the control. (
             default size of the container will hold eight items. The Items string 14
             is not truncated nor is the control filled with wasted white space if
             number is too small or too large. If the value in DropDownCount is
             larger than the number of strings in the Items property, the drop
             list will be sized to the item count. If the value is too small to dis
             the number of elements, a scroll bar is added to the drop-down
             that all items are accessible. The effect of DropDownCount is co
             gent upon the Style property being set to any value but cssimple. ?s:
                                                                                     ‘Y
        ReadOnly
                The ReadOnly property determines whether or not the user can edit
                value in the dataset field. When the value of the field is True, the
                combo box is used to display the value only; it cannot be modified
                regardless of the underlying permissions of the field. If the value is       .;.
                False, t-he field can be edited through selection from the list or entry in‘:;
                the edit box.

        Sorted

                Setting the Sorted property to True can alphabetize the item list in the-
                drop-down portion of the control. Any new kerns that are added to 9
                list will be inserted in the correct sequence as long as the value of&@(g
                property remains True. Once the list has been sorted, it cannot be      ‘pJ
                                                                                         S
                returned to its original configuration through the change of the vale __
                to False.

        Style
                Similar to the Style property used with DBListBox, this property deter*:
                mines how the DBComboBox will display the items. The control
                supports the same owner-draw options as DBListBox that we have d#&~~
                cussed. The default style for the component is csDropDown, which
                              Chapter   B-Delphi’s   Data-Aware   Components     n I93:gj
                                                                            ,

I..




           creates a drop-down list of string items and allows the user to modify
           the value shown in the edit box.
                                                                                           ::g
           The cssimple value creates an edit box with a fixed list below. This
           style still allows the user to modify the contents of the field.             4“q%
           csDropDownList is much closer to the actions of DBListBox. An edit box           :@4
           is displayed on the control but the only way to modify the contents of a ‘.
           field is to select one of the items in the list. The edit box will only dis-
           play text if the value of the field matches one of the items in the string
           list.

~8ComboBox Events
      OnChange
          The OnChange event fires immediately after a user modifies the text in
          the edit box of the control or selects an item from the list. You might
          use this to query the user and determine if they want to add a new item.
          to the list, allowing the application to modify itself to new usage.

      OnDropDown

           The OnDropDown event is triggered by the user clicking on the arrow
           to the right of the edit region to drop down the Iist of items. This event
           allows you to initiate any special processing required by the list being
           dropped down.


The DBLookupListBox and DBLookupComboBox
Components
          This pair of components is so similar in nature that it makes sense to
          discuss them in tandem. DBLookupListBox and DBLookupComboBox
          are data-aware controls that build their display items lists from either
          of two sources: The data can come from a lookup field defined in a
          dataset or from a secondary data source altogether. The controls
          present the user with a limited list of choices with which to fill the
          associated dataset field.
          Physically, these controls are the same as their non-lookup counterparts
          discussed in the previous paragraphs. The lookup controls differ in the
          source for the items contained in the list. They come from external
          sources, and because of that they offer unlimited flexibility as to the
          items presented in the list. Only data supplied by the sources can be
          selected as the value for the underlying field.
Part II-The Delphi Database Tools


l   operties
       The lookup controls take a little study to integrate properly into your :
       application, and the key to understanding them is to be knowledgeable ,*!
       about the settings for the properties.

KeyField                                                                                               ;. . x

       The KeyField property identifies the field from the source listed in the                   -~:
       ListSource property that must match the value of the field specified in                 --:z
                                                                                                 ‘.
                                                                                                 ?V
       DataField. This links the ListSource dataset to the DataSource. The
       fields do not need to share a name but must have the same values.
       The field specified in KeyField is not the field listed in the list box                       -;
       region of the control. That data is derived from the ListField property.                ‘.‘A$
                                                                                                  ;7;
                                                                                                   .,;,;
                                                                                                -. -“a
                                                                                                , .&;,cSg
ListField                                                                                        ‘t:
       The string entered in the ListField property determines the field or             -1
       fields that are displayed in the list region of a lookup control. This          +-:j
                                                                                        i:*:
       property works in tandem with the ListSource property to fully define $4
       the source for the data.                                                 ,.d> .j: ‘3
                                                                                   : “-
       MuItiple items can be specified to build the lookup list. The fields are
       listed in the ListField property in a semicolon-delimited string.
                                                                                           y -a ,:;
ListFieldlndex                                                                                                  3
                                                                                                                4::
       The ListFieldIndex property is used when the ListField property speci-                             .2
                                                                                                        .&a
       fies multiple fields to specify the field to use for incremental searches.
       Specifically for the DBLookupComboBox,        the value in ListFieldIndex
       determines the field that appears in the edit region of the control.
       This property builds flexibility into the design of the elements for your
       lookup controls. When multiple fields are used to build the item list, it                          .:
       is not necessary to place the most important element in the first po&                                ‘,
       t-ion. You can sequence your elements in the order that makes the most                          ._I,
       sense for the appearance of the application and specify the proper field                           :?
       through this property                                                                            .,:g
                                                                                                          .

ListSource
       The ListSource property identifies the dataset that contains the
       KeyField and the ListField fields. When the DataField property specifies.-:
       a lookup field, do not enter a value for this property. The data cont_rqls .$
       will automatically use the lookup field’s LookupDataSet property to CT
       ate a data source.                                                            *c-J.<
                                                                                           il.
                            Chapter 6-Delphi’s      Data-Aware Components n         @$
                               _                                    S.~


Lookup
         Lookup fields must be mentioned in any thorough discussion of this         ji
         brace of components. These field types are read-only fields that display $
         values at run time based on search criteria specified by the application. ?!$f
                                                                                       ;&
         The simplest example is a lookup field that is passed the name of an            .,G
         existing field to search on, a field value to search for, and a completely j.
         different field in a lookup dataset whose value will be displayed. A brief&$!
         sample application will help to demonstrate how these fields work.           i!J
         Two tables are needed for this exercise, ORDSHIP and DESTDIST.            ,..!*
          (Both can be found on the CD-ROM under the Chapter6 directory.) The, :-$i
         ORDSHIP relation represents customer orders on which we need to                   +,
         compute the postage. The total of the shipping cost is a formula that       .35“1
         involves the distance to the customer’s city and the value of the mer-
         chandise. Lookup fields will be added to the table definition to pull the. ‘g,
         customer city and the distance to that city. A computed field will then         i8z
         use this information to return the shipping cost. When you copy these .,:$
         tables from the CD-ROM, place them in the same directory as the              “‘i
         Borland sample files so that they will fall under the DBDEMOS Alias. Gij
         Start a new application and place two DataSource and two Table corn- .;$
         ponents on the form. Set the TableName property of Table1 to                 ,:
         ORDSHII?DB and on Table2 to DESTDISKDB. The DatabaseName                       ,i
         properties for both controls will be set to DBDEMOS. Point
         DataSource to Table1 and DataSource to Table2. Add a DBGrid
         and DBNavigator to the form, pointing both to DataSourcel. Save
         the program, naming the unit lookeru.pas and the project
         looker.dpr.
         Select Table1 and invoke the Fields Editor (right-click and select from\ _ _
         the context menu or double-click on the Table component). Add a new            i
         field to the table. The first field to be added to the ORDSHIP table will
         be called Destination and will look up the destination city from the
         DESTDIST table based on a match with the zip code entered for the              -;T3‘
         order. The Component field will fill itself in with the field name. Set
         the data type to Float and be sure that the Lookup radio button is           1
         selected. Selecting Lookup will enable the Dataset and Key Fields edit
         boxes. Set the Dataset property to Table2. Key Fields and Lookup
         Keys are the columns in each of the two datasets that will be com-
         pared when seeking a match. Set both of these to the Zip field. The
         Result Field is the data that will be returned from a match. For this     -.
         field, select City. The completed form should appear as shown in the
         example in Figure 6.10.
Figure 6. IO
Defining 0 new
field

            3.   Add anorher new field for a second lookup field. This one will be
                 called MilesTo and will represent the number of shipping miles
                 between the factory and the recipient. The link between the two tables
                 will be the Zip code again and for this column the Result Field will be
                 Distance.
                 The last new column will be a calculated field of type Float. Name this
                 field ShippingCost. After the field has been created, edit the
                 DisplayFormat property and set it to a mask of ##O.OO.
                 With the calculated field in the table, we need to establish the formula
                 for calculating the shipping costs for the furniture. Close the Fields
                 Editor and select Tablel. Double-click the OnCalcFields event and add
                 the following code to the handler:
                 procedure TForml.TablelCalcF~elds(DataSet:      TDataSet);
                 begin
                   TablelShippingCost.AsFloat     :=
                                   (TablelMilesTo.AsFloat    * 0.15) +
                                   (TablelCost.AsFloat    * 0.02);
                 end;

            6.   Compile and execute the application. The running program is shown
                 in Figure 6.11.




Figure 6, I I
The completed
form for the
Looker project
                                                     hi’s Data-Aware Components II 197


                  As you add new entries, a match is made on the zip code that you
                  enter. If there is a matching entry in the DESTDIST table, the lookup
                  fields will be filled with the appropriate information. The entry being
                  added shows the result of a non-matching entry in the Zip field.
                  As you spend more time working with them, numerous uses for lookup
                  fields will be discovered. As with all persistent fields they will remain
                  defined and prepared for the application’s use. Remember that the key
                  fields, those columns on which the match is made, do not have to be
                  similarly named; the only requirement is that they share the same val-
                  ues. Also, the columns do not need to be displayed as we did in the
                  sample application, They can be hidden within the program and used
                  in other calculations.

putting        DBLookupListf3ox and DBLookupComboBox                    to Work
                  After completing the sample project above you have a good working
                  knowledge of how lookup fields work and the concept behind them;
                  based on some matching column between two dataset objects, other
                  columns from that row can be displayed. The two data-aware lookup
                  controls work on the same principle with one great advantage: They
                  are able to modify the contents of the underlying dataset, This lets you
                  look up the entries for a field from another dataset and build consistent
                  data into the column.
                  The Look2 project is a modification of the Looker application just com-
                  pleted. In this admittedly contrived example the user will select more
                  of their order information from lookup objects, including a
                  DBLookupListBox and a DBLookupComboBox. To begin this project,
                  open the Looker project, select File 1Save As, and save your work as
                  Lbok2.
                                                                                               ‘.‘:
               I. The new layout for Form1 is shown in Figure 6.12. Use this as a guide       Ill
                  for adding and moving the components that are discussed.

                                                                                              n
                                                                                              n
Figure 6. I2
                                                                                              n
The completed
form for the
look2   project
                         9387   80439   Rechm

                                                                                              n
POw-t I‘/---The Delphi Database Tools

        The DBGrid has been left on the project so that the effects of the modi-            :;;;;
        fications can be monitored on the table. Take it out of the tab order so             .,‘g
        that it is not available for input. Also, all of the columns in the grid are
        hidden with the exception of CustNo, Zip, Item, and Cost.
  2.   Add a third DataSource and Table pair to the form. Point the
        DataSource to Table3 and select the new table. Set the
        DatabaseName to DBDEMOS and the TableName to INVENTRY.DR.                        ->, .,
  3.    Select Table1 and invoke the Fields Editor so that we can add a new                 2
       lookup field. Name this field ItemCost and set the data type to                       .
       Float. The lookup dataset is going to be Table3, the Key Fields are                       zT
       both Item,and the Result Field will be Price. This lookup field will                      *.
       return the price when an item is selected from another of the controls. *                 !i
  4.   Standard DBEdit boxes are used to represent the Customer, Destina-                 ‘-I
       tion, Cost, and Shipping fields. All of the assigned fields are from               _r.’
       DataSource and they are, in the order listed above, CustNo, Destina- 123
       tion, ItemCost, and ShippingCost.
  5.   Add a DBLookupListBox in the Zip Code position. The lookup box will
       return all of the values from another dataset column and allow the
       user to choose only one of those for the value of the underlying field:‘-:+
       Different from the lookup fields, the lookup controls will automatical&
       assign the value selected to a dataset field. The DataSource and
       DataField properties of this control determine the dataset and field                  .I’,
       that the selected value will be written to. Set these values to
       DataSource and Zip, respectively. The values that are displayed in the ” ,:,*~
       list are drawn from the DESTDIST table. Set the ListSource property to                    .g
       DataSource and the List-Field and KeyField to Zip. The word con-
       trived was used earlier as this example assumes that the only zip codes
       the company will ship to are contained in the DESTDIST table. Per-
       haps a more realistic example would have been to ship only to those
       customers who have an assigned CustNo. Oh well, perhaps in the set-                       \
       ond edition.
 6.    The next component to add will be the DBLookupComboBox.             This
       control provides the same services as the DBLookupListBox but in a
       more convenient package. The combo box rolls up when not needed,
       giving the application a cleaner interface, and the selection tools work ‘:i
       incrementally. When selecting an entry from the list, as the user types .;
       letters the selection changes to the element that most closely matches i
       that letter. Assign DataSource to the DataSource property and                 5
       Item to the DataField. The ListSource is going to be set to Table3            ’
       and the KeyField and ListField both set to Item. This example of                -
       usage is a bit closer to reality. The order clerk can only select those
       items that are in our inventory table. The entries in the order table
                              Chapter 6--Delphi’s Data-Aware Components n
                                                                     “I

         will now have consistent spelling and capitalization, making SQL and “’
         other queries much more effective.                                              .?&j
      7. As long as the Item is going to be pulled from the lookup set, we
         might as well get the price that matches it. This is easy enough to do
         by using the ItemCost field created in step 3. The data that appears in , , t:
         the edit box will have been looked up in response to the item that is         ,“,W
                                                                                       . >+4  %F
         selected in the DBLookupComboBox. We would like to retain this                  ,<?&
         number as a part of the ORDSHIP database so we need a method to                  $$
         write the value shown here into the new row. To do this, use the
         OnExit event handler of the combo box so that when the user tabs out ‘i.=
         of there, the data is written. Add the following code to the handler:              d-a
                                                                                    -:.,.&.,,’
          procedure TForml.DBLookupComboBoxlExit(Sender:          TObject);                 +3
          begin                                                                             .x
                                                                                            .
            TablelCost.AsFloat  := TablelItemCost.AsFloat;
          end;

      a. One last modification needs to be made to accommodate our changes. ,:
         The formula for the ShippingCost Calculated field needs to be updated~,:.;
         to draw the cost of the furniture from the ItemCost field. Modify the     3
         OnCalcFields handler as follows:
          procedure TForml.TablelCalcFields(DataSet:    TDataSet);
          begin
            TablelShippingCost.AsFloat      :=
                            (TablelMilesTo.AsFloat * 0.15) +
                             (TablelItemCost.AsFloat * 0.02);
          end;

      9. Compile and execute the program. As you utilize the new components,
         experiment with them to note the differences between the two so that
                                                                                                 I::
         you can make the best design decision for your own applications.


The DBRadioGroup Component
         The DBRadioGroup component is a data-aware version of the
         RadioGroup control. Similar to the data-aware list components, it
         presents the user with a limited choice of selections for a data field.
         The user selects the value that they want to enter for the field by select
         ing one of a group of radio buttons. A radio button interface should
         only be used in situations where the number of choices is very limited.
         The mutually exclusive nature and default selection options of these
         controls require familiarization and do not work well for more than a
         handful of choices. When the collection of possible values exceeds the
         number of fingers on your hand, use a list box.
200 n   Port l/-The Delphi Database Tools                                                      _3
                                                                                  _ .- eIxIe~“$*:
                                                                                             ‘.:? a

                The display actions of the DBRadioGroup control differ significantly         ‘?
                from the list components. If the value of the underlying database field .-s
                matches one of the elements in the control’s Items property, the corre- ,,$
                sponding button will be selected when the data is displayed. In                 ~q ,d
                addition to the Items string list, there is a second property called        =!$
                Values. The string collection entered in the Values property are the val-
                ues that will be passed to the data field in lieu of the strings in the   ;
                Items property. The value that is sent is selected on the basis of the        ,w<.$!
                index value of the button that is pushed.                                      i .,:1
                                                                                                 ‘$
                This second string list can cause some confusion at first glance. If the ‘2
                value in the data field does not match an element from the Items list or !;j
                the Values list if it exists, no button will be selected. However, if the    ig
                Values list exists and the data field value does not match an element in.j.j
                the Items list but does match an element in the Values list, a button v&
                be selected.

Key Pr -0perties
        Columns
                The radio controls in Delphi do not need to be designed vertically on1
                they have the capability of spreading over multiple columns horizon-
                tally as well. The Columns property specifies the number of columns
                for the DBRadioGroup control. The maximum number of columns is 16, ‘i

        Items

                The Items property contains the string list that describes each of the i               i
                buttons in the radio group. For each item in the list, a button will be            ‘;
                created. In the absence of a list in the Values property, the elements of           ‘-
                the Items string list are the data values that will be written to a field
                specified in the DataField property
                                                                                             .,:
        Value
                                                                                                    i
                                                                                                 .i
              The Value property contains the content of the current data field. If a             i
              different radio button is selected, the value of this property will change.,<::
              This property can be queried to retain a value before a change is made. $
                                                                                            &
                                                                                            2;   ^;
        Values                                                                              ,.i’”
                                                                                              ‘&1
                                                                                           ”
             In some cases your application will require that the value that is writ- ~~‘~2
              ten to the data field is different from the caption of the button. This           ‘-i
              might be for simplification or clarity purposes. When this is the case,            ”
              the string list that defines the actual data values to be written is
                                                        hi’s Data-Aware Components II 20 i         :-.
        ..\   ‘..                                                                                .a.&. f,;5
                                                                                                 n :.<
                    defined in the Values property. The elements contained in this property
                    must correspond one-to-one with the elements in the Items property in        ,. %..I
                    order to function correctly. The value that is selected from the list will   n$5
                    match the ItemIndex value.
                    To cement your understanding of the data-aware radio group control,
                    you might want to assemble the following quick demonstration project.
                    It uses the RHPS.DB table, located in the Chapter6 directory of the
                    CD-ROM.
              I. Start a new application and add a DataSource, Table, Navigator,
                  DBGrid, and two DBRadioGroup components on the form as shown in
                  Figure 6.13.




                 The Table setting for the DatabaseName will be DBDEMOS and the
                 TableName is RHPSDB. Point the DataSource to Table1 and point all                ,z.
                                                                                                  n
                 of the remaining controls to DataSourcel. Activate the table.
              2. Select the first radio button control and set the Caption property to
                                                                                                   ‘,.’
                 WHO. This component is going to control what values get entered                   ,::
                                                                                                   n
                 into the WHO field of the database. Set the DataField property to
                 WHO and then double-click on the Items property, starting the String
                 Editor. Add the following strings to the list, each on its own line: Riff
                 Raff, Columbia, and Magenta.
              3. Select the second DBRadioCroup component on the form and set the
                 caption on it to read WHAT. Set the DataField property ro WHAT and                n
                                                                                                   n
                 duplicate the string list from the previous control in the Items prop-
                 erty. In this group the value written to the underlying dataset field is
                 going to come from the Values property rather than the Items list.
                 Double-click on the Values property to get to the string editor and add
                 the following elements to the list: Handyman, Entertainer, and
                 Housekeeper.

1: ii                                                                                               n

                                                                                                    m
202 n   Part II-The Delphi Database Tools
 *”

          4. Compile and execute the application. The first selection is very
             straightforward; the user clicks on a button and the value shown as
             the caption of the radio button is transferred to the database. T
             ond radio group is a little more furtive. Rather than sending the value
             shown to the database, the element corresponding to the button’s
             ItemIndex in the Values property is written to the data field.


The DBCtrIGrid Component
              Saving the DBCtrlGrid for last makes the most sense, as the component :?
              itself is a container for a select group of the other data-aware controls. :‘;
              DBCtrlGrid begins with the fundamental concept of the grid, that is, to Lt
              display a number of columns of numerous rows simultaneously. This              -:$
              data is configured in a very familiar table layout that conveys the inforc $j
              mation to the user simply and clearly once they have divined the            ;.
              relationship between the rows and columns. The control grid begins ;:
              with this premise and then kicks it up several notches.                    %_
              The DBCtrlGrid control is composed of panels on which a selection of .:.z
              the other data-aware (or non-data-aware) controls can be placed. At $
              design time, the controls on the panel represent a single row in the
              dataset. At run time, however, the layout that you create is duplicated :
              for every row in the underlying dataset, filling the component with a I;,?
                                                                                        *
              view of multiple fields from multiple records. The navigation controls -,aIr
              that are used will cause the display to skip from panel to panel.
              The DBCtrlGrid component provides a unique interface opportunity for
              your applications. If single field controls are placed directly upon a
              form, the user can only view a single record in that format at a time.     cf
              There are many times when this is appropriate. In some cases, however, 2
              the information is better presented as discrete fields but in a way that    ,;
              more than one row of the database can be displayed at one time. For        ;j
              these times, Borland has included the DBCtrlGrid.                        ,-J


Key Properties
           The DBCtrlGrid component introduces some new concepts and proper- ‘*<
           ties to the data-aware VCL controls. Be aware that the control grid is a .zi
           container object like a panel and that some of the properties set for the ,s
           container will override the properties of the individual controls.
                              Chapter 6--Delphi’s Data-Aware Components n 3


Allow
        The setting in the AllowDelete property determines whether or not the :!g
        current record can be deleted from the dataset by pressing Ctrl-Delete. ‘;d
        When False, no deletion can occur through the DBCtrlGrid, either          ,>
        through the aforementioned key combination or through the DoKey           -$
        method passed with the applicable parameters. If the property is True, ..$?
        the default value, the row can be deleted by the key combination or usi.<$
        of the DoKey method.                                                     “$2

        The DoKey method matches a keystroke combination to a method to
        perform specific actions. The action is specified by the Key parameters
        and supports the activities shown in Figure 6.14. A number of these
        actions are specific to navigating within the DBCtrlGrid component,     -:‘:
        which can become complicated when the interface has been separated ;”
        into columns and rows.                                                       j



        gkNull                Do   nothing

        GkEditMode            Toggle the EditMode    property

        gkPriorTab            Move to the previous panel

        gkNextTab             Move to the next panel

        gkLeft                Move one panel to the left

        gkRight               Move one panel to the right

        gkUp                  Move one panel up

        gkDown                Move one panel down

        gkScrollUp            Move one panel up

        gkScrollDown          Move one panel down

        gkPageUp              Move ColCount * RowCount      number of records up   in the
                                                                                             .?2
                              dataset.   (Page measured by Columns * Rows)                  24
                          I
        gkPageDown            Move ColCount + RowCount      number of records down in the
                              dataset.   (Page measured by Columns * Rows)

        gkHome                Move to the first row in the dataset

        gkEnd                 Move to the last row in the dataset

        gklnsert              Insert a new row in the dataset above the current row. Set
                              EditMode    to True.
         gmPPend           Add a new row at the end of the dataset. Set EditMode       to
                           True.                                                             T

         gkDele !te        Delete the currently selected row from the dataset.   Set   the
                          EditMode to False.
     t                    L
         gkcancel         Set the EditMode to False and cancel any   modifications that
                           have not been written to the dataset.


         Figure 6.14 The DoKey actions


Allowlnsert
         The twin property of the previous property, AllowInsert controls the                    <.f> $1
         ability to insert new rows by using the Ctrl-Insert key combination or
         the GoKey method. When the property is set to False, no insertions are
         allowed through the grid. Setting the property to True and placing the,
         dataset into Edit mode will enable the user to insert new rows.

co/count

         The DBCtrlGrid is capable of subdividing the panels into columns, cre-
         ating smaller panels. Each of the smaller panels continues to represent .“i:
         a single row in the dataset. The new panels will fit themselves within  :-f
         the original panel, constrained by the Width property of the
         component.

Orientation
     The Orientation property determines the order in which the rows of the                       .j
     dataset are displayed by DBCtrlGrid. If the property is set to goVertical,                   -4
     the panels will be ordered in rows and the grid will display a vertical
     scroll bar. When the property is set to goHorizontal, the panels are
     arranged in columns with a horizontal scroll bar displayed.

Pane/Height
     The PanelHeight property determines the height of the panels. This      :c
     number is different from the Height property for the entire DBCtrlGrid ;i
     control.                                                               qg

Pane/Width
         Similar to the PanelHeight property, the PanelWidth property carries
         the value that determines the width, in pixels, of the panel. Again, it
         a different value from the grid’s Width property
                                  Chapter 6 -Delphi’s Data-Aware Components m
  ;_     ,..i                                                                  .>

        SelectedColor
                Changing the color of the panel can highlight the panel that contains
                the currently selected row. The SelectedColor property contains the cur-
                rent color value and should be modified to change the default shade.

        ShowFocus

                The ShowFocus property determines whether or not a focus rectangle is
                drawn in the current panel to indicate the currently selected record. Set
                the value to True to display the focus rectangle. When the value is
                False, the application should indicate the selected panel through some
                other visual cue.

DBCtrlGrid Events and Related Methods
          Just as a number of the properties are unique to this control, there are
          events not seen previously that only make sense in the context of the
          DBCtrlGrid.

        OnPaintPanel

                The OnPaintPanel event fires whenever a panel needs to be redrawn in
                the grid. The handler allows the developer to customize the paint pro-
                cess. This handler does not affect the controls contained by the panel,
                as they are drawn on their own.

        GetTabOrderList
                Executing GetTabOrderList overrides the default to remove all of the
                controls contained by the grid from the tab order. This allows the appli-
                cation to gain more control over the navigation of the database.

        KeyDown

                This method performs special processing within the application when
                specific keys are pressed. The keys handled and their associated actions
                are displayed in Figure 6.15. The KeyDown method translates the key
                presses into a logical key code and calls the DoKey method to perform
                the associated action.
206 n   Part II-The Delphi Database Tools




               UpArrow

               DownArrow                   gkDown

               PageUp                      gkPageUp

               PageDown                    gkPageDown

               Home                        gkHome

               End                         gkEnd

               Return                      gkEditMode

               F2                          gkEditMode

               Insert                      gwPPend

               Ctrl + Insert               gklnsert

               Ctrl + Delete               gkDelete

               Escape                      gkCancel

               All other keys              gkNull


               figure 6. IS Key and Shift values handled by the KeyDown   method


Putting DBCtrlGrid to Work
               The DBCtrlGrid is an esoteric component rife with possibilities, so any
               minor demonstration is going to seem insignificant. While this is cer-
               tainly true, the ticket sales demo will, at the very least. give you an idea
               of how the component works so that you might consider it as a solution
               to your next design. The application that we are going to build is the
               last piece we will construct for the Venue Manager application. The
               program written here is a stand-alone application meant to run on a
               ticket kiosk with a touch screen where the purchasers can scroll
               through the events scheduled and then press the bar that represents the
               event to order tickets.
          I.   Start a new application and add        a DBNavigator and a DBCtrlGrid to
               the form. Set the Align property        for the navigator to alBottom and
               the same property of the control       grid to alclient. The form should be
               nicely filled with both of these       components now. Save your project.
         2.    Add two Table components and a DataSource component to the form.
               Table1 should have the DatabaseName property set to DBDEMOS
               and the TableName property set to EVENTS.DB. Table2 will have the
               same database name and the TableName property set to VENUES.DB.
               Point the DataSource component to Tablel.
                                                                                    hi’s Data-Aware Components B 207
                     ,..   ,I , . , .j___,~x,l~,‘__.._~ _,.,,, I_ __~.=-~-~_lll”l,lti‘-_- .illi ..,.‘. ,,.**  \i.,, _

                   Set the DataSource property of the navigator to DataSourcel. The
                   DBCtrlGrid is a panel-based container control. When the DataSource
                   property is set to DataSourcel, this becomes the DataSource prop-
                   erty for all of the child components and cannot be modified.
               4 . We need a little information from the VENUES table so a lookup field
                   will be created. Select Table1 and invoke the Fields Editor. Add all of
                   the existing fields and then click on New Field from the context
                   menu. Name the new field Venue, set the Type property to String,
                   and make the length 25. Be sure to check the Lookup radio button to
                   access the rest of the properties. The DataSet property will be Table2,
                   the Key and Lookup fields will be VenueNo, and the Result field will
                   be Venue. This will return the name of the venue so that it can be dis-
                   played on each panel.
               5. Have a look at Figure 6.16 to see how the remaining components are
                   going to be laid out.




Figure 6. I6
The Tickets
main form

                  The top panel of the grid is the one on which all of your layout should
                  occur so that the desired behavior of the control grid is retained. The
                  striped panels represent the placement of the other rows of the data-
                  base and are shown for your design reference.
               6. As shown, add the appropriate components to the top panel of the
                  grid. There are some limitations to the DBCtrlGrid; foremost, the fol-
                  lowing components cannot be used on the grid:
208 n     Part /i-The Delphi Database Tools
/s#_pn-                                         i,    _ .,., ___ .>v .   ._ __ . ,- ---=“-‘-*-

                n   DBGrid
               n    DBNavigator
               n    DBListBox
               n    DBRadioGroup
               n    DBLookupList
                n   DBRichEdit
               All of the fields, with the exception of the DBImage, are DBText con-
               trols so that they cannot be modified.
            7. Set the SelectedColor property to clWhite so that the current record
               will really stand out on the screen.
            8. Finally, this application will use the OnKeyPress event handler to emu-
               late the touch screen. We will trap for the “B” (bar) key in the handler
               and simply pop up a message. Add the following code to the event
               handler procedure:
               procedure TForml.DBCtrlGridlKeyPress(Sender: TObject;   var          Key:
               Char);
               begin
                  if UpCase(Key) = 'B' then
                    MessageDlg('Ticket  Master down for maintenance.',
               mtError,[mbOk],O);
               end;

            9. Compile and execute the application. Your running application should
               closely match that shown in Figure 6.17.
                                    Chuoter     &--Delphi’s Data-Aware Components.
                                                                                     q 209
              x                                                     “_




                                                   _--
                                             x__----
                                        ------        -I-.               -----




                                        Women’s Basketball Finals
                                        6/21/l 336    R 00 100 PM

                                        Memorial Stadiu




figure 6.1    7
The       final   ’
components of
the Venue
Manager
application

                  Use all of the different navigation options including the Up arrow, the
                  Down arrow, and the PgUp and PgDown keys so that you can become
                  familiar with their actions.


Summary
                  This chapter has covered all of the data-aware controls and, hopefully,
                  provided the usage information needed to put them to effective use in
                  your applications. The projects that were built in the chapter have dem-
                  onstrated the interplay between the data access controls and the
                  interface components. The key to successfully designing effective
                  Delphi database applications is to understand the interplay between
                  these separate but interlocked groups of components. The interface
                  components, those data-aware controls we have just reviewed, provide
                  the windows through which the user sees their data. The management
                  of that data is centered in the data access controls we explored in the
                  previous chapter. Recognizing this difference in task assignments gives
                  the developer direction when deciding where a specific job is to be per-
                  formed in a program.
                                                                                           ..;z
                                                                                           /
2 IO H Part //-The Delphi Database Tools                                                   .&
_*;i,en.l;n(“*l_, ~._*jlCr_~j~_._“._. *...__ -- *. -- * .( -   I.   _”   *.   ,.*


Looking Forward
                                                                                                  : I’%
                    The next chapter rounds out the application development topics by cl&:$
                    cussing reporting. This item is often left to last in the application des
                    and development process but this sometimes has a detrimental effect .j
                    on the overall quality of the program.

				
DOCUMENT INFO
Shared By:
Tags: database
Stats:
views:18
posted:3/19/2013
language:English
pages:106