Rozdział 18

Delphi na serwerze SQL Sybase

W tym rozdziale zapoznamy się z Sybase SQL Server RDBMS. Przedstawimy
także specyficzne zagadnienia dotyczące współpracy serwera SQL i Delphi oraz
odmianę języka SQL - Transact-SQL.

Uruchamianie serwera

Sposób uruchomienia serwera SQL zależy od systemu operacyjnego. W systemie
UNIX zwykle uruchamiamy skrypt o nazwie

RUN_nazwa_serwera,

natomiast

w Windows NT korzystamy z programu Sybase Services Manager. Program ten
można znaleźć w grupie lub folderze Sybase SQL Server.

Uruchomić i zatrzymać serwer można też za pomocą apletu

Services

w Windows

NT Control Panel. Ponieważ serwer SQL działa jako usługa, więc można też
skorzystać z polecenia

Services

w programie Windows NT Server Manager.

Zatrzymanie serwera umożliwia także instrukcja

SHUTDOWN

języka Transact -

SQL. Wykorzystanie opcji

NOWAIT

spowoduje natychmiastowe przerwanie jego

pracy, natomiast normalnie serwer jest zatrzymywany dopiero po zakończeniu
realizowanych procesów.

Łączenie się z serwerem

Aby połączyć się z serwerem Sybase z aplikacji Delhi, funkcjonującej na
komputerze klienta w

systemie Windows 95 lub Windows NT, należy

zainstalować oprogramowanie Sybase Net Library. Oprogramowanie to występuje
w dwóch różnych wersjach: osobna dla Windows 95 i Windows NT. Wersja dla
NT (jest to część pakietu Sybase Open Client for Windows 95) instaluje się tylko
w

tym systemie. Przy próbie zainstalowania jej w

systemie Windows 95

sygnalizowany jest błąd 422.

Druga wersja jest nowsza i funkcjonuje poprawnie w obu systemach Windows.
Oczywiście, jeśli jest taka możliwość, powinniśmy posługiwać się właśnie tą
wersją.

548

Część III

Net Library - błąd oprogramowania klienta 422 w NT

Wersja przeznaczona tylko dla Windows NT niekiedy instaluje się niepoprawnie.
Począwszy od 1996 r. program instalacyjny przy pierwszej instalacji sygnalizował
często błąd 422. Okazało się, że rozwiązanie tego problemu jest proste. Mimo, że
towarzyszący oprogramowaniu podręcznik Sybase Release Bulletin nakazuje
opróżnić katalog

TEMP

, nie należy tego robić. Przy pierwszej nieudanej próbie

instalacji program pozostawia pewne pliki tymczasowe w katalogu

TEMP

. Jeśli się

ich nie usunie i uruchomi instalację ponownie, wszystko powinno przebiegać bez
zakłóceń.

Konfigurowanie oprogramowania klienta

Niezależnie od wybranej wersji oprogramowania skonfigurujemy połączenie
klienta posługując się programem Sybase SQLEDIT:

1. Uruchamiamy SQLEDIT i wpisujemy nazwę serwera do pola tekstowego

w oknie dialogowym Input Server Name.

2. Klikamy

Add

- aby dodać wprowadzoną nazwę do listy Server Entry.

3. Z listy rozwijalnej

Service Type

wybieramy

Query

.

4. Z listy Platform wybieramy odpowiednio

Windows

95

lub

NT

.

5. Następnie wybieramy odpowiedni sterownik z listy rozwijalnej

Net-Library

Driver

. Najprawdopodobniej będzie to

NLWNSCK (TCP/IP Winsock)

albo

NLWLINK

(IPX).

6. W oknie dialogowym

Connection Information\Network Address

wpisujemy

adres sieciowy serwera i numer portu (oddzielone przecinkami). Będzie to
adres TCP/IP, jeśli korzystamy z protokołu TCP/IP lub adres sieci IPX - jeśli
używamy IPX. Oto przykładowy łańcuch połączeniowy :

100.10.15.12, 3000

W tym przykładzie

100.10.15.12

jest adresem TCP/IP a

3000

- numerem

portu. W razie wątpliwości numer sieci i portu można uzyskać od administratora
sieci lub systemu.

UWAGA

Aby uzyskać połączenie można wykorzystać plik konfiguracyjny użytkownika.
Jeśli w pliku HOSTS jest linia

100.10.15.12 marketing

to do uzyskania połączenia wystarczy wprowadzić

Rozdział 18 Delphi na serwerze SQL Sybase

549

marketing 3000

7. Po ustawieniu informacji o połączeniu klikamy przycisk

Add Service

, dodając

w ten sposób usługę do listy

Connection Service Entry

.

8. Po dodaniu usługi jesteśmy gotowi do jej przetestowania. Klikamy przycisk

Ping

, umieszczony po prawej stronie w dolnej części ekranu. Ping próbuje

połączyć się a następnie zamknąć połączenie z ustalonym wcześniej serwerem.
Jeśli oprogramowanie klienta zostało właściwie skonfigurowane, nie powinny
wystąpić żadne problemy.

9. Aby zachować informację o połączeniu naciskamy CTRL+S .

