Rozdział 17

Delphi a Interbase

W tym rozdziale przedstawimy Interbase RDBMS. Omówimy elementy SQL oraz
niektóre zagadnienia dotyczące wspólnej pracy InterBase i Delphi. Opiszemy także
podstawy administrowania serwerami InterBase.

Zaczniemy oczywiście od uruchomienia serwera.

Uruchamianie serwera InterBase

Sposób uruchomienia serwera w dużym stopniu zależy od systemu operacyjnego.
Mając na uwadze fakt, że nie jest to książka poświęcona wyłącznie systemowi
InterBase, ograniczymy nasze rozważania tylko do najpopularniejszych - Windows
95 i Windows NT.

Po przeprowadzeniu standardowej instalacji, serwer InterBase uruchamia się
automatycznie przy każdym starcie Windows. Na pasku zadań w Windows 95/NT
pojawia się wtedy jego ikona ( lub ikona na pulpicie, jeśli używamy Windows NT
3.51 ).

Klikając ikonę na pasku zadań prawym przyciskiem myszy, uzyskujemy dostęp do
podstawowych ustawień serwera. Jeśli np. wybierzemy - z menu rozwijalnego -
pozycję

Startup Configuration

, serwer będzie startował równolegle z Windows. Po

wybraniu z kolei

Properties

, będziemy mogli odczytać dopuszczalną i aktualną

liczbę połączeń z serwerem.

Łączenie się z serwerem

W przeciwieństwie do omówionych dotychczas serwerów - aby uzyskać dostęp do
serwerów InterBase, nie musimy definiować oddzielnego połączenia. Jeśli
korzystamy z lokalnego serwera InterBase, oprogramowanie klienta (WISQL,
BDE, itp. ) odwołuje się bezpośrednio do pliku bazy danych (nie ma potrzeby
podawania jakichkolwiek danych odnoszących się do serwera).

W przypadku odległego serwera (InterBase), niezbędne do połączenia informacje
zależą od wyboru protokołu sieciowego. Jeśli łączymy się poprzez TCP/IP, wtedy
plik HOSTS musi zawierać dane dotyczące naszego serwera, np.:

100.10.15.12 marketing

506

Część III

Także plik TCP SERVICES powinien zostać uzupełniony o linię definiującą
protokół dostępu InterBase:

gds_db 3050/tcp

Oczywiście odpowiednie zmiany w

plikach dokonywane są automatycznie

w czasie instalacji serwera. Gdy tylko skonfigurujemy dostęp poprzez TCP/IP,
możemy korzystać z WISQL i innych narzędzi InterBase. Należy jeszcze raz
podkreślić, że podane wymagania dotyczące plików inicjacyjnych odnoszą się
tylko do TCP/IP. NetBEUI ani IPX/SPX nie nakładają tego typu wymagań.

Konfigurowanie Aliasów BDE

Teraz utworzymy alias BDE, który umożliwi aplikacjom w Delphi dostęp do
serwera. Ten temat był już omawiany wcześniej, dlatego teraz przedstawimy tylko
kolejne kroki, które pozwolą poprawnie go skonfigurować.

Chcąc zbudować alias BDE powinniśmy wybrać jedno z

narzędzi: BDE

Administrator lub Delphi Database Explorer. Opisany poniżej sposób
postępowania odnosi się do programu BDE Administrator.

1. Uruchamiamy BDE Administrator, klikamy prawym przyciskiem myszy fiszkę

Databases

i wybieramy

New

. W oknie dialogowym

New Database Alias

wybieramy, z listy typów aliasów,

INTRBASE

i klikamy

OK

.

2. Wpisujemy nazwę nadaną tworzonemu aliasowi .

3. Konfigurujemy go przez odpowiednie ustawienie parametrów na stronie

Definition

4. Klikamy parametr

SERVER NAME

i wprowadzamy nazwę serwera oraz plik

bazy danych, z którym chcemy się łączyć. Przyjęto konwencję, że ten plik ma
rozszerzenie

GDB

. Wprowadzane dane powinny przyjmować postać:

MIS:/data/interbase/accounting.gdb

gdzie

MIS

jest nazwą twojego serwera a

/data/interbase/

accounting.gdb

jest pełną ścieżką do pliku zawierającego bazę danych.

WSKAZÓWKA

Jeśli korzystamy z lokalnego serwera InterBase, pole

SERVER NAME

może

pozostać niewypełnione. Wystarczy podać tylko pełną ścieżkę dostępu do pliku
zawierającego bazę danych., np.

C:\DATA\INTERBASE\ACCOUNT.GDB

Rozdział 17 Delphi w Interbase

507

5. Jeśli podamy nazwę użytkownika (parametr

USER NAME

), to będzie on

domyślnie przyłączany do serwera. Podana nazwa będzie też umieszczana we
wbudowanym w Delphi oknie dialogowym logowania.

Jak widać jest kilka innych parametrów, którym możemy nadać odpowiednie
wartości. W szczególności jeden z nich powinien być zawsze ustawiony dla
aliasów InterBase -

ENABLE BCD. D

ecyduje on bowiem o tym, jak BDE będzie

interpretował pewne typy danych numerycznych. Chociaż paramatr ma w swej
nazwie BCD, to jednak jego zasięg nie ogranicza się tylko do danych BCD
(Binary-Coded Decimal), ale dotyczy także typów zmiennoprzecinkowych:
DECIMAL i

NUMERIC. Gdy

ENABLE BCD

jest ustawiony na

FALSE

(ustawienie domyślne), pola DECIMAL lub NUMERIC będą traktowane przez
Delphi jako integers (całkowite). W konsekwencji elementy rozpoznające rodzaj
danych uniemożliwią nam wprowadzanie części dziesiętnych liczb. Dlatego
powinniśmy ustawiać

ENABLE BCD

na

TRUE

we wszystkich aliasach.

Po wybraniu

Apply

wprowadzone zmiany zostają zapamiętane.

Rozwiązywanie problemów połączeniowych z serwerem InterBase

Poniżej zebraliśmy kilka praktycznych porad, które mogą okazać się przydatne
w

przypadku napotkania problemów połączeniowych w naszych aplikacjach

w Delphi.

1. Skorzystamy najpierw z

narzędzia Interbase Communication Diagnostic,

umieszczonego w folderze InterBase 4.2. Klikamy przycisk

Test

ze strony

DB

Connection

. Brak połączenia może być spowodowany złą konfiguracją aliasu

BDE. W takiej sytuacji powinniśmy powrócić do programu BDE Administrator
i upewnić się, czy podane ustawienia są poprawne. Jeśli nadal nie będziemy
uzyskiwali połączenia z DB Connection, powinniśmy przejść na stronę

NetBEUI

lub

Winsock

( wybierając odpowiednią fiszkę) i kliknąć

Test

. Jeśli

choć jeden będzie funkcjonował poprawnie, to prawdopodobną przyczyną
braku połączenia są złe ustawienia parametrów. Jeśli żadna z prób się nie
powiedzie (a mamy pewność, że serwer jest uruchomiony), proponujemy
kontynuowanie diagnostyki według zamieszczonego poniżej opisu.

2. Jeśli InterBase WISQL umożliwia nawiązanie komunikacji z serwerem,

natomiast nadal występują problemy połączeniowe w naszej aplikacji, to
prawdopodobnie konfiguracja aliasu DBE jest niewłaściwa. W takiej sytuacji
powinniśmy powrócić do programu BDE Administrator i upewnić się, czy
podane ustawienia są poprawne ( w szczególności nazwa serwera).

3. Brak połączenia poprzez WISQL świadczy o wadliwym funkcjonowaniu

protokołu sieciowego. Jeśli korzystamy z TCP/IP, łączność z serwerem można
sprawdzić za pomocą programu PING, który jest dostarczany z Windows 95 /

508

Część III

NT. Powinniśmy spróbować łączenia poprzez nazwę komputera zapisaną
w pliku HOSTS lub poprzez jego adres IP.

4. Jeśli korzystamy z nazwanych potoków (ang. named pipes) - do komunikacji

z serwerem pracującym w Windows NT powinniśmy użyć polecenia

net

view \\ servername

, gdzie

servername

jest nazwą komputera w sieci

NT, na którym funkcjonuje serwer SQL. Po poprawnej realizacji

net view

posłużymy się instrukcją

netuse \\servername\IPC$,

zamieniając

servername

na nazwę naszego serwera. Wystąpienie jakichkolwiek

nieprawidłowości wskazuje na brak połączenia z komputerem serwera w sieci.
W takim przypadku proponujemy zwrócić się do administratora systemu NT.
Źródłem zakłóceń może być też wadliwa obsługa potoków na serwerze.

5. W sytuacji, gdy uzyskaliśmy połączenie poprzez adres IP, co nie powiodło się

za pośrednictwem nazwy serwera, powinniśmy dokładnie sprawdzić dane
w pliku

HOSTS

, bowiem oprogramowanie klienta InterBase’a korzysta z niego

przy wyszukiwaniu serwera. W pliku

HOSTS

musi się znaleźć odpowiednia

linia z informacjami o każdym serwerze, z którym chcemy się połączyć np:

100.10.15.12 marketing

6. Gdy nie udało się nam połączyć z serwerem (ani poprzez nazwę ani adres IP),

to prawdopodobną przyczyną może być uszkodzenie sieci. Oczywiście
powinniśmy jeszcze raz sprawdzić adres IP serwera oraz uruchomić

PING

(podając adres

127.0.0.1

) - aby przekonać się, czy stos protokołu TCP/IP

funkcjonuje poprawnie. Jeśli test się nie powiedzie, to prawdopodobnie źle
skonfigurowano sam protokół. W takim przypadku proponujemy zwrócić się do
administratora sieci.

7. Może się też zdarzyć, że nie uzyskujemy połączenia poprzez WISQL, natomiast

PING umożliwia nawiązanie komunikacji. W takim przypadku możemy
skorzystać z Telnetu (dostarczanego wraz z Windows95/NT). Składnia wywołania
wygląda następująco:

TELNET 100.100.100.100

lub

TELNET hostname

gdzie

100.100.100.100

jest adresem TCP/IP komputera serwera a hostname

jego nazwą. Przyczyną braku połączenia poprzez Telnet może być wadliwe
działanie demona inet na serwerze.

8. Jeśli połączyliśmy się poprzez Telnet przy wciąż nie funkcjonującym WISQL,

powinniśmy się upewnić, czy oprogramowanie serwera InterBase’a zostało
poprawnie zainstalowane i uruchomione. Istotne jest także, czy plik

TCP

SERVICES

zawiera niezbędne informacje. Powinny one przyjmować postać:

Rozdział 17 Delphi w Interbase

509

gds_db 3050/tcp

9. Jeśli nasze działania nie przyniosły pożądanego rezultatu, powinniśmy

skonsultować się z administratorem sieci lub bazy danych.

Wstęp do SQL

Język SQL należy do podstawowych środków komunikowania się z serwerami baz
danych. Niniejszy rozdział jest wprowadzeniem do własnej, bogatej odmiany SQL,
wykorzystywanej przez InterBase. Zwrócimy tutaj uwagę na kilka właściwości,
które różnią tę implementację SQL od dostarczanych przez innych sprzedawców.
Aby móc poznawać w praktyce prezentowane elementy języka, wystarczy
zapewnić sobie dostęp do uruchomionego serwera i umieć się z nim komunikować
poprzez WISQL.

Tworzenie bazy danych

