Rozdział 4. Interfejsy
PostgreSQL
Po zapoznaniu się z podstawowym sposobem interaktywnego dostępu do bazy PostgreSQL z
interpretera psql, który korzysta z języka SQL, możemy zająć się sprawą dostępu do bazy danych z
kodu programu. Odbywa się to bardzo podobnie i wszystkie znane polecenia używane dotychczas
w wierszu poleceń działają w kodzie niemal natychmiast.
Dostęp do PostgreSQL z kodu programu
Do baz PostgreSQL można uzyskać dostęp z wielu języków programowania. Autorzy znają
jedynie takie:
C
C++
Java
Perl
Python
PHP
Tcl
Prawdopodobnie istnieją jeszcze inne języki obsługujące PostgreSQL, ale o nich na razie nic nie
wiemy.
Istnieje także sterownik ODBC, który umożliwia dostęp z wielu innych systemów, włącznie z
klientami MS Windows, które mogą korzystać ze z
ródeł danych ODBC, takimi jak na przykład
Access.
Głównym językiem używanym w przykładach w naszej książce jest język C i z tego właśnie
języka chcemy mieć dostęp do naszej bazy danych PostgreSQL. Istnieją dwie metody uzyskiwania
dostępu do PostgreSQL z kodu programu.
 Pierwszą jest utrzymane w tradycyjnym stylu korzystanie z biblioteki o nazwie libpq. W
celu uzyskania dostępu do bazy trzeba wywoływać funkcje biblioteczne z kodu
programu.
Druga metoda polega na osadzaniu poleceń SQL w kodzie języka C i ich przetwarzaniu
przez preprocesor przed ostateczną kompilacją. Według terminologii PostgreSQL nazywa
się to ecpg. Jest to podejście bardzo podobne do użycia preprocesora języka C
przetwarzającego polecenia #include i #define przed przekazaniem kodu do
głównego kompilatora. Zagadnienie to z pewnością jest znane użytkownikom niektórych
komercyjnych produktów, takich jak PRO*C firmy Oracle i ESQLC firmy Informix,
ponieważ każdy z nich spełnia w większym lub mniejszym stopniu standard ANSI
opisujący osadzanie poleceń SQL.
W tym rozdziale pokażemy praktyczne zastosowanie obydwu metod, co ułatwi dokonanie wyboru
metody najwłaściwszej dla potrzeb użytkownika lub najwygodniejszej do pracy.
Biblioteka libpq
Ogólnie rzecz biorąc, funkcje występujące w bibliotece libpq można pogrupować na trzy
następujące kategorie:
Funkcje do zarządzanie połączeniami.
Funkcje służące do uruchamiania poleceń SQL.
Funkcje zwracające wyniki zapytań.
Omówimy te grupy po kolei. W bibliotece libpq przez lata zgromadziły się pewne przestarzałe
funkcje, które są utrzymywane tylko dla zachowania zgodności ze starymi wersjami. Nie
będziemy się tu nimi zajmować i pokażemy tylko te, które powinny być używane w nowszych
programach. Jeśli przy przeglądaniu jakiegoś starszego kodu libpq spostrzeżemy nieznane
wywołania funkcji bibliotecznych, to zawsze możemy zapoznać się z nimi w dokumentacji
dostępnej na stronie internetowej PostgreSQL pod adresem: http://www.postgresql.org.
Aby skorzystać z którejś funkcji libpq, należy:
Dołączyć plik nagłówkowy libpq-fe.h.
Dopisać katalog pgsql do ścieżki zawierającej biblioteki dołączane podczas kompilacji.
Skonsolidować program z biblioteką pq.
Końcówka  fe w nazwie libpq-fe oznacza interfejs (od słów front-end). Aby skompilować
plik używający libpq, należy użyć polecenia:
$ gcc -o program -I/usr/include/pgsql program.c -lpg
Oczywiście, szczegółowe parametry zależą od położenia katalogów instalacyjnych w danym
systemie. Jeśli znajdują się one w innym miejscu, to trzeba będzie inaczej określić położenie
katalogu zawierającego pliki dołączane i wskazać alternatywny katalog biblioteki za pomocą opcji
-L/usr/local/pgsql/lib.
Funkcje do obsługi połączeń z bazą danych
Zaleca się połączenia z bazą danych PostgreSQL za pomocą polecenia PQconnectdb. Czasem
należy użyć opcji -i, gdy ma być uruchomiony demon postmaster nasłuchujący na gniazdach
TCP/IP i domeny UNIX.
PGconn *PQconnectdb(const char *conninfo);
Napis conninfo definiujący połączenie może zawierać sekwencję parametrów i wartości
oddzielonych spacjami. Jeżeli w samej wartości ma znalez
ć się spacja, to należy ją ująć w
apostrofy. Parametry, którym nie nadaje się wartości w sposób jawny, uzyskują domyślnie
wartości NULL. Również funkcje biblioteczne uzyskują wartości domyślne lub wartości
zdefiniowane przez zmienne środowiskowe. Można definiować następujące parametry połączenia:
host
Nazwa komputera, z którym będzie zestawiane połączenie.
Domyślnie jest to komputer lokalny.
port
Numer portu, na którym będzie utrzymywane połączenie.
Domyślnie jest to standardowy port używany przez PostgreSQL o
numerze 5432.
dbname
Nazwa bazy danych, z którą chcemy się połączyć. Domyślnie jest
to taka sama nazwa jak nazwa zalogowanego użytkownika systemu
Linux.
user
Nazwa użytkownika bazy danych. Domyślnie jest to nazwa
używana przy logowaniu.
password
Hasło dostępu do bazy.
options
Wymagane opcje śledzenia.
tty
Plik lub terminal używany jako wyjście komunikatów o błędach
wytwarzanych przez program.
Za każdym parametrem następuje znak równości, a następnie wartość tego parametru. Do
połączenia się np. z bazą template1 na komputerze gw1 należy użyć następującego polecenia:
conn = PQconnectdb("host=gw1 dbname=template1");
Wskaz
nik o wartości NULL jest zwracany tylko wtedy, gdy biblioteka nie zdoła przydzielić
obiektu, z którym ma nastąpić połączenie. Nawet jeśli otrzymamy wskaz
nik nie mający wartości
NULL, to nadal trzeba sprawdzać, czy połączenie nastąpiło  musimy w tym celu wywoływać
funkcję PQstatus.
ConnStatusType PQstatus(PGconn *conn);
Funkcja ta zwraca jedną z dwóch wartości: albo CONNECTION_OK, albo CONNECTION_BAD.
Znaczenie ukryte jest w ich nazwach. Po udanym nawiązaniu połączenia z bazą pozostaje ono w
takim stanie, chyba że występują problemy z siecią lub oddalona baza danych zostanie zamknięta.
Jeśli występują problemy z połączeniem, to tekst komunikatu o błędzie można uzyskać poprzez
wywołanie funkcji:
char *PQerrorMessage(PGconn *conn);
Funkcja ta zwraca wskaz
nik do ustalonego obszaru pamięci, a więc dany tekst komunikatu może
być tekstem generowanym przy póz
niejszych wywołaniach funkcji z biblioteki libpq. Jeśli trzeba
zamknąć połączenie z powodu zakończenia pracy programu lub awarii, należy czynić to za
pomocą funkcji:
void PQfinish(PGconn *conn);
Funkcja ta musi być zawsze wywoływana, nawet gdy połączenie się nie udało. Jest to potrzebne,
ponieważ powoduje ona nie tylko zamknięcie połączenia z bazą, ale także zwolnienie obszaru
pamięci i innych zasobów związanych z połączeniem. Niewłaściwie zamknięte połączenie może
spowodować, że program będzie wymagał nadmiernego zwiększenia zasobów systemowych.
Po zamknięciu połączenia wskaz
nik obiektu połączeniowego nie oznacza niczego sensownego i
nie może być przekazywany jako parametr do jakichkolwiek funkcji. Dobrym zwyczajem
programisty powinno być nadanie mu wartości NULL tuż po wywołaniu funkcji PQfinish.
Znamy już kilka funkcji i możemy napisać pierwszy program służący do połączenia się z
serwerem PostgreSQL. Nie będzie on wprawdzie zbyt użyteczny, ponieważ służy tylko do
testowania połączenia, ale dzięki niemu uczynimy pierwszy krok. Musimy pamiętać o zmianie
nazwy serwera i użytkownika na nazwy używane lokalnie, oraz o utworzeniu bazy danych o
nazwie takiej jak nazwa użytkownika (tworzenie bazy opisaliśmy w poprzednim rozdziale).
#include
#include
#include
int main()
{
PGconn *conn;
const char *connection_str = "host=localhost dbname=template1";
conn = PQconnectedb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s, connection_str,
PQerrorMessage(conn));
} else {
printf("Connected OK\n");
 }