WSKAZÓWKA

Ten sam sposób postępowania przy konfigurowaniu połączenia obowiązuje wtedy,
gdy instalujemy oprogramowanie czy też uruchamiamy SQLEDIT niezależnie .

SYBPING

Do przetestowania połączenia z serwerem można użyć programu Sybase’a
o nazwie SYBPING. Powinien się on znajdować w folderze SYBASE. Klikamy
nazwę naszego serwera na liście prezentowanej w programie, a następnie przycisk

Ping

. Jeśli nasz komputer połączy się z serwerem, to konfiguracja połączenia jest

poprawna.

Sterowniki dla Win 3.x

Podamy teraz kilka istotnych wskazówek dla osób korzystających z 16-bitowego
oprogramowania klienta (Win 3.x):

„Wcześniejsze (niż System 10) wersje Sybase SQL Server korzystają

z biblioteki połączeniowej Sybase DB-Library. Natomiast System 10 oraz
wersje późniejsze posługują się CT-Library. DB-Library umożliwia łączenie się
z serwerami System 10, ale nie obsługuje jego specyficznych własności.
Połączenia w Delphi 1.0 nawiązywane są poprzez DB-Library; Delphi 2.0
i późniejsze mogą korzystać z

obu bibliotek. Zanim przystąpimy do

konfiguracji, powinniśmy się więc upewnić, jaka biblioteka jest używana przez
naszego klienta.

„Stworzono wiele różnych wersji bibliotek DB-Library. W czasie naszej pracy

nie mieliśmy żadnych kłopotów z wersją 3-1-94, ale oczywiście nie znaczy to,
że jest ona najlepsza. Chcielibyśmy jednak ostrzec, że niekiedy mogą wystąpić
nieprawidłowości przy konwersji typów. Problem taki pojawił się wtedy, gdy

550

Część III

korzystaliśmy z Delphi 1.0 i bibliotek Sybase DB-Library z czerwca 1994 r.
(konwersja pomiędzy typami datetime i string była niepoprawna). Dlatego
pierwszym krokiem w

przypadku wystąpienia problemów związanych

z konwersją powinna być zmiana wersji biblioteki DB-Library.

„

Począwszy od Systemu 10 zmieniono metodę usuwania zapytań. Obecnie
bazuje ona na Out-Of -Band Data (OOBD). Obsługa OODB jest wbudowana
w protokół IPX, ale w przypadku TCP/IP trzeba się o tym upewnić. Np. tylko
późniejsze wersje LAN WorkPlace TCP/IP obsługują OOBD; wcześniejsze
wymagają odpowiedniej aktualizacji.

„

Jeśli korzystamy z OOBD do usuwania zapytań w środowisku Win 3.x, to
powinniśmy użyć parametru połączeniowego

URGENT

. Zwykle dodaje się

URGENT

na końcu łańcucha połączeniowego:



100.10.15.12,3000, URGENT

 Wtedy zapytania, przerywane funkcją

dbcancel()

z biblioteki DB-Library

zwracają natychmiast sterowanie do aplikacji.

„Należy także pamiętać, aby korzystać z programu WSYBPING a nie SYBPING

- ten ostatni obsługuje bowiem tylko połączenia 32-bitowe.

„Wersja DB-Library dla Windows 3.x wymaga trybu rozszerzonego Windows,

chociaż nie jest to w żaden sposób sygnalizowane. Po prostu - jeśli
uruchomimy DB-Library w trybie standardowym, to nie uzyskamy połączenia.

Tworzenie aliasów BDE

Po połączeniu się z serwerem, możemy utworzyć aliasy BDE, dzięki którym nasze
aplikacje w Delphi będą miały dostęp do bazy. Ten temat był już omawiany
wcześniej, dlatego teraz przedstawimy tylko sam sposób tworzenia aliasu BDE
w zależności od konfiguracji oprogramowania sterującego bazą .

Aliasy BDE można utworzyć za pomocą programu BDE Administrator lub Delphi
Database Explorer. Poniższe kroki zostały wykonane w

programie BDE

Administrator.

1. Uruchamiamy program DBE Administrator, klikamy prawym przyciskiem

fiszkę

Databases

i z menu wybieramy

New

. W oknie dialogowym

New

Database

Alias

wybieramy - z listy rozwijalnej - SYBASE Alias type i klikamy

OK

.

2. Gdy zostanie dodany nowy alias wpisujemy jego nazwę.

3. Konfigurujemy go, ustawiając wartości parametrów na stronie

Definition

.

Rozdział 18 Delphi na serwerze SQL Sybase

551

4. Wybieramy parametr

SERVER NAME

i wpisujemy nazwę naszego serwera

SQL - tę, której użyliśmy poprzednio w programie SQLEDIT.

5. Dodatkowo możemy ustawić parametr

USER

NAME

, podając nazwę

użytkownika, który będzie domyślnie logowany do serwera. Będzie się ona
także ukazywała we wbudowanym w Delphi oknie dialogowym logowania.

Aby zapisać zmiany, klikamy przycisk

Apply

i zamykamy sesję Administratora.

Specjalne ustawienia aliasu BDE