Zaczniemy od utworzenia bazy danych i umieszczenia w niej tymczasowych tabel,
które zostaną wykorzystane w podanych dalej przykładach. W tym celu posłużymy
się instrukcją

CREATE DATABASE

. Jej składnia zależy od konkretnej

implementacji SQL. Poniższy przykład przedstawia podstawową składnię
InterBase’a:

CREATE DATABASE "C:\DATA\IB\SALES" USER SYSDBA PASSWORD

➥

masterkey

Aby utworzyć bazę danych używając InterBase WISQL, korzystamy z polecenia

Create

Database

z menu

File

. A oto sposób postępowania:

1. Uruchamiamy program interaktywnej obsługi SQL (WISQL) - albo z grupy

programów InterBase’a albo z paska zadań z InterBase Server Manager.

2. Wybieramy polecenie

Create

Database

z menu

File

.

3. Wpisujemy pełną ścieżkę dostępu do bazy, którą chcemy utworzyć (np.

C:\DATA\IB\SALES.GB

) w

polu

Database

okna dialogowego

Create

Database

.

4. Podajemy nazwę użytkownika i hasło. Domyślne wartości tych parametrów to:

nazwa użytkownika-

SYSDBA

; hasło -

masterkey

.

5. Klikamy przycisk

OK

. InterBase powinien utworzyć bazę danych i podłączyć

nas do niej. Później - do łączenia się z istniejącą bazą - korzystamy z polecenia

Connect to

Database

z menu

File

.

W oknie dialogowym

Create DataBase

,w części

Database Options

, można ustawić

pewne parametry bazy, między innymi rozmiar stron (w bajtach) tworzących bazę.

510

Część III

Domyślnie jest przyjmowane 1024 (1K), a dopuszczalne są jeszcze wartości 2048,
4096 lub 8192 ( np.

PAGE_SIZE=2048

).

Można też określić dodatkowe pliki, które zostaną utworzone wraz
z podstawowym plikiem bazy danych oraz podać dla nich początkowe wielkości.

WSKAZÓWKA

Utworzenie bazy w WISQL jest możliwe tylko przez wybór odpowiedniej pozycji
z menu. Mamy jednak do dyspozycji program ISQL (umieszczany domyślnie
w kartotece

C:\Program Files\Borland\IntrBase\Bin

), umożliwia-

jący wydawanie w linii poleceń instrukcji SQL, dostępnych w WISQL tylko za
pośrednictwem odpowiednich pozycji menu.

Cienie (shadows)

Serwer InterBase pozwala tworzyć specjalny obiekt nazwany cieniem, do
utrzymywania lustrzanego odbicia bazy danych na innym dysku (lub dyskach).
Dzięki temu może być ona nadal dostępna - nawet w przypadku jej uszkodzenia na
podstawowym dysku lub uszkodzenia samego dysku.

Wydanie instrukcji

CREATE SHADOW

spowoduje stworzenie cienia dla aktywnej

bazy danych. Składnia polecenia jest następująca:

CREATE SHADOW 1 "sales.shd" LENGTH 5000

Podajemy unikalny numer cienia jako pierwszy parametr instrukcji. Jeśli numer
zostanie pominięty lub będzie się powtarzał, InterBase nie utworzy cienia.

Dublowanie bazy wiąże się z podwojeniem liczby zapisów, które są wykonywane
w czasie przeprowadzanych zmian. Należy się z tym liczyć przy wyborze
lokalizacji dla cienia. Praca z powolnym dyskiem może istotnie obniżyć wydajność
systemu

Instrukcja

CREATE SHADOW

ma specjalny parametr pozwalający ustalić

zachowanie się systemu, gdy cień będzie niedostępny. Po wybraniu wartości

AUTO

aplikacje klienta będą nadal przyłączone do bazy, a cień zostanie skasowany.
Ustawienie

MANUAL

zablokuje wszystkie połączenia - aż do chwili, gdy cień

będzie ponownie dostępny lub zostanie usunięty (instrukcją

DROP SHADOW).

CONDITIONAL

pozwoli kontynuować połączenia i

spowoduje utworzenie

nowego cienia. Jeśli parametr jest ustawiony na

CONDITIONAL,

wtedy

w przypadku uszkodzenia bazy jej miejsce zajmie cień i jednocześnie zostanie
utworzony cień nowo promowanego cienia. A oto jeszcze jeden przykład:

Rozdział 17 Delphi w Interbase

511

CREATE SHADOW 1 CONDITIONAL "d:\shadows\sales.shd" LENGTH

➥

5000 ;

Oczywiście zastosowane słowo kluczowe

CONDITIONAL

określa zachowanie się

bazy, gdy cień będzie niedostępny.

Instrukcja CONNECT

Instrukcja InterBase SQL - CONNECT - umożliwia łączenie się z istniejącą bazą
danych. Posiada ona następującą składnię:

CONNECT ServerAndDatabasePath USER "ValidUser" PASSWORD

➥

"UserPassword"

ServerAndDatabasePath

oznacza pełną ścieżkę do serwera i bazy danych,

z którą chcemy się połączyć. Jeśli serwer jest uruchomiony lokalnie, wystarczy
podać tylko ścieżkę do bazy danych.

ValidUser

i

UserPassword

to

odpowiednio: nazwa użytkownika i hasło na naszym serwerze bazy danych.
W przypadku lokalnej bazy danych instrukcja

CONNECT

może mieć postać:

CONNECT "C:/DATA/IB/SALES.GDB" USER "SYSDBA" PASSWORD

➥

"masterkey"

Każdorazowo, gdy łączymy się z inną bazą danych, jesteśmy odłączani od
bieżącej.

DISCONNECT

umożliwia zerwanie połączenia (ustalonego wcześniej poleceniem

CONNECT)

. Typowy przykład:

DISCONNECT ALL;

Ten sam efekt osiągniemy zastępując

ALL

. przez

DEFAULT

.W WISQL jesteśmy

zmuszeni do korzystania z poleceń

Connect to DataBase

i

Disconnect

from Database

z menu

File

(

CONNECT

i

DISCONNECT

nie działają bowiem

w WISQL bezpośrednio, ale można ich użyć w linii poleceń w ISQL).

Tworzenie tabel

Gdy jesteśmy już podłączeni do bazy, możemy rozpocząć definiowanie obiektów.
Prawie każde pojęcie odnoszące się do relacyjnych baz danych może być
zaprezentowane za pomocą trzech tabel. Utwórzmy więc na początek trzy tabele,
dzięki którym będziemy mogli zrozumieć istotę omawianych zagadnień.
Posłużymy się poleceniem SQL:

CREATE TABLE

. Wprowadźmy odpowiednią

instrukcję korzystając z programu WISQL, aby utworzyć tabelę CUSTOMER:

512

Część III

CREATE TABLE CUSTOMER
(
CustomerNumber int

NOT

NULL,

LastName Char(30),
FirstName char(30),
StreetAddress char(30),
City char(20),
State char(2),
Zip char(10),
)

Dalej utwórzmy tabelę SALE :

CREATE TABLE SALE
(
SaleNumber int NOT

NULL,

SaleDate date,
CustomerNumber int

NOT

NULL,

ItemNumber int NOT

NULL,

Amount float
)

Po zbudowaniu tabeli SALE, pozostała do utworzenia tylko tablica ITEM:

CREATE TABLE ITEM
(
ItemNumber int

NOT

NULL,

Description char(30),
Price float
)

Tabele zewnętrzne

InterBase wykorzystuje pewne interesujące odmiany instrukcji

CREATE TABLE

.

Jedna z takich alternatywnych postaci umożliwia tworzenie tabel zewnętrznych
w stosunku do bazy, np.:

CREATE TABLE SALE_HISTORY EXTERNAL FILE

➥

C:\DATA\IB\SALEHIST.DAT

(
SaleNumber int

NOT

NULL,

SaleDate date,
CustomerNumber int

NOT

NULL,

ItemNumber int

NOT

NULL,

Amount float
)

Jeśli określony w poleceniu plik zewnętrzny nie istnieje, wtedy InterBase go
utworzy, w przeciwnym wypadku pozostanie nie zmieniony. Dzięki tej instrukcji
można odwoływać się do danych utworzonych poza InterBase - np. tabeli, w której
przechowuje się dane z innego systemu DBMS. Jeśli wyspecyfikowana struktura

Rozdział 17 Delphi w Interbase

513

tabeli w InterBase będzie zgodna ze strukturą danych zewnętrznych, można
skopiować wiersze z pliku zewnętrznego do wewnętrznej tabeli poleceniem:

INSERT...SELECT.

Kolumny obliczane (Computed columns)

Instrukcja

CREATE TABLE

w InterBase umożliwia także definiowanie kolumn

obliczanych. Poniżej przedstawiamy tabelę ITEMS, rozszerzoną o dodatkową
kolumnę obliczaną - GovernmentPrice :

CREATE TABLE ITEM
(
ItemNumber int

NOT

NULL,

Description char(30),
Price float
GovernmentPrice COMPUTED BY (Price-Price* .15)
)

W tym przypadku w kolumnie

GovermentPrice

uwzględniono 15% zniżki (w

stosunku do ceny w kolumnie

PRICE

) dla klientów rządowych. Ponieważ nie

został w niej podany typ danych, Interbase ustala go na podstawie typu z kolumn
wykorzystywanych do obliczeń. Definicje kolumn wykorzystywanych przez
kolumnę obliczaną muszą oczywiście - na liście kolumn polecenia

CREATE

TABLE -

wystąpić przed nią.

Podobne możliwości daje oczywiście instrukcja SELECT czy perspektywa.
Jednak w tym wypadku wpisujemy obliczenia bezpośrednio do definicji tabeli.
Jest to dobry sposób na ustalenie pewnych zależności obliczeniowych. Wtedy
bowiem aplikacje i inne obiekty SQL mogą po prostu pobierać obliczone wartości
z kolumny obliczanej.

Kolumny tablicowe (Array Columns )

W uzupełnieniu prostych typów danych InterBase pozwala na zdefiniowanie
kolumn, które są tablicami danych zadanego typu. Zdefiniowanie kolumny jako
tablicy pozwala traktować naturalnie zgrupowane elementy danych jako zbiór.
Przedstawiamy przykład zmodyfikowanej tabeli CUSTOMER, która zawiera
kolumnę tablicową:

CREATE TABLE CUSTOMERARRAY
(
CustomerNumber int

NOT

NULL,

LastName Char(30),
FirstName char(30),
StreetAddress char(30)[3],
City char(20),

514

Część III

State char(2),
Zip char(10),
)

Należy zwrócić uwagę na oznaczenie "[3]" po prawej stronie w definicji kolumny
StreetAddress. Zgodnie z podanym opisem będzie ona interpretowana jako
trzyelementowa tablica elementów typu char (30). Umożliwia więc zapisanie
poszczególnych części adresu w trzech osobnych liniach.

Oczywiście możemy też podać zakres indeksów tablicy, np.

CREATE TABLE CUSTOMERARRAY
(
CustomerNumber int

NOT

NULL,

LastName Char(30),
FirstName char(30),
StreetAddress char(30)[1:3],
City char(30),
State char(2),
Zip char(10),
)

Także w tym przypadku StreetAddress jest jednowymiarową, trójelementową
tablicą lecz indeksowaną liczbami od 1 do 3, a nie - jak w poprzednim przykładzie
- domyślnie od 0 do 2.

Tablice w kolumnach mogą być także wielowymiarowe ( największy wymiar 16).
Prezentuje to prosty przykład:

