CALL SQL

Operator CALL SQL umożliwia wykonywanie dowolnych zapytań SQL na wskazanej bazie danych, przy wykorzystaniu danych dostępnych na magistrali wejściowej, a także parametrów projektowych i globalnych.

W zależności od konfiguracji operator może wykonywać:

• komendy DML (np. SELECT, INSERT, UPDATE, DELETE) – do manipulacji danymi,
• komendy DDL (np. CREATE, ALTER, DROP) – do zarządzania strukturą bazy danych.

Domyślnie zapytanie SQL jest wykonywane dla każdego rekordu na magistrali wejściowej. Istnieje jednak możliwość skorzystania z trybu Single execution, dzięki któremu zapytanie zostanie wykonane tylko raz – niezależnie od liczby rekordów.

W budowie zapytania możesz użyć:

• bitów magistrali danych (indywidualnych lub tablicy bitów w formacie JSON w trybie Single execution),
• parametrów projektu
• zmiennych globalnych
• metajęzyka GRAVITY
• funkcji podmiany tekstu, umożliwiających dynamiczną modyfikację treści zapytania na podstawie przetwarzanych danych.

Dzięki elastycznej składni oraz możliwości powiązania zapytań z danymi wejściowymi, operator CALL SQL doskonale sprawdza się w integracji z bazami danych, m.in. przy:

• pobieraniu i uzupełnianiu danych pomocniczych,
• zapisywaniu wyników przetwarzania,
• realizacji logiki biznesowej bezpośrednio w bazie danych.

Przykład projektu GRAVITY z użyciem operatora CALL SQL.

KONFIGURACJA OPERATORA

Operator służy do wykonania kodu SQL w bazie danych, a zatem przede wszystkim musisz wskazać podłączenie do wybranej bazy.

Sposób definicji połączenia do bazy został już opisany w przypadku operatora INPUT SQL i jest identyczny.

Masz dwa warianty wykorzystania operatora (zakładka STATEMENT).

Wariant pierwszego wyboru, to wariant wywoływania kodu SQL dla każdego rekordu, znajdującego się w strumieniu danych na magistrali wejściowej.

Możesz jednak zdecydować się na wariant jednokrotnego wywołania zapytania dla wszystkich rekordów, należy wówczas zaznaczyć opcję SINGLE EXECUTION.

Jeśli w trybie Single execution chcesz użyć wartości danych z magistrali jest to możliwe korzystając z funkcji @GET_JSON[]

Kod SQL może zwracać wynik, który jest przetworzony w sposób, w zależny od nastawy TARGET RESULT (patrz ilustracja powyżej):

• VALUE INTO AN EXISTING COLUMN → wynik polecenia SQL będzie umieszczony we wskazanej kolumnie magistrali wyjściowej (pozostałe dane magistrali wejściowej zachowują swoje wartości);

Jeśli nie posiadasz kolumny na magistrali gdzie możesz przekazać wynik działania zapytania, rozważ jej utworzenie przy użyciu operatora WIDTH BUSBAR

Jako wynik polecenia należy w tym wypadku rozumieć daną zwracaną w pierwszej kolumnie pierwszego rekordu zwracanego przez kod SQL. Pozostałe dane zostaną zignorowane.
• VALUES FROM FIRST ROW TO THE EXISTING COLUMNS → wartości kolumn zapytania SQL mogą zostać zapisane do już istniejących kolumn magistali. Wybierając ta opcję zobaczysz dodatkową zakładkę Column matching


Dopasuj do kolumny zwracanej z zapytania Column name odpowiedni bit magistali Busbar bit. Możesz dopasaować wiele kolumn do wielu bitów. Jeśli nie widzisz kolumn zwróconych przez zapytanie użyj przycisku Refresh sql columns.


Jeśli nie posiadasz kolumn na magistrali gdzie możesz przekazać wynik działania zapytania, rozważ jej utworzenie przy użyciu operatora WIDTH BUSBAR


Jeśli zapytanie zwróci więcej niż jeden wiersz tylko dane z pierwszego wiersza zostaną podstawione pod bity magistali

• CREATE A BUSBAR FROM RESULT → magistrala wyjściowa zostaje zamieniona na wynik działania polecenia SQL.

W wypadku wywołania zapytania dla każdego rekordu magistrali magistrala wyjściowa będzie sumą danych ze wszystkich wywołań.
• ADD TO THE BUSBAR THE DATA FROM THE QUERY → Kolumny pobrane z zapytania SQL zostaną dodane do danych magistali wyjściowej.


Jeśli zpytanie zwróci więcej niż jeden rekord dla przetwarzanego rekordu magistrali dane magistrali zostaną powielone tylekroć ile rekordów zwróci zapytanie SQL

