SQL w objęciach C

Marcin Jackowski
jack@infovide.pl

Tekst zawiera nieformalne, luźne omówienie podstawowych
sposobów przekazywania danych między konstrukcjami SQL zanurzonymi w języku C
dla prekompilatora Pro*C Oracle, za pomocą różnych odmian zmiennych
kontaktowych (host variables).

Poprzedzono je krótkim wprowadzeniem na temat Pro*C.

ABC Pro*C

Pro*C ucieleśnia ideę określaną angielskim mianem SQL
embeded in host language
, tłumaczoną na ogół jako zanurzony SQL. Słowo host,
znany dziś powszechnie rzeczownik, pojawi się tu jeszcze parokrotnie –
jako przymiotnik. Nie siląc się na jego dosłowne czy choćby zbliżone tłumaczenie,
można jednak przyznać, że metafora języka C podejmującego w gościnie obcojęzyczne
frazy SQL-a nie byłaby zbyt naciągana. Dodajmy dla pełności, że Oracle
oferuje podobne do Pro*C prekompilatory także dla innych języków, m.in.
Cobola, Fortranu i Ady (dwa ostatnie chyba nie są już rozwijane). Pełna nazwa
omawianego tu produktu brzmi dziś Pro*C/C++. Ze względu na brak odwołań do właściwości
czysto obiektowych, pozostaniemy przy wcześniejszym i krótszym terminie Pro*C.

Pro*C to swoisty lingua franca systemu Oracle – wymuszona
względami praktycznymi koegzystencja dwóch języków – C i SQL. Uzupełniając
się nawzajem, dają programiście możliwości, jakich nie miałby posługując
się każdym z nich z osobna. Jak wiadomo – SQL nie jest, ściśle rzecz
biorąc, językiem programowania, lecz deklaratywnym formalizmem do formułowania
zapytań. Posługując się nim wskazujemy, jaki zbiór rekordów chcemy uzyskać,
a nie jak ma on być pobrany z bazy; żadne konstrukcje sterujące w rodzaju pętli
i rozwidleń nie mają przy tym zastosowania. Ich brak sprawia, że w
SQL’u nie da się wyrazić nawet najprostszych algorytmów. Możliwość
abstrahowania od organizacji danych w bazie, choć przydatna i cenna, okazuje się
dla programistów zbyt krępująca. Stąd też różnego rodzaju proceduralne
uzupełnienia, do których zaliczają się PL/SQL i prekompilatory.

Choć PL/SQL łączy cechy proceduralne (zbliżony pod tym
względem do Pascala) z możliwościami SQL’a, to jednak jako twór
osadzony w systemie Oracle i ściśle z nim związany, stwarza istotne
ograniczenia. Programy tworzone w tym języku przechowuje i wykonuje serwer bazy
danych, a więc nie mają one żadnego kontaktu z częścią kliencką lub
warstwą pośrednią (middleware) aplikacji. Wyjątkiem będą tu produkty firmy
Oracle (Oracle Forms i Oracle Reports), jednak coraz częściej trzeba sobie
radzić bez nich. Arsenał środków PL/SQL’a jest też dość skromny w
porównaniu z C, C++ czy Javą – językami bogatszymi o liczne biblioteki,
funkcje systemowe, obiektowość itp.

Prekompilatory – np. takie jak Pro*C – pozwalają
wywoływać z kodu „standardowego” języka programowania polecenia
SQL’a. Plik źródłowy podlega wstępnemu przetworzeniu tekstowemu
zamieniającemu „wstawki” SQL’owe na wywołania odpowiednich
funkcji bibliotecznych i inne konstrukcje w pełni zgodne z językiem
podstawowym. Następnie kod jest kompilowany w zwykły sposób. W fazie
konsolidacji uczestniczą biblioteki realizujące komunikację z systemem Oracle.
Pro*C może okazać się bardzo pomocne, kiedy istniejącą aplikację napisaną
w C chcemy przystosować do współpracy z bazą Oracle. Jeśli kod był
odpowiednio ustrukturalizowany, to poza przełożeniem modelu danych wystarczy
zmienić niskopoziomowe funkcje dostępu do danych.