CREATE TABLE HOURLY_SAMPLES
(
SampleNo int

NOT

NULL,

SampleDate DATE

NOT

NULL,

WeeklySampleValues int[6,23]
)

Zdefiniowana powyżej kolumna

WeeklySampleValues

jest dwuwymiarową

tablicą, w której można gromadzić dane typu int, odnoszące się do każdej godziny,
przez cały tydzień.

Rozdział 17 Delphi w Interbase

515

OSTRZEŻENIE

Zanim wykorzystamy kolumny tablicowe, w naszych bazach danych warto
przeanalizować pewne konsekwencje implementacyjne. Kolumny tablicowe są
istotnie powtarzającymi się grupami. Mówiąc formalnie naruszają pierwszą postać
normalną. Wymagają także dodatkowego nakładu pracy przy podstawianiu (nie
można podstawić wartości kolumny tablicowej używając SQL), formatowaniu (np.
przy raportach) oraz przy zapytaniach. Jest także wiele ograniczeń nałożonych na
kolumny tablicowe, które nie dotyczą innych typów danych ( np. nie mogą być one
przekazywane lub zwracane przez procedury pamiętane). Warto ostrożnie
wyważyć wszystkie za i przeciw wprowadzaniu do struktury swojej bazy danych
takich, utrudniających manipulowanie danymi, elementów.

Specjalne wartości domyślne kolumn (special column defaults)

Oprócz definiowania stałych jako wartości domyślnych kolumn, InterBase
umożliwia użycie trzech specjalnych słów kluczowych, definiujących wartość
domyślną w kolumnie:

USER,TODAY and NOW

.

USER

oznacza nazwę

aktualnego użytkownika;

TODAY

- aktualną datę (analogicznie do funkcji Delphi -

Date

), natomiast

NOW

- aktualną datę i czas ( zgodna z funkcją

Now

w Delphi).

Poniżej przedstawiamy przykład zastosowania omówionych elementów:

CREATE TABLE REPORTLOG
(ReportUser VARCHAR(20) DEFAULT USER,
ReportData DATE DEFAULT "TODAY",
ReportDateTime DEFAULT "Now"

Warto zaznaczyć, że

"TODAY"

oraz

"NOW

" muszą być podane w cudzysłowie,

natomiast

USER

nie. Tekst

USER

podany w cudzysłowie będzie oznaczał wartość

"USER"

a nie nazwę aktualnego użytkownika.

Dodawanie i usuwanie kolumn

Dodając lub usuwając kolumnę z istniejącej tabeli korzystamy z instrukcji

ALTER

TABLE

. W przeciwieństwie do niektórych serwerów baz danych (np. Microsoft

SQL Server) InterBase Server umożliwia usuwanie kolumn. Składnia dla
dodawania kolumn wygląda następująco :

ALTER TABLE CUSTOMER
ADD PhoneNumber char(10)

natomiast dla usuwania:

ALTER TABLE CUSTOMER
DROP PhoneNumber

516

Część III

Oczywiście nie można rozszerzyć niepustej tabeli o nową kolumnę z zastrzeżeniem

NOT NULL.

Wtedy bowiem nie można byłoby odpowiednio uzupełnić

umieszczonych w niej wierszy

.

Więzy (constrains)

Więzy to mechanizmy, dzięki którym możemy ograniczyć rodzaj danych
umieszczanych w kolumnie lub powiązać je ze sobą. Pozwalają także określić
wartości domyślne dla kolumn. Definiuje się je dołączając odpowiednie instrukcje
do

CREATE TABLE

albo

ALTER TABLE

. Jednym z przykładów nakładania

więzów jest tworzenie klucza głównego:

ALTER TABLE CUSTOMER
ADD PRIMARY KEY (CustomerNumber)

W tym przykładzie zdefiniowaliśmy klucz główny dla tabeli CUSTOMER jako
pole

CustomerNumber

. Dzięki temu, na bazie pola

CustomerNumber

zostanie utworzony unikalny indeks. Oczywiście do zdefiniowania klucza
głównego tabeli nie można użyć kolumny, która zezwala na użycie wartości

NULL

.

Klucz obcy definiuje kolumnę w jednej tabeli, której wartości muszą znajdować
się innej tabeli. Nie określa jednoznacznie wierszy, tak jak jest to w przypadku
klucza głównego. Musi być jednak kluczem głównym lub unikalnym w tabeli, do
której się odnosi. Dodanie klucza obcego powoduje, że SQL Server buduje wtórny
indeks, bazujący na polu kluczowym. Oto odpowiedni przykład:

ALTER TABLE SALE
ADD CONSTRAINT INVALID_CUSTOMER_NUMBER FOREIGN KEY

➥

(CustomerNumber)REFERENCES CUSTOMER

W przykładzie zdefiniowano pole

CustomerNumber

w tabeli SALE, jako klucz

obcy odnoszący się do tej samej kolumny w tabeli CUSTOMER. Nałożone więzy
powodują, że aby numer klienta ( ang. customer number) mógł być wprowadzony
do tabeli SALE, musi najpierw istnieć w tabeli CUSTOMER. Także numery
używane w tabeli SALE nie mogą zostać usunięte z tabeli CUSTOMER.
Możliwość wymuszenia zależności pomiędzy dwiema tabelami przez
zadeklarowanie ich relacji w SQL jest nazywana deklaratywną spójnością
referencyjną (ang. declarative referential integrity). Ten termin znaczy po prostu,
że spójność danych w tabelach związanych relacją jest zapewniana przez
zdefiniowanie (lub zadeklarowanie) tej relacji w bazie, a nie przez kod programu.

Trzeci typ więzów pozwala ustalić zakres dozwolonych wartości dla
wprowadzanych danych np.

Rozdział 17 Delphi w Interbase

517

ALTER TABLE CUSTOMER
ADD CONSTRAINT INVALID_STATE CHECK (State in ('OK', 'AR',

➥

'MO'))

Zwracamy uwagę na celowość użycia elementów negacji w

konwencji

nazewniczej dotyczącej więzów. Oprogramowanie pracujące po stronie
użytkownika, które zapobiega naruszeniu więzów, ma dostarczać użytkownikowi
łatwą do poprawnego zinterpretowania informację. Gdyby informacja o błędzie
była sygnowana

VALID_STATE,

użytkownik mógłby się nie zorientować, co jest

przyczyną problemu. Dobrze dobrana nazwa ograniczenia, która służy jako
informacja zwrotna, będzie dla użytkownika wystarczającą wskazówką, w jaki
sposób próbowano naruszyć więzy. Dzięki temu nie musimy zastępować, swoimi
własnymi, komunikatów generowanych przez wyjątki obsługiwane w Delphi.

Testowanie funkcjonowania więzów

Każde więzy nałożone na bazę powinny zostać sprawdzone. Najlepszym testem
będzie próba ich naruszenia. Np. aby sprawdzić więzy

INVALID_STATE,

zdefiniowane powyżej, wprowadzamy następującą instrukcję w

programie

WISQL:

INSERT INTO CUSTOMER (CustomerNumber,State)
VALUES(123,'CA')

Ponieważ istniejące więzy pozwalają tylko na wprowadzenie wartości

'OK',

'AR' i 'MO',

to próba umieszczenia tego wiersza w bazie powinna wywołać

komunikat błędu.

Jeśli wprowadzone więzy nie funkcjonują, należy przede wszystkim sprawdzić,
czy zostały one poprawnie zapisane w bazie oraz przeanalizować ich definicje.

Tworzenie indeksów

Do budowania indeksów w InterBase SQL służy instrukcja

CREATE INDEX.

Jej

podstawowa składnia wygląda następująco:

CREATE INDEX SALE02 ON SALE (SaleDate)

SALE02

jest nazwą nowego indeksu,

SALE

- tabelą, dla której budujemy indeks

a

SaleDate -

kluczem indeksu. Należy zaznaczyć, że nazwa indeksu w systemie

InterBase musi być unikalna w bazie, w której jest on umieszczony.

Instrukcja

CREATE UNIQE INDEX

tworzy indeks zapewniający unikalność

klucza np:

CREATE UNIQE INDEX SALE01 ON SALE (SaleNumber)

518

Część III

Domyślnie indeks jest uporządkowany według rosnących wartości klucza.
InterBase pozwala także na uporządkowanie malejące, jeśli przy tworzeniu
indeksu użyjemy słowa kluczowego

DESCENDING

np.

CREATE DESCENDING INDEX SALE03 ON SALE (Amount)

Tak utworzony indeks przyspiesza wykonanie niektórych zapytań, np.:

SELECT * FROM SALE
ORDER BY Amount DESCENDING

Włączanie i wyłączanie indeksu

InterBase dysponuje użytecznym mechanizmem wyłączania indeksu. Przyspiesza
to wprowadzanie zmian do tabeli podstawowej. Wyłączony indeks włącza się (
i przy tym przebudowuje) dopiero po wprowadzeniu całej partii nowych danych.
Dzięki temu można szybko dodać do tabeli dużą liczbę wierszy, unikając
aktualizacji indeksu przy każdym nowym wierszu. Oto przykład wyłączenia
indeksu:

ALTER INDEX SALE02 INACTIVE

i ponownego włączenia:

ALTER INDEX SALE02 ACTIVE

Ponowne uaktywnienie indeksu wymusza jego przebudowę. Należy zaznaczyć, że
wyłączenie indeksu jest możliwe dopiero po jego całkowitym zwolnieniu - nie
może on być wykorzystywany przez klucz główny lub obcy. Aby wyłączyć indeks
używany przez więzy należy najpierw je usunąć.

Wprowadzanie danych

Instrukcja

INSERT

służy do wprowadzania danych do tabeli InterBase. Każde

wystąpienie klauzuli

VALUES

w instrukcji

INSERT

umożliwia dodanie jednego

wiersza danych. Można też wprowadzić od razu kilka wierszy, wybierając je
z innej tabeli. W poniższym przykładzie dodano dane do wszystkich naszych tabel.
Najpierw trzy wiersze do tabeli CUSTOMER (posłużymy się WISQL):

INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName,

➥

StreetAddress, City, State, Zip)

VALUES(1,'Doe','John','123 Sunnylane','Anywhere','MO',

➥

'73115')

INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName,

➥

StreetAddress, City, State, Zip)

Rozdział 17 Delphi w Interbase

519

VALUES(2,'Doe','Jane','123 Sunnylane','Anywhere','MO',

➥

'73115')

INSERT INTO CUSTOMER (CustomerNumber, LastName, FirstName,

➥

StreetAddress, City, State, Zip)

VALUES(3,'Philgates','Buck','57 Riverside','Reo','AR',

➥

'65803')

Teraz dodamy trzy wiersze do tabeli ITEM i:

INSERT INTO ITEM(ItemNumber, Description, Price)
VALUES(1001,'Zoso LP',13.45)

INSERT INTO ITEM(ItemNumber, Description, Price)
VALUES(1002,'White LP',67.90)

INSERT INTO ITEM(ItemNumber, Description, Price)
VALUES(1003,'Bad Co. LP',11.45)

W końcu dodamy cztery wiersze do tabeli SALE:

INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,

➥

ItemNumber, Amount)

VALUES(101,'10/18/90',1,1001,13.45)

INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,

➥

ItemNumber, Amount)

VALUES(102,'02/27/92',2,100,67.90)

INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,

➥

ItemNumber, Amount)

VALUES(103,'05/20/95',3,1003,11.45)