Omówione poniżej ustawienia wartości parametrów aliasu BDE mogą okazać się
istotne, gdy łączymy się z serwerem SQL Sybase. Chociaż możemy ograniczyć się
do ustawień domyślnych, to jednak w

przypadku napotkania problemów

znajomość funkcji poszczególnych parametrów będzie z

pewnością bardzo

przydatna. Warto zauważyć, że niektóre z nich określają sposób funkcjonowania
programów obsługi bazy i

mogą być ustawiane na stronie

Configuration

w programie BDE Administrator.

APPLICATION NAME (Nazwa aplikacji)

Wartością Parametr

APPLICATION NAME

jest nazwa, pod którą nasz proces

będzie widoczny w tablicy procesów Serwera SQL.

CONNECT TIMEOUT (Czas do zerwania połączenia)

Decyduje jak długo klient będzie ponawiał próby nawiązania połączenia
z serwerem SQL. Domyślnie jest przyjmowane 60 s. Zwiększenie tego parametru
może poprawić funkcjonowanie stacji klienckich, szczególnie gdy łączymy się
przez WAN.

HOST NAME (Nazwa hosta)

Parametr służy do nadania nazwy stacji roboczej, poprzez którą będzie ona
identyfikowana w tablicy procesów serwera SQL. Dzięki temu będzie można
odróżnić to połączenie od innych na różnych listach - takich jak np. tworzona
przez procedurę

sp_who

.

NATIONAL LANG NAME (Wybrany język)

Parametr ten definiuje język, w jakim ukazują się komunikaty o błędach. Jeśli go
nie ustalimy, przyjęte zostanie ustawienie domyślne.

552

Część III

TDS PACKET SIZE (Rozmiar pakietów TDS)

Stosowany do ustalenia rozmiaru pakietów Tabular Data Stream (TDS). TDS jest
protokołem pakietowym wysokiego poziomu, wykorzystywanym przez SQL
Server do wymiany danych z klientami. Chociaż w programie BDE Administrator
można określić rozmiar od 0 do 65535, to jednak należy ograniczyć się do zakresu
obsługiwanego przez SQL Server: od 512 do 32767. Może się okazać, że
zwiększenie rozmiaru pakietu spowoduje wzrost przepustowości systemu,
w wyniku zmniejszenia liczby pakietów wymaganych do transmisji dużych ilości
danych (np. pól BLOB). Domyślny rozmiar wynosi 4096 i najlepszą wydajność
osiąga się dla wielkości pakietów w zakresie od 4096 do 8192.

Aby ustalić maksymalny rozmiar pakietu obsługiwanego przez serwer, można użyć
procedury pamiętanej serwera SQL o nazwie

sp_configure

. Powinniśmy więc

- aby otrzymać bieżący rozmiar pakietu - wykonać instrukcję

sp_configue

'network packet size'

. Należy odpowiednio ustawić ten parametr przed

zmianą odpowiadających mu ustawień w

programie DBE Administrator.

Niezgodność

TDS PACKET SIZE

w DBE Administrator i SQL Server

max

network packet size

(lub niewłaściwa dodatkowa pamięć

addition

network memory

) może wywołać jeden z komunikatów błędów:

„Error: unknown user name or password (nieznana nazwa użytkownika lub

hasło)

„Server error-4002 Login failed (nieudane rejestracja w systemie)

„Server error - 20014 Login incorrect (niewłaściwa rejestracja)

UWAGA

Zmiana domyślnego rozmiaru pakietu jest możliwa dopiero od wersji 5 TDS

DATABASE NAME (Nazwa bazy danych)

Pozwala określić nazwę bazy danych SQL, z którą chcemy się połączyć. Zwykle
tworzy się osobny alias dla każdej wykorzystywanej bazy. Wtedy parametr

DATABASE NAME

wskazuje tę, do której odnosi się dany alias. Brak wartości

parametru

DATABASE NAME

jest równoznaczny ze wskazaniem na domyślną

bazę użytkownika. Nie polecamy jednak takiego podejścia. Jednym z powodów
jest fakt, że do ustalenia bazy trzeba wtedy tworzyć zapytanie do jednej z tabel
systemowych (ang. syslogins table) na serwerze.

BLOB EDIT LOGGING (Rejestracja zmian w polach BLOB)

Można zablokować rejestrację zmian w polach

BLOB

, ustawiając wartość tego

parametru na

FALSE

. Minimalizujemy wówczas wymagania pamięciowe,

Rozdział 18 Delphi na serwerze SQL Sybase

553

poprawiając wydajność. Ustawienie

FALSE

powoduje, że

BLOB

są transmitowane

za pomocą programu narzędziowego SQL Server bulk copy. Dlatego jeśli
zamierzamy użyć tego mechanizmu, powinniśmy ustawić opcję

set select

into/bulk copy on

w docelowej bazie. Opcję

set select into/bulk

copy ustawiamy

za pomocą procedury pamiętanej

sp_dboption

.

MAX QUERY TIME (Maksymalny czas wykonania zapytania)

