Transcript Week 10

Rapid Applications
Programming with
Windows
Week 10
Topics Week 10
The Borland Database Engine (BDE)
Database Desktop
An Overview of Delphi Database
Components
Using the Database Form Wizard
Data Modules
Creating Database Forms by Hand
Data components
Searching for Records
The Borland Database
Engine (BDE)
Provides access to local, client/server
databases
local: (single-tier) all data manipulation
occurs immediately
Client/server, two-tier:
client application talks to the database server
through database drivers
Database server manages connections
client application is responsible for managing the
integrity of the data (validation etc)
The Borland BDE...
Client/Server, MultiTier
Client application talks to one or more application
Servers, which in turn talk to the database server
Application servers are specialised e.g. data broker,
security handler
client app. may be little more than the interface
(Thin-client apps). Can be programmed by ‘juniors’
BDE Drivers vary a/c Delphi version
STANDARD driver provides access to Paradox, dBase
Other drivers provided for e.g. Sybase, Oracle,
Informix, InterBase, MSAccess95, 97,( NOT 2000)
Excel, FoxPro
The Borland BDE...
The BDE Administrator utility
The Database Page (tab)
displays list of all available DB aliases
an alias provides the path to a DB, and specifies the
driver to use
just change the alias path when changing PC’s
All Delphi database access routines will reference
the DB alias, which then references the DB tables
Object|New to create a new alias, then click ‘Apply’
speed button (or Code Examp1)
Save Table As (in Database Desktop) also displays a
list of available aliases
The Borland BDE...
The BDE Administrator utility (contd)
The Configuration Page (tab)
Configure the installed drivers: Native, ODBC
Change e.g. Block Size, StrictIntegrity, Fill Factor
Add new ODBC driver
Change settings for the System Object:
• INIT settings (for starting an app, stored in
Windows Registry)
• Formats settings e.g. Date, Time, Number
Database Desktop
 Tools|Database Desktop from within Delphi,(or
select as a stand-alone application)
Create a New Table with Database Desktop:
 File|New Table
Choose Database table type (e.g. Paradox 7)
Table details specifications dialog:
Data Type: autoIncrement, Date, Time, Money,
OLE
Validity Checks: required, Min/max values, default
secondary indexes
referential integrity
password access to the field
Database Desktop...
File|Open|Table, then choose alias to a
database (e.g. DBDEMOS), then the table
Table|Edit Data to change, add data
Table|Restructure to change table
definitions
File|Working Directory to specify
directory where the database lives
Tools/Alias Manager to change, create
aliases (as in BDE Administrator)
Delphi
Database Components
 Non-Visual data access components
Derived from TDataSet class
provide mechanism to get at the data via
specific properties:
TTable: DatabaseName, TableName
TQuery: DatabaseName, SQL
TStoredProc: DatabaseName, StoredProcName
 Visual data-aware components (controls)
enable user to view, edit the data
TDBEdit, TDBListBox, TDBImage, TDBGrid etc
 TDataSet components and visual controls communicate
via a TDataSource component.
Database Form Wizard
 File|New|Business Page ->DataBase Form Wizard OR
Database|Form Wizard
 Choose between a simple or master/detail form
 Specify Table, fields, layout for Master info
 Same for Detail information (use Grid layout) plus ‘link’
fields (usually the foreign key)
 Run the project, using the Database Navigator control
(similar to the VB Data Control) to access the records.
 May need to change component type e.g. an OLE object
is displayed as a DBEdit field
 Use the form as a starting point, then customise
Database Form Wizard
On the final screen, Form Generation -> Form
and DataModule option will store the Dataset
component (TTable or TQuery) and the
DataSource in a separate module.
Allows reuse of those data access
specifications: File|Use Unit DataMod.pas
Can also create a Data Module ‘by hand’ via
File|New -> Data Module
In the Data Module’s On Create event
handler open the dataset(s) it contains e.g.
CustTable.Open
Database Forms, ‘by Hand’
To display data from >1 table, cut/paste all the
database components from a ‘wizard-created’
second form to the first form created
Building forms without the Database Wizard
Start with a TDataset object (TTable or TQuery)
TQuery allows SQL to be written, specifying tables,
and fields to be selected
Set its DatabaseName (via an alias), then TableName
(for a TTable), or SQL statement (for a TQuery)
Add a RecordSource Object, and point its
Dataset property to the TTable/TQuery
Database Forms, ‘By Hand’...
 Place appropriate data-aware controls on the form from