INSERT INTO SALE (SaleNumber, SaleDate, CustomerNumber,

➥

ItemNumber, Amount)

VALUES(104,'11/27/97',4,1004,67.90)

Warto zauważyć, że nie trzeba uwzględniać wszystkich kolumn ani ich porządku
ustalonego przy tworzeniu tabeli ale lista podanych wartości musi być zgodna pod
względem liczby elementów i ich uporządkowania z listą kolumn w instrukcji

INSERT

, np.:

INSERT INTO ITEM (Price, ItemNumber)
VALUES(13.45, 1001)

520

Część III

Wartości specjalne

InterBase umożliwia korzystanie z domyślnych wartości specjalnych kolumn

USER

,

TODAY

i

NOW

, jak to zaprezentowano w poniższym przykładzie:

INSERT INTO REPORTLOG VALUES(USER, "TODAY", "NOW")

Instrukcja UPDATE

Instrukcja SQL

UPDATE

służy do modyfikacji danych w tabeli. Jej klauzula

WHERE

umożliwia wybór modyfikowanych wierszy. Oto odpowiedni przykład:

UPDATE CUSTOMER
SET Zip='65803'
WHERE City='SpringField'

Klauzula

WHERE

w

instrukcji

UPDATE

pozwala ograniczyć liczbę

modyfikowanych wierszy nawet do jednego ( zależnie od danych i warunku),
natomiast pomijając ją domyślnie zmodyfikujemy wszystkie rekordy:

UPDATE CUSTOMER
SET State='MO'

Poprawiając dane w kolumnie można korzystać z wartości innych kolumn danej
tabeli (także z tej kolumny). Załóżmy, że chcemy zwiększyć cenę każdego
produktu z tabeli ITEM o 7 %. Modyfikację tę można wykonać następująco:

UPDATE ITEM
SET Price=Price+(Price*.07)

Podobnie jak

INSERT

, instrukcja

UPDATE

umożliwia wykorzystanie specjalnych

wartości stałych kolumnowych InterBase, jak pokazano w przykładzie:

UPDATE SALE
SET Amount=Amount-(Amount*.25)
WHERE SaleDate>="TODAY"-30

Dzięki

TODAY

dokonano wyboru aktualizowanych wierszy. W ten sposób zostaną

zmodyfikowane wszystkie transakcje sprzedaży z ostatnich 30 dni.

Instrukcja DELETE

Dzięki instrukcji SQL

DELETE

mamy możliwość usuwania wierszy z tabeli. Np. -

aby opróżnić tabelę CUSTOMER wystarczy napisać:

DELETE FROM CUSTOMER

Rozdział 17 Delphi w Interbase

521

Instrukcja

DELETE

może też zawierać klauzulę

WHERE,

ograniczającą zakres

usuwanych wierszy. Oto odpowiedni przykład:

DELETE FROM CUSTOMER
WHERE LastName<>'Doe'

Kontrola Transakcji

Grupa powiązanych zmian w bazie nazywa się formalnie transakcją. Własne
transakcje inicjujemy za instrukcją

BEGIN TRANSACTION

. Instrukcja

COMMIT

służy do zachowania zmian dokonywanych w czasie transakcji, a instrukcja

ROLLBACK

pozwala na ich wycofanie. Obie odnoszą się tylko do zmian

dokonanych od chwili wywołania ostatniej instrukcji

COMMIT

.

ROLLBACK

nie

spowoduje wycofania zmian przyjętych poprzedzającymi ją instrukcjami

COMMIT.

InterBase WISQL otwiera transakcję automatycznie (używając odpowiednika
instrukcji

SET TRANSACTION

) przy pierwszym załadowaniu. Przy opuszczaniu

programu użytkownik jest proszony o

zatwierdzenie dokonanych zmian.

W każdym momencie pracy z WISQL można zapisać lub wycofać wprowadzone
zmiany, posługując się odpowiednio poleceniami

Commit Work

i

Rollback Work

z menu

File

.

UWAGA:

Oprócz zwykłej modyfikacji danych kontrola transakcji w InterBase uwzględnia
także instrukcje DDL (Data Definition Language). Za ich pomocą można
definiować i tworzyć obiekty bazy danych - takie, jak tabele i indeksy. Jeśli obiekt
zostanie utworzony wewnątrz transakcji, to w przypadku jej wycofania jest
usuwany. Ta zmiana jest natychmiast widoczna w całej bazie i na każdym
poziomie izolacji transakcji (Transaction Isolation Level-TIL), aktywnym w danej
chwili.

Instrukcja SELECT

Instrukcja SQL

SELECT

pobiera dane z określonych kolumn tabeli. Pozwoli nam

więc sprawdzić zawartość trzech naszych tabel. W

tym celu wykonajmy

trzykrotnie instrukcję o składni

SELECT * FROM tablename

, zamieniając za

każdym razem

tablename

na nazwę odpowiedniej tabeli (CUSTOMER

,

SALE

oraz ITEM). Jeśli wcześniej dodaliśmy proponowane dane, to każda z powstałych
w ten sposób tabel roboczych powinna mieć co najmniej trzy wiersze.

522

Część III

SELECT

* zwraca cała tabelę. Jeśli gwiazdkę zastąpimy listą nazw pól

oddzielonych przecinkami, to otrzymamy dane tylko z wybranych pól np.:

SELECT CustomerNumber, LastName, State FROM CUSTOMER

Wyrażenia kolumnowe

Instrukcja

SELECT

w SQL Server umożliwia podanie na liście kolumn nie tylko

samych nazw, ale także wyrażeń arytmetycznych, zbudowanych z wartości
kolumn, stałych i funkcji. Podajemy tutaj taki przykład zastosowania instrukcji

SELECT

, zwracającej rekordy z tabeli SALE, z wartością sprzedaży powiększoną

o $15 (opłatę za dostarczenie towaru):

SELECT SaleNumber, SaleDate, Amount+15 AmountPlusShipping
FROM SALE

Funkcje sumaryczne

Funkcje sumaryczne wykonują pewne obliczenia na zbiorach danych. Przykładami
takich funkcji są

COUNT, SUM, AVG, MIN

oraz

MAX

. Podamy teraz kilka

przykładów ich zastosowania:

SELECT COUNT(*) FROM CUSTOMER

To zapytanie daje w wyniku liczbę klientów w pliku.

SELECT MAX(Amount) FROM SALE

To z kolei podaje największą wartość sprzedaży w dolarach.

SELECT SUM(Amount) FROM SALE

Natomiast wynikiem tego zapytania jest całkowita wartość sprzedaży w dolarach.

Klauzula WHERE

Klauzula SQL

WHERE

umożliwia wybranie wierszy zwracanych przez instrukcję

SELECT

. Oto przykład:

SELECT * FROM CUSTOMER
WHERE State='MO'

W wyniku otrzymujemy tylko tych klientów, którzy mieszkają w Missouri (symbol
'MO').

SELECT * FROM CUSTOMER
WHERE StreetAddress LIKE '%Sunny%'

Rozdział 17 Delphi w Interbase

523

Rezultatem tego zapytania będą dane klientów, u których w

polu

StreetAddress

występuje słowo

Sunny

. Należy pamiętać, że porównanie

znaków uwzględnia różnice pomiędzy wielkimi i małymi literami. Zawsze jednak
można - do zamiany wszystkich liter w kolumnie i poszukiwanym wzorcu na duże
- zastosować funkcję

UPPER

.

Oto dalsze przykłady:

SELECT * FROM SALE
WHERE Amount>500

W wyniku otrzymujemy listę wszystkich transakcji, których kwota przekroczyła
500$.

SELECT

* FROM SALE

WHERE SaleDate BETWEEN '10/18/90' AND '05/20/95'

To zapytanie zwraca dane o wszystkich transakcjach dokonanych pomiędzy 18
października 1990 r. a 20. maja 1995 r. włącznie.

Złączenia (Joins)

Klauzula

WHERE

jest także wykorzystywana do łączenia tabel. Przy złączeniu

składnia klauzuli

WHERE

jest inna niż w podstawowej instrukcji

SELECT

.

Formułując odpowiednie warunki w klauzuli

WHERE

określamy dodatkowe tabele

w klauzuli

SELECT FROM

i łączymy pola będące ze sobą w relacji

.

Dobrze

ilustruje to następujący przykład:

SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerNumber=SALE.CustomerNumber

Zauważmy, że do klauzuli

FROM

włączono tabelę SALE oraz - do złączenia tabel

CUSTOMER i SALE poprzez pole

CustomerNumber

- użyto znaku równości.

Tabela wymieniona po lewej stronie znaku równości nazywana jest tabelą
zewnętrzną, a po prawej - wewnętrzną. Ze względu na ich pozycję w stosunku do
znaku równości często używa się określenia prawa i lewa. O złączeniu mówi się
lewa do prawej lub złączenie lewostronne. Jest to najczęściej używane złączenie
w zapytaniach SQL

Złączenia wewnętrzne(inner)a zewnętrzne (outer)

Wspomniane powyżej złączenie lewostronne formalnie nazywa się złączeniem
wewnętrznym. Złączenie wewnętrzne daje w wyniku tylko wiersze, dla których
spełniony jest warunek złączenia. Natomiast złączenie zewnętrzne uwzględnia też
wiersze, dla których warunek nie został spełniony. W złączeniu zewnętrznym, jeśli
nie znajdziemy pasujących wierszy w tabeli wewnętrznej, to kolumny z tabeli

524

Część III

wewnętrznej dołączane będą z wartością NULL. W zależności od rodzaju
złączenia zewnętrznego - tzn. left (lewostronne) lub right (prawostronne) -
wartościami

NULL

są odpowiednio uzupełniane wszystkie wiersze lewej albo

prawej tabeli, dla których nie był spełniony warunek złączenia.

Składnia złączenia w ANSI

Składnia ANSI definiuje specjalne operatory złączeń, umieszczane w klauzuli

FROM

. Składnię dla lewostronnego wewnętrznego złączenia zaprezentujemy na

przykładzie:

SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER LEFT JOIN SALE
ON CUSTOMER.CustomerNumber=SALE.CustomerNumber

Należy zaznaczyć, że domyślnie przyjmuje się słowo kluczowe

INNER

(złączenie

wewnętrzne). Jak pokazano wcześniej, InterBase także używa takiej składni do
zdefiniowania lewostronnego wewnętrznego złączenia :

SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerNumber=SALE.CustomerNumber

Składnię dla lewostronnego złączenia zewnętrznego przedstawiamy w poniższym
przykładzie:

SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER LEFT OUTER JOIN SALE
ON CUSTOMER.CustomerNumber=SALE.CustomerNumber

Dla określenia prawostronnego złączenia zewnętrznego wystarczy po prostu
zamienić

LEFT

na

RIGHT

. Oczywiście nie ma potrzeby rozróżniania

prawostronnego i

lewostronnego złączenia wewnętrznego, bowiem zgodnie

z podaną zasadą dają one ten sam wynik.

Złączenia wielopoziomowe (Multi-Tier Joins)

Złączenia wielopoziomowe korzystają z więcej niż dwóch tabel. Tabela A jest
złączana z tabelą B, a ta z kolei jest - z tabelą C. Rozważmy następujące zapytanie:

SELECT C.LastName, C.FirstName, I.Description, S.Amount
FROM CUSTOMER C,
SALE

S,

ITEM

I

WHERE C.CustomerNumber=S.CustomerNumber
and S.ItemNumber=I.ItemNumber