Ten parametr określa czas wyczekiwania przez SQL do momentu zakończenia
asynchronicznego zapytania (po upływie tego czasu jest ono usuwane). Sterownik
Sybase SQL Links domyślnie wykonuje zapytania synchronicznie. Wartość
parametru

TIMEOUT

określa czas zakończenia zapytania synchronicznego. Tryb

asynchroniczny ustawimy, zwiększając parametr

DRIVER FLAG do 2048.

Domyślnie SQL Links ogranicza czas zakończenia zapytania asynchronicznego do
300 s.

Różnorodne zagadnienia dotyczące Sybase SQL Server

Oprócz konfiguracji aliasów są jeszcze pewne niuanse, o których powinniśmy
wiedzieć, tworząc aplikacje w Delphi korzystające z baz danych Sybase’a. Poniżej
poruszymy kilka spraw dotyczących współpracy serwera SQL Sybase’a
z aplikacjami Delphi.

Typy danych numeric i decimal

W system Sybase 10 wprowadzono nowe numeryczne i dziesiętne typy danych.
BDE tłumaczy je na swój typ danych zmiennoprzecinkowych

fldFloat

. Należy

jednak zauważyć, że oba typy są pobierane z serwera w ich naturalnym formacie
(xltNONE), więc nie występuje żadna utrata danych przy translacji.

Tabele w innych bazach

Możemy uzyskać dostęp do obiektów w innych bazach danych, podając pełną
specyfikację obiektu. Np. można otworzyć tabelę ACCOUNTING.DEMO.

CUSTOMER, umieszczoną w bazie danych ACCOUNTING, której właścicielem
jest użytkownik DEMO, podając tylko jej pełną nazwę.

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

Poniżej zebraliśmy kilka praktycznych porad, które mogą okazać się przydatne
w przypadku napotkania problemów połączeniowych.

1. Jeśli nasza aplikacja napisana w Delphi nie łączy się z serwerem, powinniśmy

spróbować nawiązać komunikację poprzez program Sybase SYSPING.

554

Część III

Uzyskanie połączenia świadczyłoby o

tym, że prawdopodobnie źle

skonfigurowaliśmy alias BDE. W takiej sytuacji powinniśmy powrócić do
programu BDE Configuration i upewnić się, że podane ustawienia są
poprawne. (w szczególności nazwa serwera).

WSKAZÓWKA

Warto zwrócić uwagę na różnicę pomiędzy programem WSYBPING i SYBPING.
WSYBPING jest przeznaczony tylko do obsługi połączeń 16-bitowych, więc nie
należy używać go z 32-bitowymi aplikacjami Delphi.

2.

Gdy próba nawiązania połączenia poprzez SYSPING kończy się
niepowodzeniem, możemy przypuszczać, że przyczyną problemów może być
protokół sieciowy. Jeśli korzystamy z TCP/IP, to połączenie z serwerem można
sprawdzić za pomocą programu PING, który jest dostarczany z Windows 95
i Windows. Powinniśmy spróbować łączenia poprzez nazwę komputera,
zapisaną w pliku HOSTS oraz poprzez jego adres IP.

3. Jeśli łączymy się poprzez adres IP, co nie udaje się poprzez nazwę, powinniśmy

sprawdzić plik HOSTS. Tymczasowo można zmienić odwołanie sieciowe
(programem SYBEDIT), zastępując w nim nazwę komputera jego adresem IP.
Z uwagi na fakt, że odwołanie do nazwy jest wygodniejsze, powinniśmy
możliwie najszybciej usunąć przyczynę uniemożliwiającą połączenie.

4. W przypadku protokołu IPX, warto sprawdzić, czy numer IPX sieci serwera

jest zgodny z podanym w SYBEDIT. Jeśli nadal nie możemy uzyskać
połączenia albo jeśli wystąpiły kłopoty z

jeszcze innym protokołem

połączeniowym, radzimy zwrócić się o pomoc do administratora sieci.

5. Jeśli korzystamy z TCP/IP i w żaden sposób (ani poprzez nazwę ani adres IP)

nie udało się nam połączyć z serwerem, 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.

6. W sytuacji, gdy nie uzyskaliśmy połączenia poprzez SYBPING, natomiast

PING funkcjonuje poprawnie, należy sprawdzić, czy numer portu w odwołaniu
sieciowym jest zgodny z numerem ustawionym na serwerze. Informacje
o portach przypisanych do serwera można uzyskać od administratora bazy
danych. W Systemie 10 można je ustawiać, posługując się narzędziem Sybase -
SYBINIT.

7. Jeśli wszystko wydaje się być skonfigurowane poprawnie, możemy dla próby

wykorzystać inny protokół (jeśli oczywiście mamy taką możliwość). Np. autor

Rozdział 18 Delphi na serwerze SQL Sybase

555

spotkał się z wersją Sybase System 10 for Novell Netware, która nie
funkcjonowała poprawnie ze swoim macierzystym protokołem Novell IPX,
natomiast nie sprawia żadnych kłopotów z TCP/IP. Serwer pozwala na
jednoczesną obsługę kilku protokołów, więc może tymczasowo wykorzystywać
tylko ten, z którym funkcjonuje poprawnie, aż do momentu przywrócenia do
właściwej pracy drugiego protokołu.