the Data Controls page (tab) on the component palette
 Set their DataSource, DataField properties
 Set the Active property of the TTable component to
True to see the data at design time (do this last)
 By default, Delphi creates a TField object for each field
in the DataSet at run-time. You can create these at
design time, specifying which fields are to be included in
the DataSet, and set their properties: very useful with a
DBGrid
 Right-click the DataSet object (TTable, TQuery), then
Fields Editor
Database Forms, ‘By Hand’…
The Fields Editor
 Right-click, then Add Fields, and select all the fields from
the DataSet which you want to use in your application
 Select a field in the Field Editor, to set its properties in the
Object Inspector e.g.DisplayLabel, Visible, EditMask,
DisplayFormat etc (note the TField component types).
 Note that a DBGrid can’t display a BLOB type, so set
Visible to False (still available, not visible)
Create new, calculated fields, based on values from
the DataSet:
 Right-click the Fields Editor, choose New Field
 Ensure Field Type option button Calculated is selected
Database Forms, ‘By Hand’…
The Fields Editor
Code for the OnCalcFields event (of a DataSet
component) (Example 2)
The TTable Object will call the procedure once
for each record, so the calculated field will
appear for each record in the DBGrid
Lookup fields allow you to place a drop-down
list in the grid: limit user to a set of choices
determined by a DataSet component
Create a new DataSet component
(TTable/TQuery) to be the SOURCE of the data
for the drop-down list
Database Forms, ‘By Hand’…
The Fields Editor
In the New Field Dialog from the Fields Editor,
select Lookup as the field type
Set DataSet to the dataset where the data is
COMING FROM
 Key Fields e.g. SupplierID in the Stock Table will
be used to lookup the SupplierName (Result
Field) in the Supplier Table (DataSet), via the
SupplierID (LookupKeys) in the Supplier table.
So when the user chooses a supplier name from
the drop-down list, its corresponding SupplierID is
written to the Stock table when record is updated.
Database Forms, ‘By Hand’...
 Finally, make the original DBGrid field (i.e. SupplierID)
not visible
 To replicate this on another page with a SupplierID,
remove the original DBEdit field (which displayed
SupplierID), then drag the SupplierLookup field from the
Field Editor to the form
 Each TField Object has a Validation Event (Example 3)
 Filter the Table component records (like a Where clause)
Set Filtered to True
Specify the filter:
CustNumber = 1234 AND ShipDate < ‘1/1/98’
Database Forms, ‘By Hand’…
Finding a Record
Finding Records in a DataSet (Example 4)
Use the FindNearest method (only with a TTable
component) for an approximate search. Positions the
cursor on the first record that matches the criteria, or
on the first record whose values are greater than
those specified
Use the FindKey Method for an exact match
Both FindNearest and FindKey require IndexName to
be set before the search
Locate Method uses an index if available, otherwise
searches sequentially
FindKey, Locate return true/false, and position the
cursor on the record if found
Database Forms ‘By Hand’…
Referencing dataset records
The TField class represents a field in the
database, gives access to its attributes
FieldByName Method accesses the field
set field’s value with AsString, AsInteger, etc or Value
Table1.FieldByName(‘LastName’).AsString := Edit1.Text;
OR
Table1.FieldByName(‘LastName’).Value := Edit1.Text;
To Change a field’s value:
Table1.Edit;
Table1.FieldByName(‘LastName’).AsString := Edit1.Text;
Table1.Post;
Database Forms, ‘By Hand’…
Data-aware Components
Data-aware Components
 Use the ‘Field’ property to access the contents, display
NameEdit.Field.AsString := ‘Clown Fish’;
 DBGrid
use the Columns property to change the display
Options property controls behaviour e.g col. Sizing
 DBText: Label, DBEdit: Edit Box
 DBMemo: Memo