Rozdział 17 Delphi w Interbase

525

W tym zapytaniu

CUSTOMER

i

SALE

złączone są przez wspólny klucz - pole

CustomerNumber

a

SALE

i

ITEM

przez

ItemNumber

. W efekcie wszystkie

trzy tabele utworzą jeden zbiór wynikowy.

Samozłączenia (Self-Joins)

Oprócz złączenia z innymi tabelami, tabela może być złączana sama ze sobą. Ten
rodzaj złączenia nazywa się samozłączeniem. Rozważmy następujące zapytanie:

SELECT S.CustomerNumber, S.Amount,
(S.Amount/SUM(S2.Amount))*100 Precentage
FROM SALE S,
SALE

S2

WHERE S.CustomerNumber=S2.CustomerNumber
GROUP BY S.CustomerNumber, S.Amount

To zapytanie podaje kwoty wszystkich zakupów dokonanych przez klientów wraz
z rozbiciem procentowym w stosunku do całej kwoty zapłaconej przez danego
klienta. Budując takie zapytanie w ramach jednej instrukcji

SELECT

musimy

posłużyć się samozłączeniem. Najpierw jest tworzona i grupowana indywidualna
statystyka, a potem tabela SALE złączana jest ze sobą dla uzyskania łącznej kwoty
zakupów każdego klienta. To pozwala już obliczyć odpowiednią wartość
procentową, która wystąpi w wyniku zapytania.

Złączenia z użyciem innych operatorów (Theta Joins)

W warunku złączenia oprócz najczęściej stosowanego operatora porównania „jest
równe” (=) mogą wystąpić inne - zwykle jest to operator " jest różne" (<>).Podany
przykład prezentuje takie złączenie, pokazując jednocześnie wykorzystanie
samozłączenia:

SELECT C.CustomerNumber, S.Amount, (S2.Amount) OTHERS
FROM CUSTOMER C,
SALE

S,

SALE

S2

WHERE C.CustomerNumber=S.CustomerNumber
AND C.CustomerNumber<>S2.CustomerNumber
GROUP BY C.CustomerNumber, S.Amount

Faktycznie to zapytanie zawiera dwa złączenia. Pierwsze - pomiędzy

CUSTOMER

i

SALE

- aby uzyskać kwotę zakupu dla każdego klienta. Następne zaś (theta join)

- do obliczenia sumy kwot wszystkich zakupów, które nie zostały zrobione przez
klienta. Ponieważ stosuje się dwa różne typy złączenia do tej samej tabeli, więc
zapytanie, dla SALE, korzysta z dwóch różnych aliasów.

526

Część III

Złączenia pełne (Full Joins)

Pełne złączenie pozwala łączyć wiersze z dwóch tabel bez klauzuli

WHERE

. Jest

ono przydatne w przypadku łączenia tabel powiązanych relacjami. Jeśli żadna
z tabel nie zawiera wszystkich interesujących nas wierszy, wtedy łącząc je poprzez
zewnętrzne pełne złączenie otrzymamy wszystkie wiersze. Jest to po prostu
kombinacja złączenia prawo i lewostronnego. Oto przykład:

SELECT DISTINCT C.CustomerNumber, S.SaleNumber
FROM CUSTOMER C FULL JOIN SALE S
ON 1=1

UWAGA

Powyższy prosty kod zawiera tożsamość 1=1, bowiem analizator składniowy
InterBase wymaga klauzuli ON we wszystkich złączeniach, nawet pełnych, dla
których praktycznie nie jest to konieczne.

Iloczyn kartezjański

Iloczyn kartezjański jest wynikiem połączenia wszystkich wierszy z jednej tabeli
ze wszystkimi wierszami innej. Zwykle taki zbiór otrzymujemy przypadkowo, gdy
opuścimy lub niewłaściwie sformułujemy warunek złączenia. Oto odpowiedni
przykład:

SELECT SALE.SaleNumber, ITEM.ItemNumber
FROM SALE, ITEM
ORDER BY SaleNumber, ItemNumber

Dla tabel z dużą ilością danych obliczanie iloczynu kartezjańskiego może
zablokować serwer na tak długo, że konieczne będzie zamknięcie połączenia albo
zatrzymanie realizacji zapytania. W niektórych systemach zatrzymanie procesu
jest możliwe tylko przez zrestarowanie komputera serwera. Należy więc unikać
tworzenia iloczynu kartezjańskiego - zwłaszcza wtedy, gdy pracujemy z dużymi
tabelami.

Podzapytania (Subqueries)

Podzapytanie to instrukcja

SELECT

umieszczona w klauzuli

WHERE

innej

instrukcji

SELECT

. Ogólnie podzapytania to zapytania zwracające dane, które nie

są końcowym wynikiem, lecz będą wykorzystywane w dalszej części instrukcji.
Oto przykład:

SELECT * FROM CUSTOMER
WHERE CustomerNumber IN (SELECT CustomerNumber FROM SALE)

Rozdział 17 Delphi w Interbase

527

Pewne operatory funkcjonują tylko w podzapytaniach. S¹ to

ANY, ALL, SOME,

EXISTS i SINGULAR

. Chociaż słowo kluczowe

ALL

jest także używane

w instrukcji

SELECT,

to jako operator występuje tylko w podzapytaniach.

GROUP BY

Ponieważ SQL jest językiem zapytań zorientowanym na przetwarzanie zbiorów
(set - oriented), instrukcje grupujące dane są jego integralną częścią. Często osoby
tworzące bazy danych i pracujące z innymi systemami DBMS uważają takie
podejście za nietypowe, bowiem są przyzwyczajone do procedur przetwarzających
dane wiersz po wierszu. W wielu programach działających na komputerach PC,
aby zebrać dane sumaryczne iteruje się pętlę przez całą tabelę. Podejście SQL jest
zupełnie inne. Niekiedy pojedyncza instrukcja SQL zastępuje 10 lub nawet 50 linii
kodu programu napisanego w dBase. Jest to możliwe dzięki instrukcji

SELECT

,

klauzuli

GROUP BY

oraz funkcjom sumarycznym SQL. Klauzula

GROUP BY

pozwala definiować grupy wyjściowe wierszy, do których odnoszą się funkcje
sumaryczne użyte w klauzuli

SELECT

. Następny przykład pokazuje zastosowanie

klauzuli

GROUP BY

:

SELECT CUSTOMER.CustomerNumber, SUM(SALE.Amount) TotalSale
FROM CUSTOMER, SALE
WHERE CUSTOMER.CustomerNumber=SALE.CustomerNumber
GROUP BY CUSTOMER.CustomerNumber

Rezultatem tego zapytania jest lista wszystkich klientów wraz z sumaryczną
wartością transakcji każdego klienta.

Oczywiście nasuwa się pytanie, które pola połączyć klauzulą

GROUP BY

. InterBase

Server oraz ANSI SQL wymagają, aby klauzula

GROUP BY

zawierała wszystkie

kolumny wymienione na liście kolumn instrukcji

SELECT

, które nie są funkcjami

sumarycznymi. W przypadku korzystania z klauzuli

GROUP BY

na liście kolumn

instrukcji

SELECT

powinna znaleźć się choć jedna funkcja sumaryczna. Jeśli ten

warunek nie jest spełniony, InterBase Server uniemożliwia zastosowanie klauzuli

GROUP BY.

HAVING

Klauzula

HAVING

w instrukcji

SELECT

służy do selekcji wierszy zwracanych

przez klauzulę

GROUP BY

. Zależność między klauzulami

GROUP BY

oraz

HAVING

przypomina relację pomiędzy instrukcją

SELECT

a klauzulą

WHERE

.

Klauzula

HAVING

działa podobnie jak klauzula

WHERE,

ale na zbiorze

wynikowym, a nie na wierszach w tabelach zapytania.

Ogólnie

HAVING

jest mniej efektywne niż

WHERE

, ponieważ selekcjonuje zbiór

wynikowy, po tym jak zostanie on zgrupowany, zaś

WHERE

czyni to najpierw. Są

528

Część III

jednak sytuacje, w których korzystamy z klauzuli

HAVING

. Przeanalizujmy

poniższy przykład:

SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName
HAVING COUNT(*)>1

HAVING

wykorzystujemy przy selekcji wierszy otrzymywanych przez zapytanie

bazujące na funkcji sumarycznej. Zastosowanie

WHERE

jest niemożliwe, bowiem

potrzebna informacja nie istnieje - aż do momentu wykonania zapytania
i obliczenia odpowiednich wartości.

ORDER BY

Klauzula ta służy do odpowiedniego posortowania zbioru wynikowego. Oto
przykład:

SELECT * FROM CUSTOMER
ORDER BY State

Bez

ORDER BY

nie ma żadnej gwarancji, że wiersze zostaną odpowiednio

posortowane. Wtedy nawet ta sama instrukcja

SELECT,

wywołana dwukrotnie,

może stworzyć zbiory wynikowe, za każdym razem uporządkowane inaczej.

Aliasy kolumn

W kilku prezentowanych wcześniej przykładach używaliśmy logicznych nazw
kolumn, w których umieszczaliśmy wyniki obliczeń funkcji sumarycznych, jak

COUNT()

czy

SUM().

Etykiety tego typu nazywamy aliasami kolumn. Dzięki

nim zapytanie i jego wynik są czytelniejsze. W InterBase SQL alias kolumny
umieszcza się bezpośrednio po prawej stronie odpowiadającej mu kolumny - na
liście pól instrukcji

SELECT

. Na przykład w podanym poniżej zapytaniu aliasem

wartości funkcji sumarycznej

COUNT()

jest etykieta

NumberWithName:

SELECT CUSTOMER.LastName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName
HAVING COUNT(*)>1

Aliasy można stosować nie tylko dla funkcji sumarycznych, lecz dla każdej
pozycji w zbiorze wynikowym, np.:

SELECT CUSTOMER.LastName LName, COUNT(*) NumberWithName
FROM CUSTOMER
GROUP BY CUSTOMER.LastName

Rozdział 17 Delphi w Interbase

529

W tym zapytaniu zastąpiliśmy w zbiorze wynikowym nazwę kolumny

LastName

przez jej alias

LName

. Aliasów nie można jednak stosować w innych częściach

zapytania, takich jak klauzula

WHERE

czy

GROUP BY

. Wtedy musimy posłużyć

się nazwą kolumny lub wartością.

Aliasy tabel

Składnia instrukcji

SELECT

umożliwia korzystanie ze skrótu, zamiast podawania

pełnej nazwy tabeli. Nazywamy go aliasem tabeli. Jest definiowany w klauzuli

FROM

instrukcji

SELECT

i

umieszczany bezpośrednio po prawej stronie

właściwej nazwy, tak jak w poniższym przykładzie:

SELECT C.LastName, COUNT(*) NumberWithName
FROM CUSTOMER C
GROUP BY C.LastName

Może dziwić fakt, że alias występuje na liście pól instrukcji

SELECT,

czyli zanim

go zdefiniowano. Powinniśmy jednak pamiętać, że odwołanie się do obiektów
bazy danych musi nastąpić przed wykonaniem zapytania

Perspektywy (Views)

Perspektywa SQL składa się z instrukcji

SELECT

, którą można traktować jak

tabelę i, w

dalszej kolejności, zapytania z

innymi instrukcjami

SELECT

.

W pewnych sytuacjach może być ona parametrem instrukcji

INSERT, DELETE

i UPDATE

. Perspektywa nie zapamiętuje jednak żadnych danych. Jest ona tylko