Wstęp do SQL

Ten paragraf jest wprowadzeniem do odmiany SQL, używanej przez serwery
Sybase SQL, zwanej Transact-SQL. Zwracamy w nim szczególną uwagę na kilka
cech, które różnią tę implementację SQL od dostarczanej 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 program ISQL.

Tworzenie bazy danych

Zaczniemy od utworzenia bazy danych i umieszczenia w niej tymczasowych tabel,
które posłużą do analizy podanych dalej przykładów. Skorzystamy z instrukcji

CREATE DATABASE

. Dokładna postać jej składni zależy od konkretnej

implementacji. Tutaj zaprezentujemy jej podstawową postać w języku Transact-
SQL:

CREATE DATABASE dbname ON datadevice=size LOG ON

➥

logdevice=size

Planując umieszczenie tworzonej bazy danych, korzystamy z urządzeń logicznych.
Koncepcja urządzeń logicznych pozwala uniknąć odwołań do fizycznego
położenia danych na dysku. Stanowią one warstwę pośrednią pomiędzy
sterownikami dysku i bazami danych.

Definiujemy je korzystając z instrukcji

DISK INIT

. Oto prosty przykład jej

użycia:

DISK INIT
name='saltrn00'
physname='c:\sybasesql\data\saltrn00.dat',
vdevno=5,
size = 2048

Parametr

name

definiuje nazwę logiczną (ang. logical handler), która występuje

w odwołaniach do danego urządzenia.

556

Część III

Parametr

physname

określa szczegółowo fizyczne położenie i nazwę urządzenia

plikowego. Może to być plik w systemie plikowym lub surowa partycja
(w przypadku systemu operacyjnego, który przewiduje taką możliwość).

vdevno

jest logicznym numerem urządzenia wirtualnego. Musi on być unikalny

wśród numerów urządzeń zdefiniowanych na serwerze. Może przyjmować
wartości z zakresu od 1 do 255 - 0 jest zarezerwowane dla urządzenia głównego
(ang. master device). Jednak, aby móc skorzystać z numerów powyżej 10
powinniśmy najpierw powiększyć maksymalną liczbę dopuszczalnych urządzeń za
pomocą procedury pamiętanej

sp_configure

Size

określa liczbę stron o

wielkości 2K, przeznaczonych dla danego

urządzenia. Np. wielkość 4M (4096K) uzyskamy podając 2048.

Analogicznie tworzymy urządzenia do rejestracji zdarzeń (ang. log devices)

DISK INIT
name='sallog00'
physname='c:\sybasesql\data\sallog00.dat',
vdevno=6,
size = 512

Po tych przygotowaniach możemy przystąpić do utworzenia bazy. Instrukcja

CREATE DATABASE

w poniższym przykładzie korzysta ze zdefiniowanych

wcześniej urządzeń :

CREATE DATABASE sales ON saldat00=4 LOG ON sallog00=1

Instrukcja USE

Instrukcja USE w języku SQL służy do wyboru aktywnej bazy. Składnia instrukcji
jest następująca:

USE dbname

Tworzenie tabel

Po utworzeniu bazy czynimy ją aktywną i możemy już rozpocząć definiowanie
obiektów. Prawie każde pojęcie odnoszące się do relacyjnych baz danych może
być zaprezentowane za pomocą co najwyżej 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ę ISQL, aby utworzyć tabelę CUSTOMER:

CREATE TABLE CUSTOMER
(
CustomerNumber int

NOT

NULL,

Rozdział 18 Delphi na serwerze SQL Sybase

557

LastName Char(30) NULL,
FirstName char(30) NULL,
StreetAddress char(30)

NULL,

City char(20)

NULL,

State char(2)

NULL,

Zip char(10)

NULL

)

Dalej utwórzmy tabelę SALE :

CREATE TABLE SALE
(
SaleNumber int

NOT

NULL,

SaleDate datetime NULL,
CustomerNumber int

NOT

NULL,

ItemNumber int

NOT

NULL,

Amount money
)

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

CREATE TABLE ITEM
(
ItemNumber int

NOT

NULL,

Description char(30)

NULL,

Price money NULL
)

Dodawanie i usuwanie kolumn

Do dodania lub usunięcia kolumny z istniejącej tabeli służy instrukcja

ALTER

TABLE

. Aby dodać kolumnę korzystamy z następującej składni:

ALTER TABLE CUSTOMER
ADD PhoneNumber char(10)

NULL

natomiast aby ją usunąć:

ALTER TABLE CUSTOMER
DROP PhoneNumber

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.

OSTRZEŻENIE

Instrukcja

ALTER TABLE... DROP

jest realizowana w Sybase tylko jako

nieudokumentowany element języka. Powinniśmy brać to pod uwagę, korzystając
z niej w swoich programach.

558

Część III

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 Sybase 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 nazywana jest 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.

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 po stronie użytkownika, które

Rozdział 18 Delphi na serwerze SQL Sybase

559

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 wyjątków generowanych przez 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ć zdefiniowane powyżej więzy

INVALID_STATE,