display large text fields
If AutoDisplay is False, Double-click to display
the data
Database Forms, ‘By Hand’…
Data-aware Components
 DBImage:
display binary large object data stored in image format
change via Clipboard at design, run-time or via code:
DBImage1.Picture.LoadFromFile(‘clouds.bmp’);
 DBListBox, DBComboBox
items for selection are specified via the Items property
when an item is selected, it is written to the
DataField field in the DataSet
 DBCheckBox
DBCheckBox1.ValueChecked := ‘On;Yes;True’;
Database Forms, ‘By Hand’…
Data-aware Components
 DBLookupListBox, DBLookupComboBox
Displays a list of values from a lookup field in a
dataset: ListSource, ListField
When an item is selected, its value is written to
another field in another dataset: DataSource, DataField
 DBCtrlGrid
create custom scrollable grid components
place any data components on the first cell of the grid,
and Delphi will duplicate those components for each
record in the dataset at run-time
Database Forms, ‘By Hand’…
Data-aware Components
 DBNavigator
Allows extensive manipulation of dataset records
first, next, previous, last, insert, delete, edit, cancel
edits, post edits, refresh
ConfirmDelete, VisibleButtons property
Add, remove buttons at design, run-time (Examp. 5)
simulate a DBNavigator button-click (Example 6)
 Display fly-over help
Set ShowHint property to True
Use the Hints property String list editor to enter a
separate line of hint text for each button
Database Forms, ‘By Hand’…
Dataset programming
Providing Default Values, Appending via Code
The DataSet event OnNewRecord fires when an app.
inserts or appends a new record into a dataset.
insert your own ‘next’ primary key, today’s date etc
procedure TForm1.Button1Click(Sender: TObject);
begin
Table1.Append; //create a new record
Table1.FieldByName(‘Name’).AsString := Edit1.text;
Table1.FieldByName(‘Age’).AsInteger :=
StrToInt(Edit2.text);
Table1.Post;
end;
TQuery Components
Use ExecSQL for SQL statements which do
not return records:
Query1.SQL.Clear;
Query1.SQL.Add(‘insert into CountryTable’);
Query1.SQL.Add(‘ (Name, Capital) ‘);
Query1.SQL.Add(‘ (values (“Australia”, “Canberra”) ’);
Query1.ExecSQL;
 Can also build up the query as a string, then ‘Add’ the
whole string
var query: string
query := query + ‘Select * from Orders where ‘; etc
TQuery Components
Using Parameters in SQL statements:
 At design time, the SQL statement is
select * from CountryTable where name = :ParCountry
 At run-time e.g. OnChange event of the edit control
With Query1 do
begin
DisableControls;
Close;
ParamByName(‘ParCountry’).AsString := edtCountry.Text;
Open;
EnableControls;
end;
TQuery components
 Use the Datasource property to automatically link
to another datasource and access any matchedname fields, which can be used as parameters in
the SQL for the Query.
 Useful in master/detail forms. E.g.
table1/ds1 accesses the customer table,
query1/ds2 retrieves orders which match the custno
of the ‘current’ customer record: (custno is a foreign
key in the orders table)
set the Datasource of the Query to point to ds1:
custno becomes the parameter for the query e.g.
select * from orders, customer where orders.custno =
customer.custno
Dataset Programming
Disable the data-aware controls when
making changes or performing long
operations on the dataset (e.g. loop)
Use a bookmark to keep track of where
you started in the dataset
Use a try-finally block to catch exceptions
that may occur when reading table data
See Example 8
Tutorial Week 10
 Use the tute exercises to become familiar with Delphi 5
 Start Delphi Assignment Task 1 (create Paradox DB tables,
add data)
 Start Delphi Assignment Task 3
Begin with the Database Form Wizard
Add, customise to meet task requirements
 Note: Because of limited availability of Delphi 5 (K1.07,
K1.08, K1.10, B3.46, B3.43, B3.43B, T2.16), and since
most of you will not have the software at home, tutes will
be used to work on the weekly assignment tasks.
 The remaining 4 Tute Exercise marks will be added to the
Delphi Assignment, which will now be /14