pewną konstrukcją logiczną. Można o niej myśleć jako o małym programie
w języku SQL, uruchamianym przy każdym zapytaniu stworzonym na jej bazie.
Jest podobna do procedury wyboru - omówionej w następnej sekcji " Procedury
pamiętane". Gdy jest wykonywane zapytanie posługujące się perspektywą,
optymalizator zapytań tworzy najpierw perspektywę, łączy ją z właściwym
zapytaniem i optymalizuje obie czynności wykonując je jako jedno zapytanie.

Perspektywy w SQL tworzy się instrukcją

CREATE VIEW,

np.:

CREATE VIEW MOCUSTOMERS AS
SELECT *
FROM CUSTOMER
WHERE State='MO'

Perspektywa może zostać wykorzystana w zapytaniu jak zwykła tabela - np.:

SELECT * FROM MOCUSTOMERS

Mimo braku klauzuli

WHERE w

zapytaniu, zbiór wynikowy uwzględnia

oczywiście klauzulę

WHERE,

podaną w definicji perspektywy.

530

Część III

Instrukcja

SELECT,

tworząca perspektywę, ma prawie takie same możliwości

jak podstawowa instrukcja

SELECT

. Nie może tylko korzystać z klauzuli

ORDER

BY

. To ograniczenie odnosi się do wszystkich omawianych w tym rozdziale

serwerów baz danych.

Dodatkowe ograniczenia obowiązują przy tworzeniu perspektyw
modyfikowalnych.

„Instrukcja

SELECT

może dotyczyć tylko jednej tabeli lub innej

modyfikowalnej perspektywy.

„Kolumny, które nie zostały włączone do perspektywy, muszą dopuszczać

wartość

NULL

, aby było możliwe wykonanie instrukcji

INSERT.

„Instrukcja

SELECT

użyta w

definicji perspektywy nie może zawierać

podzapytań, predykatu

DISTINCT

, klauzuli

HAVING

, funkcji sumarycznych,

złączeń, funkcji użytkownika oraz procedur pamiętanych.

Tworząc modyfikowalną perspektywę, można ograniczyć poprawianie lub
dodawanie wierszy do wartości spełniających zadane przez nią warunki. Serwer
blokuje wtedy wszelkie zmiany wykraczające poza perspektywę. W tym celu
wystarczy - w instrukcji

CREATE VIEW

- dodać klauzulę

WITH CHECK

OPTION

, np.:

CREATE VIEW MOCUSTOMERS AS
SELECT *
FROM CUSTOMER
WHERE State='MO'
WITH CHECK OPTION

W ramach zdefiniowanej perspektywy będzie można dodawać tylko takie rekordy,
dla których

State

=

'MO'

. Nie ma także możliwości zmiany wartości pola

State

Procedury pamiętane (Stored Procedures)

Procedury pamiętane są kompilowanymi programami SQL (często zawierającymi
wiele instrukcji SQL), przechowywanymi wraz z innymi obiektami bazy danych.
W InterBase występują dwa podstawowe rodzaje tych procedur: procedury
wyboru (ang. select procedures) i procedury wykonywalne (ang. executable
procedures). Procedur wyboru można użyć zamiast tabel lub perspektyw
w instrukcji SELECT. Ponadto muszą one zwracać wynik ( jedną lub wiele
wartości). Natomiast w przypadku procedur wykonywalnych nie jest to konieczne.

Procedury pamiętane tworzy się instrukcją

CREATE PROCEDURE

. Poniżej

przedstawiamy przykład procedury w InterBase:

Rozdział 17 Delphi w Interbase

531

CREATE PROCEDURE listcustomers RETURNS (LastName CHAR(30),

➥

FirstName CHAR(30))

AS BEGIN
FOR SELECT LastName, FirstName FROM CUSTOMER
INTO :LastName, :FirstName
DO SUSPEND;
END

Dla procedury z parametrami składnia instrukcji ulega drobnej modyfikacji:

CREATE PROCEDURE listcustomersbystate (LastName CHAR(30),

➥

FirstName CHAR(30))

RETURNS (LastName CHAR(30), FirstName CHAR(30))
AS BEGIN
FOR SELECT LastName, FirstName FROM CUSTOMER
WHERE State= :State and LastName like :LastNameMask
INTO :LastName, :FirstName
DO SUSPEND;
END

Procedury wyboru (Select Procedures)

W procedurze wyboru rodzaj zwracanych danych definiuje się po słowie
kluczowym

RETURNS

(jak to pokazano w poniższym przykładzie):

CREATE PROCEDURE listcustomers (State CHAR(2))
RETURNS (LastName CHAR(30))
AS
BEGIN
FOR SELECT LastName
FROM CUSTOMER
WHERE State= :State
INTO :LastName
DO
SUSPEND;
END

Warto zwrócić uwagę, że w powyższym przykładzie konstrukcja

FOR

SELECT ... DO

powoduje przekazanie wierszy wynikowych do miejsca

wywołania. Instrukcja

SUSPEND

przerywa wykonanie procedury - aż do chwili,

gdy wywołanie zażąda następnego wiersza. Wartości przypisane parametrom
wyjściowym zostają przekazane przed przerwaniem realizacji procedury.

Procedury wykonywalne

Procedury wykonywalne różnią się od procedur wyboru tym, że nie wymagają
użycia instrukcji

RETURNS

. Oto przykład procedury wykonywalnej w InterBase

SQL:

532

Część III

CREATE PROCEDURE insertitem (ItemNumber Integer, Description

➥

Char(30), Price Float)

AS
BEGIN

INSERT INTO ITEM (ItemNumber, Description, Price)

VALUES (:ItemNUmber, :Description, :Price);

END

Skrypty

Instrukcje Data Definition Language (DDL), zawierające procedury pamiętane,
warto umieszczać w specjalnych plikach zwanych skryptami. Są to zwykłe pliki
tekstowe, zawierające ciągi poleceń SQL, więc można ich tworzyć korzystając
z dowolnego edytora tekstów. Warto tu przypomnieć, że powinny one zawierać
wszystkie niezbędne instrukcje

CONNECT i SET TERM

. Skrypt SQL można

wykonać, wybierając polecenie

Run an

SQL Script

z menu

File

programu

WISQL. Przykład zawiera listing 17.1

Listing 17.1. Procedura pamiętana w

skrypcie SQL.

CONNECT "C:\DATA\IB\SALES.GDB" USER "SYSDBA" PASSWORD

➥

"masterkey";

SET TERM ^ ;
CREATE PROCEDURE GET_CUSTOMER_BY _STATE (State CHAR(2))
RETURNS (LastName CHAR(30))
AS
BEGIN

FOR SELECT LastName

FROM

CUSTOMER

WHERE State= :State

INTO

:LastName

DO
SUSPEND;
END^
SET TERM ; ^
EXIT;

Instrukcja

CONNECT

na początku pliku ustala połączenie z bazą danych.

Następnie

SET TERM

zmienia znak kończący instrukcje SQL - z domyślnego

średnika (;) na (^). Zmiana ta powoduje, że instrukcje zawarte w przechowywanej
procedurze nie są wykonywane, gdy jest ona dopiero tworzona - za pomocą

CREATE PROCEDURE

. Instrukcja

SET TERM

przywraca domyślny znak

zakończenia instrukcji.

Rozdział 17 Delphi w Interbase

533

Uruchamianie procedur pamiętanych

Procedury pamiętane InterBase uruchamiamy instrukcją

EXECUTE PROCEDURE

.

Jej składnia przedstawia się następująco:

EXECUTE PROCEDURE procedurename parameters

W miejsce

procedurename

należy podać nazwę uruchamianej procedury,

a w miejsce

parameters

jej parametry np.:

EXECUTE PROCEDURE GET_CUSTOMER_BY_STATE "MO"

Ta instrukcja uruchamia procedurę pamiętaną

GET_CUSTOMER_BY_STATE

z parametrem "MO".

Wyjątki (Exceptions)

Wyjątki są mechanizmami przekazującymi aplikacji określone przez programistę
sygnały o błędach, jakie powstały w czasie wykonania procedur pamiętanych
i procedur zdarzeń. W

InterBase wyjątki definiujemy instrukcją

CREATE

EXCEPTION,

natomiast instrukcja

EXCEPTION

służy do ich przechwycenia

i wygenerowania odpowiedniego komunikatu. W listingu 17.2 pokazano sposób
definiowania wyjątku i jego wykorzystania w procedurze przechowywanej.

Listing 17.2. Wyjątek InterBase w

procedurze pamiętanej.

CONNECT "C:\DATA\IB\SALES.GDB" USER "SYSDBA" PASSWORD

➥

"masterkey";

CREATE EXCEPTIONSALE_TOO_LOW;
"The sale amount is to low. Only purchases of $1 or more are
allowed";

SET TERM ^ ;
CREATE PROCEDURE insertsale(SaleNumber int, SaleDate date,

➥

CustomerNumber int, ItemNumber int, Amount float)

AS
BEGIN
If

(:Amount<1)THEN

EXCEPTION

SALE_TOO_LOW;

ELSE
INSERT

INTO

SALE

VALUES (:SaleNumber, :SaleDate, :CustomerNumber,

➥

:ItemNumber, :Amount);

END^
SET TERM ;
EXIT;

534

Część III

Ta procedura ustala minimalną kwotę sprzedaży - 1$. Instrukcja wyboru

IF...THEN

sprawdza, czy kolumna

Amount

w wierszu przygotowanym do

wprowadzenia zawiera kwotę mniejszą niż 1$. Natomiast instrukcja

EXCEPTION

została użyta do zgłoszenia wyjątku

SALE_TOO_LOW

, gdy kwota ta jest mniejsza

niż 1 $.

Procedury zdarzeń (Triggers)

Procedury zdarzeń są to (podobnie jak procedury pamiętane) podprogramy SQL,
uruchamiane gdy dane z zadanej tabeli są wprowadzane, modyfikowane lub
usuwane. Procedura zdarzenia jest skojarzona ze specyficzną operacją
dokonywaną na tabeli (zdarzeniem): wstawianiem wiersza, modyfikacją lub
usuwaniem. Oto odpowiedni przykład w InterBase SQL:

CREATE TRIGGER SALEDelete FOR CUSTOMER
BEFORE DELETE
AS
BEGIN

DELETE FROM SALE

WHERE

CustomerNumber=OLD.CustomerNUmber;

END

Ta procedura zdarzenia likwiduje transakcje danego klienta w tabeli SALE, gdy
jego rekord jest usuwany z tabeli CUSTOMER (usuwanie kaskadowe).Operacja
usuwania z tabeli uruchamia "kaskadowy" proces eliminacji odpowiednich danych
w innych tabelach, połączonych z nią za pomocą wspólnego klucza.

Należy zwrócić uwagę na użycie zmiennej kontekstowej

OLD. O

dwołuje się ona

do bieżącej wartości kolumny w danym wierszu przed wykonaniem operacji

UPDATE

lub

DELETE

. Zmienna kontekstowa

NEW

odwołuje się do nowej

wartości, która ma być wprowadzona realizacją

INSERT

lub

UPDATE

.

Warto też odnotować wystąpienie słowa kluczowego

BEFORE

. Procedura

zdarzenia może być uruchomiona przed (before) lub po (after)

INSERT,

UPDATE, DELETE

.