wprowadzamy następującą instrukcję w programie ISQL:

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

Ponieważ pozwalają one 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

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 Sybase SQL Server 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)

Wprowadzanie danych

Instrukcja

INSERT

służy do wprowadzania danych do tabeli. 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.

560

Część III

W poniższym przykładzie dodano dane do wszystkich naszych tabel. Najpierw trzy
wiersze do tabeli CUSTOMER (posłużymy się ISQL):

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)

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:

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)

Rozdział 18 Delphi na serwerze SQL Sybase

561

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)

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:

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)

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

Instrukcja

DELETE

może też zawierać klauzulę

WHERE,

ograniczającą zakres

usuwanych wierszy. Oto odpowiedni przykład:

DELETE FROM CUSTOMER
WHERE LastName<>'Doe'

562

Część III

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

.

UWAGA

Oprócz zwykłych instrukcji języka DML (Data Manipulation Language ) (np.

INSERT, UPDATE, DELETE)

kontrola transakcji uwzględnia także instrukcje

DDL (Data Definition Language). Jeśli wycofa się transakcję, w której został
utworzony obiekt, to zostanie on usunięty z bazy. Transakcji można więc użyć do
warunkowego utworzenia tabeli roboczej. Pozostanie ona w bazie pod warunkiem,
że wszystkie dane zostaną do niej poprawnie wprowadzone. Jeśli bowiem
instrukcje

CREATE TABLE i INSERT

są umieszczone w tej samej transakcji

i instrukcja

INSERT

nie zostanie wykonana poprawnie, to stworzona tabela

zostanie automatycznie usunięta.

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.

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

Instrukcji

SELECT

w Sybase 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 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:

Rozdział 18 Delphi na serwerze SQL Sybase

563

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 użycia:

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%'

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 zastosować - do zamiany wszystkich liter w kolumnie i poszukiwanym
wzorcu na duże - funkcję

UPPER

.

Oto dalsze przykłady:

SELECT * FROM SALE
WHERE Amount>500

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

564

Część III

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

.

Określa się dodatkowe tabele w klauzuli

SELECT FROM

i łączy się pola będące

ze sobą w relacji, formułując odpowiednie warunki w klauzuli

WHERE.

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 użyto znaku

równości do złączenia tabel CUSTOMER i SALE poprzez pole

Customer-

Number

. Tabela wymieniona po lewej stronie znaku równości jest nazywana

tabelą zewnętrzną, a po prawej tabelą 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 lewostronne złączenie. Jest to
najczęściej używane złączenie w zapytaniach SQL.

Złączenia wewnętrzne(inner) oraz 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 jest
spełniony warunek złączenia. Natomiast 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
wewnętrznej są dołączane 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. Oto przykład złączenia
zewnętrznego, opisany w języku Transact-SQL:

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

Rozdział 18 Delphi na serwerze SQL Sybase

565

Składnia złączenia w ANSI

Oprócz własnej składni SQL Server akceptuje przy konstruowaniu złączeń także
składnię ANSI. ANSI definiuje specjalne operatory złączeń, umieszczane
w klauzuli

FROM

. Np. lewostronne wewnętrzne złączenie może wyglądać

następująco:

SELECT CUSTOMER.CustomerNumber, SALE.Amount
FROM CUSTOMER LEFT JOIN SALE
ON 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

. Zauważmy, że prawostronne i lewostronne złączenia

wewnętrzne dają ten sam wynik i dlatego nie różnią się składniowo.

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 - 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

W tym zapytaniu

CUSTOMER

i

SALE

są złączone 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

566

Część III

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 jest złączana ze sobą w celu 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 korzysta z dwóch różnych aliasów dla SALE.

Pełne złączenia (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, to łącząc je poprzez
zewnętrznego pełne złączenie otrzymamy wszystkie wiersze. Jest to po prostu
kombinacja złączenia prawo i lewostronnego. Oto przykład:

SELECT ITEM.Description, ITEM.Price SALE.Amount
FROM SALE FULL OUTER JOIN ITEM
ON ITEM.ItemNumber=SALE.ItemNumber

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:

Rozdział 18 Delphi na serwerze SQL Sybase

567

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 wykonania zapytania. W niektórych systemach zatrzymanie procesu
jest możliwe tylko przez zrestarowanie komputera serwera. Należy więc unikać
tworzenia iloczynu kartezjańskiego szczególnie w przypadku, 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)

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 przechodzi się wiersz po wierszu 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

568

Część III

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

. Sybase

SQL Server wymaga, 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, Sybase SQL 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 zależność 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ą

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.

Rozdział 18 Delphi na serwerze SQL Sybase

569

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 Sybase SQL Server 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

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

.

570

Część III

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 w Oracle 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.

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.

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.

Rozdział 18 Delphi na serwerze SQL Sybase

571

Procedury pamiętane (Stored Procedures)

Procedury pamiętane są kompilowanymi programami SQL, przechowywanymi
wraz z innymi obiektami baz danych. Do tworzenia procedur pamiętanych służy
instrukcja

CREATE PROCEDURE

. Poniżej przedstawiamy przykład takiej

