Externale do realizacji zapytań SQL

Praktyka

Aby ułatwić korzystanie z eksternali zostały stworzone funkcje w języku skryptowym. Korzystanie z nich jest zalecane.

Należy wpierw inlcudować plik utils/sqllib.inc - w zależności od lokalizacji skryptu trzeba wskazać odpowiednią ścieżkę względną (np. ..\..\..\utils\sqllib.inc)

Funkcja	Rezultat	Opis
`query_prepare()`	`q_id`	Przygotowanie zapytania
`query_bind_string(params, name, value)`	`params`	Do listy parametrów `params` dodaje zmienną napisową `name` o wartości `value`
`query_bind_int(params, name, value)`	`params`	Do listy parametrów `params` dodaje zmienną liczbową `name` o wartości `value`
query_execute(q_id, sql, params)	err	Wykonuje zapytanie
query_get_row(q_id)	q_result	Zwraca kolejny wiersz wyniku
query_get_field(q_result, indx)	value	Zwraca kolumnę indx wiersza wyniku
`query_unprepare(q_id)`	`err`	Zakończenie zapytania

Przykład zapytania

[PLIK utils/sqllib.inc]

procedure zapytanie
begin
    q_id=query_prepare()
    if q_id
        err='SQLPREPARE: pusty rezultat'
    else
        sql = 'select fld_rec_type,fld_rec_id,fld_str_type,fld_str_id from tbl_items_raw where fld_kind=:kind and fld_rec_id > :rec_id'

        params = query_bind_string('', 'kind', 'OT')
        params = query_bind_int(params, 'rec_id', 1000)

        err=query_execute(q_id, sql, params)
        if err 
            q_ok=''
            while q_ok
                q_result = query_get_row(q_id)
                if !q_result

                    rec_type = query_get_field(q_result, 0)
                    rec_id   = query_get_field(q_result, 1)
                    str_type = query_get_field(q_result, 2)
                    str_id   = query_get_field(q_result, 3)            

                else
                    q_ok='N'
                endif
            enddo
        endif
    endif
end
err=sep(err,'. ',query_unprepare(q_id))

Funkcje pomocnicze

Przy budowaniu zapytań, w szczególności skomplikowanych warunków, pomocne mogą być następujące funkcje zdefiniowane w pliku sqllib.inc

Funkcja	Parametry	Opis
query_cond_eq(field, value)	field - nazwa pola value - wartość	Zwraca prosty warunek dla równości wartości. Ale zapewnia bezpieczeństwo pod kątem apostrofu w wartości.
query_cond_in(field, opts)	field - nazwa pola opts - lista opcji w postaci `opcja1\|opcja2\|opcja3\|`	Zwraca warunek dla wyboru wartości z listy. Jeśli nie zostanie podana żadna opcja, warunek będzie zawsze fałszem (!!!)
query_cond_date(field, start_date, end_date)	field - nazwa pola typu "date" lub "datetime" start_date - data startowa (wyrażona YYYYMMDD) end_date - data końcowa (wyrażona YYYYMMDD)	Zwraca warunek dla zakresu daty (można go stosować tylko dla pól "date" lub "datetime") Jeśli nie zostanie podana któraś z dat - uznajemy zakres za otwarty z tej strony.
query_cond_month(field, start_year, start_month, end_year, end_month)	field - nazwa pola typu "date"lub "datetime"	Zwraca warunek dla zakresu daty w miesiącach wyrażonych.

SQL

Nowy model externali SQL znacznie różni się od poprzedniego. Tworzenie zapytań jest o wiele prostsze, a same zapytania, zapisane w czytelniej, sparametryzowanej formie. Operacje są wykonywane w ramach zagnieżdżonej transakcji, a po ich zakończeniu w SQLUNPREPARE, jest wykonywany rollback, dzięki czemu w ramach jednej pary SQLPREPARE - SQLUNPREPARE, można wykonywać dowolne zapytania, tworzyć tabele tymczasowe, niszczyć je, wszystko na co tylko mamy ochotę, zmiany te i tak nie zostaną ostatecznie zapisane do bazy.

Funkcja	Opis

Funkcja	Opis
`query_prepare()`	Przygotowanie zapytania. Zwraca `q_id`

SQLPREPARE

Przygotowanie obiektu do wykonania zapytania SQL

parametry: brak
rezultat: QUERY_ID, lub pusta wartość jeśli operacja się nie udała.

Tego externala NIE WOLNO zagnieżdżać.

Nawet jeśli jako rezultat zostanie zwrócona pusta wartość (operacja nieudana), konieczne jest wywołanie SQLUNPREPARE, inaczej licznik operacji PREPARE i UNPREEPARE nie będzie się zgadzał i cały skrypt zostanie zakończony błędem, a zmiany nie ostaną zapisane do bazy.

SQLUNPREPARE|<query_id>

Zakończenie operacji SQL i zniszczenie obiektów po stronie serwera.

parametry: query_id
rezultat: komunikat błędu, lub pusty string jeśli OK

Jeśli nie będzie się zgadzała w skrypcie ilość wykonanych SQLPREPARE i SQLUNPREPARE, cały skrypt zostanie zakończony błędem i jego wynik zostanie anulowany przez rollback.