Zmienne kontaktowe

Współdziałanie dwóch zasadniczo różnych języków
wymaga ustanowienia między nimi połączeń na poziomie składniowym. W Pro*C
rolę styku między poleceniami SQL, a otaczającym je kodem proceduralnym, pełnią
zwykłe zmienne zadeklarowane w C, a następnie „osadzone” we frazie
INTO czy WHERE przy pomocy dwukropka. Oryginalne dokumentacje określają
je mianem host variables. Bazowy kod C gościnnie użycza tych zmiennych
wstawkom z wyższych, deklaratywnych sfer w celu obustronnej komunikacji.
Ponieważ dosłowne, czy zbliżone tłumaczenia nie wydają się sensowne,
proponuję nazwać je zmiennymi kontaktowymi dla podkreślenia spełnianej przez
nie roli.

W poniższym fragmencie kodu zmienna id pełni rolę
wejściową (parametru), a pozostałe dwie – rolę wyjściową.

long id=123;
long prodid;
char descrip[LEN];

EXEC SQL SELECT prodid, descrip
    INTO :prodid, :descrip
    FROM product
    WHERE prodid = :id;

Fraza EXEC SQL sygnalizuje następujące po niej
polecenie SQL zakończone średnikiem.

Podobnie jak w PL/SQL, miejscem parametryzacji zapytania jest
fraza WHERE, a przejęciu rezultatu służy fraza INTO. Użycie
zmiennej w miejscu kolumny, czy tabeli jest niedozwolone, choć legalne przy
parametryzacji skryptów SQL symbolami &. W pełni dynamiczne
budowanie poleceń umożliwiają inne tryby, bez zmiennych kontaktowych (tutaj
nie omawiane). Pomijając kwestie błędów i wyjątków, przykładowe zapytanie
pobierze pojedynczy rekord na podstawie identyfikatora dostarczonego w :id i
umieści dane dwóch kolumn w :prodid i :descrip.

Tablice kontaktowe

Polecenia SQL każdego z czterech podstawowych rodzajów,
dotyczące wielu rekordów, zdarzają się równie często, co selektywne,
operujące kluczem. Pro*C wychodzi naprzeciw tym potrzebom w naturalny i prosty
sposób – daje możliwość użycia wieloelementowych zmiennych, czyli
tablic kontaktowych (ang. host arrays). Prekompilator rozpoznaje, z jaką
kategorią zmiennej ma do czynienia, więc formalnie ich stosowanie niczym się
nie różni, jak widać w poniższym fragmencie kodu.

char pattern[LEN] = "A%";
long prodid[SIZE];
char descrip[SIZE][LEN];

EXEC SQL SELECT prodid, descrip
    INTO :prodid, :descrip
    FROM product
    WHERE descrip LIKE :pattern;

W porównaniu z poprzednim przykładem, zmieniliśmy tylko
deklaracje – uzyskując tablice zdolne pomieścić liczniejszy wynik i
warunek, który go uprawdopodobni.

Jeśli liczba pobranych rekordów jest z góry wiadoma lub
chcemy ją ograniczyć, to takim sposobem unikniemy deklaracji otwierania i
zamykania kursora.

Wartości tekstowych nie trzeba ujmować w apostrofy.

Załóżmy teraz, że hipotetyczna aplikacja gromadzi dane
nowych produktów w dwóch zsynchronizowanych tablicach, zliczając je zarazem w
jakiejś zmiennej. Produkty mają trafiać do bazy danych nie pojedynczo, lecz
po uzbieraniu „paczki”.

Dbając o modularność, piszemy niewielką procedurę
przeznaczoną do zasilenia tabeli PRODUCT. Jedna z realizacji tego
zadania mogłaby przybrać następującą postać:

/** Funkcja wstawia num rekordów do tabeli PRODUCT (wersja iteracyjna) */
    void
sql_product_multi_ins ( long* ids, char** descs, int num )
{
    int i;
    for ( i=0; i<num && i<SIZE; i++ )
    {
        EXEC SQL INSERT
            INTO product
            VALUES (
               
:ids[i],
               
:descs[i]
            );
    }
}