procedury dla SQL Server:

CREATE PROCEDURE listcustomers
AS
BEGIN

SELECT LastName FROM CUSTOMER

END

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

CREATE PROCEDURE listcustomersbystate (@State varchar(2),
@LastNameMask varchar(30))
AS
BEGIN

SELECT LastName FROM CUSTOMER

WHERE State= @State AND LastName LIKE @LastNameMask

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 je tworzyć korzystając
z dowolnego edytora tekstów. Warto przypomnieć, że skrypty powinny zawierać
niezbędne instrukcje

USE

oraz polecenie GO, które kończy wykonanie pliku

wsadowego. Skrypty SQL w Sybase SQL Server uruchamia się, podając w linii
poleceń programu ISQL instrukcję (jak w poniższym przykładzie):

ISQL -Usa -Imyscript.sql -Omyscript.out

Parametr -I określa nazwę skryptu do wykonania; -O określa nazwę pliku
wynikowego dla skryptu. Przykład został przedstawiony w listingu 18.1. Polecenie

sp_dir

w procedurze pamiętanej działa analogicznie, jak DIR w systemie DOS .

Listing 18.1. Skrypt SQL zawierający procedurę pamiętaną

sp_dir

/*
* DROP PROC dbo.sp_dir
*/
use sybsystemprocs
go
IF OBJECT_ID('dbo.sp_dir') IS NOT NULL
BEGIN

DROP PROC dbo.sp_dir

572

Część III

PRINT '<<< DROPPED PROC dbo.sp_dir >>>'

END
go
create procedure sp_dir @mask char(30) = '%', @obtype char(2)

➥

= 'U', @orderby char(3)='/N' as

select o.id,
o.name,
o.type,
date_created=o.crdate,

row_count = rowcnt(i.doampg),

--

row_len=1*null,

size = convert(numeric(13),(reserved_pgs(i.id,

➥

i.doampg)+reserved_pgs(i.id, i.ioampg))),

data_space = convert(numeric(13),data_pgs(i.id,

➥

i.doampg)),

index_space = convert(numeric(13),data_pgs(i.id,

➥

i.ioampg)),

unused_space = convert(numeric(13),((reserved_pgs(i.id,

➥

i.doampg)+reserved_pgs(i.id, i.ioampg))-

➥

(data_pgs(i.id, i.doampg)+data_pgs(i.id,

➥

i.ioampg)))),

owner=user_name(o.uid),
sequencer=0.0
into #sp_dir
from sysobjects o,
sysindexes i
where o.name like @mask and o.type like @obtype
and o.id*=i.id
and i.indid<=1
select @orderby=upper(@orderby)

/*
declare @tablename char(30),@row_len int
set rowcount 1
while exists (select @tablename=name from #sp_dir where type

➥

in ('U','S') and row_len = null)

begin

exec sp_estspace @tablename,1,@rowlength=@row_len

➥

output

update #sp_dir set row_len=@row_len

where

name=@tablename

end
set rowcount 0
*/

if @orderby = '/N'
begin

create clustered index sp_dirind on #sp_dir (name,id)

Rozdział 18 Delphi na serwerze SQL Sybase

573

end
else
if @orderby = '/R'
begin

create clustered index sp_dirind on #sp_dir

(row_count,id)
end
else
if @orderby = '/S'
begin

create clustered index sp_dirind on #sp_dir (size,id)

end
else
if @orderby = '/D'
begin

create clustered index sp_dirind on #sp_dir

➥

(date_created,id)

end
else
if @orderby = '/DS'
begin

create clustered index sp_dirind on #sp_dir

➥

(data_space,id)

end
else
if @orderby = '/IS'
begin

create clustered index sp_dirind on #sp_dir

➥

(index_space,id)

end
else
if @orderby = '/US'
begin

create clustered index sp_dirind on #sp_dir

➥

(unused_space,id)

end
else
if @orderby = '/O'
begin

create clustered index sp_dirind on #sp_dir (owner,id)

end

alter table #sp_dir drop sequencer

alter table #sp_dir add sequencer numeric(10,0) identity

insert into #sp_dir
(name,row_count,size,data_space,index_space,unused_space,id,

➥

type,date_created))

574

Część III

select
'TOTAL:',row_count=isnull(sum(row_count),0),size=isnull(sum

➥

(size),0),date_space=isnull(sum(data_space),0),index_space

➥

=isnull(sum(index_space),0),unused_space=isnull(sum(

➥

unused_space),0),id=0,type=@obtype,date_created=getdate()

from #sp_dir

select name,type,date_created, row_count, size, data_space,
index_space,
unused_space, owner
from #sp_dir
order by sequencer
go
IF OBJECT_ID('dbo.sp_dir') IS NOT NULL

PRINT '<<< CREATED PROC dbo.sp_dir >>>'

ELSE

PRINT '<<< FAILED CREATING PROC dbo.sp_dir >>>'

go

sp_dir

zwraca listę obiektów wszystkich typów z danej bazy danych. Może ona

zostać posortowana według dowolnej ze zwracanych kolumn. Wynik działania
procedury

sp_dir

został przedstawiony na rysunku 18.1.