PQfinish(conn);
return EXIT_SUCCESS;
}
Program ten bardzo łatwo można przeanalizować: najpierw tworzony jest napis używany jako
parametr połączenia z bazą template1 na serwerze localhost, potem następuje próba
połączenia, wyświetlenie komunikatu (jeżeli wystąpi błąd) i zamknięcie połączenia przed
zakończeniem działania programu.
Uruchamianie poleceń SQL
Okazuje się niespodziewanie, że uruchamianie zapytań na serwerze jest bardzo proste. Istnieje
tylko jedna funkcja służąca do wywołania polecenia i trzy funkcje wykorzystywane do
sprawdzenia wyniku i odczytu informacji o błędach. Uruchomienie polecenia SQL odbywa się
następująco:
PGresult *PQexec(PGconn *conn, const char *sql_string);
Funkcja ta zwraca wskaz
nik o wartości NULL tylko w nadzwyczajnych okolicznościach, więc musi
on być przechwytywany, ponieważ w przeciwnym wypadku można otrzymać wyniki na podstawie
wskaz
nika innej funkcji:
ExecStatusType *PQresultStatus(PGresult *result);
Wynik zawiera wyliczenie typu ExecStatusType, które ma jedną z podanych niżej wartości:
PGRES_EMPTY_QUERY
Nic nie zostało zrobione.
PGRES_COMMAND_OK
Polecenie zostało wykonane poprawnie, lecz żadne dane nie zostały
zwrócone, ponieważ nie było to polecenie SELECT.
PGRES_TUPLES_OK
Polecenie zostało wykonane poprawnie i jakieś dane mogły być
zwrócone.
PGRES_COPY_OUT
Trwa operacja kopiowania do pliku zewnętrznego.
PGRES_COPY_IN
Trwa operacja kopiowania z pliku zewnętrznego.
PGRES_BAD_RESPONSE
Zdarzyło się coś nieoczekiwanego.
PGRES_NONFATAL_ERROR
Wystąpił błąd niekrytyczny.
PGRES_FATAL_ERROR
Wystąpił błąd krytyczny.
Zwróćmy szczególną uwagę na definicję PGRES_TUPLES_OK. Otrzymanie takiej odpowiedzi
oznacza, że polecenie SELECT zostało wykonane poprawnie, ale jednocześnie nie znaczy to, że
jakieś dane zostały zwrócone. W następnym podrozdziale zobaczymy, jak sprawdzić, czy
rzeczywiście dane zostały zwrócone. Błędy typu COPY dotyczą ładowania bazy danych lub
tworzenia kopii zapasowej tej bazy.
Jeśli chcemy otrzymać tekst komunikatu o błędzie, to używamy funkcji:
const char *PQresultErrorMessage(PGresult *result);
Zauważmy, że tekstowy komunikat o błędzie otrzymujemy tu w inny sposób niż przy funkcjach
obsługujących połączenia, gdzie stosuje się PQerrorMessage.
Często opłaca się znać liczbę wierszy, na które zadziałało polecenie SQL. Dotyczy to zwłaszcza
polecenia DELETE, ponieważ PostgreSQL traktuje jako udane rónież wykonanie poprawnego
składniowo polecenia, które faktycznie nie usunie żadnych wierszy.
Liczbę wierszy objętych poleceniami INSERT, UPDATE i DELETE można znalez
ć za pomocą
PQcmdTuples.
const char *PQcmdTuples(PGresult *result);
Zwróćmy uwagę na to, że takie wywołanie zwraca char *result, czyli ciąg cyfr kończący się
wartością NULL w formacie znakowym, a nie liczbę całkowitą, której można się spodziewać.
Wyznaczenie liczby wierszy objętych przez polecenie SELECT jest bardziej złożone i omówimy je
póz
niej.
Zakończymy teraz omawianie kodu wynikowego i zajmiemy się przekazaniem do biblioteki
informacji o konieczności zwolnienia przydzielonej pamięci. Podobnie jak w kodzie dotyczącym
połączeń, błąd popełniony tutaj spowoduje  wycieki pamięci (ang. memory leaks) w naszej
aplikacji.
void PQclear(PQresult *result);
Inną funkcją, która pasuje do tego podrozdziału i która może być przydatna przy wyszukiwaniu
błędów, jest:
const char *PQresStatus(ExecStatusType status);
Przekształca ona numer statusu na komunikat tekstowy.
Wiemy już teraz wystarczająco dużo, aby napisać nasz pierwszy program w języku C, który
będzie uruchamiał polecenia SQL. Ponieważ nie mówiliśmy jeszcze o tym, jak uzyskiwać wyniki
zapytań, więc na razie zajmiemy się uruchomianiem polecenia DELETE. Oto pierwsza funkcja o
nazwie del1.c wykonująca coś użytecznego i rozszerzająca pierwotną funkcję con1.c.
Będziemy łączyć się z bazą danych rick na serwerze gw1.
W tym rozdziale opiszemy operacje na pojedynczej tabeli o nazwie children w bazie danych,
którą utworzyliśmy w poprzednim rozdziale. Tabelę tę można w razie potrzeby utworzyć
ponownie za pomocą polecenia SQL użytego w interpreterze psql:
CREATE TABLE children (
childno SERIAL,
fname VARCHAR,
 age INTEGER
);
W podanym niżej kodzie zaznaczone są różnice między con1.c i del1.c:
#include
#include
#include
int main()
{
PGconn *conn;
PGresult *result;
const char *connection_str = "host=gw1 dbname=rick";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str,
PQerrorMessage(conn));
} else {
printf("Connected OK\n");
}
result = PQexec(conn, "DELETE FROM children WHERE fname = "'freda'");
if (!result) {
printf("PQexec command failed, no error code\n");
} else {
switch (PQresultStatus(result)) {
case PGRES_COMMAND_OK:
printf("Command executed OK, %s rows
affected\n", PQcmdTuples(result));
break;
case PGRES_TUPLES_OK:
printf("Query may have returned data\n");
break;
default:
 printf("Command failed with code %s, error message %s\n',
PQresStatus(PQresultStataus(result)),
PQresultErrorMessage(result));
break;
}
PQclear(result);
}
PQfinish(conn);
return EXIT_SUCCESS;
}
Jeśli upewnimy się, że w tabeli children znajduje się wiersz zawierający w polu fname nazwę
freda, to po kompilacji i uruchomieniu tego programu zobaczymy:
[rick@gw1 psql]$ ./del1
Connected OK
Command executed OK, 1 rows affected
Teraz nie ma już wiersza, który pasowałby do tych kryteriów i mógłby być usunięty. Jeśli więc
uruchomimy program ponownie, to nadal jest on wykonywany poprawnie, ale nie dotyczy
żadnego wiersza:
[rick@gw1 psql]$ ./del1
Connected OK
Command executed OK, 0 rows affected
Należy dokładnie odróżniać polecenia działające i nie wpływające na wiersze tabeli od poleceń,
które nie udają się z powodu błędów składniowych.
Uzyskiwanie wyników zapytań
Przejdziemy teraz do najczęściej używanej, ale także najbardziej złożonej części biblioteki libpq,
czyli do pobierania danych.
Podczas pobierania danych z bazy może się pojawić wiele problemów. Przede wszystkim nie
będziemy wiedzieli z góry, ile wierszy zostanie pobranych. Jeśli uruchamiamy polecenie SELECT,
używając '*' jako nazwy pola (chcąc pobrać wszystkie pola), to możemy nie wiedzieć nawet, ile
pól lub jakiego rodzaju dane znajdują się w pobieranych wierszach. Przewidywanie tych
okoliczności czyni tę część API bardziej złożoną. Nie trzeba jednak popadać w panikę, po prostu
musimy poznać kilka dodatkowych wywołań API.
Najpierw przekształcimy program testowy del1.c na zapytanie zwracające dane i, korzystając z
okazji, nieco go zmodyfikujemy, by łatwiej było dodawać nowe funkcje po wykonaniu polecenia
SQL. Niech nowy plik nazywa się sel1.c:
#include
#include
#include
PGconn *conn = NULL;
void tidyup_and_exit();
int main()
{
PGresult *result;
const char *connection_str = "host=gw1 dbname=rick";
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to %s failed, %s", connection_str,
PQerrorMessage(conn));
tidyup_and_exit();
} else {
printf("Connected OK\n");
}
result = PQexec(conn, "SELECT age, fname FROM children WHERE age < '6'");
if (!result) {
printf("PQexec command failed, no error code\n");
tidyup_and_exit();
} else {
switch (PQresultStatus(result)) {
case PGRES_COMMAND_OK:
printf("Command executed OK, %s rows affected\n",
PQsmdTuples(result));
break;
case PGRES_TUPLES_OK:
printf("Query may have returned data\n");
break;
default:
 printf("Command failed with code %s, error message %s\n",
PQresStatus(PQresultStatus(result)),
PQresultErrorMessage(result));
PQclear(result);
tidyup_and_exit();
break;
}
}
/* Tutaj dodany jest nowy kod */
if (result) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}
void tidyup_and_exit() {
if (conn != NULL) PQfinish(conn);
exit(EXIT_FAILURE);
}
Dodaliśmy tu nową funkcję o nazwie tidyup_and_exit, która pozwala na wyjście z programu
wtedy, gdy nie uda się operacja w bazie danych. Oczywiście, przerywanie działania aplikacji z
powodu jednego nieudanego polecenia SQL jest w kodzie produkcyjnym zbyt rygorystyczne, ale
dla celów testowych łatwiej pracować z kodem działającym w taki sposób. Zmieniliśmy także
polecenie DELETE na polecenie SELECT, które będzie zwracać jakieś dane.
Po uruchomieniu nowej wersji programu można się przekonać, czy kod prawidłowo rozpoznaje
możliwość zwracania danych:
[rick@gw1 psql]$ ./sel1
Connected OK
Query may have returned data
Teraz znajdziemy sposób określenia liczby faktycznie zwracanych wierszy. Możemy to zrobić za
pomocą wywołania Pqntuples (pamiętając, że w PostgreSQL wiersze to  krotki ):
int PQntuples(PGresult);
Przy zmianie sel1.c na sel2.c trzeba po prostu zmienić jeden wiersz programu, w którym
sprawdzany jest kod zwracany przez PQresultStatus:
case PGRES_TUPLES_OK:
printf("Query was OK and returned %d rows\n", PQntuples(result));
break;
Po ponownym uruchomieniu zapytania otrzymamy następujący wynik:
[rick@gw1 psql]$ ./sel2
Connected OK
Query was OK and returned 3 rows
Wygląda to obiecująco, ale potrzebujemy przecież dostępu do pobranych danych. Od tego
momentu zajmiemy się więc najszybszym i najprostszym sposobem osiągnięcia tego celu. Sposób
ten polega na użyciu jednej ze specjalnych funkcji z biblioteki psql przekazującej wszystkie dane
w postaci strumienia do pliku. Funkcja ta jest łatwa w użyciu i stanowi dużą pomoc przy
wyszukiwaniu błędów.
Funkcja nazywa się PQprint, a jej wywołanie ma postać:
void PQprint(FILE *stream, PGresult, PQprintOpt *options);
Korzystając z niej, musimy określić strumień wyjściowy, wskaz
nik wynikowy zwracany przez
polecenie SQL oraz wskaz
nik do struktury określającej opcje.
Struktura określająca opcje zdefiniowana w pliku nagłówkowym libpq-fe ma następującą
postać:
typedef struct_PQprintOpt
{
pqbool header; /* druk nagłówków pól i zliczenia wierszy */
pqbool align; /* wyrównywanie pól */
pqbool standard; /* przestarzały format */
pqbool html3; /* wyjście w postaci tabel html */
pqbool expanded; /* rozszerzanie tabel */
pqbool pager; /* podział wyjścia na strony, jeśli konieczny */
char *fieldSep; /* separator pól */
char *tableOpt; /* wstaw do HTML: */
char *caption; /* tytuł HTML:

*/
char ** fieldName; /* tablica zamienników nazw pól, ogr. przez NULL */
} PQprintOpt;
Opcje te umożliwiają zachowanie pewnej kontroli nad danymi wyjściowymi. Można zauważyć, że
w pliku nagłówkowym istnieją także inne funkcje służące do zapisu danych do strumieni, ale
zastępuje je funkcja PQprint.
Na początku programu musimy zdefiniować dwie nowe zmienne:
FILE *output_stream;
PQprintOpt print_options;
Po pobraniu danych można je wyświetlić:
output_stream = fopen("/dev/tty", "w");
if (output_stream == NULL) {
PQclear(result);
tidyup_and_exit();
}
memset(&print_options, '\0, sizeof(print_options));
print_options.header = 1; /* druk nagłówków */
print_options.align = 1; /* wyrównane pola */
print_options.html3 = 0; /* wyjście jako tabele html */
print_options.fieldSep = "|"; /* separator pola */
print_options.fieldName = NULL; /* alternatywne nazwy pól */
PQprint(output_stream, result, &print_options);
Zwróćmy uwagę na to, że nie zostały wyraz
nie podane wszystkie pola w strukturze PQprintOpt,
a zapotrzebowanie na pamięć wymaganą przez wartości domyślne zostało określone za pomocą
memset. Należy tu jednak pamiętać, że ważne jest określenie separatora pól fieldSep.
Po uruchomieniu programu w tej wersji otrzymamy:
[rick@gw1 psql]$ ./sel3
Connected OK
Query was OK and returned 3 rows
age|fname
---+------
5|Adrian
4|Allen
1|Fred
(3 rows)
Jest to pierwszy fragment naszego osadzonego kodu SQL, który pobiera dane.
Niestety, jest tu kilka problemów. Po pierwsze, kierowanie danych do strumienia spełnia bardzo
dobrze swoją rolę przy wyszukiwaniu błędów, ale na pewno nie jest rozwiązaniem, które można
zastosować przy faktycznym przetwarzaniu danych. Po drugie, wszystkie dane są pobierane na
raz, co może być dobre, jeśli nie ma ich zbyt wiele, ale stanie się nieznośne przy dużej ilości
danych.
Kursory
Załóżmy, że nasz program korzysta z wielkiej bazy danych zawierającej tysiące wpisów i że
uruchamiamy zapytanie zwracające wszystkie wpisy. Program zażąda wówczas przydzielenia
bardzo dużego obszaru pamięci na przechowanie wyników. Ponieważ dostęp do bazy może z
dużym prawdopodobieństwem odbywać się z sieci, to potrzebny jest sposób pobierania danych w
niewielkich porcjach, np. po jednym wierszu. Istnieje standardowa metoda realizacji takiego
zadania stosowana przy pobieraniu danych z bazy SQL do programu w języku C (a także do
programów napisanych w innych językach). Mamy tu na myśli tzw. kursory.
Z kursorem nie zetknęliśmy się jeszcze w tej książce, ponieważ zwykle jest on używany w
poleceniach SQL osadzonych w zewnętrznych programach lub w funkcjach języka
proceduralnego, które są przechowywane w bazie danych (nazywane są często  procedurami
zachowanymi ). W zasadzie kursory nie bywają używane w wierszu poleceń. Standard SQL92
określa, że kursory mają być stosowane w osadzonych programach i w większości baz danych są
to jedyne miejsca, gdzie można ich użyć. PostgreSQL, rozszerzając standard SQL, pozwala na
użycie kursorów także w wierszu poleceń.
Kursor (cursor) oznacza sposób przemieszczania się wewnątrz zestawu wyników polegający na
pobieraniu danych małymi porcjami. Aby użyć kursora, należy najpierw go zadeklarować
(DECLARE), nadając mu nazwę powiązaną z poleceniem SELECT. Następnie, używając polecenia
FETCH, można pobierać dane zazwyczaj wiersz po wierszu, chociaż można je także pobierać w
porcjach obejmujących wiele wierszy.
Standard SQL92 (podobnie jak wiele innych) wymaga użycia dodatkowego polecenia OPEN, które
służy do otwarcia kursora o podanej nazwie i które musi być umieszczone między poleceniami
DECLARE i FETCH. Biblioteka libpq z bazy PostgreSQL nie wymaga takiej operacji, ponieważ
polecenie DECLARE wywołuje je automatycznie. Przy okazji omawiania alternatywnego sposobu
osadzania kodu SQL w programie w języku C, czyli tzw. ecpg, pokażemy konieczność stosowania
polecenia OPEN CURSOR.
Korzystając z pseudokodu możemy zapisać algorytm korzystania z kursora:
BEGIN A TRANSACTION
DECLARE CURSOR mycursor FOR SELECT-statement
[OPEN mycursor]
DO {
FETCH some data from mycursor
Process the row(s) retrived
} WHILE the FETCH command found data
CLOSE mycursor
COMMIT WORK
Składnia deklaracji kursora jest bardzo prosta:
DECLARE nazwa_kursora [BINARY] CURSOR FOR
Taka deklaracja tworzy kursor o podanej nazwie i automatycznie otwiera go, jeśli korzystamy z
biblioteki libpq. Zauważmy, że kursor jest związany z pojedynczym poleceniem SELECT. Nazwa
kursora może być od tego momentu używana w odwołaniach do tego polecenia. Opcja BINARY
jest wymagana tylko wówczas, gdy chcemy pobierać dane binarne przechowywane w jakiejś
kolumnie tabeli  jest to zaawansowane i rzadko spotykane zagadnienie, a więc nie będziemy się
nim tutaj zajmować. Eksperci znający standard SQL92 zauważyli zapewne brak niektórych słów
kluczowych SQL, a mianowicie SCROLL i FOR READ ONLY lub FOR UPDATE. W bazie
PostgreSQL wszystkie kursory mogą być przemieszczane, a więc słowo kluczowe SCROLL jest
ignorowane, chociaż można go tu użyć. PostgreSQL pozwala na zastosowanie kursorów tylko
przy odczycie danych z bazy, a nie przy ich modyfikacji w bazie, a zatem słowo FOR jest zbędne
(można jednak użyć FOR READ ONLY dla zachowania zgodności ze standardem).
Składnia polecenia FETCH jest również bardzo prosta:
FETCH [FORWARD|BACKWARD] [liczba|ALL|NEXT] [IN nazwa_kursora];
Zazwyczaj słowo FORWARD lub BACKWARD jest pomijane, a domyślnym słowem kluczowym jest
FORWARD. Można pobierać (FETCH) określoną liczbę wierszy lub wszystkie wiersze (ALL). Słowo
kluczowe NEXT oznacza to samo, co liczba 1. Polecenie SELECT podane w deklaracji kursora
określa faktyczny rodzaj i zawartość pobieranych wierszy.
Po zapoznaniu się z tymi zasadami musimy wypróbować je w praktyce. W podanym niżej
programie sel4.c zastosowano kursor do pobierania danych z bazy:
#include
#include
#include
#include
PGconn *conn = NULL;
void tidyup_and_exit();
int execute_one_statement(const char *stmt_to_exec, PGresult **result);
int main ()
{
PGresult *result;
int stmt_ok;
const char *connection_str = "host=gw1 dbname=rick";
 FILE *output_stream;
PQprintOpt print_options;
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprint(stderr, "Connection to %s failed, %s", connection_str,
PQerrorMessage(conn));
tidyup_and_exit();
} else {
printf("Connected OK\n");
}
stmt_ok = execute_one_statement ("BEGIN WORK", &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("DECLARE age_fname_cursor CURSOR FOR
SELECT age, fname FROM children
WHERE age < '6'", &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement ("FETCH ALL IN age_fname_cursor",
&result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("COMMIT WORK", &result);
}
}
}
if (stmt_ok) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}
int execute_one_statement(const char *stmt_to_exec, PGresult **res_ptr) {
int retcode = 1;
 const char *str_res;
PGresult *local_result;
printf("About to execute %s\n", stmt_to_exec);
local_result = PQexec(conn, stmt_to_exec);
*res_ptr = local_result;
if (!local_result) {
printf("PQexec command failed, no error code\n");
retcode = 0;
} else {
switch (PQresultStatus(local_result)) {
case PGRES_COMMAND_OK:
str_res = PQcmdTuples(local_result);
if (strlen(str_res) > 0) {
printf("Command executed OK, %s rows affected\n", str_res);
} else {
printf("Command executed OK, no rows affected\n");
}
break;
case PGRES_TUPLES_OK:
printf("Select executed OK, %d rows found\n",
PQntuples(local_result));
break;
default:
printf("Command failed with code %s, error message %s\n",
PQresStatus(PQresultStatus(local_result)),
PQresultErrorMessage(local_result));
PQclear(local_result);
retcode = 0;
break;
}
}
return retcode;
} /* execute_one_statement */
 void tidyup_and_exit() {
if (conn != NULL) PQfinish(conn);
exit(EXIT_FAILURE);
}
Ważniejsze zmiany w stosunku do poprzedniej wersji kodu zostały wyróżnione w powyższym
tekście. Usunięto również fragmenty dotyczące wyświetlania wyników. Wkrótce pokażemy
bardziej użyteczny sposób dostępu do pobranych danych.
Po uruchomieniu tego programu otrzymamy następujące komunikaty:
Connected OK
About to execute BEGIN WORK
Command executed OK, no rows affected
About to execute DECLARE age_fname_cursor CURSOR FOR SELECT age, fname FROM
children WHERE age < '6'
Command executed OK, no rows affected
About to execute FETCH ALL IN age_fname_cursor
Select executed OK, 3 rows found
About to execute COMMIT WORK
Command executed OK, no rows affected
Pobieranie danych w pojedynczych wierszach jest teraz bardzo łatwe. Wystarczy zmienić słowo
kluczowe ALL na 1 w poleceniu FETCH i sprawdzić, czy rzeczywiście zwracane są pojedyncze
wiersze. Polecenie FETCH, podobnie jak SELECT, może się udać bez zwracania żadnych danych.
Zmienionymi wierszami w sel5.c są:
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
fprint(stderr, "Connection to %s failed, %s, connection_str,
tidyup_and_exit();
} else {
printf("Connected OK\n");
}
stmt_ok = execute_one_statement("BEGIN WORK", &result);
if (stmt)ok) {
PQclear(result);
stmt_ok = execute_one_statement("DECLARE age_fname_cursor CURSOR FOR
SELECT
age, fbname FROM children WHERE age < '6'", &result);
 stmt_ok = execute_one_statement("FETCH 1 IN age_fname_cursor", &result);
while(stmt_ok && PQntuples(result) > 0) {
PQclear(result);
stmt_ok = execute_one_statement("FETCH NEXT IN age_fname_cursor"'
&result);
}
stmt_ok = execute_one_statement("COMMIT WORK", &result);
}
if (stmt_ok) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}
Na wyjściu programu otrzymamy:
[rick@gw1 psql]$ ./sel5
Connected OK
About to execute BEGIN WORK
Command executed OK, no rows affected
About to execute DECLARE age_fname_cursor CURSOR FOR SELECT age, fname FROM
children WHERE age < '6'
Command executed OK, no rows affected
About to execute FETCH 1 IN age_fname_cursor
Select executed OK, 1 rows found
About to execute FETCH NEXT IN age_fname_cursor
Select executed OK, 1 rows found
About to execute FETCH NEXT IN age_fname_cursor
Select executed OK, 1 rows found
About to executed FETCH NEXT IN age_fname_cursor
Select executed OK, 0 rows found
About to execute COMMIT WORK
Command executed OK, no rows affected
Jak widać, bardzo łatwo pobiera się dane w postaci pojedynczych wierszy. Jedyną wadą takiego
sposobu, która zazwyczaj nie ma większego znaczenia, jest nieznajomość liczby pobieranych
danych aż do chwili zakończenia całej operacji. Dzieje się tak, ponieważ PQntuples(result)
ma wartość 1 przy pobieraniu jednego wiersza za pomocą polecenia FETCH, czego należało się
przecież spodziewać.
Ponieważ mamy już ułatwiony dostęp do danych, to teraz zajmiemy się dostępem do
poszczególnych informacji.
Pobieranie informacji o kolumnach
Pierwszą użyteczną porcją informacji pobraną z bazy danych może być informacja o kolumnach
tabeli (czyli o ich nazwach i typach przechowywanych w nich danych). Zadanie to można
wykonać w prosty sposób za pomocą trzech funkcji: jedna z nich podaje liczbę kolumn, druga
podaje nazwę każdej kolumny, a trzecia rozmiar danych w każdej kolumnie. Można oczywiście
podać nazwę każdej żądanej kolumny, ale w teorii znany jest przecież z góry typ zawartych w nich
danych.
Dobrym zwyczajem jest podawanie nazwy każdej pobieranej kolumny. Dzięki temu nie trzeba
obawiać się niespodzianek po dodaniu nowej kolumny do tabeli w bazie danych. Oprócz tego,
jeżeli jakaś kolumna zostanie usunięta, to za pomocą programu grep można łatwo wykryć te
miejsca w kodzie, w których występuje nazwa usuniętej kolumny. Przy założeniu, że typ kolumny
w kodzie nie jest dokładnie określony, można automatycznie dostosować się do jego zmian,
określając go dopiero po uruchomieniu programu. Niestety, wymaga to dopisania większych
fragmentów kodu i stwarza ryzyko popełnienia większej liczby błędów oraz zmniejsza nieco
wydajność kodu.
Liczbę kolumn zwraca funkcja PQnfields:
int PQnfields(PGresult *result);
Nazwę kolumny można określić za pomocą funkcji PQfname, podając w jej wywołaniu indeks
kolumny (pierwsza kolumna ma indeks 0):
char *PQfname(PGresult *result, int field_index);
Pewne pojęcie o rozmiarze danych w kolumnie można uzyskać za pomocą funkcji PQfsize.
Użyliśmy tu celowo słowa  pojęcie , ponieważ funkcja ta zwraca rozmiar obszaru pamięci
użytego wewnętrznie przez PostgreSQL i np. dla pól o zmiennej długości, takich jak VARCHAR,
wynosi on -1.
int PQfsize(PGresult *result, int field_index);
Oczywistym brakiem w zestawie tych trzech funkcji jest to, że nie jest podawany typ kolumny.
Niestety, funkcja PQftype, która ma to robić, zwraca typ Oid (czyli definicję typedef dla liczby
całkowitej bez znaku). Jest więc to informacja o wewnętrznej reprezentacji typu, która nigdzie nie
jest udokumentowana i dlatego jest prawie bezużyteczna. Obecnie nie będziemy więc używać tej
funkcji, mając nadzieję, że w kolejnych wydaniach bazy PostgreSQL lub biblioteki libpq zostanie
uwzględniona jakaś funkcja zwracająca typ danych w kolumnie.
Możemy teraz wykorzystać zdobytą wiedzę, zmodyfikować nasz program cel5.c i nadać mu
nazwę sel6.c. Nie ma znaczenia, który wiersz pobranych danych zostanie użyty do uzyskania
informacji o nagłówku kolumny, ponieważ nawet gdy polecenie SELECT nie zwróci żadnych
wierszy, to informacja o kolumnach nadal będzie dostępna.
Zmiany w kodzie są niewielkie, a więc pokazujemy tu tylko dopisane fragmenty, nie pokazując
całego programu.
Najpierw dodamy prototyp naszej nowej funkcji:
void show_column_info(PGresult *result);
Następnie wywołamy ją przy pobieraniu danych z bazy. Aby pokazać, że wszystko działa jak
należy, wywołanie będzie odbywać się za każdym razem, gdy zwracane są dane. Oczywiście, w
kodzie produkcyjnym nie stosuje się takiego rozwiązania.
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("FETCH 1 IN age_fname_cursor"'
&result);
if (stmt_ok) show_column_info(result);
while(stmt_ok && PQntuples(result) > 0) {
show_column_info(result);
PQclear(result);
stmt_ok = execute_one_statement("FETCH NEXT IN age_fname_cursor",
&result);
}
stmt_ok = execute_one_statement("COMMIT WORK", &result);
}
Mamy więc ostateczną postać funkcji show_column_info:
void show_column_info(PGresult *result) {
int num_columns;
int i;
if (!result) return;
num_columns = PQnfields(result);
printf("%d columns in the result set\n", num_columns);
for(i = 0; i < num_columns; i++) {
printf("Field %d, Name %s, Internal size %d\n",
i,
PQfname(result, i),
PQfsize(result, i));
}
} /* show_column_info */
Po uruchomieniu tego programu otrzymujemy następujący wynik:
About to execute FETCH NEXT IN age_fname_cursor
Select executed OK, 1 rows found
2 columns in the result set
Field 0, Name age, Internal size 4
Field 1, name fname, Internal size -1
Z powodu braku miejsca nie pokazujemy tu pełnego wyniku, lecz tylko istotny dla nas fragment.
Należy zwrócić uwagę na to, że rozmiar fname jest podawany jako -1, ponieważ jest to pole o
zmiennym rozmiarze (VARCHAR).
Dostęp do przetworzonych danych
Oczywiste jest, że musimy uzyskać dostęp do pobranych danych. Jak już wspominaliśmy
wcześniej, w żaden sensowny sposób nie można poznać typu pobranych danych, a zatem
Czytelnik może mieć wątpliwości, w jaki sposób mamy zamiar zapisać to w kodzie programu.
Odpowiedz
jest bardzo prosta: funkcje z biblioteki libpq zawsze zwracają wartości jako napisy,
które można przekształcać we własnym zakresie (w rzeczywistości nie jest to do końca
prawdziwe, ponieważ po użyciu słowa kluczowego BINARY w deklaracji kursora dane będą
zwracane w postaci binarnej  z tej zaawansowanej właściwości bazy PostgreSQL korzysta
jednak niewielka liczba użytkowników).
Musimy więc określić rozmiar obszaru pamięci, który będzie zajęty przez pobrane dane. Zadanie
to realizuje funkcja PQgetlength:
int PQgetlength(PGresult *result, int tuple_number, int field_index);
Zauważmy, że występuje tu argument tuple_number, co, jak pamiętamy, oznacza wiersz w
terminologii używanej przez autorów PostgreSQL. Jest on użyteczny wówczas, gdy nie
korzystamy z kursora i pobieramy wszystkie dane podczas jednej operacji albo żądamy pobrania
jednorazowo więcej niż jednego wiersza (tak jak w ostatnim przykładzie). Bez tego argumentu
pobieranie kilku wierszy na raz byłoby bezsensowne, ponieważ mielibyśmy dostęp tylko do
danych z wiersza pobranego na samym końcu.
Napis reprezentujący dane otrzymujemy za pomocą funkcji PQgetvalue:
char *PQgetvalue(PGresult *result, int tuple_number, int field_index);
Napis ten kończy się wartością NULL. Faktyczna treść napisu jest umieszczona w strukturze
PQresult, więc jeśli chcemy ją udostępnić, to należy skopiować ją stamtąd przed wykonaniem
jakichś operacji na tej strukturze. W tym momencie bardziej wnikliwi czytelnicy mogą zauważyć
przeszkodę  w jakiż to sposób można rozróżnić pusty napis zwracany dlatego, że napis w bazie
ma zerową długość, od napisu zwracanego wówczas, gdy pole w bazie ma wartość NULL (co
oznacza, że wartość jest nieznana, a nie że pole jest puste)? Odpowiedz
na to pytanie daje
specjalna funkcja PQgetisnull, która rozróżnia te dwie wartości w bazie danych:
int PQgetisnull(PGresult *result, int tuple_number, int field_index);
Funkcja ta zwraca 1, jeżeli pole w bazie danych ma wartość NULL, w przeciwnym wypadku
zwraca 0.
Nareszcie dotarliśmy do takiego miejsca, w którym można utworzyć ostateczną wersję naszego
testowego programu, który zwróci dane pobrane z bazy wiersz po wierszu oraz wyświetli
informacje o kolumnach i pokaże zawarte w nich dane. Ponieważ chcemy ten program
uruchamiać, to ustawimy dodatkowo w jednym z pobieranych wierszy wartość NULL, co posłuży
do sprawdzenia poprawności jej wykrywania. W zależności od danych wprowadzonych do tabeli
children można do tego celu użyć różnego identyfikatora childno. Załóżmy np. że dla
childno równego 9 mamy jako age wpisane 1, zaś polu fname nadamy wartość NULL za pomocą
polecenia psql:
UPDATE children set fname = NULL where childno = 9;
Mamy więc ostateczną wersję programu w języku C korzystającego z polecenia SELECT.
Nazwaliśmy go sel7.c, zaznaczając główne zmiany w stosunku do wersji poprzedniej i usuwając
niektóre wiersze pomocnicze służące do wyszukiwania błędów:
#include
#include
#include
#include
PGconn *conn = NULL;
void tidyup_and_exit();
int execute_one_statement(const char *stmt_to_exec, PGresult **result);
void show_column_info(PGresult *result);
void show_one_row_data (PGresult *result);
int main()
{
PGresult *result;
int stmt_ok;
char *connection_str = "host=gw1 dbname=rick";
FILE *output_stream;
PQprintOpt print_options;
conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
 fprint(stderr, "Connection to %s failed, %s", connection_str,
PQerrorMessage(conn));
tidyup_and_exit();
} else {
printf("Connected OK\n");
}
stmt_ok = execute_one_statement("BEGIN WORK", &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("DECLARE age_fname_cursor CURSOR FOR
SELECT age, fname FROM children WHERE
age <  6 ", &result);
if (stmt_ok) {
PQclear(result);
stmt_ok = execute_one_statement("FETCH 1 IN age_fname_cursor",
&result);
if (stmt_ok) show_column_info(result);
while(stmt_ok && PQntuples(result) > 0) {
show_one_row_data(result);
PQclear(result);
stmt_ok = execute_one_statement("FETCH NEXT IN age_fname_cursor",
&result);
}
stmt_ok = execute_one_statement("COMMIT WORK", & result);
}
}
if (stmt_ok) PQclear(result);
PQfinish(conn);
return EXIT_SUCCESS;
}
int execute_one_statement(const char *stmt_to_exec, PGresult **res_ptr) {
int retcode = 1;
const char *str_res;
PGresult *local_result;
 printf("About to execute %s\n", stmt_to_exec);
local_result = PQexec(conn, stmt_to_exec);
*res_ptr = local_result;
if (!local_result) {
printf("PQexec command failed, no error code\n");
retcode = 0;
} else {
switch (PQresultStatus(local_result)) {
case PGRES_COMMAND_OK:
str_res = PQcmmdTuples(local_result);
if (strlen(str_res) > 0) {
printf("Command executed OK, %s rows affected\n", str_res);
} else {
printf("Command executed OK, no rows affected\n");
}
break;
case PGRES_TUPLES_OK:
printf("Select executed OK, %d rows found\n",
PQntuples(local_result));
break;
default:
printf("Command failed with code %s, error message %s\n",
PQresStatus(PQresultStatus(local_result)),
PQresultErrorMessage(local_result));
PQclear(local_result);
retcode = 0;
break;
}
}
return retcode;
} /* execute_one_statement */
void show_columns_info(PGresult *result) {
int num_columns = 0;
int i;
 if (!result) return;
num_columns = PQnfields(result);
printf("%d columns in the result set\n", num_columns);
for(i = 0; i < num_columns; i++) {
printf("Field %d, Name %s, Internal size %d\n",
i,
PQfname(result, i),
PQfsize(result, i));
}
} /* show_column_info */
void show_one_row_data(PGresult *result) {
int col;
for(col = 0; col < PQnfields(result); col++) {
printf("DATA: %s\n", PQgetisnull(result, 0, col) ? "":
PQgetvalue(result, 0, col));
}
} /* show_one_row_data */
void tidyup_and_exit() {
if (conn != NULL) PQfinish(conn);
exit(EXIT_FAILURE);
}
Zauważmy, że są tu sprawdzane wartości NULL we wszystkich kolumnach. Po uruchomieniu tego
programu otrzymamy:
Connected OK
2 columns in the result set
Field 0, Name age, Internal size 4
Field 1, Name fname, Internal size -1
DATA: 4
DATA: Adrian
DATA: 4
DATA: Allen
DATA: 1
DATA:
Na tym zakończymy prezentację biblioteki libpq. Opisaliśmy tutaj sposób korzystania z tej
biblioteki przy dostępie do danych za pomocą kursorów. Pokazaliśmy również, jak można uzyskać
informacje o kolumnach i przetwarzać wartości NULL z bazy danych.
Biblioteka ecpg
Nadeszła pora na zapoznanie się z alternatywnym sposobem połączenia języków SQL i C,
polegającym na wstawianiu poleceń SQL do kodu programu w języku C i użyciu preprocesora
przed wywołaniem kompilatora tego języka. Istnieje oczywiście biblioteka wykorzystywana jako
interfejs między językiem C i bazą danych, ale jej funkcje są ukrywane przez preprocesor.
Biblioteka ecpg rozpowszechniana w pakiecie PostgreSQL spełnia wymagania ANSI dotyczące
osadzania poleceń SQL w kodzie C, więc to, co napisano niżej, będzie zapewne znane
programistom korzystającym z pakietów PRO*C firmy Oracle lub ESQL-C firmy Informix.
Podczas pisania tej książki biblioteka ecpg nie obsługiwała jeszcze niektórych rzadziej używanych
właściwości  osadzonego SQL , zaś standardowa dokumentacja biblioteki dostarczana z
PostgreSQL była dość uboga.
Ponieważ zapoznaliśmy się już z podstawami SQL, to ten podrozdział będzie dość krótki.
Preprocesor ecpg rozpoznaje w programie fragmenty kodu, które ma przetwarzać, na podstawie
sekwencji  exec sql umieszczonej na początku wiersza. Za tym oznaczeniem następuje
właściwe polecenie SQL zakończone średnikiem. W zależności od składni przetwarzany fragment
może obejmować jeden wiersz lub cały blok.
Jeżeli chcemy napisać prosty program w języku C, który będzie wywoływał polecenie UPDATE, to
osadzamy je po prostu w kodzie z
ródłowym.
Czy może być coś prostszego? Utwórzmy więc taki program. Zgodnie z tradycją  nazwy plików
programów zawierających osadzone polecenia SQL mają końcówkę pgc, zatem nasz program
nazwiemy upd1.pgc:
#include
exec sql include sqlca;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
exec sql UPDATE children SET fname = 'Gavin' WHERE childno = 9;
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}
Na pierwszy rzut oka trudno doszukać się w tym programu w języku C. Jeżeli jednak pominiemy
wiersze rozpoczynające się od exec sql, to zobaczymy strukturę najprostszego programu w tym
języku zawierającego tylko funkcję main(). Kompilacja powyższego programu przebiega
dwuetapowo. Najpierw jest uruchamiany preprocesor ecpg, a następnie wynik jest przetwarzany
przez kompilator języka C i konsolidowany z biblioteką ecpg. Aby kompilacja się udała, należy
także dodać w opcji -I odpowiednią ścieżkę do dołączanego pliku nagłówkowego ecpg, aby
kompilator mógł go znalez
ć. Polecenia służące do kompilacji powyższego programu są więc
następujące:
$ ecpg -t -I/usr/include/pgsql upd1.pgc
$ gcc -o upd1 -I/usr/include/pgsql upd1.c -lecpg -lpg
Preprocesor ecpg przetwarza najpierw plik z
ródłowy, dając w wyniku plik .c, który następnie jest
normalnie kompilowany i konsolidowany z dwiema bibliotekami PostgreSQL. Opcja -t w
wierszu poleceń wywołującym ecpg oznacza, że chcemy zarządzać własnymi transakcjami,
podając bezpośrednio polecenia BEGIN WORK i COMMIT WORK w kodzie programu. Domyślnie
ecpg uruchamia transakcję w momencie połączenia się z bazą, co nie jest szkodliwe, lecz autor
woli definiować ją sam.
Zwróćmy uwagę na parametr połączenia rick@gw1. Oznacza on żądanie połączenia się z bazą
danych rick na serwerze gw1. Nie jest tu wymagane żadne hasło, ponieważ jest to komputer
lokalny, na którym łączący się użytkownik jest już zalogowany. Ogólnie mówiąc, należy jednak
podawać pełne dane w stylu URL:
:://:/ as
as using
Konkretny przykład wyjaśni to lepiej. Załóżmy, że używając protokołu tcp chcemy się połączyć z
usługą postgresql udostępnianą przez port 5432 na komputerze dbs6. Połączenie do bazy
danych rick wywołuje użytkownik neil posługujący się hasłem secret. W takim przypadku do
naszego programu należałoby wstawić następujący wiersz:
exec sql connect to tcp:postgresql://dbs6:5432/rick as connect_2 user neil
using
secret;
Jeżeli chcemy rozdzielić poszczególne elementy definiujące połączenie, to możemy posłużyć się
tzw. zmiennymi systemowymi. Jak wiadomo, odwołania do tych zmiennych są poprzedzane
dwukropkiem. Zajmiemy się nimi w dalszych częściach tego rozdziału, zaś tutaj będziemy je
traktowali jako zwykłe zmienne języka C.
exec sql BEGIN DECLARE SECTION;
char connect_str[256];
char as_str[25];
char user_str[25];
char using_str[25];
exec sql END DECLARE SECTION;
strcpy(connect_str, "tcp:postgresql://localhost:5432/rick");
strcpy(as_str, "connect_2");
strcpy(user_str, "neil");
strcpy(using_str, "secret");
exec sql connect to :connect_str as :as_str user :user_str using :using_str
;
if (sqlca.sqlcode != 0) {
pg_print_debug(__FILE__, __LINE__, sqlca, "Connect failed");
return DVD_ERR_BAD_DATABASE;
}
Po zapoznaniu się z podstawami przejdz
my do szczegółów zastosowania ecpg.
Prawie zawsze trzeba dołączyć do kodu plik nagłówkowy udostępniający informacje o błędach i
statusie bazy PostgreSQL. Ponieważ ten plik ma być przetwarzany przez preprocesor ecpg przed
uruchomieniem kompilatora C, to zwykłe polecenie include nie wystarczy. Musimy użyć tu
składni exec sql include. Ponieważ istnieje już plik nagłówkowy o nazwie sqlca, który
prawie zawsze trzeba dołączyć, to program pgc zazwyczaj rozpoczyna się od następującego
wiersza:
exec sql include sqlca;
Powoduje on dołączenie pliku sqlca.h z domyślnego katalogu /usr/include/pgsql (w
konkretnej instalacji może on być zlokalizowany gdzie indziej). Ten ważny plik nagłówkowy
zawiera deklarację struktury sqlca i zmiennej o tej samej nazwie, które umożliwiają odczyt
wyników poleceń SQL. Struktura sqlca jest standardowo używana przy osadzaniu poleceń SQL
w kodzie C, ale jej konkretne implementacje mogą się nieco różnić między sobą. W instalacji
PostgreSQL omawianej w książce ma ona następującą postać:
struct sqlca
{
char sqlcaid[8];
long sqlabc;
long sqlcode;
struct
{
int sqlerrml;
char sqlerrmc[70];
} sqlerrm;
char sqlerrp[8];
long sqlerrd[6];
char sqlwarn[8];
char sqlext[8];
};
Faktyczna interpretacja zawartości sqlca może wydawać się nieco dziwna. Biblioteka ecpg
rozpowszechniana z pakietem PostgreSQL nie zawiera tak wielu funkcji jak np. biblioteki
komercyjne rozpowszechniane przez Oracle. Oznacza to, że niektórych elementów tej struktury
nie będzie można użyć. Pomimo tego ograniczenia wszystkie ważniejsze funkcje są uwzględnione
i można z nich korzystać bez jakichkolwiek problemów.
Podczas przetwarzania struktury sqlca najpierw należy sprawdzić sqlca.code. Jeżeli wartość
tego elementu jest mniejsza od zera, oznacza to, że stało się coś naprawdę złego; jeżeli jest
zerowa, to wszystko przebiegło pomyślnie. Wartość równa 100 oznacza zaś niemożność
znalezienia danych, ale nie błąd.
Jeżeli polecenia INSERT, UPDATE lub SELECT zakończą się pomyślnie, to element
sqlca.sqlerrd[2] będzie zawierał liczbę przetworzonych wierszy.
Jeżeli element sqlca.sqlwarn[0] ma wartość  W , oznacza to wystąpienie mało znaczącego
błędu i udane zazwyczaj pobranie danych, których nie udało się przyporządkować do zmiennej
systemowej (którą omówimy w dalszych częściach rozdziału).
Jeżeli wystąpi błąd, to sqlca.sqlerrm.sqlerrmc zawiera napis będący treścią komunikatu o
tym błędzie.
Komercyjne pakiety zawierają więcej pól w tej strukturze, np. informujących o  kosztach
połączenia, ale pola te nie są obecnie obsługiwane przez PostgreSQL. Ze względu na rzadko
spotykane zastosowania tych pól nie będziemy tu odczuwać ich braku.
Podsumujmy więc dotychczasowe wyjaśnienia:
sqlca.sqlcode
Zawiera ujemną wartość dla błędów poważnych, zero dla
udanego wywołania, 100 przy niemożności znalezienia danych.
sqlca.sqlerrm.sqlerrmc
Zawiera tekst komunikatu o błędzie.
sqlca.sqlerrd[2]
Zawiera liczbę przetworzonych wierszy.
sqlca.sqlwarn[0]
Zawiera  W , gdy dane zostały pobrane, lecz nie udało się ich
poprawnie przekazać do programu.
Wypróbujmy to wszystko, modyfikując kod upd1.pgc tak, aby dołączyć sqlca i jednocześnie
celowo wprowadz
my błąd, używając błędnej nazwy tabeli:
#include
#include
exec sql include sqlca;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
exec sql UPDATE XXchildren SET fname = 'Emma' WHERE age = 0;
printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}
Plik ten nazwiemy upd2.pgc. Wyróżniono tu fragmenty nie występujące w poprzednim
programie. Kompilacja odbywa się podobnie jak poprzednio:
$ ecpg -t -I/usr/include/pgsql upd2.pgc
$ gcc -g -o upd2 -I /usr/include/pgsql/ upd2.c -lecpg -lpg
Po uruchomieniu tego programu generowany jest błąd:
error code -400, message Postgres error: ERROR: xxchildren: Table does not
exist.
line 10., rows 0, warning
Jest to trochę za mało informacji, ale spełniają swoje zadanie.
Po tych podstawowych informacjach zajmiemy się najważniejszym problemem: w jaki sposób
uzyskać dostęp do danych, które zwracają osadzone polecenia SQL?
Odpowiedz
jest bardzo prosta: wykorzystuje się do tego celu tzw. zmienne systemowe dostępne
zarówno dla poleceń ograniczonych przez exec sql ... ;, jak i dla zwykłego kompilatora
języka C.
W tym celu, zwykle na początku pliku, należy zadeklarować sekcję, która będzie przetwarzana
zarówno przez preprocesor ecpg, jak i kompilator C. Deklaracja ta jest umieszczana między
dwoma wierszami ograniczającymi:
exec sql begin declare section;
i
exec sql end declare section;
Załóżmy, że chcemy zadeklarować dwie zmienne child_name i child_age, które mają być
dostępne dla osadzonych poleceń SQL i kodu w języku C w pozostałej części programu. Oto jak
wygląda taka deklaracja:
exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
exec sql end declare section;
Zwróćmy tu uwagę na dwie tajemnicze rzeczy: po pierwsze, na magiczną liczbę 50 oznaczającą
długość napisu i po drugie, na to że VARCHAR nie jest typem danych używanym normalnie w
języku C. Użyliśmy tu konkretnej wartości liczbowej, ponieważ preprocesor ecpg działa przed
kompilatorem C i nie można skorzystać z polecenia #define lub deklaracji stałej. Typ VARCHAR
został użyty z tego względu, że typ kolumny fname w SQL nie może być bezpośrednio
odwzorowany na typ danych używany w języku C. Musimy trzymać się konwencji stosowanej w
PostgreSQL, która zostanie przekształcona na prawidłową strukturę języka C przez preprocesor
ecpg. Wynikiem działania takiej deklaracji umieszczonej w kodzie z
ródłowym jest utworzenie
struktury o nazwie child_name zawierającej dwa elementy: tablicę char array o nazwie arr i
liczbę całkowitą len służącą do przechowywania rozmiaru. Kompilator C otrzymuje więc
następujący kod:
struct varchar_child_name {int len; char arr[50];} child_name;
Mamy więc dwie zmienne dostępne dla SQL i C. Przy pobieraniu danych z bazy i przekazywaniu
ich do nazwanych zmiennych używamy pewnego rozszerzenia składni języka SQL, a mianowicie
słowa kluczowego  into , za którym następuje nazwa zmiennej poprzedzona dwukropkiem.
Dzięki temu nie pomylą się zmienne systemowe z nazwami zwykłych zmiennych lub tablic.
Musimy także pamiętać, że słowo  into nie oznacza tego, co spotyka się w niektórych
rozszerzeniach standardu SQL, czyli interaktywnego pobierania danych z jednej tabeli i
przepisania ich do drugiej. Jeśli używamy osadzonych poleceń SQL, to słowo kluczowe  into
ma nieco inne znaczenie.
exec sql SELECT fname ;child_name FROM children WHERE age = :child_age;
Preprocesor ecpg przekształca taką instrukcję w kod języka C, który będzie kompilowany w
normalny sposób. Pełny kod programu selp1.pgc ma teraz następującą postać:
#include
#include
exec sql include sqlca;
exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
exec sql end declare section;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
child_age = 14;
exec sql SELECT fname into :child_name FROM children WHERE age =
:child:age;
printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0];
 if (sqlca.sqlcode == 0) {
printf("Child's name was %s\n", child_name.arr);
}
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}
Ważniejsze zmiany wprowadzone do kodu są tu wyróżnione. Zwróćmy uwagę na to, że aby mieć
dostęp do pobranych danych, należy skorzystać z child_name.arr. Deklaracje VARCHAR są
wymagane tylko wtedy, gdy chcemy mieć dane poza bazą. Jeżeli chcemy zapisać dane w bazie w
polu typu VARCHAR, to wystarczy posłużyć się zwykłym napisem z języka C, kończącym się
wartością NULL.
W powyższym programie istnieje potencjalne zagrożenie. Widzimy, że element child_name typu
VARCHAR trzeba zadeklarować jako element o stałym rozmiarze nawet wtedy, gdy nie wiemy z
góry, jaki będzie rozmiar zwracanych danych. Co się na przykład stanie, gdy jako rozmiar
elementu child_name przyjmiemy 3, a pole zachowane w bazie będzie mieć rozmiar większy? W
takim przypadku biblioteka ecpg zwróci jedynie trzy pierwsze znaki i ustawi znacznik
ostrzegawczy. Sprawdz
my to w praktyce, wprowadzając do programu zmienioną deklarację
VARCHAR child_name[3] i uruchamiając go:
error code 0, message, rows 1, warning W
Child's name was Jen
W takim przypadku można także spotkać się z pewnymi komplikacjami, które omówimy za
chwilę.
Jak widać, w elemencie sqlca.sqlwarn[0] znalazł się znak ostrzegawczy  W , a zwracana
nazwa została obcięta. Dzieje się tak, ponieważ deklaracja child_name jest przekształcana na
strukturę zawierającą tablicę znakową o trzech elementach, czyli nie ma już miejsca na
zapamiętanie ogranicznika napisu. Na szczęście działa jeszcze wydruk, gdyż specjalnie użyliśmy
w programie formatowania napisów za pomocą polecenia printf. Aby mieć pewność, że dane
typu VARCHAR są pobrane do normalnego napisu używanego w języku C, należy sprawdzać
zawartość i kopiować napis do oddzielnego miejsca, dodając jawnie ogranicznik NULL. Takie
właściwości ma kolejna wersja naszego programu nosząca nazwę selp3.c:
#include
#include
exec sql include sqlca;
exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
exec sql end declare section;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
child_age = 14;
exec sql SELECT fname into :child_name FROM children WHERE age =
:child_age;
printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0];
if (sqlca.sqlcode == 0) {
child_name.arr[sizeof(child_name.arr) -1] = '\0';
printf("Child's name was %s\n, child_name.arr);
}
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}
Ponieważ umiemy już pobierać dane z bazy, spróbujemy teraz użyć kursorów łącznie z ecpg do
określenia warunków polecenia SELECT i pobierania danych do zmiennych języka C. W
odróżnieniu od przykładu podanego dla biblioteki libpq, podczas korzystania z ecpg konieczne jest
otwieranie kursora przed użyciem za pomocą słowa kluczowego OPEN (tak przynajmniej było w
chwili pisania tej książki). Przykład o nazwie selp4.pgc jest zauważalnie krótszy niż jego
odpowiednik wykorzystujący bibliotekę libpq:
#include
#include
exec sql include sqlca;
exec sql begin declare section;
int child_age;
VARCHAR child_name[50];
int req_age;
exec sql end declare section;
main() {
exec sql connect to 'rick@gw1';
exec sql BEGIN WORK;
req_age = 6;
exec sql DECLARE mycursor CURSOR FOR SELECT age, fname FROM children
WHERE age > :req_age;
exec sql OPEN mycursor;
exec sql FETCH NEXT IN mycursor into :child_age, :child_name;
if (sqlca.sqlcode <0)
printf("error code %d, message %s, rows %d, warning %c\n",
sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0];
while (sqlca.sqlcode == 0) {
if (sqlca.sqlcode >= 0) {
child_name.arr[sizeof(child_name.arr) -1] = '\0';
printf("Child's name and age %s, %d\n", child_name.arr, child_age);
}
exec sql FETCH NEXT IN mycursor into :child_age, :child_name;
 if (sqlca..sqlcode <0) printf("error code %d, message %s, rows %d,
warning
%c\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrfd[2],
sqlca.sqlwarn[0];
}
exec sql CLOSE mycursor;
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}
Po uruchomieniu tego programu otrzymamy:
Child's name and age Andrew, 10
Child's name and age Jenny, 14
Child's name and age Alex, 11
Niektórzy mogą pomyśleć, że całe to zamieszanie z VARCHAR jest zupełnie bez sensu i znacznie
łatwiej będzie użyć napisów o ustalonym rozmiarze, dbając o to, aby rozmiar ten nie był zbyt
duży. Niestety, takie podejście stwarza kolejny problem, bowiem PostgreSQL nie może
przechowywać znaku \0 w kolumnach typu CHAR. Zamiast tego całe pole jest dopełniane spacjami
aż do końca, więc jeśli zachowaliśmy napis  Foo w polu typu CHAR(10), to pobrawszy go
stamtąd dostaniemy  Foo  i końcowe spacje trzeba będzie usuwać samemu. Uzyskujemy
jednak końcowy znak \0 podczas pobierania napisu, a więc jest to zgodne z konwencją stosowaną
w języku C.
Przekonamy się teraz, jak można wykrywać wartości NULL, korzystając z ecpg. Podobnie jak w
standardowych rozwiązaniach z osadzaniem SQL, tak i w tym przypadku jest to trochę bardziej
skomplikowane zadanie niż wówczas, gdy korzysta się z biblioteki libpq. Pamiętając, że NULL
oznacza  nieznany , nie możemy użyć jakiegoś  magicznego napisu do pokazania wartości
NULL, ponieważ w rzeczywistości może on już tam występować.
Aby wykrywanie wartości NULL było możliwe, trzeba zadeklarować dodatkową zmienną
(nazywaną często zmienną sygnalizacyjną) śledzącą zachowanie się zmiennej używanej przy
pobieraniu danych. Ta dodatkowa zmienna sygnalizacyjna ma nadawaną wartość wówczas, gdy
wartością pobranej danej jest NULL. Nazwa nowej zmiennej jest dowolna, chociaż bywa tworzona
na podstawie nazwy zmiennej śledzonej, czyli np. ind_nazwa_rzeczywistej_zmiennej lub
nazwa_rzeczywistej_zmiennej_ind. Jest ona zawsze typu integer, a jej wartość ujemna
oznacza, że związana z nią zmienna ma wartość NULL.
Załóżmy na przykład, że w naszym poprzednim przykładzie chcemy wykrywać wartość NULL w
polu age. Deklarujemy więc następującą zmienną dodatkową:
int ind_child_age;
Przy pobieraniu danych za pomocą polecenia FETCH i kursora używamy zarówno zmiennej
właściwej, jak i zmiennej sygnalizacyjnej,  sprzęgając je dwukropkiem:
exec sql FETCH NEXT IN mycursor into :child_age:ind_child_age, :child_name;
Następnie, jeśli ind_child_age nie ma wartości ujemnej, dowiadujemy się, że child_age
zawiera poprawne dane  w przeciwnym wypadku dane nie są poprawne, ponieważ w bazie
danych występowała wartość NULL. Nasz przykład w końcowej postaci powinien więc
uwzględniać takie wykrywanie wartości NULL w bazie danych.
Zmodyfikujmy tabelę w bazie danych w taki sposób, aby zarówno w polu age, jak i w polu fname
pojawiła się wartość NULL. Dane testowe będą wyglądały następująco:
SELECT * from children;
childno|fname|age
-------+------+---
1|Andrew| 10
2|Jenny | 14
3|Alex | 11
4|Adrian| 5
19| | 17
16|Emma | 0
18|TBD |
20|Gavin | 4
(8 rows)
Jak widać, w bazie występuje siedemnastolatek o nieznanym imieniu oraz nienarodzone dziecko,
którego imię będzie dopiero wybrane, a wiek nie może być jeszcze określony.
Kolejną wersję programu nazwiemy selp5.pgc. Przy okazji użyjemy tu również alternatywnej
postaci parametru połączenia.
#include
#include
exec sql include sqlca;
exec sql begin declare section;
int child_age;
int ind_child_age;
VARCHAR child_name[50];
int ind_child_name;
exec sql end declare section;
main() {
exec sql connect to tcp:postgresql://localhost:5432/rick as rick user
rick
using secretpassword;
exec sql BEGIN WORK;
exec sql DECLARE mycursor CURSOR FOR SELECT age, fname FROM children;
exec sql OPEN mycursor;
exec sql FETCH IN mycursor into :child_age:ind_child_age,
:child_name:ind_child_name,
if (sqlca.sqlcode < 0)
printf("error code %d, message %s, rows %d, warning %c\n",
sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
while (sqlca.sqlcode == 0) {
if (sqlca.sqlcode >= 0) {
if (ind_child_name >= 0) {
child_name.arr[sizeof(child_name.arr) -1] = '\0';
} else {
strcpy(child_name.arr, "Unknown");
}
if (ind_child_age >= 0) {
printf("Child's name and age %s, %d\n", child_name.arr, child_age);
 } else {
printf("Child's name %s\n", child_name.arr);
}
}
exec sql FETCH NEXT IN mycursor into :child_age:ind_child_age,
:child_name:ind_child_name;
if (sqlca.sqlcode < 0)
printf("error code %d, message %s, rows %d, warning %c\n",
sqlca.sqlcode,
sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);
} /* end of while loop */
exec sql CLOSE mycursor;
exec sql COMMIT WORK;
exec sql disconnect all;
return EXIT_SUCCESS;
}
Fragmenty kodu związane z detekcją wartości NULL są wyróżnione. Po uruchomieniu tego
programu otrzymamy:
Child's name and age Andrew, 10
Child's name and age Jenny, 14
Child's name and age Alex, 11
Child's name and age Adrian, 4
Child's name and age Unknown, 17
Child's name and age Emma, 0
Child's name TBD
Child's name and age Gavin, 4
Widać już, że wartości NULL w bazie danych są wykrywane poprawnie.
Na tym kończymy zapoznawanie się z ecpg, czyli osadzonym preprocesorem poleceń SQL
używanym w pakiecie PostgreSQL (ang. embedded SQL pre-processor for PostgreSQL).
Jaką metodę zastosować?
Ponieważ mamy dwie metody dostępu do bazy PostgreSQL z poziomu kodu języka C, to którą z
nich należy wybrać? Jak zwykle, nie ma tu jednoznacznej poprawnej odpowiedzi. Należy użyć tej
metody, która wydaje się najlepiej pasować do zagadnienia i z którą najlepiej się pracuje. Możemy
doradzić tylko to, by nie mieszać dwóch metod w jednym projekcie. Po dokonaniu wyboru
metody, należy ściśle jej przestrzegać.
Zalety biblioteki libpq są następujące:
Proste wywołania funkcji, z którymi większość użytkowników jest już zaznajomiona.
Jest całkiem dobrze udokumentowana.
Wady libpq są następujące:
Wymaga długiego kodu.
Trudno wyróżnić polecenia SQL wśród kodu języka C.
Zalety ecpg są następujące:
Jest to standard osadzania poleceń SQL.
Polecenia SQL są dobrze widoczne, ponieważ nie są ukryte w wywołaniach funkcji
bibliotecznych.
Wady ecpg są następujące:
Wykrywanie błędów może być utrudnione, ponieważ przed zadziałaniem kompilatora C
plik jest przetwarzany przez preprocesor. Oznacza to, że trudniej jest śledzić błędy
wykrywane przez kompilator C, zaś użycie debuggera gdb może dawać mylące wyniki ze
względu na niewłaściwą numerację wierszy kodu. Preprocesor ecpg wstawia dyrektywy
#line do tworzonego kodu w języku C i to zazwyczaj pomaga w wykrywaniu błędów,
ponieważ komunikaty o błędach odnoszą się do pierwotnej numeracji wierszy kodu w
pliku .pgc. Nie zawsze jednak tego oczekujemy i może to powodować pomyłki w gdb.
Można sobie z tym poradzić, zezwalając, by preprocesor ecpg wygenerował plik .c,
następnie używając polecenia grep -v '^#line' > _1.c, potem kopiując plik _1.c
ponownie do pliku .c utworzonego przez ecpg i na końcu kompilując ten plik. Dzięki
takiemu postępowaniu usuniemy dyrektywy włączające numerację wierszy i komunikaty
o błędach pochodzące z kompilatora, natomiast polecenia gdb będą dotyczyć numerów
wierszy w pliku .c, a nie w pliku .pgc. Takie kłopoty nie są specyfiką samego ecpg,
bowiem występują one we wszystkich systemach z osadzonymi poleceniami SQL.
Wielu użytkowników nie zna tak dobrze tej metody, zna natomiast standardową metodę
korzystania z funkcji bibliotecznych.
Trzeba z góry znać liczbę kolumn i typy danych pobieranych za pomocą poleceń SELECT.
Jeżeli mamy program w języku C, który zawiera niewielki dodatek poleceń SQL, to trudności
powstające podczas wykrywania błędów w kodzie utworzonym przez preprocesor mogą
decydować o odrzuceniu drugiej metody.
Aplikacja
Ponieważ poznaliśmy już podstawowe informacje o dostępie do bazy PostgreSQL z kodu języka
C, nadszedł teraz czas na wykorzystanie tej wiedzy w programie obsługującym naszą bazę danych
dla wypożyczalni płyt DVD.
Pierwszym i prawdopodobnie najważniejszy problemem do rozwiązania jest projekt tabel bazy
danych. Potrzebna jest tabela przechowująca informacje o kliencie. Musimy także pomyśleć o
faktycznych płytach DVD. Ważne jest, aby zdać sobie w tym momencie sprawę z faktu, że są dwa
rodzaje informacji o płytach: informacja o filmie umieszczonym na płycie, np. że jest to film
 Death and Maiden , w którym występuje  Sigourney Weaver , który ma reżysera, datę wydania
itd., oraz informacja o faktycznej płycie DVD dostępnej w wypożyczalni. Film istnieje niezależnie
od dostępności płyty w wypożyczalni, czyli w danym momencie może się w niej znajdować zero
lub więcej płyt. Takie rozważania dowodzą, że informację o filmie i informację o płycie należy
przechowywać w dwóch oddzielnych tabelach. Na pewno informacje te są ze sobą powiązane, ale
nie są takie same.
Następna porcja informacji do przechowania dotyczy klientów rezerwujących tytuły filmów.
Dodamy w tym celu tabelę o nazwie member_booking, która będzie zawierać identyfikator
klienta, identyfikator tytułu oraz datę rezerwacji. Działa ona jako połączenie między klientem a
zarezerwowanym przez niego filmem. Takie podejście pozwala również na rezerwację tego
samego filmu przez więcej niż jednego klienta tego samego dnia, czyli jest to klasyczna relacja
 wiele do wielu . Oczywiście, nasza aplikacja musi kontrolować liczbę faktycznie dostępnych
płyt!
Przechodząc do faktycznie wypożyczonych płyt możemy postąpić podobnie, czyli dodać tabelę
łączącą płytę z klientem. Musimy tu pamiętać o oczywistej optymalizacji  pomiędzy klientem
wypożyczającym płytę a płytą z wypożyczalni może nie wystąpić żadne połączenie albo może
wystąpić połączenie  jeden do jednego . Możemy zatem przechowywać informacje o
wypożyczeniach bezpośrednio w tabeli z informacjami o płytach i jako identyfikator klienta
wpisywać wartość NULL dla płyt nie wypożyczonych. Takie postępowanie jest nazywane
denormalizacją i powinno być stosowane tylko wtedy, gdy mamy pewność, że struktura danych
została właściwie przeanalizowana. Takie rozwiązanie stosujemy tutaj głównie po to, aby pokazać
jedną z poprawnych metod optymalizacji i dodatkowo uprościć kod.
Potrzebujemy jeszcze trzech tabel pomocniczych: jednej na przechowywanie komunikatów o
błędach, jednej na przechowywanie rodzaju filmu i jednej na przechowywanie klasyfikacji filmu.
Zarówno rodzaj, jak i klasyfikacja filmu wiążą się bezpośrednio z jego tytułem i są jedynymi
występującymi tu wartościami. Mamy więc przed sobą następny wybór. Możemy np.
przechowywać informację o rodzaju filmu i klasyfikujący go tekst w tabeli z danymi opisującymi
film, pozwalając aplikacji na pobieranie ich z tabel pomocniczych. Rozwiązaniem alternatywnym
jest przechowywanie tylko identyfikatorów i użycie w bazie danych relacji odnoszącej się do
faktycznej tabeli z przechowywanym tekstem.
Jeżeli tekst jest bardzo krótki i jesteśmy przekonani, że aplikacja będzie używać poprawnych
danych, to prawdopodobnie łatwiej będzie przechowywać ten tekst w tabeli z danymi filmu
(tytułu). Takie podejście upraszcza strukturę bazy i ułatwia pisanie poleceń SQL. Jeżeli jednak
teksty są dłuższe i chcemy być pewni, że będą one poprawne, to w głównej tabeli powinniśmy
użyć identyfikatorów tekstu, a sam tekst przechowywać w odrębnej tabeli. Obniża to wymagania
odnośnie do pamięci, ponieważ każdy unikatowy napis jest przechowywany tylko w jednym
miejscu. Dla celów pokazowych w naszej aplikacji będziemy przechowywać klasyfikację filmu
bezpośrednio w tabeli głównej, zaś rodzaj filmu będzie przechowywany w oddzielnej tabeli.
Dzięki temu można będzie porównywać obydwie metody.
Zalecamy, by w rzeczywistej aplikacji przechowywać tylko połączenia z rzeczywistymi tabelami
zawierającymi dane. Ułatwia to znacznie utrzymanie danych w dobrym stanie, co w bazie danych
jest sprawą najważniejszą. Doskonale zbudowana baza danych zawierająca niepoprawne dane jest
tylko trochę lepsza niż brak jakiejkolwiek bazy.
Aby ułatwić zarządzanie tabelami w bazie danych, zapiszemy w oddzielnym pliku polecenia SQL
wykorzystywane do tworzenia tych tabel. Dzięki temu możemy łatwo je modyfikować i odtwarzać
w razie konieczności. Polecenia SQL przechowywane w pliku można uruchamiać, korzystając z
polecenia \i file.sql interpretera psql. Oto kod SQL tworzący naszą bazę danych:
create table member (
member_id SERIAL,
member_no CHAR(6) NOT NULL,
title CHAR(4),
fname CHAR(26),
lname CHAR(26) NOT NULL,
house_flat_ref CHAR(26) NOT NULL,
address1 CHAR(51) NOT NULL,
address2 CHAR(51),
town CHAR(51) NOT NULL,
state CHAR(3),
phone CHAR(31),
zipcode CHAR(11) NOT NULL,
CONSTRAINT member_no_uniq UNIQUE(member_no)
);
create table title (
title_id SERIAL,
title_text CHAR(61) NOT NULL,
asin CHAR(11),
director CHAR(51),
genre_id INT,
classification CHAR(11),
actor1 CHAR(51),
actor2 CHAR(51),
release_date CHAR(9),
rental_cost CHAR(7)
);
create table disk (
disk_id SERIAL,
title_id INT NOT NULL,
member_id INT, /* wstawiany po wypożycz., inaczej NULL */
rented_date CHAR(9)
);
create table member_booking (
member_id INT NOT NULL,
title_id INT NOT NULL,
date_required CHAR(9) NOT NULL
);
create tables filmclass (
class_film CHAR(11)
);
create table genre (
genre_id INT NOT NULL,
genre_name CHAR(21),
CONSTRAINT genre_id_uniq UNIQUE(genre_id)
);
create table errtext (
err_code INT,
err_text CHAR(50)
);
Można tu zauważyć dodatkowe ograniczenia, dodane dla przykładu:
CONSTRAINT genre_id_uniq UNIQUE(genre_id)
Nie chcemy, by identyfikator rodzaju filmu genre_id był zmienną typu SERIAL, ponieważ po
przeładowaniu danych musimy odtworzyć dokładnie taką samą jego wartość, co poprzednio  w
przeciwnym wypadku informacja odnosząca się do tabeli title będzie błędna. Z drugiej strony,
wymagana jest także unikatowość tych wartości. Właśnie dlatego dodaliśmy ograniczenie
pozwalające na wskazywanie wartości genre_id, jeżeli taka sama wartość jeszcze w bazie nie
istnieje.
Na poniższym rysunku pokazano strukturę bazy danych w postaci graficznej:
Nie mamy tu wystarczająco dużo miejsca na prezentację całego kodu, pokażemy więc tylko małe
fragmenty ilustrujące proces powstawania aplikacji. Te fragmenty kodu tworzą najniższy poziom
aplikacji i są wywoływane po ogólnym sprawdzeniu poprawności działania (np. po upewnieniu
się, że jest połączenie z bazą danych lub że argumenty wskaz
nikowe nie mają wartości NULL).
Jako przykład takiego kodu podajemy poniżej fragment, który pobiera strukturę opisującą
szczegóły tytułu filmowego, wyszukuje odpowiedni identyfikator genre_id i dopisuje wiersz do
tabeli title.
int pg_title_insert(dvd_title *title_ptr) {
exec sql BEGIN WORK;
strcpy(title_text, title_ptr->title_text);
strcpy(asin, title_ptr->asin);
strcpy(director, title_ptr->director);
sprintf(genre_name, "%s%c", title_ptr->genre, '%');
strcpy(classification, title_ptr->classification);
strcpy(actor1, title_ptr->actor1);
strcpy(actor2, title_ptr->actor2);
strcpy(release_date, title_ptr->release_date);
strcpy(rental_cost, title_ptr->rental_cost);
/* Wyszukanie genre_id */
exec sql SELECT genre_id INTO :genre_id:genre_id_ind FROM genre WHERE
genre_name LIKE :genre_name;
if ((sqlca.sqlcode < 0) || (sqlca.sqlcode == 100 || (genre_id_ind == 1)) {
pg_print_debug(__FILE__,__LINE__, sqlca, "unknown genre\n");
exec sql ROLLBACK WORK;
return DVD_ERR_BAD_GENRE;
}
exec sql INSERT INFO title(
title_text, asin,
director, genre_id, classification,
actor1, actor2, release_date,
rental_cost)
VALUES (
:title_text, :asin,
:director, :genre_id, :classification,
:actor1, :actor2, :release_date,
:rental_cost);
if (sqlca.sqlcode < 0) {
pg_print_debug(__FILE__, __LINE__, sqlca, "insert into title failed\n");
exec sql ROLLBACK WORK;
return DVD_ERR_BAD_TITLE_TABLE;
} else {
if (sqlca.sqlerrd[2] != 1) {
pg_print_debug(__FILE__, __LINE__, sqlca, "insert into title
failed\n");
exec sql ROLLBACK WORK;
return DVD_ERR_BAD_TITLE_TABLE;
}
}
exec sql SELECT MAX(title_id) INTO :title_id FROM title;
if (sqlca.sqlcode < 0) {
pg_print_debug(__FILE__, __LINE__, sqlca, "select max title
 failed\n"0'
exec sql ROLLBACK WORK;
return DVD_ERR_BAD_TITLE_TABLE;
}
exec sql COMMIT WORK;
/* Aktualizacja struktury klienta za pomocą obecnie znanych danych */
title_ptr->title_id = title_id;
return DVD_SUCCESS;
} /* pg_title_insert */
Został tu wyróżniony kod wyszukujący odpowiedni identyfikator genre_id. Zwracamy uwagę na
to, że sprawdza się nie tylko poprawność wykonania polecenia SQL (tzn. czy sqlca.sqlcode nie
jest mniejsze od 0), ale także to, czy dane zostały zwrócone (tzn. czy sqlca.sqlcode nie było
równe 100) oraz czy wartość pobieranego identyfikatora genre_id nie była równa NULL (tzn. czy
wartość genre_id_ind nie była równa 1). Ukryte znaczenie takiego użycia genre_id polega na
tym, że dopóki aplikacja nie spróbuje umieścić niepoprawnego napisu, dopóty nie ma się co
martwić o faktyczne przechowywanie danych  jest to więc godny uwagi podział
odpowiedzialności.
Poniżej zamieszczono kod pobierający dane o filmie. Tutaj również ukryto szczegóły
przechowywania danych oraz pokazano sposób łączenia tabel (czyli jednoczesnego pobierania
danych z więcej niż jednej tabeli).
int pg_title_get(int req_title_id, dvd_title *title_ptr) {
title_id = req_title_id;
exec sql BEGIN WORK;
exec sql SELECT
title_id, title_text, asin,
director, genre_name, classification,
actor1, actor2, release_date,
rental_cost
INTO
:title_id:ind_title_id, :title_text, :asin,
:director, :genre_name, :classification,
 :actor1, :actor2, :release_date,
:rental_cost
FROM title, genre WHERE title.title_id = :title_id AND title.genre_id
= genre.genre_id;
if (sqlca.sqlcode < 0) {
pg_print_debug(__FILE__, __LINE__, sqlca, "title get failed\n");
exec sql ROLLBACK WORK;
return DVD_ERR_BAD_TITLE_TABLE;
}
if ((sqlca.sqlcode == 100) || (ind_title_id != 0)) {
pg_print_debug(__FILE__, __LINE__, sqlca, "title get failed - no
entry\n");
exec sql ROLLBACK WORK;
return DVD_ERR_NOT_FOUND;
}
title_ptr->title_id = title_id;
strcpy(title_ptr->title_text, title_text);
strcpy(title_ptr->asin, asin);
strcp(title_ptr->director, director);
strcp(title_ptr->genre, genre_name);
strcp(title_ptr->classification, classification);
strcp(title_ptr->actor1, actor1);
strcp(title_ptr->actor2, actor2);
strcp(title_ptr->release_date, release_date);
strcp(title_ptr->rental_cost, rental_cost);
exec sql COMMIT WORK;
return DVD_SUCCESS;
} /* pg_title_get */
Kolejny interesujący fragment kodu dotyczący filmu służy do wyszukiwania. API umożliwia
wyszukiwanie filmu przez klienta na podstawie tytułu, a także nazwiska aktora. Być może trzeba
będzie zapisać to jako oddzielne funkcje, ale w SQL bardzo łatwo można to uczynić za pomocą
jednego polecenia. Trzeba jedynie pamiętać, że  % w SQL oznacza dopasowanie do napisu.
int pg_title_search(char *title_to_find, char *name_to_find, int
*result_ids[],
int *count) {
int result_size = 0;
int *results = NULL;
if (title_to_find == NULL) strcpy(title_text, "%");
else sprintf(title_text, "%c%s%c", '%', title_to_find, '%');
if (name_to_find == NULL) strcpy(actor1, "%");
else sprintf(actor1, "%c%s%c", '%', name_to_find, '%');
exec sql BEGIN WORK;
exec sql DECLARE mycursor CURSOR FOR SELECT title_id from title WHERE
(title_text LIKE :title_text) AND ((actor1 LIKE :actor1) OR
(actor2 LIKE:actor1)) ORDER by title_text, actor1, actor2;
exec sql OPEN mycursor;
if (sqlca.sqlcode < 0) {
pg_print_debug(__FILE__, __LINE__, sqlca, "mycursor");
exec sql ROLLBACK WORK;
return DVD_ERR_BAD_TABLE;
}
exec sql FETCH NEXT in mycursor INTO :title_id;
while (sqlca.sqlcode == 0) {
result_size++;
results = (int *)realloc(results, sizeof(int) * result_size);
if (results == NULL) { /* Poważny błąd, nie próbujemy pobierać */
exec sql ROLLBACK WORK;
return DVD_ERR_NO_MEMORY;
}
results[result_size - 1] = title_id;
exec sql FETCH NEXT in mycursor INTO :title_id;
} /* while */
if (sqlca.sqlcode < 0) {
pg_print_debug(__FILE__, __LINE__, sqlca, "mycursor");
 exec sql ROLLBACK WORK;
return DVD_ERR_BAD_TABLE;
}
exec sql COMMIT WORK;
*result_ids = results;
*count = result_size;
return DVD_SUCCESS;
} /* pg_title_search */
Wynik porządkujemy według pól title i actor1, które najpewniej będą poprawne.
Pozostała części kodu aplikacji, podobnie jak i przydatne fragmenty z tej książki, są dostępne na
serwerze ftp wydawnictwa Helion (ftp://ftp.helion.pl/przyklady/zaprli.zip).
Podsumowanie
W tym rozdziale omówiliśmy dwie metody dostępu do bazy danych PostgreSQL z poziomu kodu
w języku C. Najpierw opisywaliśmy metodę wykorzystującą konwencjonalne wywołania funkcji
bibliotecznych, a następnie pokazaliśmy sposób osadzania poleceń SQL bezpośrednio w kodzie C.
Porównaliśmy także obydwie metody, pokazując, że każda z nich ma zalety i wady. Na
zakończenie przedstawiliśmy fragmenty kodu naszej przykładowej aplikacji obsługującej
wypożyczalnię płyt DVD, która korzysta z dostępu do danych przechowywanych w bazie
PostgreSQL.