Podejście to ma – może niezbyt widoczne na pierwszy
rzut oka, ale dość istotne wady. Dla wielokolumnowych tabel lista argumentów
znacznie się wydłuży. W przypadku dodawania czy usuwania kolumn trzeba będzie
poprawiać definicję każdej z funkcji operujących na zmienianej tabeli oraz
zmieniać wszystkie linie kodu zawierające wywołania tych funkcji. Również
ew. zmiana kolejności kolumn narazi nas na takie modyfikacje, jeśli chcemy,
aby listy argumentów miały zgodny z nią porządek. Na szczęście jest lepsza
alternatywa, którą poznamy za chwilę na przykładzie przebudowanej funkcji sql_product_multi_ins.

Tablice w kontekście DML są jedynie uproszczeniem składni
i komunikacji z serwerem, więc nietrudno się domyślić, że w analogiczny
sposób można je stosować także w poleceniach UPDATE i DELETE.

Agregacja

Jedna z ciekawszych (a jednocześnie bardzo naturalnych) własności
Pro*C, którą teraz omówimy, powinna przypaść do gustu zwłaszcza miłośnikom
obiektowości: jako zmienne kontaktowe możemy stosować struktury.

Przy pobraniu danych fraza INTO upraszcza się do
pojedynczego elementu, jak w poniższym odpowiedniku pierwszego przykładu:

struct product {
    long prodid;
    char descrip[LEN];
}
struct product prod;

EXEC SQL SELECT *
    INTO :prod
    FROM product
    WHERE prodid = :id;

Stosowanie gwiazdki bywa – nieraz słusznie – piętnowane.
W tej jednak sytuacji, zakładając ścisłą odpowiedniość między kolumnami
tabeli a strukturą – typem agregującym, nie wydaje się ono naganne, a
raczej uzasadnione, choćby zwięzłością formy i ułatwieniem zmian, o których
była już mowa. Jakie konsekwencje przyniesie w tym wypadku rozszerzenie tabeli
PRODUCT o nową kolumnę? Zmusi nas do dopisania pola w strukturze, ale
za to w zanurzonym zapytaniu nie trzeba będzie nic poprawiać.

Wielo-Agregacja

Czas na ostatnią w tym skrótowym przeglądzie odsłonę omówienia
symbiozy języków C i SQL. Elementami struktur kontaktowych mogą być tablice
(każda tego samego rozmiaru). Od wersji 8.0 Pro*C pozwala też deklarować
tablice struktur kontaktowych, które mogą wydawać się bardziej naturalne od
struktur tablic. Oba podejścia różni w gruncie rzeczy jedynie organizacja
danych, więc zademonstrujemy tylko starsze z nich.

Agregacja zarówno kolumn jak i wierszy w jednym tworze
pozwala wykonywać zbiorowe odwołania do tabel znacznie prościej i
naturalniej, niż przy pomocy poprzednio omówionych sposobów. Przekonajmy się
o tym na przykładzie zwielokrotnionej operacji INSERT zastosowanej w
poniższej procedurze korzystającej z podanej struktury multi_product.

struct multi_product {
    long prodid[SIZE];
    char descrip[SIZE][LEN];
}

/** Funkcja wstawia num zamówień do tabeli PRODUCT (wersja hurtowa) */
void
sql_product_multi_ins( struct multi_product* prods, int num )
{
    EXEC SQL FOR :num
        INSERT INTO product
    VALUES ( :prods );
}

Samo porównanie złożoności z poprzednią wersją działającą
iteracyjnie jest dość wymowne.

W tym wariancie procedura ma bardzo istotne zalety:

  • Wywołuje usługę serwera bazy danych tylko raz, więc
    znacząco zmniejsza się narzut na komunikację.
  • Brak jawnych odwołań do zmiennych odpowiadających
    kolumnom, dzięki zagregowaniu uniezależnia funkcję od zmian w strukturze
    tabeli, zmniejszając koszty modyfikacji.
  • Lista argumentów i ciało funkcji ulegają skróceniu z pożytkiem
    dla czytelności kodu źródłowego.