Instrukcja

USE

na początku pliku określa bazę danych, w której procedura

zostanie utworzona. Natomiast polecenie

GO,

kończące wykonanie zadania

wsadowego, separuje oddzielne części skryptu.

Rysunek 18.1
Można użyć
procedury sp_dir
do utworzenai listy
obiektów na
serwerze

Rozdział 18 Delphi na serwerze SQL Sybase

575

Uruchamianie procedur pamiętanych

Procedury pamiętane SQL Server uruchamiamy instrukcją

EXECUTE

. Składnia

instrukcji jest następująca:

EXECUTE

procedurename parameters

Możemy skrócić

EXECUTE

do

EXEC

, a nawet opuścić, jeśli

EXEC

jest pierwszą

instrukcją w linii. Na przykład, uruchomienie procedury

listcustomer

za

pomocą ISQL może wyglądać następująco:

listcustomers

Dopuszczalne jest przekazanie parametrów wg. kolejności, w jakiej występują
w definicji procedury:

exec listcustomersbystate 'MO', '%'

lub przez nazwę parametru:

exec listcustomersbystate @LastName Mask='%', @State='MO'

Procedury systemowe

Procedury systemowe są to specjalne procedury pamiętane, których nazwa
rozpoczyna się od

sp_

i wykonywane kontekstowo na bieżącej bazie danych. Np.

sp_spaceused

jest procedurą systemową. Jeśli wykonamy ją wtedy, gdy jest

aktywna baza pubs2, to otrzymamy informację na temat pamięci używanej przez
bazę pubs2. Jeśli wykonamy ją dla bazy głównej (master database), otrzymamy
dla niej analogiczną informację. Procedury systemowe mogą być umieszczone
w bazie głównej (master database) albo w

bazie procedur systemowych

(sybsytemproc database)

WSKAZÓWKA

Jeśli zamierzamy uruchomić procedurę systemową dla bazy innej niż bieżąca,
wygodniej jest - zamiast zmiany kontekstu poleceniem USE - poprzedzić nazwę
procedury nazwą bazy, dla której chcemy ją uruchomić. Załóżmy, że chcemy
wykonać procedurę systemową dla bazy głównej a pubs2 jest aktywna. Można to
zrobić następująco:

master..sp_dir '%', 'S'

Chociaż

sp_dir

jest umieszczona w bazie sybsystemprocs, lecz przy jej

uruchomieniu kontekst będzie chwilowo zmieniony na bazę główną. Wynik
działania jest pokazany na rysunku 18.2.

576

Część III

Warto też zwrócić uwagę na dwie kropki pomiędzy nazwą bazy danych a nazwą
procedury. Jest to standardowe w Sybase wywołanie procedury pamiętanej.
Przyjmuje się wtedy domyślnie, że właścicielem procedury (który powinien być
także wyspecyfikowany) jest

dbo

(database owner)

Procedury zdarzeń (Triggers)

Procedury zdarzeń są to (podobnie jak procedury pamiętane) podprogramy SQL,
uruchamiane wtedy, gdy dane z danej tabeli są wprowadzane, modyfikowane lub
usuwane. Można skojarzyć procedurę zdarzenia ze specyficzną operacją
dokonywaną na tabeli: wstawianiem wiersza, modyfikacją lub usuwaniem. Oto
odpowiedni przykład w Transact-SQL:

CREATE TRIGGER SALEDelete ON CUSTOMER FOR DELETE
AS
BEGIN

DELETE FROM SALE

WHERE CustomerNumber=(SELECT CustomerNumber FROM

deleted);
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.

Rysunek 18.2
Można użyć
uproszczonej
metody wskazania
bazy danych, dla
której chcemy
uruchomić
procedurę
pamiętaną

Rozdział 18 Delphi na serwerze SQL Sybase

577

Warto zwrócić uwagę na użycie logicznej tabeli DELETE. Zawsze, gdy
uruchamiana jest procedura zdarzenia skojarzona z

DELETE

, to z wierszy

przeznaczonych do usunięcia tworzona jest tabela logiczna o nazwie

deleted

.

W tej procedurze wykonywane jest najpierw podzapytanie do

deleted

, które

pozwala określić najpierw wartość

CustomerNumber

dla usuwanego rekordu.

Następnie są usuwane te wiersze z

tabeli SALE, mające taką wartość

CustomerNumber

.

Gdy zostaną uruchomione procedury związane z

INSERT

albo

UPDATE

, to

analogicznie serwer tworzy tabelę logiczną o nazwie

inserted

. Istnieje ona

tylko w pamięci serwera, ale jest dostępna dla procedury zdarzenia, jak każda
normalna tabela.

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, w procedurach pamiętanych, mogą okazać
się użyteczne.

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.

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

:

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. Sybase SQL Server

578

Część III

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

CLOSE

) i ponownie otworzyć (

OPEN

) kursor.

UWAGA

Mimo że Sybase SQL Server 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 w obiektach

TDataSets,

takich jak

TQuery i TTable,

można przewijać w obie strony.

Wiersze zwracane przez modyfikowalny kursor mogą być zmodyfikowane 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

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