ExecuteSQL()

Zastosowanie

Funkcja uruchamia zapytanie SQL korzystając z połączenia do wskazanej bazy danych. Pozwala na wymianę danych z systemami zewnętrznymi. Pozwala odczytywać dane z zewnętrznych baz danych (SELECT), aktualizować je (UPDATE), dodawać nowe rekordy (INSERT), a także usuwać (DELETE). Możliwe jest również uruchamianie procedur składowanych.

Składnia

ExecuteSQL(DSN,query);
ExecuteSQL(DSN,query,timeout);
ExecuteSQL(DSN,query,resultType);
ExecuteSQL(DSN,query,resultType,timeout);

Argumenty

• DSN – (String) skonfigurowane połączenie do źródła danych ODBC, bezpośrednie połączenie do bazy danych (ang. connection string) albo połączenie nazwane;
• query – (String) polecenie SQL zgodna z silnikiem bazy danych, do której zapytanie się odnosi.  W zapytaniu można odwoływać się do
  • pól systemowych (np. [CaseId],[CaseTitle]),
  • pól z formularza (np. [Data transakcji], [NIP Kontrahenta] itd.),
  • pól z tabel  (np. [Dekretacja.Konto] ),
  • dla słowników przypiętych do pól słownikowych, kiedy słownik jest importowanym słownikiem zewnętrznym, jego zewnętrzny identyfikator (i.e. [Kontrahent.id] itp.);
• timeout – (String) [opcja] ograniczenie czasu wykonania zapytania do określonej w tym parametrze liczby sekund (domyślnie 30s);
• resultType – (String) [opcja] określnie typu zwracanego wyniku, a może to być:
  • „scalar” – wartość domyślna, w przypadku zapytania SQL typu SELECT zostanie zwrócona wartość z komórki pierwszego wiersza, pierwszej kolumny. Dla zapytań typu INSERT, UPDATE lub DELETE zostanie zwrócona ilość wierszy, na których z powodzeniem wykonano dane polecenie SQL,
  • „json” – w praktyce do użycia tylko z zapytaniami typu SELECT, zostanie zwrócona cała tabela jako ciąg znaków w formacie JSON.

Przekazywanie wartości pól z formularza jako parametrów zapytań/poleceń SQL

W celu zwiększenia bezpieczeństwa wywołania zapytań lub poleceń SQL nie zaleca się tworzenia ich poprzez sklejanie ciągów znaków. Przykład niepoprawnej konstrukcji zapytania SQL:

_tel=ExecuteSQL("DSN=jakas_baza","select Nr_telefonu from dbsrc_8 where Nr='"+[Numer pracownika]+"';","scalar")

W powyższym przykładzie zawartość pola (pewnie tekstowego) [Numer pracownika] jest wklejana w treści zapytania SQL. To może powodować zwiększenie ryzyka wystąpienia ataku typu „SQL injection” lub po prostu wygenerowania błędnego zapytania SQL, jeśli użytkownik we wspomnianym polu wprowadziłby jakiś złośliwy kod SQL.

Poprawnym podejściem jest bezpośrednie odwołanie się do zawartości pola w zapytaniu lub poleceniu SQL:

_tel=ExecuteSQL("DSN=jakas_baza","select Nr_telefonu from dbsrc_8 where Nr=[Numer pracownika]","scalar")

W tym przykładzie mechanizmy systemu AMODIT będą wstanie wykryć potencjalnie złośliwy kod w przekazywanej zawartości pola [Numer pracownika] i nie dopuścić do ewentualnego wykonania się zapytania lub polecenia SQL.

Zwracana wartość

Zwraca tekst.
Na ogół zwracana jest wartość w pierwszej kolumnie, pierwszego wiersza wyników. Patrz również: opis argumentu resultType.

Przykłady

Przykład 1