W InterBase z danym zdarzeniem można skojarzyć do 32768 procedur zdarzenia.
Dlatego w sytuacji, gdy jedno zdarzenie aktywuje wiele procedur obsługi,
powinno się ustalić kolejność ich uruchamiania. Służy do tego słowo kluczowe

POSITION,

które zastosowano w poniższym przykładzie:

CREATE TRIGGER SALEDelete FOR CUSTOMER
BEFORE DELETE
POSITION 0
AS
BEGIN

DELETE FROM SALE

Rozdział 17 Delphi w Interbase

535

WHERE

CustomerNumber=OLD.CustomerNUmber;

END

Numery mogą przyjmować wartości od 0 do 32767, a procedura o niższym
numerze wykonywana jest najpierw. Procedury zdarzeń z tym samym numerem
uruchamiane są w porządku losowym.

Funkcje

InterBase umożliwia rozszerzanie swojej odmiany SQL - przez dołączenie
własnych funkcji. Zasadniczo można zdefiniować własne funkcje w zewnętrznej,
współdzielonej bibliotece funkcji ( w Windows jest to DLL) i udostępniać je za
pomocą instrukcji

DECLARE EXTERNAL FUNCTION

. Przy takim podejściu są

one przechowywane w

naszej bazie danych i

zachowują się jak funkcje

wbudowane. Oto przykład prostej biblioteki DLL, w której zdefiniowano dwie
funkcje.

library IBUDFLIB;

uses
SysUtils,
Classes;

function MaxInt(var Int1, Int2 :Integer) : Integer; far cdecl

➥

export;

begin

if (Int1>Int2) then Result:=Int1

else

Result:=Int2;

end;

function MinInt(var Int1, Int2 :Integer) : Integer; far cdecl

➥

export;

begin

if (Int1<Int2) then Result:=Int1

else

Result:=Int2;

end;
exports
MaxInt,
MinInt;

begin
end.

A teraz przedstawimy przykład skryptu SQL, który udostępni te funkcje dla
InterBase:

536

Część III

CONNECT "C:\DATA\IB\SALES.GDB" USER SYSDBA PASSWORD

➥

masterkey;

DECLARE EXTERNAL FUNCTION MAXINT
INTEGER, INTEGER
RETURNS INTEGER BY VALUE
ENTRY_POINT "MaxInt" MODULE_NAME "IBUDFLIB.DLL";

DECLARE EXTERNAL FUNCTION MININT
INTEGER, INTEGER
RETURNS INTEGER BY VALUE
ENTRY_POINT "MinInt" MODULE_NAME "IBUDFLIB.DLL";

EXIT;

Teraz podamy kilka uwag praktycznych dla osób chcących wzbogacać InterBase
SQL o własne funkcje,:

„InterBase pobiera funkcje z biblioteki DLL, posługując się nazwą funkcji

i rozróżniając w niej wielkie i małe litery. Jeśli nie zadba się o poprawne użycie
wielkich i małych liter w nazwie funkcji, w instrukcji

DECLARE EXTERNAL

FUNCTION

InterBase nie będzie w stanie jej zlokalizować.

„Parametry są przekazywane przez zmienną (zwracamy uwagę na słowo

kluczowe VAR na liście parametrów funkcji MaxInt i MinInt) a nie przez
wartość. Dlatego zadeklarowane funkcje powinny spodziewać się wskaźników
do zmiennych, a nie ich wartości.

„InterBase przyjmuje, że funkcja jest zdefiniowana z wywołaniem

cdecl

. Nie

należy więc używać wywołań

far pascal

,

stdcall

,

or

oraz

safecall,

bowiem nie będą funkcjonowały poprawnie.

„Wyniki mogą być zwracane przez zmienną lub przez wartość. Jeśli wybierzemy

przekazanie przez zmienną, należy pamiętać, aby zwrócić wskaźnik do wartości
a nie samą wartość, inaczej bowiem InterBase zatrzyma się próbując wykonać
funkcję.

Zdefiniowane funkcje

MinInit

i

MaxInit

możemy przetestować, wykonując

następującą instrukcję w WISQL:

SELECT MaxInt(3,5) FROM CUSTOMER;

Składnia InterBase SQL wymaga użycia nazwy co najmniej jednej tabeli
w instrukcji

SELECT

. W

podanym przykładzie wykorzystaliśmy tabelę

CUSTOMER. Funkcja

MaxInt

będzie wykonywana dla każdego wiersza tabeli,

więc uruchamiając podany kod możemy otrzymać wielokrotnie powtarzaną
wartość.

Rozdział 17 Delphi w Interbase

537

WSKAZÓWKA

Możemy utworzyć jednowierszową tabelę InterBase, którą wykorzystamy do
tworzenia zapytań nie odnoszących się do tabel. Tabela taka może być
wykorzystana w instrukcji

SELECT

wtedy, gdy praktycznie nie korzystamy

z danych z żadnej tabeli. Oto przykład utworzenia i użycia takiej tabeli (każde
polecenie należy wykonać osobno w WISQL):

CREATE TABLE DUAL
(A CHAR(1));
INSERT INTO DUAL (A) VALUES ('A');

SELECT 22/7 AS ApproximatePI
FROM DUAL;

Deklarację zewnętrznej funkcji można usunąć z bazy za pomocą instrukcji

DROP

EXTERNAL FUNCTION.

Kursory (Cursors)

Koncepcja kursorów jest wynikiem zastosowania zorientowanej na zbiory
koncepcji SQL w odniesieniu do przetwarzania wierszowego. Pozwalają one
pracować z jednym wierszem w danej chwili. Ponieważ ich tworzenie i obsługa
jest w BDE zautomatyzowana, więc w zasadzie nie ma potrzeby tworzenia
własnych kursorów. Jednak niekiedy mogą się one okazać użyteczne
w procedurach pamiętanych.

Są cztery podstawowe operacje wykonywane na kursorze: deklarowanie (ang.
declare), otwarcie (ang. open), pobranie (ang. fetch), zamknięcie (ang. close).
Można go też wykorzystać do zmodyfikowania lub usunięcia pojedynczego
wiersza tabeli.

UWAGA

Nie można wykonywać tych instrukcji samodzielnie w programie WISQL. Są one
przeznaczone do użycia tylko w procedurach pamiętanych.

Deklaracja kursora składa się z

instrukcji

SELECT

i

(dla kursorów

modyfikowalnych) - z listy modyfikowalnych kolumn. Oto przykład:

DECLARE CUSTOMER_SELECT CURSOR
FOR SELECT * FROM CUSTOMER

Zanim uzyskamy dostęp do wierszy przy użyciu kursora, musi on być najpierw
otwarty. Aby przygotować zapytanie zapisane w definicji kursora należy użyć
instrukcji

OPEN

:

538

Część III

OPEN CUSTOMER_

SELECT

Samo

OPEN

nie wprowadza jednak wierszy do aplikacji klienta. Niezbędna jest

instrukcja

FETCH

:

FETCH CUSTOMER_SELECT

W ten sposób otrzymamy jeden wiersz ze zbioru wynikowego kursora. Każde
następne

FETCH

pozwala uzyskać następny wiersz w zbiorze. InterBase posiada

tylko kursory jednokierunkowe. Aby przejść do wierszy poprzednich należy
zamknąć (

CLOSE

) i ponownie otworzyć (

OPEN

) kursor.

UWAGA

Mimo że InterBase nie obsługuje kursorów dwukierunkowych, można ich użyć
w aplikacji stworzonej w Delphi. BDE emuluje bowiem dwukierunkowy kursor na
poziomie programowym - bez względu na to, czy serwer baz danych go obsługuje.
Dlatego można przewijać w obie strony w obiektach

TDataSets

takich jak

TQuery i TTable

.

Wiersze zwracane przez modyfikowalny kursor mogą być zmieniane za pomocą
specjalnych wersji instrukcji

UPDATE i DELETE

jak np.:

DECLARE CUSTOMER_UPDATE CURSOR
FOR SELECT *FROM CUSTOMER
FOR UPDATE OF LastName

UWAGA

Należy się upewnić, czy na liście kolumn klauzuli

FOR UPDATE OF

umieszczono tylko te kolumny, które mają być rzeczywiście aktualizowane.
Umieszczenie na tej liście innych pól niepotrzebnie wiąże zasoby serwera.

Aby zmodyfikować lub usunąć bieżący wiersz modyfikowalnego kursora, należy
posłużyć się wyrażeniem

WHERE CURRENT OF

cursorname

, jak to pokazano

w poniższym przykładzie:

UPDATE CUSTOMER
SET LastName="Cane"
WHERE CURRENT OF CUSTOMER_UPDATE

lub:

DELETE FROM CURSOR
WHERE CURRENT OF CUSTOMER_UPDATE

Rozdział 17 Delphi w Interbase

539

Gdy kończymy pracę z kursorem należy go zamknąć (instrukcją

CLOSE)

.

Zamknięcie kursora zwalnia wszystkie zasoby systemowe, których używał. Oto
przykład:

CLOSE CUSTOMER_UPDATE

Administrowanie bazą Interbase

Zwykle osoby tworzące bazy danych wykonują tylko najprostsze czynności
administracyjne. W naszych rozważaniach ograniczymy się do podstawowych,
niezbędnych narzędzi administrowania bazą. Oczywiście skomplikowane
problemy najlepiej pozostawić do rozwiązania administratorom bazy danych.

Archiwizacja i odzyskiwanie danych

Podstawową troską projektantów, administratorów i użytkowników baz danych
jest bezpieczeństwo danych. Musimy więc przede wszystkim zabezpieczyć bazę na
wypadek uszkodzenia dysków, na których jest umieszczona. W tym celu tworzymy
kopie bazy danych. Nie wystarczy jednak po prostu skopiować pliki z danymi.
Część z nich może być bowiem akurat w użyciu i znajdować się częściowo
w pamięci serwera. Dlatego zwykła kopia, sporządzona narzędziami systemu
operacyjnego, nie daje prawdziwego obrazu stanu bazy w danym momencie i nie
gwarantuje jej spójności. Dopiero Server Manager zapewni nam właściwą
archiwizację danych. Postępujemy następująco :

1. Uruchamiamy program Server Manager

2. Klikamy

File\Server Login

i łączymy się z serwerem.

3. Wybieramy

Tasks\Backup

- aby wyświetlić okno dialogowe do archwizacji

4. W lini

Database Path

wpisujemy pełną ścieżkę dostępu do danych, które

chcemy archiwizować. Gdy archiwizujemy odległą bazę na platformie innej niż
NetWare, wybieramy

Remote

.

5. Wpisujemy nazwę pliku lub urządzenia, którego chcemy użyć, w linii

Backup

File or

Device

. W Windows NT można wpisać domyślne urządzenie taśmowe

jako

\\.\tape0

.

6. Aby rozpocząć archiwizację klikamy

OK

. Sytuację ilustruje rys 17.1.

Odzyskiwanie zarchiwizowanych danych jest po prostu odwróceniem poprzednio
opisanego procesu. Wybieramy opcję

Restore

z menu

Tasks

, określając swoją

kopię jako

Restore Source

, a swoją bazę jako

Restore Destination

540

Część III

Zarządzanie kontami użytkowników

Aby móc łączyć się z bazą danych, trzeba być zarejestrowanym jako użytkownik
i znać swoje hasło. Do utworzenia konta nowego użytkownika można użyć
polecenia

Task\User Security

z menu programu Server Manager. Postępujemy

wtedy zgodnie z poniższą procedurą:

1. Uruchamiamy program Serwer Manager.

2. Klikamy

File\Sever Login