Fraza FOR dedykowana do zbiorowych poleceń określa
rozmiar „paczki”, w tym wypadku – jak wiele rekordów ma być
wstawionych do tabeli. Można ją pominąć, a wtedy zostanie przyjęta liczba równa
rozmiarowi użytych tablic. Za każdym razem o konieczne zwielokrotnienie
przetwarzania troszczy się serwer bazy danych.

Prekompilator przekazuje parametry polecenia INSERT
tak jak pojedyncze zmienne, jedynie element struktury sterującej odpowiedzialny
za liczbę iteracji otrzymuje wartość num; oto fragment kodu po prekompilacji:

sqlstm.stmt = "insert into product values (:s1 ,:s2)";
sqlstm.iters = (unsigned int) num;

:s1 i :s2 odpowiadają wskazaniom na pola
struktury multi_product.

Jeśli w danych wstawianych tą metodą spodziewamy się wystąpienia
błędów (np. rekordów naruszających unikalność), trzeba zapewnić ich
odpowiednią obsługę, np. z kontynuacją wstawiania po błędzie. Warto zapamiętać,
że wystąpienie błędu nie wpływa na rekordy wstawione przed nim, mimo że
formalnie przekazaliśmy jedno polecenie. Serwer wykonuje je jednak iteracyjnie.
Po zakończeniu przebiegu (poprawnym lub nie), liczbę faktycznie przetworzonych
wierszy można pobrać z sqlca.sqlerrd[2], w szczególności wykorzystać
ją do wznowienia wstawiania dalszych pozycji tablic kontaktowych.

Pro*C (szerzej) stosowane

Czytelników, którzy wytrwali do końca, może zainteresować
kilka praktycznych sugestii przydatnych przy stosowaniu Pro*C na poważnie i na
większą skalę.

Plikom źródłowym Pro*C nadaje się na ogół rozszerzenie .pc
(m.in. dla odróżnienia ich na użytek przetwarzania przez make). Dobry
zwyczaj każe umieszczać w nich tylko kod bezpośrednio związany z dostępem
do bazy danych. Oprócz względów porządkowych, przemawia za tym również
narzut na tworzenie plików wynikowych. Każdy plik .pc przechodzi dwie
fazy: prekompilacji, a następnie kompilacji właściwej, z których pierwsza
jest dla fragmentów pozbawionych dyrektyw EXEC SQL… oczywistą stratą
czasu.

W praktyce dobrze zdają egzamin niewielkie funkcje, opakowujące
pojedyncze polecenia, jak podane w tekście przykłady. Wykonanie w awku czy
perlu generatora takich prefabrykowanych funkcji na podstawie skryptów CREATE
TABLE
, nie stanowi problemu i szybko się zwraca. Wcześniej jednak warto
ustalić, a potem ściśle przestrzegać, konwencji nazewnictwa i lokalizacji,
co ułatwia wyszukiwanie potrzebnych funkcji, zapobiega ich dublowaniu i ogólnie
podnosi jakość kodu. Dla przykładu – gdy nazwa funkcji zawiera skrót
wskazujący polecenie SQL i nazwę tabeli, której dotyczy, a wszystkie funkcje
związane z jedną tabelą rezydują w pliku tabela.pc, to nie traci się
czasu na poszukiwania i domysły. Dzięki odrobinie konsekwencji nasz kod w
Pro*C może być naprawdę „programmer friendly„, co w przy
pokaźnych powierzchniowo diagramach ERD z dziesiątkami tabel i wieloosobowym
zespole, nabiera przecież ogromnej wagi.

Zaznaczmy dla pełności, że opisane wyżej wybrane możliwości
Pro*C i sugestie co do ich stosowania nie wyczerpują cech i zastosowań języka.
Wiele z nich, jak choćby dynamiczne wykonywanie poleceń i obudowywanie ich
klasami w Pro*C++, wykraczałoby poza ramy tego krótkiego tekstu.

Źródła:

Programmer’s Guide to the Oracle Pro*C/C++ Precompiler,
Release 2.2
Programmer’s Guide to the Oracle Pro*C/C++ Precompiler,
Release 8.0