Nawet jeśli jako rezultat zostanie zwrócona pusta wartość (operacja nieudana), konieczne jest wywołanie SQLUNPREPARE, inaczej licznik operacji PREPARE i UNPREEPARE nie będzie się zgadzał i cały skrypt zostanie zakończony błędem, a zmiany nie ostaną zapisane do bazy.

SQLPLAIN|<query_id>|<szablon zapytania>

Pozwala sformułować zapytanie SQL otwartym tekstem.

W treści nie należy podawać wartości zmiennych, w ich miejscu powinno się podawać :NAZWA - za który zostanie w momencie wykonania zapytania podstawiona wartość parametru przekazanego dalszym wywołaniem externala SQLPARAM.

UWAGA: w treści zapytania nie wolno używać znaku apostrofu (to zabezpieczenie przed atakiem sql-injection). Natomiast w <zapytanie> mogą się znajdować np. znaki "||" używane np. do konkatenacji łańcuchów.

parametry: query_id, zapytanie
rezultat: komunikat błędu, lub pusty string jeśli OK

Przykład sformatowanego szablonu zapytania SQL

select fld_name from tbl_params where fld_name ilike :name

SQLPARAM|<query_id>|<nazwa>|<typ>|<wartość>

Dokonuje podstawienia wartości dla :NAZWA z zapytania w SQLPLAIN.

<nazwa> to nazwa parametru (można używać wielokrotnie)

<typ> to:

i - liczba (podana wartość powinna być numeryczna)
s - łańcuch (podana wartość może być dowolnym napisem)

<wartość> musi być zgodna z typem. W przypadku łańcuchów nie musimy escape'ować żadnych znaków.

rezultat: komunikat błędu (w szczególności gdy podani typ "i" a wartość nie jest liczbowa), lub pusty string jeśli OK

SQLEXECUTE|<query_id>

Wykonanie zapytania SQL

parametry: query_id
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLOPEN|<query_id>

Wywołanie zapytania, które zwraca dane, po których chcemy się poruszać przy pomocy SQLGET

parametry: query_id
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLGET|<query_id>

Pobranie pojedynczego rekordu z wyniku

parametry: query_id

rezultat:

0|komunikat błędu
1|lista pól z wynikami (wartości poszczególnych pól są oddzielone pionową kreską)
2|EOF

qid = external('SQLPREPARE')
aux = external('SQLPLAIN|'+qid+'|select fld_name, fld_value from tbl_params where fld_name like :name and fld_value is not null')
aux = external('SQLPARAM|'+qid+'|name|s|CFG_ZAM_%')
aux = external('SQLOPEN|'+qid)
(...)
aux = external('SQLUNPREPARE|'+qid)

SQLPREPARE

SQLPREPARE

Przygotowanie obiektu do wykonania zapytania SQL

parametry: brak
rezultat: QUERY_ID

Tego externala NIE WOLNO zagnieżdżać.

SQLSELECT

SQLSELECT|<query_id>|<lista_pól>

Lista pól jakie mają być zwrócone w ramach zapytania

parametry: query_id, lista pól (nazwy pól oddzielone pionowymi kreskami)
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLFROM

SQLFROM|<query_id>|<nazwa_tabeli>

Nazwa tabeli na której ma być wykonywane zapytanie

parametry: query_id, nazwa tabeli
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLWHERE

SQLWHERE|<query_id>|<warunek_zapytania>

Warunek zapytania SQL

parametry: query_id, warunek
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLORDERBY

SQLORDERBY|<query_id>|<kolejność_sortowania>

Kolejność sortowania

parametry: query_id, kolejność sortowania (nazwy pól oddzielone przecinkami)
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLLIMIT

SQLLIMIT|<query_id>|<oczekiwana_ilość_rekordów>

Ustawienie ilości rekordów które mają być zwrócone w ramach zapytania

parametry: query_id, ilość rekordów
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLEXECUTE

SQLEXECUTE|<query_id>

Wykonanie zapytania SQL

parametry: query_id
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLGET

SQLGET|<query_id>

Pobranie pojedynczego rekordu z wyniku

parametry: query_id

rezultat:

0|komunikat błędu
1|lista pól z wynikami (wartości poszczególnych pól są oddzielone pionową kreską)
2|EOF

SQLUNPREPARE

SQLUNPREPARE|<query_id>

Zniszczenie obiektu po stronie serwera

parametry: query_id
rezultat: komunikat błędu, lub pusty string jeśli OK

SQLFULL

SQLFULL|<query_id>|<lista_pól>|<nazwa_tabeli>|<warunek_zapytania>|<kolejność_sortowania>|<oczekiwana_ilość_rekordów>

Przygotowanie zapytania

parametry: query_id, lista pól (oddzielona przecinkami), nazwa tabeli, warunek zapytania, kolejność sortowania, ilość rekordów.
rezultat: komunikat błędu, lub pusty string jeśli OK

W celu użycia w SQLFULL ciągu znaków || koniecznych podczas sumowania stringów w PostgreSQL, należy użyć:

+STRING+

Aby użyć pojedynczego znaku | należy użyć (od SowaSQL 1.65.19):

+STRING