INF354 Advanced Programming

1
INF354 Advanced Programming

• Study Unit 2 Database Programming Part 2

2
Study objectives

• Students must be able to
• Understand Delphis database structure
• Write database programs

3
Database Applications
Delphi Database Explorer
Database Management System
Other Interfaces

• Paradox
• Interbase
• Access
• Oracle
• MS SQL Server
• DB2
• Etc.

Database

• ODBC
• BDE

Database Application Interface
Delphi Application
Data Module
Form1
Form2
Form1
4
Major steps in creating a DB application

• Create database (Using DB interface)
• Create an ODBC or BDE alias
• Link Database to Delphi (using alias created in
  step 2)
• Use linked database

5
Databases

• Databases consists of multiple related tables
• The most commonly used databases are
• Paradox
• Interbase
• Access
• Oracle
• MS SQL Server
• DB2 (Mainframe)
• But it is also possible to use for instance
  MSExcel and text files as databases

6
BDE

• The BDE provides an interface for applications to
  connect to any database for which the BDE has
  drivers
• Programmers need not be worried about how to
  access data in a certain type of file, he/she
  need only be concerned with what data must be
  retrieved from/saved to the database.
• All databases that will be accessed using the BDE
  must first be set up using the BDE administrator,
  once this has been done the database is assigned
  an alias.
• An alias is merely a name given to the database
  so that it can be easily accessed.
• It is a Borland product, and used with Borland
  products

7
ODBC

• Is an interface very similar to BDE but it is
  only applicable to SQL databases
• It is more general than BDE and would be used,
  for instance, if you link to Visual Basic
• If you define an ODBC alias you would
  automatically see it in BDE (In some cases not,
  then change DATABASE NAME property to path of
  database)
• NOTE rather use ODBC than BDE

8
Accessing BDE and ODBC
Found under Start\Settings\Control Panel
9
Setting up a BDE alias

• Step 1 Open BDE
• Screen shows all BDE connected databases

10
Setting up a BDE alias

• Step 2 Open menu ObjectNew

11
Setting up a BDE alias

• Step 3 Select appropriate driver

12
Setting up a BDE alias

• Step 4 Change databases name on the left side
  (optional)

13
Setting up a BDE alias

• Step 4 Fill in the following information on the
  right hand

14
Setting up an ODBC alias
Step 1 Open ODBC (User DSN)
15
Setting up an ODBC alias
Step 2 Click Add button
16
Setting up an ODBC alias
Step 3 Select appropriate driver
17
Setting up an ODBC alias
Step 4 Fill in applicable fields (1 of
2)
18
Setting up an ODBC alias
Step 4 Fill in applicable fields (2 of
2)
19
Linking DB to Delphi (using alias)
Step 1 Open form or Datamodule
(Menu FileNewData Module)
Note Better to place data access components once
in data module
20
Linking DB to Delphi (using alias)
Step 2 Copy database component (Component
Data Access) to form or data module
21
Linking DB to Delphi (using alias)
Step 3 Invoke Database Editor (double-click
database component or right-click)
22
Linking DB to Delphi (using alias)
Step 4 Select database alias

• Fill in Name field to create a local alias to the
  database
• Choose BDE alias

Uncheck the Login Prompt control to prevent the
automatic login dialog. If the automatic login
prompt is omitted, the application must supply
the user name and password by either providing
hard-coded parameter overrides, or by supplying
an OnLogin event handler that sets the values for
these parameters.
23
Linking DB to Delphi (using alias)
Step 5 Set parameters (not already
set)
Specify the name of an existing BDE alias.
Normally chosen in previous step. GLOBAL ALIAS
Set Connected to True to establish a database
connection without opening a dataset. Normally
chosen in previous step
Specifies the name of the database to associate
with this database component. LOCAL ALIAS
Note click on a parameter, press F1 and the help
for that parameter will appear
24
Use linked database

• Step 1 Set up the following database access
  components
• Table
• SQL
• UpdateSQL
• Stored Procedure

25
Use linked database
Step 1a Set up a table
Specifies whether or not a dataset is open. NOTE
if database is disconnected all linked tables,
queries, etc. will be made inactive
Choose alias of connected database. NOTE both
local and global aliases will be shown. Rather
choose local alias.
Choose the applicable table
Note click on a parameter, press F1 and the help
for that parameter will appear
26
Use linked database
Step 1b Set up a query
Specifies whether or not a dataset is open. NOTE
if database is disconnected all linked tables,
queries, etc. will be made inactive
Choose alias of connected database. NOTE both
local and global aliases will be shown. Rather
choose local alias.
Contains the parameters for a querys SQL
statement.
Contains the text of the SQL statement to execute
for the query.
27
Use linked database
Step 2 Set up a datasource component

Specifies the dataset for which the data source
component serves as a conduit to data-aware
controls.
28
Use linked database
Step 3 Set up data aware data controls
(Components Data Control)
Identifies the link to the dataset where the
data-aware component finds its data.
29
Common DB programming tasks

• The following common DB programming tasks will
  be explained in the following sections
• Viewing and maintaining DB data
• Navigating DB data
• Using master/detail tables
• Input validation
• Formatting fields
• Creating calculated fields
• Lookup fields

30
Viewing and maintaining DB data

• Multiple rows

31
Viewing and maintaining DB data

• Multiple rows (manipulate columns)

Right-click on grid to get Columns Editor
32
Viewing and maintaining DB data

• Multiple rows (manipulate columns)

• Order of columns can be changed by drag-and-drop
• Columns can be added
• Columns can be deleted

Column properties can be set individually
33
Viewing and maintaining DB data

• Single field (Text)

34
Viewing and maintaining DB data

• Single field (Image)

35
Navigating DB data
First record Prior record Next record Last
record Insert record Delete record Edit
record Post edit Cancel edit Refresh data
36
Using master/detail tables
The two tables are related with Orders table the
master and Items the detail table. Note that the
second table show all records in the second table

37
Using master/detail tables

Note that the second table shows only the records
related to the selected record in the first table.
38
Formatting fields
Right-click on table to get Field Property Editor
Right-click inside Field Property Editor
39
Formatting fields
For a list of field types see Help Find Working
with field components
Field formatting and validation properties can be
set here. See for instance DisplayFormat. To get
quick help highlight property and press F1.
40
Formatting fields
String type fields have an extra property EditMask

41
Creating calculated fields

42
Creating calculated fields
procedure TForm1.EmployeeCalcFields(DataSet
TDataSet) begin EmployeeSalaryIncrease.Va
lue EmployeeSalary.Value 1.1 end

43
Lookup fields
When entering orderno we need to ensure that only
valid order numbers from the Orders table can be
selected using a lookup table.

44
Theory class exercise

• Create three tables
• Client (ClientID, Surname, Initials, Title)
• Orders (Client, OrderNo, Quantity, Amount)
• Title (TitleID, Description)
• Create a screen that shows all clients with their
  respective orders
• Use navigation bar
• The client title must be selected in a drop down
  box
• Do basic validation
• Do basic output formatting