i łączymy się z serwerem

3. Wybieramy

Tasks\User Security

, aby wyświetlić okienko dialogowe InterBase

Security.

4. Klikamy przycisk

Add User

.

5. Wprowadzamy nazwę i hasło dla nowego użytkownika.

6. Klikamy

OK

, aby dodać nowego użytkownika. Ilustruje to rysunek 17.2

Rysunek 17.1
Wykorzystanie
Server Managera
do archiwizacji
bazy danych

Rysunek 17.2
Dodawanie nowego
uzytkownika
InterBase
w programie Server
Manger. Okno
dialogowe User
Configuration

Rozdział 17 Delphi w Interbase

541

Po utworzeniu konta użytkownika możemy się łączyć z dowolną bazą danych
InterBase’a. Jednak korzystanie z bazy jest możliwe dopiero po nadaniu praw
dostępu do poszczególnych obiektów bazy.

Konfiguracja serwera

Jest parę sposobów dojścia do okna dialogowego

Interbase Server Configuration

.

Umożliwia to Server Manager poleceniem

Config

z menu

Task\Server

. Możemy

też kliknąć prawym przyciskiem myszy ikonę

InterBase Server

na pasku zadań

Windows i wybrać polecenie

Properties

.

W oknie dialogowym

Server Configuration

parametry serwera InterBase zostały

podzielone na dwie klasy: IB (InterBase) Settings i OS (Operating system)
Settings. Ustawienia IB składają się z dwóch parametrów:

Database Cache

i

Client map size

.

Database cache

określa liczbę stron

zarezerwowanych na pamięć cache dla baz. W zasadzie wszystkie bazy można by
umieścić w pamięci RAM, jeśli cache będzie odpowiednio duży. Przyspieszyłoby
to znacznie wszelkie operacje na bazie. Jednak ustalając zbyt duży cache
w stosunku do zasobów komputera powodujemy, że system operacyjny przenosi
część pamięci cache na dysk, zmniejszając w ten sposób wydajność serwera.

Client map size

określa rozmiar bufora rezerwowanego dla każdego

połączonego z bazą klienta. Zwykle nie ma potrzeby zmieniania tego parametru -
można go zwiększyć przy transmisjach dużych porcji danych (np. BLOB) do i
z aplikacji klienta.

Ustawienia OS zawierają trzy pozycje:

Min Process Working Set

,

Max Process

working

Set

i

Process

Priority

Class

.

Min

i

Max Process working Set

kontrolują

wielkość oddanej do dyspozycji serwera bazy danych pamięci fizycznej.
Podwyższając wartość maksymalną pozwalamy na zwiększenie ustawień Database
Cache, jednak zbyt duża wartość tego parametru może powodować błędy
stronnicowania (ang. page faults). W Windows NT można sprawdzić - poprzez NT
Performance Monitor - czy parametry pracy serwera wymagają korekty. Aby
określić, czy ustawienia

Min

i

Max

process

Working

Set

są właściwe, wykonujemy

następujące kroki:

1. Jeśli InterBase server jest już uruchomiony, włączamy program NT

Performance Monitor (najlepiej na tym samym komputerze)

2. Wybieramy nowy wykres i naciskamy CTRL+I - aby dodać nowy licznik do

wykresu.

3. Wybieramy

Object Process, Instance

:

IBSERVER, Counter:Page File Bytes

i klikamy

Add

.

4. Dodajemy licznik dla

IBSERVER

Page

faults/sec

i

% Processor

Time

. (patrz

rysunek 17.3)

542

Część III

Jeśli stwierdzimy dużą ilość błędów stronicowania (Page Faults) lub % Processor
Time jest mniejszy niż 50%, można zwiększyć zakres Process Working Set, co
powinno poprawić wydajność serwera. Wprowadzone zmiany zostaną
uwzględnione przy następnym starcie serwera.

Process Priority Class określa priorytet procesów inicjowanych przez serwer
InterBase wśród innych procesów aktualnie uruchomionych na komputerze
serwera. Zmieniając priorytet na High można uzyskać znaczny wzrost wydajności
- szczególnie na komputerach wydzielonych (ang. dedicated) wyłącznie do pracy
z serwerem InterBase, oraz w sytuacjach, gdy duża ilość aplikacji jest aktywna na
komputerze serwera. Zmiany tego ustawienia realizowane są natychmiast po
kliknięciu

Apply

.

Przeglądanie statystyki blokad

Systemy klient/serwer wykorzystują pesymistyczny mechanizm sterowania
współbieżnością, więc są wyposażane w różnorodne narzędzia obsługujące
blokady i rywalizację o dostęp. InterBase, jak większość wielodostępnych DBMS,
odpowiednio blokuje zasoby - aby zapobiec jednoczesnemu, niekontrolowanemu
procesowi wprowadzania zmian przez wielu użytkowników. Oczywiście niezbędne
są narzędzia do kontrolowania i

usuwania problemów związanych

z wielodostępem. Blokady zakładane przez serwer InterBase’a można obejrzeć
wybierając polecenie

Tasks\Lock Manager Statistics

w programie Server Manager

(patrz rysunek 17.4).

Rysunek 17.3
Użycie NT
Performabce
Monitor do
określenia potrzeb
serwera
InterBaze’a
w zakresie RAM.

Rozdział 17 Delphi w Interbase

543

Program zarządzający blokadami w InterBase korzysta ze specjalnej tablicy do ich
śledzenia. Są w

niej informacje na temat statusu wszystkich blokad.

LOCK_HEADER BLOCK podaje szeroki zakres danych podsumowujących - jak
np. aktualny i maksymalny rozmiar zablokowanych tablic, liczbę zakleszczeń itp.
W części szczegółowej dotyczącej poszczególnych procesów możemy sprawdzić,
który proces zarządził daną blokadę, które blokady zostały zwolnione, a które
czekają itd. Wszystkie te informacje mogą się okazać pomocne w rozwiązywaniu
problemów związanych z blokadami, jak np. zakleszczenie (ang. deadlock)

Sprawdzanie integralnośći bazy

Oprócz regularnej archiwizacji danych należy - dla zapewnienia sobie
wiarygodności danych w bazie - podjąć inne środki. Powinno się regularnie
sprawdzać ich poprawność, najlepiej bezpośrednio przed lub po dokonaniu
archiwizacji. Umo¿liwia to polecenie menu

Maintance\Database Validation

w InterBase Server Manager. Rysunek 17.5 pokazuje okno dialogowe

Database

Validation

.

Database Validation wykonuje trzy podstawowe funkcje: inwentaryzuje
uszkodzone struktury na dysku, pokazuje strony z błędną alokacją i zwalnia
miejsce na dysku po "osieroconych" stronach. Standardowo korekty w strukturze,

RYSUNEK 17.4
Okno Lock
Manager Statistics
wyświetla ważne
informacje
dotyczące blokad
InterBase.

Rysunek 17.5
Sprawdzanie bazy -
okienko dialogowe
Database
Validation

544

Część III

które nie dotyczą danych użytkownika wykonywane są automatycznie. Można
jednak zablokować przeprowadzanie zmian przez wybranie opcji

Read-only

validation

.

Każdy błąd wykryty przez program sprawdzający wyświetlany jest w okienku. Po
kliknięciu przycisku

Repair

program podejmie próbę ich naprawy. Program

sprawdzający może także warunkowo pomijać wykryty błąd sumy kontrolnej. Aby
naprawić uszkodzoną bazę wykonujemy kroki opisane poniżej

1. Kopiujemy bazę z pominięciem programu ServerManager Backup. Można użyć

zwykłego polecenia

Copy

albo Eksploratora Windows. Oprogramowanie

InterBase nie archiwizuje bowiem uszkodzonych baz.

2. Dla sprawdzenia uszkodzeń w bazie wybieramy polecenie

Maintenance\

Database

Validation

.

3. Po zakończeniu weryfikacji klikamy przycisk

Repair

- aby zaznaczyć lub

naprawić uszkodzone struktury

4. Jeśli powstały błędy sumy kontrolnej ponownie wybieramy polecenie

Database

Validation

, tym razem jednak z opcją

Ignore checksum errors

ustawioną na

TRUE

.

5. Kiedy proces się zakończy klikamy

Repair

- aby naprawić nowo wykryte

uszkodzenia.

6. Uruchamiamy Database Validation, wybierając tym razem opcję

Read

-

only

validation

. Warto zwrócić uwagę na fakt, że zwolnione stronice nie są już

wyświetlane, a niepełne rekordy zaznaczone jako uszkodzone. Każdy rekord
zaznaczony jako uszkodzony nie będzie archiwizowany przez program Backup
z Server Managera.

7. Archiwizujemy naprawioną bazę programem archiwującym z Server Managera.

Uszkodzone rekordy zostaną oczywiście wyłączone z archiwizacji.

8. Odzyskujemy zarchiwizowaną bazę. Ta operacja pozwoli na przebudowanie

pewnych struktur wewnętrznych i usunięcie uszkodzonych części z bazy.

9. Uruchamiamy Database Validation po raz ostatni - aby sprawdzić, czy

rzeczywiście baza została naprawiona. Tym razem wybieramy opcję

Read

-

only

validation

.

Statystyki bazy danych

Opisywane tutaj polecenia Server Managera wymagają aktywnego połączenia
z daną bazą. Musimy być więc zarejestrowani na serwerze oraz połączeni z bazą
(polecenie

File\Database Connect

w Server Manager).

Rozdział 17 Delphi w Interbase

545

Polecenie

Task\Database Statistics

wyświetla różnorodne informacje odnoszące

się do bazy. Są wśród nich między innymi: data utworzenia bazy, najstarsza
aktywna transakcja, rozmiar strony, liczba zdefiniowanych cieni. Niektóre z tych
danych są także dostępne poprzez polecenia

Maintance\Database Properties

.

Rysunek 17.6 pokazuje okno utworzone przez

Database

Statistics

.

Gdy okno

Database Statistics

jest na ekranie, można wybrać polecenie

Database

Analysis

z menu

View

- aby zobaczyć ilość stron alokowanych dla bazy i ich

wypełnienie. Ułatwi to nam zrozumienie, jak struktura logiczna bazy wpływa na
fizyczne rozmieszczenie danych.

Czyszczenie bazy (Database sweeping)

Polecenie

Maintenance\Database Sweep

zwalnia miejsce po starych wersjach

rekordów z odrzuconych transakcji. Chociaż nie jest to niezbędne, należy zamknąć
bazę na czas jej oczyszczenia - ze względu na wpływ tej operacji na działanie
programów użytkowników. Oczyszczanie dużych baz może bowiem drastycznie
spowolnić realizację innych procesów.

Odzyskiwanie transakcji (Transaction Recovery)

Każda transakcja, obejmująca zakresem wiele baz, jest w InterBase zatwierdzana
dwufazowo. Zapewnia to zachowanie niepodzielności transakcji dla wszystkich
baz składowych. Dwufazowe zatwierdzanie może się nie udać ze względu na
zerwanie połączenia sieciowego lub awarii dysku, która uniemożliwi dostęp do
części baz. Polecenie

Maintaince\Transaction Recovery

ma za zadanie obsługę

transakcji pozostających w „zawieszeniu” po nieudanym procesie dwufazowego
zatwierdzania. Pozwala ono odzyskać te transakcje i - w zależności od sytuacji -
wycofać lub zatwierdzić.

Rysunek 17.6
Okno Database
Statistics
z podstawowymi
informacjami
o bazie.