• IGNORE RESULT → dane zwrócone poleceniem SQL są ignorowane; na wyjściu magistrali pojawi się strumień danych identyczny jak na magistrali wejściowej;

Pole SEPARATE TRANSACTION pozwala na uruchomienie kodu SQL w oddzielnej transakcji, która zostanie zakończona po wykonaniu zapytania.

Nowa połączenie z osobną transakcja jest wykorzystane dla wykonania polecenia SQL dla wszystkich rekordów magistrali wejściowej.

Jeśli projekt się nie powiedzie, a operator Call SQL zdążył już wykonać polecenie SQL pomyślnie, to używając trybu SEPARATE TRANSACTION nie będzie możliwości wycofania zmian. Więcej o zarządzaniu transakcjami dowiesz się Zarządzanie transakcjami baz danych w projekcie.

BUDOWA ZAPYTANIA SQL

W polu STATEMENT możesz edytować wyrażenie SQL. Dostęp do bitów magistrali wejściowej, parametrów projektu oraz funkcji wbudowanych zapewnia słownik. Całe wyrażenie można przetestować wybierając opcję TEST.

Parametry projektu i bity magistrali doklejane są do zapytania w formie @[nazwa_bitu] i podlegają parsowaniu w celu wyeliminowania ataku sql injection.

przykład użycia bitu o nazwie “id” w zapytaniu:

select id, name from g.customer where id=@id

Poza możliwością wstawienia bitów magistrali oraz parametrów projektu istnieje możliwość wstawienia wbudowanych funkcji oraz metajęzyka.

• @EXECUTEID → parametr przechowujący unikalny identyfikator przetwarzanego procesu. Możesz go użyć w dowolnym zapytaniu sql.

Przykład użycia w zapytaniu

INSERT INTO etl.articles(index, index_name, code, unit, processId) VALUES (@index, @index_name, @code, @unit, @EXECUTEID);

• @GET_JSON[] → parametr, który zwraca tablicę obiektów JSON złożoną z wszystkich wskazanych bitów magistrali.

Przykład definicji:

zakładamy, że magistrala wygląda w następujący sposób:

id	name	unit
1	name 1	t
2	name 1	t

wykonując zapytanie:

select @GET_JSON[@id, @name] as json_data

otrzymamy wartość pola json_data

Copy

[{"id": 1,"name":"name 1","unit":"t"},{"id":2,"name":"name 2","unit":"t"}]


W zależności od ustawienia przełącznika SINGLE EXECUTION otrzymamy na magistrali wyjściowej jeden rekord (przy zaznaczonym polu) lub tyle rekordów ile jest rekordów na magistrali wejściowej.

Parametr @GET_JSON[] możesz wykorzystać używając trybu SINGLE EXECUTION aby wykonać tylko jedno zapytanie, a zależy Ci na użyciu danych z magistrali.

Wykorzystując funkcję bazy danych możesz przekształcić tablice JSON do formatu danych tabeli danych i wykorzystać w zapytaniu SQL.

Przykład dla bazy PostgreSQL z wykorzystaniem funkcji json_to_recordset

select id, name from json_to_recordset(cast(@GET_JSON[@id, @name] as json)) as x(id int, name text)

pełne wykorzystanie w zapytaniu:

select id, index, index_name, code, unit from etl.articles where id in ( select id from json_to_recordset(cast(@GET_JSON[@id, @name] as json)) as x(id int, name text) )


Ponieważ wartość zwracana przez parametr @GET_JSON[] jest w formie tekstowej należy ją w pierwszej kolejności rzutować na typ json
• {metalanguage_phrase} → funkcja sprawdzająca warunek: {[wyrażenie] ? “wyrażenie spełnione” : “wyrażenie niespełnione”}

przykładowe użycie w kodzie SQL:

select id, name from g.customer {@id != null && @id != 0 ? " where and id = @id" : ""}

• {IF_NOT_EMPTY} → buduje frazę metajęzyka sprawdzającą czy wybrany bit ma wartość i nie jest null-em.

przykładowe użycie w zapytaniu:

select id, name from g.customer {@id != null && @id != 0 ? " where id = @id" : "''"}

• {TO_SQLTEXT} → funkcja dodająca tekst podlegający parsowaniu (zapobieganie atakowi sql injection) w apostrofach

przykładowe użycie w zapytaniu:

select id, name, {TO_SQLTEXT(@email)} as email from g.customer

• {TO_PURE_VALUE} → funkcja dodająca tekst bez żadnej ingerencji: {TO_PURE_VALUE([text doklejany])}

przykładowe użycie w zapytaniu:

select id, name from {TO_PURE_VALUE(@schema)}.customer


Wstawianie tekstu w ten sposób jest wrażliwe na atak typu sql injection, ponieważ wstawiany tekst nie podlega parsowaniu. Używaj tego typu tylko dla danych których jesteś absolutnie pewien.