W tym przykładzie połączenie z bazą danych odbywa się za pomocą nazwanego połączenia „ELBEDEMO_MYSQL”, skonfigurowanego w ustawieniach systemu AMODIT (patrz: opis poniżej przykładowego kodu). Przy okazji wynik zapytania typu SELECT zostanie zwrócony w formacie JSON. Wartości wynikowe pobierane są w pętli foreachobject().

_json=ExecuteSQL("ELBEDEMO_MYSQL", "select * from Account where id=[Account]","json");
foreachobject(_json)
{
  [Name]=this.Name
  [Address]=this.Address
}

Nazwane połączenie do bazy danych

W systemie AMODIT istnieje możliwość zarządzania nazwanymi połączeniami do baz danych w jednym miejscu. Wykorzystywane są one m.in. we wspomnianej funkcji ExecuteSQL(), słownikach zewnętrznych lub zewnętrznych źródłach danych.

W celu skonfigurowania nazwanego źródła danych należy przejść do ustawień systemowych [1] (na poniższym obrazku), następnie przejść do zakładki „Ustawienia bazy danych” [2] i sekcji „Database Connection Strings” [3]. Kliknąć przycisk „Edit” [4].

Konfigurację nazwanego połączenia „ELBEDEMO_MYSQL” z przykładu nr 4 prezentuje poniższy obrazek.

Przykład 2

W tym przykładzie połączenie do bazy danych jest realizowane za pomocą źródła danych ODBC (ang. DSN – Data Source Name). Połączenie musi być zdefiniowane na serwerze aplikacyjnym systemu AMODIT.

ExecuteSQL("DSN=zebra", "exec AddInvoice [CaseId],[CaseTitle],[Customer.id],[Positions.Price],
[Positions.Product.id]");

Przykład 3

Jak w przykładzie nr 2 również tutaj połączenie z bazą danych odbywa się za pomocą źródła danych ODBC. Dodatkowo została podana wartość 60 (sekund) dla argumentu timeout.

ExecuteSQL("DSN=zebra", "exec AddInvoice [CaseId],[CaseTitle],[Customer.id],[Positions.Price],
[Positions.Product.id]",60);

Przykład 4

W tym przykładzie połączenie do bazy danych MS SQL Server jest realizowane za pomocą bezpośredniego połączenia (ang. connection string) z wykorzystaniem sterownika ODBC. Sterownik można pobrać ze strony https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16.

UWAGA nr 1! Sterownik musi zostać zainstalowany na serwerach, gdzie znajdują się komponenty systemu AMODIT – aplikacja webowa i/lub usługa Windows AMODAsynchronousService. Nie ma potrzeby jego instalacji na serwerze bazy danych, gdzie znajduje się tylko baza systemu AMODIT i nie ma innych komponentów.

ExecuteSQL("Driver={ODBC Driver 18 for SQL Server};Server=myServerAddress;Database=myDataBase;
Uid=myUsername;Pwd=myPassword;","Insert into Invoice (CaseId,CaseTitle,CustomerId,Price,ProductId)
values ([CaseId],[CaseTitle],[Customer.id],[Positions.Price],[Positions.Product.id])");

UWAGA nr 2! W przykładzie tym podajemy, jak można skorzystać z bezpośredniego połączenia do bazy danych z użyciem loginu (UID) i hasła użytkownika (PWD). Ze względów bezpieczeństwa nie jest zalecane, aby te parametry były wpisywane jawnie w kodzie reguł. Dlatego należy korzystać z przykładów 1, 2 lub 3.

Przykład 5

Od wydań 240331(.71), 240630(.37) i 240930(.0) systemu AMODIT możliwe jest podanie kilka razy odwołania się do tego samego pola w zapytaniach SQL przekazywanych jako argument funkcji ExecuteSQL() i RemoteExecuteSQL().

ExecuteSQL("SOME_CONN_STR", "SELECT * FROM some_table WHERE column1=[some_form_field] OR column2=[some_form_field]");